萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

ASP.NET 使用 NPOI 實現 Excel 匯入匯出完整指南 (支援 .xls/.xlsx/.docx)

為什麼選擇 NPOI?

  • **免費開源** (Apache License 2.0),無 Office 授權成本。
  • **無需安裝 Office** 於伺服器,適合 Linux Docker、雲端部署。
  • **完整支援** Excel 97-2003 (.xls)、Excel 2007+ (.xlsx)、Word 2007+ (.docx)。
  • **記憶體效率**:`SXSSFWorkbook` (串流模式) 處理百萬列不爆記憶體。
  • **豐富功能**:樣式、公式、圖表、圖片、資料驗證、分頁列印設定。

NuGet 套件安裝

Install-Package NPOI
Install-Package NPOI.OOXML   // .xlsx/.docx 支援 (NPOI 2.0+ 已整合在主包)
Install-Package ICSharpCode.SharpZipLib  // 依賴項

命名空間對照

格式WorkbookSheetRow/Cell命名空間
.xls (BIFF)HSSFWorkbookHSSFSheetHSSFRow/HSSFCellNPOI.HSSF.UserModel
.xlsx (OOXML)XSSFWorkbookXSSFSheetXSSFRow/XSSFCellNPOI.XSSF.UserModel
通用介面IWorkbookISheetIRow/ICellNPOI.SS.UserModel
建議:全程使用介面 `IWorkbook`/`ISheet`/`IRow`/`ICell`,程式碼同時相容 .xls/.xlsx。

通用輔助類別:NPOIExcelHelper

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

public static class NPOIExcelHelper
{
    /// 自動偵測副檔名建立 Workbook
    public static IWorkbook CreateWorkbook(string filePath)
    {
        string ext = Path.GetExtension(filePath).ToLowerInvariant();
        return ext == ".xls" ? (IWorkbook)new HSSFWorkbook() : new XSSFWorkbook();
    }

    /// 讀取 Excel → DataTable (支援指定 Sheet、標題列索引)
    public static DataTable ImportToDataTable(string filePath, string sheetName = null, int headerRowIndex = 0)
    {
        using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
        {
            IWorkbook wb = WorkbookFactory.Create(fs); // 自動偵測 .xls/.xlsx
            ISheet sheet = string.IsNullOrEmpty(sheetName) ? wb.GetSheetAt(0) : wb.GetSheet(sheetName);
            if (sheet == null) throw new ArgumentException($"找不到工作表: {sheetName}");

            var dt = new DataTable();
            IRow headerRow = sheet.GetRow(headerRowIndex);
            if (headerRow == null) return dt;

            // 建立欄位
            for (int c = headerRow.FirstCellNum; c < headerRow.LastCellNum; c++)
            {
                string colName = headerRow.GetCell(c)?.ToString()?.Trim() ?? $"Column{c}";
                dt.Columns.Add(colName);
            }

            // 讀取資料列
            for (int r = headerRowIndex + 1; r <= sheet.LastRowNum; r++)
            {
                IRow row = sheet.GetRow(r);
                if (row == null) continue;
                bool allNull = true;
                var dr = dt.NewRow();
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    var cell = row.GetCell(c);
                    string val = cell?.ToString().Trim() ?? "";
                    dr[c] = val;
                    if (!string.IsNullOrEmpty(val)) allNull = false;
                }
                if (!allNull) dt.Rows.Add(dr);
            }
            return dt;
        }
    }

    /// DataTable → Excel 檔案 (串流模式 SXSSF,適合大數據)
    public static void ExportDataTable(DataTable dt, string filePath, string sheetName = "Sheet1", bool xlsx = true)
    {
        IWorkbook wb = xlsx ? (IWorkbook)new XSSFWorkbook() : new HSSFWorkbook();
        // 大數據量改用 SXSSFWorkbook (僅 .xlsx)
        // if (dt.Rows.Count > 50000) wb = new SXSSFWorkbook(); 

        ISheet sheet = wb.CreateSheet(sheetName);

        // 樣式:標題列粗體、背景色
        ICellStyle headerStyle = wb.CreateCellStyle();
        IFont headerFont = wb.CreateFont(); headerFont.IsBold = true; headerStyle.SetFont(headerFont);
        headerStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
        headerStyle.FillPattern = FillPattern.SolidForeground;

        // 寫入標題
        IRow hRow = sheet.CreateRow(0);
        for (int c = 0; c < dt.Columns.Count; c++)
        {
            var cell = hRow.CreateCell(c);
            cell.SetCellValue(dt.Columns[c].ColumnName);
            cell.CellStyle = headerStyle;
        }

        // 寫入資料
        for (int r = 0; r < dt.Rows.Count; r++)
        {
            IRow row = sheet.CreateRow(r + 1);
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                var cell = row.CreateCell(c);
                object val = dt.Rows[r][c];
                SetCellValue(cell, val);
            }
        }

        // 自動欄寬 (僅前 100 列取樣,避免效能問題)
        for (int c = 0; c < dt.Columns.Count; c++)
            sheet.AutoSizeColumn(c);

        Directory.CreateDirectory(Path.GetDirectoryName(filePath));
        using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            wb.Write(fs);
    }

    private static void SetCellValue(ICell cell, object value)
    {
        if (value == null || value == DBNull.Value) { cell.SetCellValue(""); return; }
        switch (value)
        {
            case DateTime dt: cell.SetCellValue(dt); break;
            case bool b: cell.SetCellValue(b); break;
            case double d: cell.SetCellValue(d); break;
            case decimal d: cell.SetCellValue((double)d); break;
            case int i: cell.SetCellValue(i); break;
            case long l: cell.SetCellValue(l); break;
            default: cell.SetCellValue(value.ToString()); break;
        }
    }
}

ASP.NET Core Controller 範例

[HttpPost("import")]
public async Task Import(IFormFile file)
{
    if (file == null || file.Length == 0) return BadRequest("無檔案");
    var tempPath = Path.GetTempFileName();
    using (var stream = File.Create(tempPath)) await file.CopyToAsync(stream);
    
    var dt = NPOIExcelHelper.ImportToDataTable(tempPath);
    // 驗證、轉換、批次寫入 DB...
    System.IO.File.Delete(tempPath);
    return Ok(new { Rows = dt.Rows.Count });
}

[HttpGet("export")]
public IActionResult Export()
{
    var dt = GetReportData(); // DataTable
    var ms = new MemoryStream();
    // 修改 ExportDataTable 接受 Stream 參數或直接寫入 ms
    NPOIExcelHelper.ExportDataTable(dt, ms, "Report", true); // 需調整多載
    ms.Position = 0;
    return File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx");
}

進階技巧

  • 公式計算:`cell.CellFormula = "SUM(A2:A100)"; wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();`
  • 資料驗證下拉選單:`DVConstraint.CreateExplicitListConstraint(new[]{"A","B","C"})` → `sheet.AddValidationData()`。
  • 凍結窗格:`sheet.CreateFreezePane(0, 1);` (凍結首列)。
  • 自訂日期格式:`cellStyle.DataFormat = wb.CreateDataFormat().GetFormat("yyyy/mm/dd");`
  • 圖片插入:`sheet.CreateDrawingPatriarch().CreatePicture(anchor, pictureIdx)`。

效能調優建議

資料量策略
< 5 萬列XSSFWorkbook 記憶體模式,簡單快速
5 萬 ~ 100 萬列SXSSFWorkbook (串流),設定 `wb.CompressTempFiles = true`
> 100 萬列分批寫入多個 Sheet/檔案、或改用 CSV/Parquet 格式
copyright © 萬盛學電腦網 all rights reserved