c#用OleDb这种方式导入excel并转换为datatable

辅助方法：

public void AcquireTwoArray(DataSet ds) { foreach (DataTable dt in ds.Tables)//遍历所有的DataTable { foreach (DataRow dr in dt.Rows)//遍历所有的行 { foreach (DataColumn dc in dt.Columns)//遍历所有的列 { Console.Write("{0},{1},{2}", dt.TableName, dc.ColumnName, dr[dc].ToString());//表名，列名，单元格数据 } } } } public System.Data.DataSet ExcelSqlConnection(string filepath, string tableName) { string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; OleDbConnection ExcelConn = new OleDbConnection(strCon); try { string strCom = string.Format("SELECT * FROM [Sheet1$]"); ExcelConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn); DataSet ds = new DataSet(); myCommand.Fill(ds, "[" + tableName + "$]"); ExcelConn.Close(); return ds; } catch { ExcelConn.Close(); return null; } }

调用： string path = AppDomain.CurrentDomain.BaseDirectory + “report.xlsx”;

Excel1 ex=new Excel1 (); string path1 = AppDomain.CurrentDomain.BaseDirectory + "excel1.xls"; DataSet dse = ex.ExcelSqlConnection(path, "report.xls"); ex.AcquireTwoArray(dse);

注意：当excel版本不一样时，会弹出外部表不是预期格式错误，只需修改链接字符窜即可 string strCon = “Provider=Microsoft.Ace.OleDb.12.0;” + “data source=” + filepath + “;Extended Properties=’Excel 12.0; HDR=Yes; IMEX=1’”;