使用Oledb读取Excel数据的时候，总是读取不到数据 ！而打开源文件另存一下就可以读取到，这是什么问题？Excel是xlsx文件
public static DataTable DBExcelToDataTable(string pathName, string sheetName = "")
{
System.Data.DataTable dt = new System.Data.DataTable();
string ConnectionString = string.Empty;
FileInfo file = new FileInfo(pathName);
if (!file.Exists) { throw new Exception("文件不存在"); }
string extension = file.Extension;
switch (extension) // 连接字符串
{
case ".xls":
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
break;
case ".xlsx":
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=no;IMEX=1;'";
break;
default:
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
break;
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(ConnectionString);
try
{
con.Open();
if (sheetName != "") //若指定了工作表名
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + sheetName + "$] ", con);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
try
{
apt.Fill(dt);
}
catch (Exception ex) { throw new Exception("该Excel文件中未找到指定工作表名," + ex.Message); }
dt.TableName = sheetName;
}
else
{
//默认读取第一个有数据的工作表
var tables = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { });
if (tables.Rows.Count == 0)
{ throw new Exception("Excel必须包含一个表"); }
foreach (System.Data.DataRow row in tables.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
//过滤无效SheetName
if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + strSheetTableName + "]", con);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
apt.Fill(dt);
dt.TableName = strSheetTableName.Replace("$", "").Replace("'", "");
break;
}
}
}
return dt;
}
catch (Exception ee)
{ throw ee; }
finally
{ con.Close(); }
}
这是Excel中的文件和DataTable获取的数据
我打开Excel文件只需要保存一下，就可以读取到数据!
Excel文件是通过 HttpPostedFile 中的SaveAs上传上的，我不知道是否跟这个有关系
