利用 NPOI 讀取 Excel

2018-08-08

這個範例可以完成 Excel to DataTable。

要先安裝 Microsoft Access Database Engine 2010 可轉散發套件，避免發生錯誤「'Microsoft.ACE.OLEDB.12.0' 提供者並未登錄」。
網址：http://www.microsoft.com/downloads/zh-tw/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=zh-tw

/// <summary>
/// 將 Excel 資料匯入 DataTable
/// </summary>
/// <param name="FileName">要匯入的 Excel 位址(含完整檔名)</param>
/// <param name="SheetIndex">分頁索引值</param>
/// <param name="HaveTitle">Excel 表格是否含有標題</param>
/// <param name="ExcelData">匯入的 DataTable</param>
public void Reader(string FileName, int SheetIndex, bool HaveTitle, ref DataTable ExcelData)
{
    using (OleDbConnection OleDbConn = new OleDbConnection(string.Format(@"Provider=Microsoft.Ace.OleDb.12.0; Data Source={0}; Extended Properties='Excel 12.0; HDR=No;IMEX=1;'", FileName)))
    {
        OleDbConn.Open();

        for (int i = 0; i < OleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows.Count; i++)
        {
            ExcelData = new DataTable();

            using (OleDbDataAdapter OleDbAptr = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", OleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[i]["TABLE_NAME"]), OleDbConn))
            {
                if (!HaveTitle)
                    OleDbAptr.Fill(ExcelData);
                else
                {
                    DataTable _DataTable = new DataTable();
                    OleDbAptr.Fill(_DataTable);

                    int Index = 0, ColumnIndex = 0;
                    foreach (DataRow dr in _DataTable.Rows)
                    {

                        ColumnIndex = 0;
                        foreach (DataColumn dc in _DataTable.Columns)
                        {
                            if (Index.Equals(0))
                                ExcelData.Columns.Add(dr[ColumnIndex].ToString(), typeof(string));
                            else
                                ExcelData.Rows[Index - 1][ColumnIndex] = dr[ColumnIndex].ToString();
                            ColumnIndex++;
                        }

                        if (Index < _DataTable.Rows.Count - 1)
                            ExcelData.Rows.Add();

                        Index++;
                    }
                }
            }

            /*if (ExcelData.Columns.Count > 20 && ExcelData.Rows.Count > 10)
            {
                break;
            }*/
        }
    }
}

回首頁