使用 NPOI 戴入 EXCEL 並轉存入 DataTable。

_LoadExcelAsDataTable.cs

/// <summary>
/// load Excel97/Excel2007 as DataTable
/// </summary>
public static DataTable LoadExcelAsDataTable(String xlsFilename)
{
    FileInfo fi = new FileInfo(xlsFilename);
    using(FileStream fstream = new FileStream(fi.FullName, FileMode.Open))
    {
        IWorkbook wb;
        if(fi.Extension == ".xlsx")
            wb = new XSSFWorkbook(fstream); // excel2007
        else
            wb = new HSSFWorkbook(fstream); // excel97

        // 只取第一個sheet。
        ISheet sheet = wb.GetSheetAt(0);

        // target
        DataTable table = new DataTable();

        // 由第一列取標題做為欄位名稱
        IRow headerRow = sheet.GetRow(0);
        int cellCount = headerRow.LastCellNum; // 取欄位數
        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            //table.Columns.Add(new DataColumn(headerRow.GetCell(i).StringCellValue, typeof(double)));
            table.Columns.Add(new DataColumn(headerRow.GetCell(i).StringCellValue));
        }

        // 略過第零列(標題列)，一直處理至最後一列
        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
        {
            IRow row = sheet.GetRow(i);
            if (row == null) continue;

            DataRow dataRow = table.NewRow();

            //依先前取得的欄位數逐一設定欄位內容
            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                ICell cell = row.GetCell(j);
                if (cell != null)
                {
                    //如要針對不同型別做個別處理，可善用.CellType判斷型別
                    //再用.StringCellValue, .DateCellValue, .NumericCellValue...取值

                    switch (cell.CellType)
                    {
                        case CellType.Numeric:
                            dataRow[j] = cell.NumericCellValue;
                            break;
                        default: // String
                            //此處只簡單轉成字串
                            dataRow[j] = cell.StringCellValue;
                            break;
                    }
                }
            }

            table.Rows.Add(dataRow);
        }

        // success
        return table;
    }
}

LoadExcel97ToDataTable.cs

// =============================================
// 使用NPOI戴入EXCEL 並轉存入 DataTable
// NPOI版本：NPOI binary 2.1.3.1
// =============================================

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;

namespace IMPORT_EXCEL_TO_DATATABLE
{
    public class Misc
    {
        /// <summary>
        /// 自Excel 97檔案載入資料並存入 DataTable。
        /// </summary>
        /// <param name="fileContent">EXCEL檔案</param>
        /// <param name="skipRowCnt">忽略前排行數</param>
        /// <returns></returns>
        public static DataTable LoadExcel97ToDataTable(Stream fileContent, int skipRowCnt)
        {
            HSSFWorkbook wb = new HSSFWorkbook(fileContent);
            ISheet sheet = wb.GetSheetAt(0);
            DataTable dataTable = new DataTable();

            ////由第一列取標題做為欄位名稱
            //IRow headerRow = sheet.GetRow(0);
            //int cellCount = headerRow.LastCellNum;
            //for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            //    //以欄位文字為名新增欄位，此處全視為字串型別以求簡化
            //    dataTable.Columns.Add(
            //        new DataColumn(headerRow.GetCell(i).StringCellValue));

            //欄位名稱取"F1","F2",...
            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                dataTable.Columns.Add(new DataColumn(string.Format("F{0}", i+1)));

            //從忽略排數處理至最後一列
            for (int i = sheet.FirstRowNum + skipRowCnt; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null) continue;

                //依先前取得的欄位數逐一設定欄位內容
                DataRow dataRow = dataTable.NewRow();
                for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                    if (row.GetCell(j) != null)
                        //如要針對不同型別做個別處理，可善用.CellType判斷型別
                        //再用.StringCellValue, .DateCellValue, .NumericCellValue...取值
                        //此處只簡單轉成字串
                        dataRow[j] = row.GetCell(j).ToString();

                dataTable.Rows.Add(dataRow);
            }

            return dataTable;
        }

        public static DataTable LoadExcel97ToDataTable(string filename, int skipRowCnt)
        {
            using (FileStream fs = new FileStream(filename, FileMode.Open))
            {
                return LoadExcel97ToDataTable(fs, skipRowCnt);
            }
        }
    }
}

LoadExcelXlsxToDataTable.cs

using System;
using System.Data;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.OpenXml4Net.OPC;

/// <summary>
/// load Excel.xlsx as DataTable
/// </summary>
public static DataTable ExcelXlsxToDataTable(String xlsxFilename)
{
    OPCPackage pkg = null;
    try
    {
        pkg = OPCPackage.Open(xlsxFilename, PackageAccess.READ);
        XSSFWorkbook xls = new XSSFWorkbook(pkg);
        XSSFSheet sheet = xls.GetSheetAt(0) as XSSFSheet;

        // 
        DataTable table = new DataTable();

        // 由第一列取標題做為欄位名稱
        XSSFRow headerRow = sheet.GetRow(0) as XSSFRow;
        int cellCount = headerRow.LastCellNum; // 取欄位數
        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            table.Columns.Add(new DataColumn(headerRow.GetCell(i).StringCellValue));
        }

        // 略過第零列(標題列)，一直處理至最後一列
        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
        {
            XSSFRow row = sheet.GetRow(i) as XSSFRow;
            if (row == null) continue;

            DataRow dataRow = table.NewRow();

            //依先前取得的欄位數逐一設定欄位內容
            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                {
                    //如要針對不同型別做個別處理，可善用.CellType判斷型別
                    //再用.StringCellValue, .DateCellValue, .NumericCellValue...取值
                    switch(cell.CellType)
                    {
                        case CellType.Numeric:
                            dataRow[j] = cell.NumericCellValue;
                            break;
                        default: // String
                            //此處只簡單轉成字串
                            dataRow[j] = cell.StringCellValue;
                            break;
                    }
                }
            }

            table.Rows.Add(dataRow);
        }

        // success
        return table;
    }
    finally
    {
        // release resource
        if(pkg != null)
            pkg.Close();
    }                     
}

SaveDataTableAsExcelXlsx

///
/// ref → http://einboch.pixnet.net/blog/post/274497938-%E4%BD%BF%E7%94%A8npoi%E7%94%A2%E7%94%9Fexcel%E6%AA%94%E6%A1%88
///

public static  void SaveDataTableAsExcelXlsx(DataTable dt, String xlsxFilename)
{
    ////建立Excel 2007檔案
    IWorkbook wb = new XSSFWorkbook();
    ISheet ws;

    if (dt.TableName != string.Empty)
    {
        ws = wb.CreateSheet(dt.TableName);
    }
    else
    {
        ws = wb.CreateSheet("Sheet1");
    }

    ws.CreateRow(0);//第一行為欄位名稱
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        ws.GetRow(0).CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
    }

    for (int i = 0; i < dt.Rows.Count; i++)
    {
        ws.CreateRow(i + 1);
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            ws.GetRow(i + 1).CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
        }
    }

    FileStream file = new FileStream(xlsxFilename, FileMode.Create);//產生檔案
    wb.Write(file);
    file.Close();
}

非常謝謝您的分享，相當有用！