ASP.Net动态读取Excel文件最简方法
注意：页面分别拖拽一个FileUpload、Button1、Label1、GridView控件，并新建一个UploadedExcel文件夹
Default.aspx.cs代码：
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.Data.OleDb;
usingSystem.IO;
usingSystem.Linq;
usingSystem.Web;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
namespaceWebApplication2
{
publicpartialclassWebForm1:System.Web.UI.Page
{
protectedvoidPage_Load(objectsender,EventArgse)
{
delete();
}
protectedvoidButton1_Click(objectsender,EventArgse)
{
OleDbConnectionconn=newOleDbConnection();
OleDbCommandcmd=newOleDbCommand();
OleDbDataAdapterda=newOleDbDataAdapter();
DataSetds=newDataSet();
stringquery=null;
stringconnString="";
stringstrFileName=DateTime.Now.ToString("ddMMyyyy_HHmmss");
//stringstrFileName=Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName);
stringstrFileType=Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
if(strFileType==".xls"||strFileType==".xlsx")
{
FileUpload1.SaveAs(Server.MapPath("~/UploadedExcel/"+strFileName+strFileType));
}
else
{
return;
}
stringstrNewPath=Server.MapPath("~/UploadedExcel/"+strFileName+strFileType);
if(strFileType.Trim()==".xls")
{
connString="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+strNewPath+";ExtendedProperties=\"Excel8.0;HDR=Yes;IMEX=2\"";
}
elseif(strFileType.Trim()==".xlsx")
{
connString="Provider=Microsoft.ACE.OLEDB.12.0;DataSource="+strNewPath+";ExtendedProperties=\"Excel12.0;HDR=Yes;IMEX=2\"";
}
query="SELECT*FROM[Sheet1$]";
conn=newOleDbConnection(connString);
if(conn.State==ConnectionState.Closed)
{
conn.Open();
}
try
{
cmd=newOleDbCommand(query,conn);
da=newOleDbDataAdapter(cmd);
ds=newDataSet();
da.Fill(ds);
GridView1.DataSource=ds.Tables[0];
GridView1.DataBind();
Label1.Text="读取成功";
}
catch(Exceptionex)
{
Label1.Text="读取失败";
Response.Write(ex);
}
finally
{
da.Dispose();
conn.Close();
conn.Dispose();
}
}
//定时任务
privatevoiddelete()
{
DirectoryInfodi=newDirectoryInfo(Server.MapPath("/UploadedExcel/"));
FileInfo[]fi=di.GetFiles("*."+"*");
DateTimedtNow=DateTime.Now;
foreach(FileInfotmpfiinfi)
{
TimeSpants=dtNow.Subtract(tmpfi.LastWriteTime);
if(ts.Milliseconds>100)
{
tmpfi.Attributes=FileAttributes.Normal;
tmpfi.Delete();
}
}
}
}
}
注意：FileUpload控件并不能直接获取到文件的绝对路径（IE6及以下除外），只能通过上传到服务器再进行数据加载，然后再删除