ASP.NET Core NPOI 導入導出 Excel 完整實戰：模板、樣式、大數據、避坑指南

為何選擇 NPOI？

NPOI 是 Apache POI 的 .NET 移植版，**純 managed、無 Office 相依、跨平台 (Linux/Docker/Windows)、支援 .xls (HSSF) 與 .xlsx (XSSF) 、MIT 授權免費商用**。對比 EPPlus (LGPL/商業)、ClosedXML (僅 .xlsx)、Microsoft Interop (需 Office、Windows only)，NPOI 是**企業級通用首選**。

一、核心架構：IWorkbook / ISheet / IRow / ICell

// .xls (BIFF8)
IWorkbook wb = new HSSFWorkbook();

// .xlsx (OOXML)
IWorkbook wb = new XSSFWorkbook();

// 統一介面操作
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("Hello NPOI");

二、導出 Excel 完整實戰 (Controller → FileResult)

2.1 基礎導出 (對應文章範例)

[HttpGet("export")]
public IActionResult ExportUsers()
{
    var users = _userService.GetAll(); // List

    using var wb = new XSSFWorkbook();
    var sheet = wb.CreateSheet("用戶清單");

    // 樣式定義
    var headerStyle = CreateHeaderStyle(wb);
    var dataStyle = CreateDataStyle(wb);
    var dateStyle = CreateDateStyle(wb);

    // 表頭
    var headerRow = sheet.CreateRow(0);
    var headers = new[] { "編號", "姓名", "Email", "手機", "角色", "建立時間" };
    for (int i = 0; i < headers.Length; i++)
    {
        var cell = headerRow.CreateCell(i);
        cell.SetCellValue(headers[i]);
        cell.CellStyle = headerStyle;
    }

    // 資料列
    for (int i = 0; i < users.Count; i++)
    {
        var u = users[i];
        var row = sheet.CreateRow(i + 1);
        var vals = new object[] { u.Id, u.Name, u.Email, u.Phone, u.Role, u.CreatedAt };
        for (int j = 0; j < vals.Length; j++)
        {
            var cell = row.CreateCell(j);
            SetCellValue(cell, vals[j], dataStyle, dateStyle);
        }
    }

    // 自動欄寬
    for (int i = 0; i < headers.Length; i++) sheet.AutoSizeColumn(i);

    // 輸出串流
    using var ms = new MemoryStream();
    wb.Write(ms);
    ms.Position = 0;
    return File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"Users_{DateTime.Now:yyyyMMdd}.xlsx");
}

private ICellStyle CreateHeaderStyle(IWorkbook wb)
{
    var style = wb.CreateCellStyle();
    var font = wb.CreateFont();
    font.Boldweight = (short)FontBoldWeight.Bold;
    font.Color = IndexedColors.White.Index;
    style.SetFont(font);
    style.FillForegroundColor = IndexedColors.DarkBlue.Index;
    style.FillPattern = FillPattern.SolidForeground;
    style.BorderBottom = BorderStyle.Thin;
    style.Alignment = HorizontalAlignment.Center;
    return style;
}

private void SetCellValue(ICell cell, object value, ICellStyle defaultStyle, ICellStyle dateStyle)
{
    cell.CellStyle = defaultStyle;
    switch (value)
    {
        case null: cell.SetCellValue(""); break;
        case string s: cell.SetCellValue(s); break;
        case int i: cell.SetCellValue(i); break;
        case long l: cell.SetCellValue(l); break;
        case double d: cell.SetCellValue(d); break;
        case decimal m: cell.SetCellValue((double)m); break;
        case DateTime dt: cell.SetCellValue(dt); cell.CellStyle = dateStyle; break;
        case bool b: cell.SetCellValue(b); break;
        default: cell.SetCellValue(value.ToString()); break;
    }
}

2.2 大數據導出：SXSSF 串流模式 (記憶體恆定)

XSSF 會將整份工作簿載入記憶體，**百萬筆會 OOM**。使用 SXSSFWorkbook (僅 .xlsx) 實現**滾動視窗寫入磁碟**：

public IActionResult ExportHugeData()
{
    // windowSize = 保留在記憶體的行數 (預設 100)
    using var wb = new SXSSFWorkbook(windowSize: 100, compressTmpFiles: true);
    var sheet = wb.CreateSheet("大數據");

    // 寫入邏輯相同...
    for (int i = 0; i < 1_000_000; i++)
    {
        var row = sheet.CreateRow(i);
        row.CreateCell(0).SetCellValue(i);
        row.CreateCell(1).SetCellValue($"Data_{i}");
    }

    using var ms = new MemoryStream();
    wb.Write(ms); // 此時會將暫存檔合併寫入串流
    ms.Position = 0;
    return File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "HugeData.xlsx");
}

2.3 模板導出：保留複雜格式、圖表、條件格式

public IActionResult ExportWithTemplate()
{
    // 1. 讀取模板 (含格式、圖表、命名範圍)
    using var templateStream = _hostingEnv.ContentRootFileProvider.GetFileInfo("Templates/ReportTemplate.xlsx").CreateReadStream();
    using var wb = new XSSFWorkbook(templateStream);
    var sheet = wb.GetSheet("Report");

    // 2. 填充資料 (使用命名範圍或固定位置)
    var data = _reportService.GetData();
    var startRow = 5; // 模板預留表頭 5 行
    foreach (var item in data)
    {
        var row = sheet.CreateRow(startRow++);
        row.CreateCell(0).SetCellValue(item.Name);
        row.CreateCell(1).SetCellValue(item.Value);
        // 樣式自動繼承自模板對應行
    }

    // 3. 重新計算公式 (模板含 SUM 等)
    var helper = new XSSFFormulaEvaluator(wb);
    helper.EvaluateAll();

    using var ms = new MemoryStream();
    wb.Write(ms);
    return File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx");
}

三、導入 Excel 完整實戰 (驗證、錯誤收集、進度)

[HttpPost("import")]
public async Task ImportUsers(IFormFile file)
{
    if (file == null || file.Length == 0) return BadRequest("無檔案");

    using var stream = file.OpenReadStream();
    using var wb = WorkbookFactory.Create(stream); // 自動偵測 .xls/.xlsx
    var sheet = wb.GetSheetAt(0);

    var results = new List();
    var errors = new List();

    // 假設第 1 列為表頭，從第 2 列開始
    for (int rowIdx = 1; rowIdx <= sheet.LastRowNum; rowIdx++)
    {
        var row = sheet.GetRow(rowIdx);
        if (row == null) continue;

        try
        {
            var dto = new UserImportDto
            {
                Name = GetCellString(row.GetCell(0)),
                Email = GetCellString(row.GetCell(1)),
                Phone = GetCellString(row.GetCell(2)),
                Role = GetCellString(row.GetCell(3))
            };

            // 業務驗證
            var validationErrors = ValidateUser(dto);
            if (validationErrors.Any())
            {
                errors.Add(new ImportError { Row = rowIdx + 1, Errors = validationErrors });
                continue;
            }

            results.Add(await _userService.CreateAsync(dto));
        }
        catch (Exception ex)
        {
            errors.Add(new ImportError { Row = rowIdx + 1, Errors = new[] { ex.Message } });
        }
    }

    return Ok(new { SuccessCount = results.Count, Errors = errors });
}

private string GetCellString(ICell cell)
{
    if (cell == null) return null;
    return cell.CellType switch
    {
        CellType.String => cell.StringCellValue.Trim(),
        CellType.Numeric => DateUtil.IsCellDateFormatted(cell) 
            ? cell.DateCellValue.ToString("yyyy-MM-dd") 
            : cell.NumericCellValue.ToString("G"),
        CellType.Boolean => cell.BooleanCellValue.ToString(),
        CellType.Formula => cell.StringCellValue, // 公式結果文字
        _ => ""
    };
}

四、進階技巧與避坑指南

五、效能基準 (參考)

六、單元測試範例

[Fact]
public void Export_Should_Generate_Valid_Xlsx()
{
    // Arrange
    var users = new[] { new UserDto { Id = 1, Name = "Test", Email = "test@test.com" } };

    // Act
    var result = _controller.ExportUsers() as FileContentResult;

    // Assert
    Assert.NotNull(result);
    Assert.Equal("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", result.ContentType);
    
    using var ms = new MemoryStream(result.FileContents);
    using var wb = new XSSFWorkbook(ms);
    var sheet = wb.GetSheetAt(0);
    Assert.Equal("用戶清單", sheet.SheetName);
    Assert.Equal("編號", sheet.GetRow(0).GetCell(0).StringCellValue);
    Assert.Equal("Test", sheet.GetRow(1).GetCell(1).StringCellValue);
}