[C#][WinForm] Excel 匯入資料庫
讀取 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
範例檔案 下載
參考資料: