使用FileUpload获取文件名后,不能对文件进行读取(注释部分运行不成功)
protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataSource = GetExcelTableByOleDB(@"C:\Users\Administrator\Desktop\导入\ma.xlsx");
GridView1.DataBind();
//if (FileUpload1.HasFile)//FileUpload1选择C:\Users\Administrator\Desktop\导入\ma.xlsx的文件
//{
// string fileName = Server.HtmlEncode(FileUpload1.FileName);
// string extension = System.IO.Path.GetExtension(fileName);
// if ((extension == ".xlsx") || (extension == ".xls"))
// {
// GridView1.DataSource = GetExcelTableByOleDB(@"C:\Users\Administrator\Desktop\导入\ma.xlsx");
// GridView1.DataBind();
// }
//}
}
private DataTable GetExcelTableByOleDB(string path)
{
try
{
DataTable dtExcel = new DataTable();
DataSet dst = new DataSet();
string strExtension = System.IO.Path.GetExtension(path);//获取文件后缀名.xlsx
string strFileName = System.IO.Path.GetFileName(path);//获取文件扩展名test.xlsx
//Excel的连接
OleDbConnection objConn = null;
switch (strExtension)
{
case ".xls":
objConn = new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ path + ";" +
"Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"");
break;
case ".xlsx":
objConn = new OleDbConnection
("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ path + ";" +
"Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"");
break;
default:
objConn = null;
break;
}
if (objConn == null)
{
return null;
}
objConn.Open();
//获取Excel中所有Sheet表的信息
DataTable schemaTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第一个Sheet表名
string tableName = schemaTable.Rows[0][2].ToString().Trim();
string strSql = "select * from [" + tableName + "]";
//获取Excel指定Sheet表中的信息
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
myData.Fill(dst, tableName);//填充数据
objConn.Close();
//dtExcel即为excel文件中指定表中存储的信息
dtExcel = dst.Tables[tableName];
return dtExcel;
}
catch
{
return null;
}
}