[C#][WinForm] Excel 匯入資料庫

2013-08-19

29396
0

讀取 Excel 資料有兩個做法

1. 把 Excel 當資料庫的話，用 OleDB 來做。 (與這篇使用 OleDb 讀取 Excel 類似)
2. 把 Excel 當檔案，使用 Microsoft.Office.Interop.Excel 來做。

這邊還是使用 OleDB 來讀取 Excel

首先，建立一個 TextBox 與兩個 Button 還有一個 CheckBox

private void btnOpenFile_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog())
            {
                ofd.Filter = "Excel 活頁簿 (*.xlsx)|*.xlsx|Excel 97-2003 (*.xls)|*.xls|文字檔 (Tab 字元分隔) (*.txt)|*.txt";
                if (ofd.ShowDialog() == DialogResult.OK)
                    txtbSourceFile.Text = ofd.FileName;
                else
                    txtbSourceFile.Text = string.Empty;
            }
        }

接下來就是處理匯入資料 Button 事件了

在事件中，先讀取 Excel 部份，再將資料匯入資料庫

這邊將 IMEX = 1 設定有標題列

        /// <summary>
        /// 取得 Excel 文件中指定工作表的內容
        /// </summary>
        /// <param name="FileFullPath">檔案路徑</param>
        /// <param name="SheetName">工作表名稱</param>
        /// <returns>DataTable 工作表內容</returns>
        private DataTable GetExcelSheetData(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=YES; IMEX=1'"; //此連接可以操作.xls與.xlsx文件
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                DataSet ds = new DataSet();
                //   ("select * from [Sheet1$]", conn);
                OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}$]", SheetName), conn);
                odda.Fill(ds, SheetName);
                return ds.Tables[0];
            }
        }

在 Excel 中預設為三個工作表，這邊可以取得所有工作表名稱，

須要注意的是，如果 Excel 中有使用篩選...等一些功能，取得的

工作表名稱可能會超出你實際設定的的工作表數量，不過實際工作表名稱

在取得後都會有 $ 這符號，再用這符號去檢核就好。

        /// <summary>
        /// 取得 Excel 文件中所有工作表名
        /// </summary>
        /// <param name="excelFile">Excel 檔案路徑</param>
        /// <returns>string[] 工作表名稱集合</returns>
        private String[] GetExcelSheetNames(string excelFile)
        {
            System.Data.DataTable dt = null;
            try
            {
                //string connString = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelFile + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此連接只能操作Excel2007之前(.xls)文件
                string connString = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'"; //此連接可以操作.xls與.xlsx文件

                // 建立連結
                using (OleDbConnection  objConn = new OleDbConnection(connString))
                {
                    objConn.Open();
                    
                    // 取得 Excel 資料結構
                    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dt == null)
                        return null;

                    // 取得 Excel 資料表中的工作表
                    var excelSheets = dt.AsEnumerable().Where(r => r["TABLE_NAME"].ToString().Contains("$")).Select(s => s["TABLE_NAME"].ToString()).ToArray();
                    return excelSheets;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            if (dt != null)
                dt.Dispose();
        }

接著就是組合 SQL 語法新增至資料庫了

        private struct RegExcel
        {
            public string Path;
            public string DefaultValue;
            public bool Exist;

            public RegExcel(string path, string value, bool exist)
            {
                this.Path = path;
                this.DefaultValue = value;
                this.Exist = exist;
            }
        };

        private void btnImportData_Click(object sender, EventArgs e)
        {
            if (txtbSourceFile.Text != "")
            {
                string[][] stringArr = null;
                RegExcel[] RegExcelPath = {new RegExcel(@"SOFTWARE\Microsoft\Jet\3.5\Engines\Excel" ,"",false)
                                                        , new RegExcel(@"SOFTWARE\Microsoft\Jet\4.0\Engines\Excel","",false)
                                                        , new RegExcel(@"SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel","",false)
                                                        , new RegExcel(@"SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel","",false)
                                                        , new RegExcel(@"SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel","",false)
                                                        , new RegExcel(@"SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel","",false) };
                string[] SheetName =GetExcelSheetNames(txtbSourceFile.Text);
                switch (Path.GetExtension(txtbSourceFile.Text))
                {
                    case ".xls":
                    case ".xlsx":
                        if (cbIsRowType.Checked)
                        {
                            for (int i = 0; i < RegExcelPath.Length; i ++ )
                            {
                                using (RegistryKey myKey = Registry.LocalMachine.OpenSubKey(RegExcelPath[i].Path, true))
                                {
                                    if (myKey != null)
                                    //檢查子機碼是否存在，檢查資料夾是否存在。
                                    {
                                        //若目錄存在，則取出 key=cnstr 的值。
                                        RegExcelPath[i].DefaultValue = myKey.GetValue("TypeGuessRows").ToString();
                                        RegExcelPath[i].Exist = true;
                                        myKey.SetValue("TypeGuessRows", "0", RegistryValueKind.DWord);
                                    }
                                }
                            }
                        }
                        DataTable dataTable = GetExcelSheetData(txtbSourceFile.Text, SheetName[0]);
                        stringArr = dataTable.AsEnumerable().Select(r => r.ItemArray.Select(ra => ra.ToString()).ToArray()).ToArray();
                        dataTable.Dispose();
                        break;
                    case ".txt":
                        using (StreamReader sr = new StreamReader(txtbSourceFile.Text, Encoding.Default))
                        {
                            sr.ReadLine();//去掉標題列
                            stringArr = (sr.ReadToEnd().Split(new string[] { "\r\n" }, StringSplitOptions.None)).ToArray().Select(r => r.Trim().Split('\t')).ToArray();
                        }
                        break;
                }
                if (stringArr.Length <= 0)
                {
                    MessageBox.Show("匯入資料不符合格式，請重新選擇檔案進行匯入！");
                    return;
                }
                // Excel 資料欄位至少大於2欄
                if (stringArr[0].Length < 2)
                {
                    MessageBox.Show("匯入資料不符合格式，請重新選擇檔案進行匯入！");
                    return;
                }

                string strSQL = "";
                //匯入資料庫前，將資料表清空
                //string strSQL = "Truncate Table [Northwind].[dbo].[Categories];";
                for (int i = 0; i < stringArr.Length; i++)
                {
                    string str_CategoryName,str_Description;
                    string[] temp = stringArr[i];
                    str_CategoryName = temp[0].Trim();
                    str_Description = temp[1].Trim();
                    strSQL += "INSERT INTO [Northwind].[dbo].[Categories] ([CategoryName],[Description]) VALUES('{0}','{1}');";
                    strSQL = string.Format(strSQL, str_CategoryName, str_Description);
                }

                using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True"))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL,conn);
                    int sqlcount =cmd.ExecuteNonQuery();
                    if (sqlcount == stringArr.Length)
                        MessageBox.Show("匯入資料成功");
                    else
                        MessageBox.Show("匯入資料中 " + (stringArr.Length - sqlcount) +" 筆資料失敗");
                }

                //回復登錄檔的設定
                if (cbIsRowType.Checked)
                {
                    var ReDefaultReg = RegExcelPath.AsEnumerable().Where(r => r.Exist == true);
                    foreach (var p in ReDefaultReg)
                    {
                        using (RegistryKey myKey = Registry.LocalMachine.OpenSubKey(p.Path, true))
                        {
                            myKey.SetValue("TypeGuessRows", p.DefaultValue, RegistryValueKind.DWord);
                        }
                    }
                }

            }
        }

這裡要注意的是 Excel 儲存格的資料長度

例如：下圖的 Demo010 的 Description 長度在程式執行後，讀取出來的長度會被截斷

截圖如下

原因可以參考德瑞克_設定登錄機碼 TypeGuessRows、連線字串 IMEX

匯入完成後的截圖

附上登陸檔路徑，來源為德瑞克_設定登錄機碼 TypeGuessRows、連線字串 IMEX

-- Microsoft.Jet.OLEDB.3.5，例如：Excel 97
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

-- Microsoft.Jet.OLEDB.4.0，例如：Excel 2000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Windows Server 2008 R2 x64 平台上，登錄機碼：TypeGuessRows 的路徑：
-- 32位元，Microsoft.Jet.OLEDB.4.0 資料提供者
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

-- 32位元，Microsoft.ACE.OLEDB.14.0 資料提供者
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

x64 作業系統上，同時安裝了 32位元與64位元版本的 Microsoft.ACE.OLEDB 資料提供者
-- 32位元，Microsoft.ACE.OLEDB.12.0 資料提供者
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

-- 64位元，Microsoft.ACE.OLEDB.14.0 資料提供者
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

範例檔案下載

參考資料：

MSDN_FileDialog.Filter 屬性

MSDN_Registry 類別
 德瑞克：SQL Server 學習筆記

回首頁