using System; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApp1 { public static class ExcelHelper { #region 导入 /// <summary> /// 导入EXCEL(默认的sheet) /// </summary> /// <param name="fileName">excel文件路径</param> /// <returns></returns> public static System.Data.DataTable ImpExcelDt(string fileName) { return ImpExcelDt(fileName, "Sheet1"); } /// <summary> /// excel 导入 /// </summary> /// <param name="fileName">excel文件路径</param> /// <param name="sheetName"></param> /// <returns></returns> public static System.Data.DataTable ImpExcelDt(string fileName, string sheetName) { try { if (!File.Exists(fileName)) { return null; } // 连接字符串:Provider = Microsoft.Jet.OLEDB.4.0; Data Source = d: est.xls; Extended Properties = 'Excel 8.0;HDR=Yes;IMEX=1;' //provider:表示提供程序名称 //Data Source:这里填写Excel文件的路径 //Extended Properties:设置Excel的特殊属性 //Extended Properties 取值: //Excel 8.0 针对Excel2000及以上版本,Excel5.0 针对Excel97。 //HDR = Yes 表示第一行包含列名,在计算行数时就不包含第一行 // IMEX 0:导入模式,1:导出模式: 2混合模式 string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = " SELECT * FROM [" + sheetName + "$] "; myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet, "[" + sheetName + "$]"); myConn.Close(); System.Data.DataTable dt = myDataSet.Tables[0]; return dt; } catch (Exception ex) { throw ex; } } #endregion #region 导出到EXCEL /// <summary> /// 将数据导出到指定的Excel文件中 /// </summary> /// <param name="listView">System.Windows.Forms.ListView,指定要导出的数据源</param> /// <param name="destFileName">指定目标文件路径</param> /// <param name="tableName">要导出到的表名称</param> /// <param name="overWrite">指定是否覆盖已存在的表</param> /// <returns>导出的记录的行数</returns> public static int ExportToExcel(System.Data.DataTable dt, string destFileName, string tableName) { if (File.Exists(destFileName)) { File.Delete(destFileName); } //得到字段名 string szFields = ""; string szValues = ""; for (int i = 0; i < dt.Columns.Count; i++) { szFields += "[" + dt.Columns[i] + "],"; } szFields = szFields.TrimEnd(','); //定义数据连接 OleDbConnection connection = new OleDbConnection(); connection.ConnectionString = GetConnectionString(destFileName); OleDbCommand command = new OleDbCommand(); command.Connection = connection; command.CommandType = CommandType.Text; //打开数据库连接 try { connection.Open(); } catch { throw new Exception("目标文件路径错误。"); } //创建数据库表 try { command.CommandText = GetCreateTableSql("[" + tableName + "]", szFields.Split(',')); command.ExecuteNonQuery(); } catch (Exception ex) { //如果允许覆盖则删除已有数据 throw ex; } try { //循环处理数据------------------------------------------ int recordCount = 0; for (int i = 0; i < dt.Rows.Count; i++) { szValues = ""; for (int j = 0; j < dt.Columns.Count; j++) { szValues += "'" + dt.Rows[i][j] + "',"; } szValues = szValues.TrimEnd(','); //组合成SQL语句并执行 string szSql = "INSERT INTO [" + tableName + "](" + szFields + ") VALUES(" + szValues + ")"; command.CommandText = szSql; recordCount += command.ExecuteNonQuery(); } connection.Close(); return recordCount; } catch (Exception ex) { throw ex; } } //得到连接字符串 private static String GetConnectionString(string fullPath) { string szConnection; szConnection = "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + fullPath; return szConnection; } //得到创建表的SQL语句 private static string GetCreateTableSql(string tableName, string[] fields) { string szSql = "CREATE TABLE " + tableName + "("; for (int i = 0; i < fields.Length; i++) { szSql += fields[i] + " VARCHAR(200),"; } szSql = szSql.TrimEnd(',') + ")"; return szSql; } #endregion } } //导入到数据库 var getDT= ExcelHelper.ImpExcelDt(@"F:新建 XLS 工作表 (2).xls"); var rowcount = 0; for (int i = 0; i < getDT.Rows.Count; i++) { var sql = "insert into Users values('"; sql += getDT.Rows[i].ItemArray[1]+"')"; rowcount+=DBHelper.ExecuteNonQuery(sql); } //导入到Excel var dt = DBHelper.ExecuteDataSet("select * from Users").Tables[0]; var result= ExcelHelper.ExportToExcel(dt, @"F:新建 XLS 工作表.xls", "Users"); Console.ReadKey();
HDR=Yes,这代表第一行是标题,不做为数据使用(但是我在实际使用中,如果第一行存在复杂数值,那么读取得到的Datatable列标题会自动设置为F1、F2等方式命名,与实际应用不符,所以当时是通过HDR=No方式将所有内容读取到Datatable中,然后手动将第一行设置成标题的);IMEX ( IMport EXport mode )设置 IMEX 有三种模式: 0 is Export mode 1 is Import mode 2 is Linked mode (full update capabilities) 我这里特别要说明的就是 IMEX 参数了,因为不同的模式代表著不同的读写行为: 当 IMEX=0 时为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。 当 IMEX=1 时为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。 当 IMEX=2 时为“链接模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
---------------------------------
另外,读取Excel2007版本的文件时,版本应该从8.0改为12.0,同时驱动不能再用Jet,而应该用ACE。负责会造成“找不到可安装的 ISAM”的错误。