在网上搜了oledb操作数据的帮助类，结合自己的实际调试以及碰到的问题，放出我测试过的帮助类
public class OfficesHelper { #region 读取excel文件 /// <summary> /// 读取Excel /// </summary> /// <param name="fileName">文件路径</param> /// <param name="sheetName">sheet的名字</param> /// <returns></returns> public static DataSet ReadExcel(string fileName,string sheetName) { //provider：表示提供程序名称 //Data Source：这里填写Excel文件的路径 //Extended Properties：设置Excel的特殊属性 //Extended Properties 取值： //Excel 8.0 针对Excel2000及以上版本，Excel5.0 针对Excel97。 //HDR = Yes 表示第一行包含列名,在计算行数时就不包含第一行 // IMEX 0:写入模式,1:只读模式: 2混合模式（第三个没啥用） String sConnectionString = string.Empty; if(!fileName.Contains(".xlsx")) sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + fileName + ";" +"Extended Properties='Excel 8.0;HDR=Yes;IMEX=0'"; else sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + "; Extended Properties = 'Excel 12.0; HDR = YES;IMEX=0'"; //实例化一个Oledbconnection类(实现了IDisposable,要using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString)) { ole_conn.Open(); using (OleDbCommand ole_cmd = ole_conn.CreateCommand()) { //类似SQL的查询语句这个[Sheet1$对应Excel文件中的一个工作表] ole_cmd.CommandText = "select * from [" + sheetName + "$]"; OleDbDataAdapter adapter = new OleDbDataAdapter(ole_cmd); DataSet ds = new DataSet(); adapter.Fill(ds, "table"); return ds; } } } #endregion #region 获取工作薄中所有的工作表 /// <summary> /// 获取工作薄中所有的工作表 /// </summary> /// <param name="fileName">excel文件路径</param> /// <returns></returns> public static List<string> GetAllExcelSheets(string fileName) { String sConnectionString = string.Empty; if (!fileName.Contains(".xlsx")) sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=0'"; else sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + "; Extended Properties = 'Excel 12.0; HDR = YES;IMEX=0'"; List<string> list = new List<string>(); //实例化一个Oledbconnection类(实现了IDisposable,要using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString)) { ole_conn.Open(); using (OleDbCommand ole_cmd = ole_conn.CreateCommand()) { DataTable tb = ole_conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow row in tb.Rows) { list.Add(row["TABLE_NAME"].ToString()); } } } return list; } #endregion #region 写数据到excel中 /// <summary> /// 写数据到excel中 /// </summary> /// <param name="fileName">文件路径</param> /// <param name="ds">要写入的数据集</param> /// <param name="tableName">Sheet表名</param> /// <returns></returns> public static bool WriteExcel(string fileName,DataSet ds,string sheetName) { String sConnectionString = string.Empty; if (!fileName.Contains(".xlsx")) sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + "Extended Properties='Excel 8.0;HDR = Yes;IMEX = 0'"; else sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + "; Extended Properties = 'Excel 12.0; HDR = YES;IMEX = 0'"; try { //实例化一个Oledbconnection类(实现了IDisposable,要using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString)) { ole_conn.Open(); using (OleDbCommand ole_cmd = ole_conn.CreateCommand()) { //获取列数 int colCount = ds.Tables[0].Columns.Count; int rowCount = ds.Tables[0].Rows.Count; string cols = string.Empty; //列名集合 string values = string.Empty; //值集合 string insertSql = string.Empty; //最后拼接的sql语句 for (int i = 0; i < rowCount; i++) { //进入内循环前把变量置空 cols = string.Empty; values = string.Empty; for (int j = 0; j < colCount; j++) { cols += ds.Tables[0].Columns[j].ColumnName + ","; values += "'" + ds.Tables[0].Rows[i][j].ToString() + "'" + ","; } //去掉最后的,好 cols = cols.Substring(0, cols.Length - 1); values = values.Substring(0, values.Length - 1); //执行插入操作 insertSql = string.Format("insert into[{0}$]({1})values({2})", sheetName, cols, values); ole_cmd.CommandText = insertSql; ole_cmd.ExecuteNonQuery(); } return true; } } } catch(Exception ex) { return false; } } #endregion #region 创建表并写入excel数据 /// <summary> /// 创建表并写入excel数据 /// </summary> /// <param name="fileName">文件路径</param> /// <param name="ds">要写入的数据集</param> /// <param name="tableName">Sheet1表名</param> /// <param name="sqlCreTable">创建表头 比如：CREATE TABLE [Sheet1$] ([Name] VarChar)</param> /// <returns></returns> public static bool WriteExcel(string fileName, DataSet ds, string sheetName,string sqlCreTable) { String sConnectionString = string.Empty; if (!fileName.Contains(".xlsx")) sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source = " + fileName + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=0'"; else sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + "; Extended Properties = 'Excel 12.0; HDR = YES;IMEX=0'"; try { //创建连接 OleDbConnection ole_conn = new OleDbConnection(sConnectionString); //打开连接 ole_conn.Open(); //给命令赋值（初始化表头） OleDbCommand ole_cmd = new OleDbCommand(sqlCreTable, ole_conn); //执行创建表头的语句 ole_cmd.ExecuteNonQuery(); //获取列数 int colCount = ds.Tables[0].Columns.Count; int rowCount = ds.Tables[0].Rows.Count; string cols = string.Empty; //列名集合 string values = string.Empty; //值集合 string insertSql = string.Empty; //最后拼接的sql语句 for (int i = 0; i < rowCount; i++) { //进入内循环前把变量置空 cols = string.Empty; values = string.Empty; for (int j = 0; j < colCount; j++) { cols += ds.Tables[0].Columns[j].ColumnName + ","; values += "'" + ds.Tables[0].Rows[i][j].ToString() + "'" + ","; } //去掉最后的,好 cols = cols.Substring(0, cols.Length - 1); values = values.Substring(0, values.Length - 1); //执行插入操作 insertSql = string.Format("insert into[{0}$]({1})values({2})", sheetName, cols, values); ole_cmd.CommandText = insertSql; ole_cmd.ExecuteNonQuery(); } ole_conn.Close(); return true; } catch (Exception ex) { return false; } } #endregion }
说下使用过程中遇到的坑：
1、首先就是.xls和 .xlsx版本不同，创建的连接字符串也不同的，不然会报错
2、关于连接串中IMEX的配置 建议都配置0， 给1或者2 在创建表头的时候会提示 文件为只读
3、关于创建表头没反应的问题：创建语句为：
CREATE TABLE TestSheet ([ID] INTEGER,[Username] VarChar,[UserPwd] VarChar)
经过我.xlsx文件测试，这样写不起作用
必须加$符号写为：
CREATE TABLE TestSheet$ ([ID] INTEGER,[Username] VarChar,[UserPwd] VarChar)
参考：
https://www.cnblogs.com/liubaojing/p/8676701.html
https://www.cnblogs.com/yidaimu/p/3528188.html
附带：