protected void Button1_Click(object sender, EventArgs e)
{
if (station.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = Path.GetExtension(station.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xlsx" && IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
string filename = station.FileName; //获取Execle文件名 DateTime日期函数
string savePath = "F:\数据处理___________\" + filename;//Server.MapPath 获得虚拟服务器相对路径
DataTable dt = new DataTable();
//station.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
dt = GetExcelDatatable(savePath); //调用自定义方法
}
public System.Data.DataTable GetExcelDatatable(string fileUrl)
{
//支持.xls和.xlsx，即包括office2010等版本的 HDR=Yes代表第一行是标题，不是数据；
string cmdText = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
System.Data.DataTable dt = null;
//建立连接
OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
try
{
//打开连接
if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
{
conn.Open();
}
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string strSql = "select * from [Sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
return dt;
}
catch (Exception exc)
{
throw exc;
}
finally
{
conn.Close();
conn.Dispose();
}
}