.Net读取Excel(包括Excel2007)
转载自http://blog.sina.com.cn/s/blog_507668a00100h297.html
出现没有配置OleDb.12.0 什么的错误 具体提示忘记了~~
就去下载这个:AccessDatabaseEngine.exe
下载地址:
/// <summary> ///根据Excel物理路径、表名(Sheet名)获取数据集
/// </summary> /// <param name="FileFullPath">Excel物理路径param> /// <param name="SheetName">表名(Sheet名) 示例"Sheet1$"</param> /// <returns>DataTable</returns> public static System.Data.DataTable GetExcelToDataTableBySheet(string FileFullPath, string SheetName) { //string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件 string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 12.0; HDR=1; IMEX=1'"; //此连接可以操作xls与.xlsx文件 OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", SheetName), conn); //("select * from [Sheet1$]", conn); odda.Fill(ds, SheetName); conn.Close(); return ds.Tables[0]; }
/// <summary> /// 根据YExcel物理路径获取Excel文件中所有表名 /// </summary> /// <param name="excelFile">Excel物理路径</param> /// <returns>String数组</returns> public static String[] GetExcelSheetNames(string excelFile) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { //string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelFile + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件 string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作xls与.xlsx文件 objConn = new OleDbConnection(strConn); objConn.Open(); dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } return excelSheets; } catch { return null; } finally { if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }