EXCEL 類別 - 透過 OLEDB 來存取 EXCEL 檔案
透過 OLEDB 來存取 EXCEL 檔案，若要存取 EXCEL 2007 之後的版本，請務必要安裝 Microsoft Access Database Engine 2010 或 2013 版。
程式經由登錄檔 HKLM\SOFTWARE\ODBC\ODBCINST.INI\Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) 來判斷是否已安裝新版 ACE.OLEDB 的驅動程式，在讀取舊版EXCEL檔案時會優先使用新版的驅動程式，當讀取新版EXCEL檔時，若無安裝新版驅動時，則會回傳無法建立檔案的訊息。
提供的方法：
CreateXlsFile - 建立 EXCEL 檔案，可以將 DataGridView/DataSet / DataTable 匯出為 EXCEL 檔案
GetAllSheetName - 取回 EXCEL 檔中所有 Sheet 的名稱
InsertSheetData - 將 DataGridView/DataSet / DataTable 附加到 EXCEL 檔案中，成為 Sheet 資料。
ReadXlsFile - 將 EXCEL 檔案中所有的 Sheet 資料讀入到 DataSet ，或是指定 Sheet 資料讀入到 DataTable 。
ReadXlsFirstShee - 將 EXCEL 檔案中第一個 Sheet 資料讀入到 DataTable。
using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
namespace Utility
{
/// <summary>
/// EXCEL 檔案處理類別
/// </summary>
/// <remarks>
/// 系統會自行偵測目前可以使用的驅動程式。<br/>
/// 當匯出格式為 Excel 97~2003 時，先採用 ACE.OLEDB 12.0 的驅動程式，沒有安裝時則會改用 Jet.OLEDB 4.0 的驅動程式。<br />
/// 當匯出格式為 Excel 2007+ 時，則只會採用 ACE.OLEDB 12.0 的驅動程式，沒有安裝時則無法建立檔案。
/// </remarks>
public class EXCEL
{
/// <summary>
/// 定義 Excel 檔案格式類別
/// </summary>
/// <remarks>支援的 EXCEL 格式。</remarks>
public enum ExcelMode
{
/// <summary>
/// Excel 97 ~ 2003
/// </summary>
Excel97_2003 = 1,
/// <summary>
/// Excel 2007 ~ 2010
/// </summary>
Excel2007_2010 = 2
}
/// <summary>
/// 取回 OLEDB 的連線字串
/// </summary>
/// <param name="ExcelFileMode">EXCEL 檔案格式</param>
private static string OleDBConnectionString(ExcelMode ExcelFileMode)
{
Microsoft.Win32.RegistryKey regHKLM = My.Computer.Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBCINST.INI");
string strReturn = "";
if (ExcelFileMode == ExcelMode.Excel97_2003)
{
if (regHKLM.OpenSubKey("Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)") == null)
{
strReturn = "Data Source='{0}'; Provider='Microsoft.Jet.OLEDB.4.0'; Extended Properties='Excel 8.0; HDR=No; IMEX=0;'";
}
else
{
strReturn = "Data Source='{0}'; Provider='Microsoft.ACE.OLEDB.12.0'; Extended Properties='Excel 8.0; HDR=No; IMEX=0;'";
}
}
else
{
if (regHKLM.OpenSubKey("Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)") == null)
{
strReturn = "";
}
else
{
strReturn = "Data Source='{0}'; Provider='Microsoft.ACE.OLEDB.12.0'; Extended Properties='Excel 12.0 Xml; HDR=No; IMEX=0;'";
}
}
return strReturn;
}
/// <summary>
/// 取回 EXCEL 的所有 SheetName
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <remarks>讀取 Excel 檔案，傳回所有活頁簿的名稱。</remarks>
/// <returns>所有活頁簿名稱</returns>
public static string[] GetAllSheetName(string ExcelFileName, ExcelMode ExcelFileMode)
{
if (ExcelFileName.Trim() == "") return null;
if (!My.Computer.FileSystem.FileExists(ExcelFileName)) return null;
OleDbConnection conXLS = new OleDbConnection();
DataTable tmpTable = new DataTable();
string[] strReturn;
conXLS.ConnectionString = string.Format(OleDBConnectionString(ExcelFileMode), ExcelFileName);
if (conXLS.ConnectionString == "")
{
conXLS.Dispose();
return null;
}
try
{
conXLS.Open();
tmpTable = conXLS.GetSchema("TABLES");
conXLS.Dispose();
}
catch
{
conXLS.Dispose();
return null;
}
if ((tmpTable == null) || (tmpTable.Rows.Count == 0)) return null;
for (int i = 0; i < tmpTable.Rows.Count; i++) { tmpTable.Rows[i]["Table_Name"] = (tmpTable.Rows[i]["Table_Name"].ToString().Replace("'", "")).Replace("$", ""); }
tmpTable.AcceptChanges();
tmpTable = tmpTable.DefaultView.ToTable(true, new string[] { "Table_Name" });
strReturn = new string[tmpTable.Rows.Count];
for (int i = 0; i < tmpTable.Rows.Count; i++) { strReturn[i] = tmpTable.Rows[i]["Table_Name"].ToString(); }
return strReturn;
}
#region 建立 EXCEL 檔案
/// <summary>
/// 產生 EXCEL 檔案 (DataTable)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="ExportDataTable">輸出的資料表</param>
/// <remarks>將資料表(DataTable)匯出到指定的 EXCEL 檔案，如果檔案存在將會被覆寫。<br />
/// 欄位名稱使用資料欄(Column)的標題(Caption)名稱，無標題名稱時，將不會匯出該欄位。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, DataTable ExportDataTable)
{
return CreateXlsFile(ExcelFileName, ExcelFileMode, ExportDataTable, false);
}
/// <summary>
/// 產生 EXCEL 檔案 (DataGridView)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="GridViewData">匯出的資料檢視表</param>
/// <remarks>將資料表(DataTable)匯出到指定的 EXCEL 檔案，如果檔案存在將會被覆寫。<br />
/// 欄位名稱使用資料欄(Column)的標題(HeaderText)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// 資料欄設定為不顯示時，也不會匯出該欄位。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, DataGridView GridViewData)
{
return CreateXlsFile(ExcelFileName, ExcelFileMode, GridViewData, false);
}
/// <summary>
/// 產生 EXCEL 檔案 (DataTable)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="ExportDataTable">輸出的資料表</param>
/// <param name="InsertMode">附加資料</param>
/// <remarks>將資料表(DataTable)匯出並附加到指定的 EXCEL 檔案，如果檔案不存在則會自動新增檔案。<br />
/// 欄位名稱使用資料欄(Column)的標題(Caption)名稱，無標題名稱時，將不會匯出該欄位。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, DataTable ExportDataTable, bool InsertMode)
{
if (ExcelFileName.Trim() == "") return false;
if (My.Computer.FileSystem.FileExists(ExcelFileName))
{
try { if (!InsertMode) My.Computer.FileSystem.DeleteFile(ExcelFileName); }
catch { return false; }
}
return InsertSheetData(ExcelFileName, ExcelFileMode, "Sheet1", ExportDataTable);
}
/// <summary>
/// 產生 EXCEL 檔案 (DataGridView)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="GridViewData">匯出的資料檢視表</param>
/// <param name="InsertMode">附加資料</param>
/// <remarks>將資料表(DataTable)匯出並附加到指定的 EXCEL 檔案，如果檔案不存在則會自動新增檔案。<br />
/// 欄位名稱使用資料欄(Column)的標題(HeaderText)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// 資料欄設定為不顯示時，也不會匯出該欄位。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, DataGridView GridViewData, bool InsertMode)
{
if (ExcelFileName.Trim() == "") return false;
if (My.Computer.FileSystem.FileExists(ExcelFileName))
{
try { if (!InsertMode) My.Computer.FileSystem.DeleteFile(ExcelFileName); }
catch { return false; }
}
return InsertSheetData(ExcelFileName, ExcelFileMode, "Sheet1", GridViewData);
}
/// <summary>
/// 產生 EXCEL 檔案 (DataTable)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="UseTableNameForSheetName">使用資料表名稱為活頁簿名稱</param>
/// <param name="ExportDataTable">輸出的資料表</param>
/// <remarks>將資料表(DataTable)匯出到指定的 EXCEL 檔案，並使用資料表名稱做為活頁簿(SheetName)名稱。<br />
/// 如果檔案存在將會被覆寫，活頁簿有相同名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(Caption)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, Boolean UseTableNameForSheetName, DataTable ExportDataTable)
{
if (UseTableNameForSheetName)
{
return CreateXlsFile(ExcelFileName, ExcelFileMode, ExportDataTable, ExportDataTable.TableName, false);
}
else
{
return CreateXlsFile(ExcelFileName, ExcelFileMode, ExportDataTable, false);
}
}
/// <summary>
/// 產生 EXCEL 檔案 (DataTable)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="UseTableNameForSheetName">使用資料表名稱為活頁簿名稱</param>
/// <param name="ExportDataTable">輸出的資料表</param>
/// <param name="InsertMode">附加資料</param>
/// <remarks>將資料表(DataTable)匯出並附加到指定的 EXCEL 檔案，使用資料表名稱做為活頁簿(SheetName)名稱。<br />
/// 如果檔案不存在則會自動新增檔案，活頁簿有相同名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(Caption)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, bool UseTableNameForSheetName, DataTable ExportDataTable, bool InsertMode)
{
if (UseTableNameForSheetName)
{
return CreateXlsFile(ExcelFileName, ExcelFileMode, ExportDataTable, ExportDataTable.TableName, true);
}
else
{
return CreateXlsFile(ExcelFileName, ExcelFileMode, ExportDataTable, true);
}
}
/// <summary>
/// 產生 EXCEL 檔案，使用指定活頁簿名稱 (DataTable)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="ExportDataTable">輸出的資料表</param>
/// <param name="SheetName">活頁簿名稱</param>
/// <remarks>將資料表(DataTable)匯出到指定的 EXCEL 檔案並使用指定的活頁簿名稱(SheetName)。<br />
/// 如果檔案存在將會被覆寫，活頁簿有相同名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(Caption)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, DataTable ExportDataTable, string SheetName)
{
return CreateXlsFile(ExcelFileName, ExcelFileMode, ExportDataTable, SheetName, false);
}
/// <summary>
/// 產生 EXCEL 檔案，使用指定活頁簿名稱 (DataGridView)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="GridViewData">匯出的資料檢視表</param>
/// <param name="SheetName">活頁簿名稱</param>
/// <remarks>將資料表(DataTable)匯出到指定的 EXCEL 檔案並使用指定的活頁簿名稱(SheetName)。<br />
/// 如果檔案存在將會被覆寫，活頁簿有相同名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(HeaderText)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// 資料欄設定為不顯示時，也不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, DataGridView GridViewData, string SheetName)
{
return CreateXlsFile(ExcelFileName, ExcelFileMode, GridViewData, SheetName, false);
}
/// <summary>
/// 產生 EXCEL 檔案，使用指定活頁簿名稱 (DataTable)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="ExportDataTable">輸出的資料表</param>
/// <param name="SheetName">活頁簿名稱</param>
/// <param name="InsertMode">附加資料</param>
/// <remarks>將資料表(DataTable)匯出並附加到指定的 EXCEL 檔案，並使用指定的活頁簿名稱(SheetName)。<br />
/// 如果檔案不存在則會自動新增檔案，活頁簿有相同名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(Caption)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, DataTable ExportDataTable, string SheetName, bool InsertMode)
{
if (My.Computer.FileSystem.FileExists(ExcelFileName))
{
try { if (!InsertMode) My.Computer.FileSystem.DeleteFile(ExcelFileName); }
catch { return false; }
}
return InsertSheetData(ExcelFileName, ExcelFileMode, SheetName, ExportDataTable);
}
/// <summary>
/// 產生 EXCEL 檔案，使用指定活頁簿名稱 (DataGridView)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="GridViewData">匯出的資料檢視表</param>
/// <param name="SheetName">活頁簿名稱</param>
/// <param name="InsertMode">附加資料</param>
/// <remarks>將資料表(DataTable)匯出並附加到指定的 EXCEL 檔案，並使用指定的活頁簿名稱(SheetName)。<br />
/// 如果檔案不存在則會自動新增檔案，活頁簿有相同名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(HeaderText)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// 資料欄設定為不顯示時，也不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, DataGridView GridViewData, string SheetName, bool InsertMode)
{
if (My.Computer.FileSystem.FileExists(ExcelFileName))
{
try { if (!InsertMode) My.Computer.FileSystem.DeleteFile(ExcelFileName); }
catch { return false; }
}
return InsertSheetData(ExcelFileName, ExcelFileMode, SheetName, GridViewData);
}
/// <summary>
/// 產生 EXCEL 檔案 (DataSet)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="UseTableNameForSheetName">使用資料表名稱為活頁簿名稱</param>
/// <param name="ExportDataSet">輸出的資料集</param>
/// <remarks>將資料集(DataSet)匯出到指定的 EXCEL 檔案，並使用資料表名稱做為活頁簿(SheetName)名稱。<br />
/// 如果檔案存在將會被覆寫，活頁簿有相同名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(Caption)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, bool UseTableNameForSheetName, DataSet ExportDataSet)
{
return CreateXlsFile(ExcelFileName, ExcelFileMode, UseTableNameForSheetName, ExportDataSet, false);
}
/// <summary>
/// 產生 EXCEL 檔案 (DataSet)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="UseTableNameForSheetName">使用資料表名稱為活頁簿名稱</param>
/// <param name="ExportDataSet">輸出的資料集</param>
/// <param name="InsertMode">附加資料</param>
/// <remarks>將資料集(DataSet)匯出並附加到指定的 EXCEL 檔案，並使用資料表(DataTable)的名稱為活頁簿名稱(SheetName)。<br />
/// 如果檔案不存在則會自動新增檔案，活頁簿有相同名稱時，則會自動在名稱後面附加 "_x"。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns>是否成功產生檔案</returns>
public static bool CreateXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, bool UseTableNameForSheetName, DataSet ExportDataSet, bool InsertMode)
{
if (My.Computer.FileSystem.FileExists(ExcelFileName))
{
try { if (!InsertMode) My.Computer.FileSystem.DeleteFile(ExcelFileName); }
catch { return false; }
}
string strSheetName;
int intCount = 1;
for (int i = 0; i < ExportDataSet.Tables.Count; i++)
{
if (UseTableNameForSheetName)
{
if ((ExportDataSet.Tables[i].TableName == null) || (ExportDataSet.Tables[i].TableName.Trim() == ""))
{
strSheetName = "Sheet" + intCount.ToString();
intCount++;
}
else
{
strSheetName = ExportDataSet.Tables[i].TableName.Trim();
}
}
else
{
strSheetName = "Sheet" + intCount.ToString();
intCount++;
}
if (!InsertSheetData(ExcelFileName, ExcelFileMode, strSheetName, ExportDataSet.Tables[i])) return false;
}
return true;
}
#endregion
#region 活頁簿(Sheet)處理
/// <summary>
/// 插入活頁簿資料，使用指定 Sheet 名稱 (DataGridView)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="SheetName">活頁簿名稱</param>
/// <param name="GridViewData">匯出的資料檢視表</param>
/// <remarks>將資料檢視表的資料匯出，並使用指定的活頁簿名稱。<br />
/// 如果有相同名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(HeaderText)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// 資料欄設定為不顯示時，也不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns>是否成功匯出資料</returns>
public static bool InsertSheetData(string ExcelFileName, ExcelMode ExcelFileMode, string SheetName, DataGridView GridViewData)
{
if ((ExcelFileName.Trim() == "") || (SheetName.Trim() == "")) return false;
if ((GridViewData == null) || (GridViewData.Rows.Count == 0) || (GridViewData.Columns.Count == 0)) return false;
SheetName = SheetName.Trim() == "" ? "Sheet1" : SheetName.Trim();
SheetName = NormalizeFieldName(SheetName);
SheetName = CheckSheetName(ExcelFileName, ExcelFileMode, SheetName);
if (SheetName.Length > 31) return false;
OleDbConnection conXLS = new OleDbConnection();
OleDbCommand cmdXLS = new OleDbCommand();
string strXLS = "", strXLSBody = "";
try
{
conXLS.ConnectionString = string.Format(OleDBConnectionString(ExcelFileMode), ExcelFileName);
if (conXLS.ConnectionString == "")
{
conXLS.Dispose();
return false;
}
conXLS.Open();
strXLS = "Create Table [" + SheetName + "] ";
strXLSBody = "";
for (int i = 0; i < GridViewData.Columns.Count; i++)
{
if (GridViewData.Columns[i].HeaderText.Trim() == "") continue;
if (!GridViewData.Columns[i].Visible) continue;
strXLSBody = strXLSBody == "" ? "" : strXLSBody + ", ";
strXLSBody += "[" + NormalizeFieldName(GridViewData.Columns[i].HeaderText.Trim()) + "] ";
if (GridViewData.Columns[i].ValueType == typeof(DateTime))
{
strXLSBody += "Date";
}
else if ((GridViewData.Columns[i].ValueType == typeof(int)) || (GridViewData.Columns[i].ValueType == typeof(Int16))
|| (GridViewData.Columns[i].ValueType == typeof(Int32)) || (GridViewData.Columns[i].ValueType == typeof(Int64))
|| (GridViewData.Columns[i].ValueType == typeof(long)) || (GridViewData.Columns[i].ValueType == typeof(decimal))
|| (GridViewData.Columns[i].ValueType == typeof(Decimal)))
{
strXLSBody += "Integer";
}
else if ((GridViewData.Columns[i].ValueType == typeof(Single)) || (GridViewData.Columns[i].ValueType == typeof(Double)) || (GridViewData.Columns[i].ValueType == typeof(double)))
{
strXLSBody += "Decimal";
}
else
{
strXLSBody += "NVarChar";
}
}
cmdXLS.Connection = conXLS;
cmdXLS.CommandType = CommandType.Text;
cmdXLS.CommandText = strXLS + "(" + strXLSBody + ")";
cmdXLS.ExecuteNonQuery();
if (ExcelFileMode == ExcelMode.Excel97_2003)
{
strXLS = "INSERT INTO [" + SheetName + "$] VALUES (";
}
else
{
strXLS = "INSERT INTO [" + SheetName + "] VALUES (";
}
for (int i = 0; i < GridViewData.Rows.Count; i++)
{
strXLSBody = "";
for (int j = 0; j < GridViewData.Columns.Count; j++)
{
if (GridViewData.Columns[j].HeaderText.Trim() == "") continue;
if (!GridViewData.Columns[j].Visible) continue;
strXLSBody += strXLSBody == "" ? "" : ",";
if ((GridViewData.Rows[i].Cells[j].Value == null) || (GridViewData.Rows[i].Cells[j].Value.ToString().Length == 0))
{
strXLSBody += "Null";
}
else
{
if (GridViewData.Columns[j].ValueType == typeof(DateTime))
{
strXLSBody += "#" + DateTime.Parse(GridViewData.Rows[i].Cells[j].Value.ToString()).ToString("yyyy/MM/dd HH:mm:ss") + "#";
}
else if ((GridViewData.Columns[j].ValueType == typeof(int)) || (GridViewData.Columns[j].ValueType == typeof(Int16))
|| (GridViewData.Columns[j].ValueType == typeof(Int32)) || (GridViewData.Columns[j].ValueType == typeof(Int64))
|| (GridViewData.Columns[j].ValueType == typeof(long)) || (GridViewData.Columns[j].ValueType == typeof(decimal))
|| (GridViewData.Columns[j].ValueType == typeof(Decimal)) || (GridViewData.Columns[j].ValueType == typeof(double))
|| (GridViewData.Columns[j].ValueType == typeof(Single)) || (GridViewData.Columns[j].ValueType == typeof(Double)))
{
strXLSBody += GridViewData.Rows[i].Cells[j].Value.ToString();
}
else
{
if (GridViewData.Rows[i].Cells[j].Value.ToString().Trim() == "")
{
strXLSBody += "' '";
}
else
{
strXLSBody += "'" + GridViewData.Rows[i].Cells[j].Value.ToString() + "'";
}
}
}
}
cmdXLS.CommandText = strXLS + strXLSBody + ")";
cmdXLS.ExecuteNonQuery();
}
cmdXLS.Dispose();
conXLS.Dispose();
return true;
}
catch
{
cmdXLS.Dispose();
conXLS.Dispose();
return false;
}
}
/// <summary>
/// 插入活頁簿資料，使用指定 Sheet 名稱 (DataTable)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="SheetName">活頁簿名稱</param>
/// <param name="ExportDataTable">匯出的資料表</param>
/// <remarks>將資料表(DataTable)的資料匯出，並使用指定的活頁簿名稱。<br />
/// 如果有相同名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(Caption)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns>是否成功匯出資料</returns>
public static bool InsertSheetData(string ExcelFileName, ExcelMode ExcelFileMode, string SheetName, DataTable ExportDataTable)
{
if ((ExcelFileName.Trim() == "") || (SheetName.Trim() == "")) return false;
if ((ExportDataTable == null) || (ExportDataTable.Rows.Count == 0) || (ExportDataTable.Columns.Count == 0)) return false;
SheetName = SheetName.Trim() == "" ? "Sheet1" : SheetName.Trim();
SheetName = NormalizeFieldName(SheetName);
SheetName = CheckSheetName(ExcelFileName, ExcelFileMode, SheetName);
if (SheetName.Length > 31) return false;
OleDbConnection conXLS = new OleDbConnection();
OleDbCommand cmdXLS = new OleDbCommand();
string strXLS = "", strXLSBody = "";
try
{
conXLS.ConnectionString = string.Format(OleDBConnectionString(ExcelFileMode), ExcelFileName);
if (conXLS.ConnectionString == "")
{
conXLS.Dispose();
return false;
}
conXLS.Open();
strXLS = "Create Table [" + SheetName + "] ";
strXLSBody = "";
for (int i = 0; i < ExportDataTable.Columns.Count; i++)
{
if (ExportDataTable.Columns[i].Caption.Trim() == "") continue;
strXLSBody = strXLSBody == "" ? "" : strXLSBody + ", ";
strXLSBody += "[" + NormalizeFieldName(ExportDataTable.Columns[i].Caption) + "] ";
if (ExportDataTable.Columns[i].DataType == typeof(DateTime))
{
strXLSBody += "Date";
}
else if ((ExportDataTable.Columns[i].DataType == typeof(int)) || (ExportDataTable.Columns[i].DataType == typeof(Int16))
|| (ExportDataTable.Columns[i].DataType == typeof(Int32)) || (ExportDataTable.Columns[i].DataType == typeof(Int64))
|| (ExportDataTable.Columns[i].DataType == typeof(long)) || (ExportDataTable.Columns[i].DataType == typeof(decimal))
|| (ExportDataTable.Columns[i].DataType == typeof(Decimal)))
{
strXLSBody += "Integer";
}
else if ((ExportDataTable.Columns[i].DataType == typeof(Single)) || (ExportDataTable.Columns[i].DataType == typeof(Double)) || (ExportDataTable.Columns[i].DataType == typeof(double)))
{
strXLSBody += "Decimal";
}
else
{
strXLSBody += "NVarChar";
}
}
cmdXLS.Connection = conXLS;
cmdXLS.CommandType = CommandType.Text;
cmdXLS.CommandText = strXLS + "(" + strXLSBody + ")";
cmdXLS.ExecuteNonQuery();
if (ExcelFileMode == ExcelMode.Excel97_2003)
{
strXLS = "INSERT INTO [" + SheetName + "$] VALUES (";
}
else
{
strXLS = "INSERT INTO [" + SheetName + "] VALUES (";
}
for (int i = 0; i < ExportDataTable.Rows.Count; i++)
{
strXLSBody = "";
for (int j = 0; j < ExportDataTable.Columns.Count; j++)
{
if (ExportDataTable.Columns[j].Caption.Trim() == "") continue;
strXLSBody += strXLSBody == "" ? "" : ",";
if ((ExportDataTable.Rows[i][j] == null) || (ExportDataTable.Rows[i][j].ToString().Length == 0))
{
strXLSBody += "Null";
}
else
{
if (ExportDataTable.Columns[j].DataType == typeof(DateTime))
{
strXLSBody += "#" + DateTime.Parse(ExportDataTable.Rows[i][j].ToString()).ToString("yyyy/MM/dd HH:mm:ss") + "#";
}
else if ((ExportDataTable.Columns[j].DataType == typeof(int)) || (ExportDataTable.Columns[j].DataType == typeof(Int16))
|| (ExportDataTable.Columns[j].DataType == typeof(Int32)) || (ExportDataTable.Columns[j].DataType == typeof(Int64))
|| (ExportDataTable.Columns[j].DataType == typeof(long)) || (ExportDataTable.Columns[j].DataType == typeof(decimal))
|| (ExportDataTable.Columns[j].DataType == typeof(Decimal)) || (ExportDataTable.Columns[j].DataType == typeof(double))
|| (ExportDataTable.Columns[j].DataType == typeof(Single)) || (ExportDataTable.Columns[j].DataType == typeof(Double)))
{
strXLSBody += ExportDataTable.Rows[i][j].ToString();
}
else
{
if (ExportDataTable.Rows[i][j].ToString().Trim() == "")
{
strXLSBody += "' '";
}
else
{
strXLSBody += "'" + ExportDataTable.Rows[i][j].ToString() + "'";
}
}
}
}
cmdXLS.CommandText = strXLS + strXLSBody + ")";
cmdXLS.ExecuteNonQuery();
}
cmdXLS.Dispose();
conXLS.Dispose();
return true;
}
catch
{
cmdXLS.Dispose();
conXLS.Dispose();
return false;
}
}
/// <summary>
/// 插入活頁簿資料 (DataGridView)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="GridViewData">匯出的資料檢視表</param>
/// <remarks>將資料檢視表的資料匯出。<br />
/// 如果有相同的活頁簿名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(HeaderText)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// 資料欄設定為不顯示時，也不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns></returns>
public static bool InsertSheetData(string ExcelFileName, ExcelMode ExcelFileMode, DataGridView GridViewData)
{
return InsertSheetData(ExcelFileName, ExcelFileMode, "Sheet1", GridViewData);
}
/// <summary>
/// 插入活頁簿資料 (DataTable)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="ExportDataTable">匯出的資料表</param>
/// <remarks>將資料表(DataTable)的資料匯出。<br />
/// 如果有相同的活頁簿名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(Caption)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns></returns>
public static bool InsertSheetData(string ExcelFileName, ExcelMode ExcelFileMode, DataTable ExportDataTable)
{
return InsertSheetData(ExcelFileName, ExcelFileMode, "Sheet1", ExportDataTable);
}
/// <summary>
/// 插入活頁簿資料 (DataSet)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="ExportDataSet">匯出的資料集</param>
/// <param name="UserTableNameForSheetName">使用資料表名稱為活頁簿名稱</param>
/// <remarks>將資料集(DataSet)的資料匯出，並且使用資料名稱做為活頁簿的名稱。<br />
/// 如果有相同的活頁簿名稱時，則會自動在名稱後面附加 "_x"。<br />
/// 欄位名稱使用資料欄(Column)的標題(Caption)名稱，無標題名稱時，將不會匯出該欄位。<br />
/// <br />
/// <br />
/// 註：活頁簿名稱最大長度為 31 個字元。</remarks>
/// <returns></returns>
public static bool InsertSheetData(string ExcelFileName, ExcelMode ExcelFileMode, DataSet ExportDataSet, bool UserTableNameForSheetName)
{
string strSheetName;
int intCount = 1;
for (int i = 0; i < ExportDataSet.Tables.Count; i++)
{
if (UserTableNameForSheetName)
{
if ((ExportDataSet.Tables[i].TableName == null) || (ExportDataSet.Tables[i].TableName.Trim() == ""))
{
strSheetName = "Sheet" + intCount.ToString();
intCount++;
}
else
{
strSheetName = ExportDataSet.Tables[i].TableName;
}
}
else
{
strSheetName = "Sheet" + intCount.ToString();
intCount++;
}
if (!InsertSheetData(ExcelFileName, ExcelFileMode, strSheetName, ExportDataSet.Tables[i])) return false;
}
return true;
}
#endregion
#region 讀取 EXCEL 檔案
/// <summary>
/// 讀取 EXCEL 指定活頁簿的資料 (DataTable)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="SheetName">Sheet 名稱</param>
/// <remarks>將 Excel 檔案中指定的活頁簿資料取出，並儲存到資料表(DataTable)中。</remarks>
/// <returns>讀取的資料表</returns>
public static DataTable ReadXlsFile(string ExcelFileName, ExcelMode ExcelFileMode, string SheetName)
{
if ((ExcelFileName.Trim() == "") || (SheetName.Trim() == "")) return null;
if (!My.Computer.FileSystem.FileExists(ExcelFileName)) return null;
OleDbConnection conXLS = new OleDbConnection();
OleDbCommand cmdXLS = new OleDbCommand();
OleDbDataAdapter odaXLS = new OleDbDataAdapter();
DataTable dtReturn = new DataTable();
conXLS.ConnectionString = string.Format(OleDBConnectionString(ExcelFileMode), ExcelFileName);
if (conXLS.ConnectionString == "")
{
conXLS.Dispose();
return null;
}
try
{
conXLS.Open();
cmdXLS.Connection = conXLS;
cmdXLS.CommandType = CommandType.Text;
cmdXLS.CommandText = "Select * from [" + SheetName + "$]";
odaXLS.SelectCommand = cmdXLS;
odaXLS.Fill(dtReturn);
odaXLS.Dispose();
cmdXLS.Dispose();
conXLS.Dispose();
}
catch
{
odaXLS.Dispose();
cmdXLS.Dispose();
conXLS.Dispose();
return null;
}
return dtReturn;
}
/// <summary>
/// 讀取 EXCEL 所有活頁簿的資料 (DataSet)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <remarks>將 Excel 檔案中所有的活頁簿資料取出，並儲存到資料集(DataSet)中。</remarks>
/// <returns>讀取的資料集</returns>
public static DataSet ReadXlsFile(string ExcelFileName, ExcelMode ExcelFileMode)
{
if (ExcelFileName.Trim() == "") return null;
if (!My.Computer.FileSystem.FileExists(ExcelFileName)) return null;
string[] arySheetName = GetAllSheetName(ExcelFileName, ExcelFileMode);
if ((arySheetName == null) || (arySheetName[0].Trim() == "")) return null;
DataSet dsReturn = new DataSet();
DataTable tmpTable = new DataTable();
for (int i = 0; i < arySheetName.Length; i++)
{
tmpTable = ReadXlsFile(ExcelFileName, ExcelFileMode, arySheetName[i]);
if (tmpTable == null) return null;
dsReturn.Tables.Add(tmpTable);
}
return dsReturn;
}
/// <summary>
/// 讀取 EXCEL 的第一個 Sheet 資料 (DataTable)
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <remarks>將 Excel 檔案中第一個活頁簿的資料取出，並儲存到資料表(DataTable)中。</remarks>
/// <returns>讀取的資料表</returns>
public static DataTable ReadXlsFirstSheet(string ExcelFileName, ExcelMode ExcelFileMode)
{
if (ExcelFileName.Trim() == "") return null;
if (!My.Computer.FileSystem.FileExists(ExcelFileName)) return null;
string[] arySheetName = GetAllSheetName(ExcelFileName, ExcelFileMode);
if ((arySheetName == null) || (arySheetName[0].Trim() == "")) return null;
return ReadXlsFile(ExcelFileName, ExcelFileMode, arySheetName[0]);
}
#endregion
/// <summary>
/// 檢查 Sheet 名稱，產生唯一的 Sheet 名稱
/// </summary>
/// <param name="ExcelFileName">Excel 完整檔案名稱與路徑</param>
/// <param name="ExcelFileMode">Excel 檔案格式</param>
/// <param name="SheetName">要設定的 Sheet 名稱</param>
private static string CheckSheetName(string ExcelFileName, ExcelMode ExcelFileMode, string SheetName)
{
string[] arySheetName = GetAllSheetName(ExcelFileName, ExcelFileMode);
if (arySheetName == null) return SheetName;
if ((arySheetName.Length == 0) || (SheetName.Trim() == "")) return SheetName;
string strReturn = SheetName;
string[] aryTemp;
foreach (string tmpString in arySheetName)
{
if (tmpString.ToUpper().Trim() == SheetName.ToUpper())
{
aryTemp = strReturn.Split('_');
Array.Reverse(aryTemp);
if (aryTemp.Length > 1)
{
if (Tools.IsNumeric(aryTemp[0]))
{
strReturn = strReturn.Replace("_" + aryTemp[0], "_" + (Convert.ToInt32(aryTemp[0], 10) + 1).ToString());
}
else
{
strReturn += "_1";
}
}
else
{
strReturn += "_1";
}
}
}
return strReturn;
}
/// <summary>
/// 正規化欄位名稱，去除特殊字元
/// </summary>
/// <param name="FieldName">欄位名稱</param>
private static string NormalizeFieldName(string FieldName)
{
string strReturn = FieldName.Trim();
strReturn = strReturn.Replace("'", "").Replace(".", "").Replace("[", "").Replace("]", "");
strReturn = strReturn.Replace("*", "").Replace(":", "").Replace("?", "").Replace("/", "").Replace("\\", "");
return strReturn;
}
}
}程式是運氣與直覺堆砌而成的奇蹟。
若不具備這兩者，不可能以這樣的工時實現這樣的規格。
修改規格是對奇蹟吐槽的褻瀆行為。
而追加修改則是相信奇蹟還會重現的魯莽行動。
