如何用最简单的方式让ASP.Net动态读取Excel文件？

本文共计502个文字，预计阅读时间需要3分钟。

请注意：页面分别拖拽一个FileUpload、Button1、Label1、GridView控件，并新建一个UploadedExcel文件夹 + Default.aspx.cs代码如下：

csharpusing System;using System.Collections.Generic;using System.Data;using System.Data.OleDb;

public partial class Default : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { // 初始化GridView GridView1.DataSource=new List(); GridView1.DataBind(); } }

protected void Button1_Click(object sender, EventArgs e) { if (FileUpload1.HasFile) { string filePath=Server.MapPath(~/UploadedExcel/ + FileUpload1.FileName); FileUpload1.SaveAs(filePath);

// 读取Excel文件 string connectionString=Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + filePath + ;Extended Properties=\Excel 8.0;HDR=YES;IMEX=1;\; OleDbConnection connection=new OleDbConnection(connectionString); OleDbCommand command=new OleDbCommand(SELECT * FROM [Sheet1$], connection); OleDbDataAdapter adapter=new OleDbDataAdapter(command); DataTable dataTable=new DataTable(); adapter.Fill(dataTable);

// 绑定数据到GridView GridView1.DataSource=dataTable; GridView1.DataBind(); } }}

注意：页面分别拖拽一个FileUpload、Button1、Label1、GridView控件，并新建一个UploadedExcel文件夹

Default.aspx.cs代码：

using System; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.IO; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace WebApplication2 { public partial class WebForm1 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { delete(); } protected void Button1_Click(object sender, EventArgs e) { OleDbConnection conn = new OleDbConnection(); OleDbCommand cmd = new OleDbCommand(); OleDbDataAdapter da = new OleDbDataAdapter(); DataSet ds = new DataSet(); string query = null; string connString = ""; string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss"); //string strFileName = Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName); string strFileType = Path.GetExtension(FileUpload1.FileName).ToString().ToLower(); if (strFileType == ".xls" || strFileType == ".xlsx") { FileUpload1.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType)); } else { return; } string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType); if (strFileType.Trim() == ".xls") { connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (strFileType.Trim() == ".xlsx") { connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } query = "SELECT * FROM [Sheet1$]"; conn = new OleDbConnection(connString); if (conn.State == ConnectionState.Closed) { conn.Open(); } try { cmd = new OleDbCommand(query, conn); da = new OleDbDataAdapter(cmd); ds = new DataSet(); da.Fill(ds); GridView1.DataSource = ds.Tables[0]; GridView1.DataBind(); Label1.Text = "读取成功"; } catch (Exception ex) { Label1.Text = "读取失败"; Response.Write(ex); } finally { da.Dispose(); conn.Close(); conn.Dispose(); } } //定时任务 private void delete() { DirectoryInfo di = new DirectoryInfo(Server.MapPath("/UploadedExcel/")); FileInfo[] fi = di.GetFiles("*." + "*"); DateTime dtNow = DateTime.Now; foreach (FileInfo tmpfi in fi) { TimeSpan ts = dtNow.Subtract(tmpfi.LastWriteTime); if (ts.Milliseconds > 100) { tmpfi.Attributes = FileAttributes.Normal; tmpfi.Delete(); } } } } }

注意：FileUpload控件并不能直接获取到文件的绝对路径（IE6及以下除外），只能通过上传到服务器再进行数据加载，然后再删除