public DataTable GetExcelTable(string fileName)
{
string cmdText = "SELECT * FROM [Sheet1$]";
DataTable tmp = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter(cmdText, getExcelOleDbConnectionString(fileName));
da.Fill(tmp);
return this.FilterTable(tmp);
}
private DataTable FilterTable(DataTable dt)
{
//去除空行
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
bool isNull = true;
foreach (DataColumn col in dt.Columns)
{
if (!(dt.Rows[i][col] is DBNull) && dt.Rows[i][col].ToString().Trim() != string.Empty)
{
isNull = false;
break;
}
}
if (isNull)
dt.Rows.RemoveAt(i);
}
return dt;
}
private string getExcelOleDbConnectionString(string fileName)
{
if (fileName.ToLower().EndsWith(".xls"))
return "provider=Microsoft.Jet.OLEDB.4.0" +
";data source=" + fileName +
";Extended Properties=\"Excel 8.0;\"";
else
return "provider=Microsoft.ACE.OLEDB.12.0" +
";data source=" + fileName +
";Extended Properties=\"Excel 12.0 xml;\"";
}
我想通过Excel将数据导入到数据库中,运行时总报“无效操作,连接被关闭”,还有“ServerVersion = “da.ServerVersion”引发了“System.InvalidOperationException”类型的异常”,麻烦各位大虾帮忙看看啊,小弟都快急死了!
{
string cmdText = "SELECT * FROM [Sheet1$]";
DataTable tmp = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter(cmdText, getExcelOleDbConnectionString(fileName));
da.Fill(tmp);
return this.FilterTable(tmp);
}
private DataTable FilterTable(DataTable dt)
{
//去除空行
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
bool isNull = true;
foreach (DataColumn col in dt.Columns)
{
if (!(dt.Rows[i][col] is DBNull) && dt.Rows[i][col].ToString().Trim() != string.Empty)
{
isNull = false;
break;
}
}
if (isNull)
dt.Rows.RemoveAt(i);
}
return dt;
}
private string getExcelOleDbConnectionString(string fileName)
{
if (fileName.ToLower().EndsWith(".xls"))
return "provider=Microsoft.Jet.OLEDB.4.0" +
";data source=" + fileName +
";Extended Properties=\"Excel 8.0;\"";
else
return "provider=Microsoft.ACE.OLEDB.12.0" +
";data source=" + fileName +
";Extended Properties=\"Excel 12.0 xml;\"";
}
我想通过Excel将数据导入到数据库中,运行时总报“无效操作,连接被关闭”,还有“ServerVersion = “da.ServerVersion”引发了“System.InvalidOperationException”类型的异常”,麻烦各位大虾帮忙看看啊,小弟都快急死了!
我遇到过这个问题,重新拷贝一份excel问题解决