為什麼選擇 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 // 依賴項
命名空間對照
| 格式 | Workbook | Sheet | Row/Cell | 命名空間 |
| .xls (BIFF) | HSSFWorkbook | HSSFSheet | HSSFRow/HSSFCell | NPOI.HSSF.UserModel |
| .xlsx (OOXML) | XSSFWorkbook | XSSFSheet | XSSFRow/XSSFCell | NPOI.XSSF.UserModel |
| 通用介面 | IWorkbook | ISheet | IRow/ICell | NPOI.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 格式 |