C#使用OleDb读取Excel，生成SQL语句

C#使用OleDb读取Excel，生成SQL语句

      之前在C#读取Excel数据动态生成对象并进行序列化也要对读取Excel文件，那时用的是Microsoft.Office.Interop.Excel组件，有一个致命的问题：读取效率低到不能忽略。所以一直想着有机会去换一个读取的机制，上网google了下，发现使用OleDb读写的效率是很不错的，所以在写把Excel的数据导成SQL语句的工具时，我就使用了OleDb，效率那是毫无疑问的。其实使用Microsoft.Office.Interop.Excel组件去读取Excel是相当于打开一个excel程序，而OleDb其实就是对Excel按数据库的方式进行读写。

     少说废话，直接贴代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
namespace ReadXlsxData
{
    static class ParseXlsx
    {
        public static readonly int COMMENT_INDEX=4;   //字段说明行下标
        public static readonly int KEY_INDEX = 5;    //主键行下标
        public static readonly int TYPE_INDEX = 6;   //字段类型行下标
        public static readonly int SQLNAME_INDEX = 7;      //数据库字段名行下标
        public static readonly int VALUE_INDEX = 8;      //value 行下标
        public static StringBuilder objectData = new StringBuilder();
        public static DataTable ToDataSet(string filePath)
        {
            string connStr = "";
           
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType)) return null;
            if (fileType == ".xls")
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
            else
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";
            string sql_F = "Select * FROM [{0}]";
            OleDbConnection conn = null;
            OleDbDataAdapter da = null;
            DataTable dataTable = new DataTable();
            try
            {
                // 初始化连接，并打开                  
                conn = new OleDbConnection(connStr);
                conn.Open();
                da = new OleDbDataAdapter();
                da.SelectCommand = new OleDbCommand(String.Format(sql_F, "Sheet1$"), conn);
                da.Fill(dataTable);
            }
            catch (Exception ex)
            {

            }
            finally
            {                  // 关闭连接                  
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    da.Dispose();
                    conn.Dispose();
                }
            }
            conn.Close();
            da.Dispose();
            conn.Dispose();
            return dataTable;
        }
        public static string ReadExcelFile(string namef, string sqlfile, string sqlcomment)
        {
            objectData.Clear();
            DataTable dt = ToDataSet(namef);
            string temp, key,temp1,temp2;
            List<int> index = new List<int>();
         
            //创建表头
            objectData.Append("DROP TABLE IF EXISTS `" + sqlfile + "`;\n");
            objectData.Append("CREATE TABLE `" + sqlfile + "` (\n");
            int columnSize = dt.Columns.Count;
            int rowSize = dt.Rows.Count;
            DataColumn dc;
            DataRow dr;
            temp = string.Empty;
            key = string.Empty;
            temp1 = string.Empty;
            temp2 = string.Empty;
            DataRow dr5 = dt.Rows[COMMENT_INDEX],dr9=dt.Rows[SQLNAME_INDEX],dr8=dt.Rows[TYPE_INDEX];
            for (int i = 1; i < columnSize; i++)
            {
                dc = dt.Columns[i];
                temp2 = dr5[dc].ToString();
                temp1 = dr9[dc].ToString();
                if (temp2 == string.Empty)//空列判断
                    break;
                else if (temp1.ToString() != string.Empty)  //数据库字段
                {
                    index.Add(i);
                    temp = dr8[dc].ToString();
                    if (temp.Contains("vachar"))
                        objectData.Append("\t`" + temp1 + "` " + temp + " NOT NULL DEFAULT '' COMMENT '" + temp2 + "',\n");
                    else
                        objectData.Append("\t`" + temp1 + "` " + temp + " NOT NULL DEFAULT '0' COMMENT '" + temp2 + "',\n");
                    temp = dt.Rows[KEY_INDEX][dc].ToString();
                    if (temp != null && temp.Contains("key"))
                    {
                        key += "`" + temp1 + "` ";

                    }

                }
            }
            if(key!=string.Empty)
                objectData.Append("\tPRIMARY KEY (" + key + ")\n");
            objectData.Append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='" + sqlcomment + "';\n");
            for (int i = VALUE_INDEX; i < rowSize; i++)   //读取数据记录
            {
                objectData.Append("INSERT INTO `" + sqlfile + "` VALUES ('");
                dr = dt.Rows[i];
                int length = index.Count;
                for (int j = 0; j < length; j++)
                {
                    objectData.Append(dr[index[j]] + "','");
                }
                objectData.Remove(objectData.Length - 3, 2);
                objectData.Append(");\n");
            }  
            return objectData.ToString();
        }
    }
 

}

 注：这段代码去掉了通用性，只考虑了读取Excel第一个sheet的情况。

    下面附上自己做的例子：

 
         

        转载在文首注明出处：http://dsqiu.iteye.com/blog/1895255

更多精彩请关注D.S.Qiu的博客和微博（ID：静水逐风）
 

 

 

 

 

参考：

①Jyson： http://www.cnblogs.com/jys509/archive/2011/07/25/2116143.html