最近遇到个很特殊的情况,很老旧的系统,修改需求的时候不能安装插件,很烦,只能用存代码来解决。
public static DataTable ImportExcel(string path,string worksheetName)
{
//.xls
string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
//.xlsx
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sourceFile + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
OleDbConnection conn = null;
StreamWriter wrtr = null;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
DataTable dt = new DataTable();
try
{
conn = new OleDbConnection(strConn);
conn.Open();
cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);
//OleDbDataReader reader = cmd.ExecuteReader();
da = new OleDbDataAdapter(cmd);
//dt.Load(reader);
da.Fill(dt);
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
cmd.Dispose();
da.Dispose();
wrtr.Close();
wrtr.Dispose();
}
return dt;
}
catch (Exception exc)
{
return dt;
}
}
public void GetData()
{
string path="";
string worksheetName="sheet1";
var dt Import(path,worksheetName);
}
经过上面代码的处理生成了datatable,处理datatable可以说大家都是轻车熟路,这里就不说了,然后这个方法没有做文件的是否打开,路径是否存在的验证,如果生产环境还是要做这些处理的。