C# oledb方式操作excel

 

在网上搜了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

 

 

附带：

基于NPOI的Excel导入导出类库

https://www.cnblogs.com/bluesummer/p/13744421.html