Install-Package NPOI// 實體類示例
public class UserEntity {
public string Name { get; set; }
public int Age { get; set; }
public string GenderName { get; set; }
public TranscriptsEntity TranscriptsEn { get; set; } // 子對象
}
public class TranscriptsEntity {
public int ChineseScores { get; set; }
public int MathScores { get; set; }
}
// 列頭映射字典：Key=實體屬性路徑，Value=Excel 列名
var cellHeader = new Dictionary<string, string> {
{ "Name", "姓名" },
{ "Age", "年齡" },
{ "GenderName", "性別" },
{ "TranscriptsEn.ChineseScores", "語文成績" }, // 支持子屬性「.」路徑
{ "TranscriptsEn.MathScores", "數學成績" }
};
public static List<T> ImportExcel<T>(string filePath, Dictionary<string, string> cellHeader, out StringBuilder errorMsg)
where T : new()
{
errorMsg = new StringBuilder();
var result = new List<T>();
using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) {
// 自動識別 xls/xlsx
IWorkbook workbook = WorkbookFactory.Create(fs);
ISheet sheet = workbook.GetSheetAt(0);
var keys = cellHeader.Keys.ToList();
for (int rowIdx = 1; rowIdx <= sheet.LastRowNum; rowIdx++) { // 跳過表頭行
IRow row = sheet.GetRow(rowIdx);
if (row == null) break; // 遇空行停止
T entity = new T();
string rowError = "";
for (int colIdx = 0; colIdx < keys.Count; colIdx++) {
string propPath = keys[colIdx]; // 如 "TranscriptsEn.ChineseScores"
ICell cell = row.GetCell(colIdx);
string cellVal = cell?.ToString() ?? "";
try {
// 反射賦值（支持一級子對象）
SetPropertyValue(entity, propPath, cellVal);
} catch (Exception ex) {
rowError += (rowError == "" ? $"第{rowIdx}行數據轉換異常：" : "") + cellHeader[propPath] + "列；";
}
}
if (rowError != "") errorMsg.AppendLine(rowError);
result.Add(entity);
}
}
return result;
}
// 反射工具：支持 "Parent.ChildProp" 路徑
private static void SetPropertyValue(object obj, string propPath, string value) {
if (propPath.Contains(".")) {
var parts = propPath.Split('.');
var parentProp = obj.GetType().GetProperty(parts[0]);
var childObj = parentProp.GetValue(obj);
if (childObj == null) {
childObj = Activator.CreateInstance(parentProp.PropertyType);
parentProp.SetValue(obj, childObj);
}
var childProp = childObj.GetType().GetProperty(parts[1]);
childProp.SetValue(childObj, Convert.ChangeType(value, childProp.PropertyType));
} else {
var prop = obj.GetType().GetProperty(propPath);
prop.SetValue(obj, Convert.ChangeType(value, prop.PropertyType));
}
}
public void ValidateAndSave(List<UserEntity> list, StringBuilder errorMsg) {
// 1. 必填校驗
for (int i = 0; i < list.Count; i++) {
var e = list[i];
if (string.IsNullOrWhiteSpace(e.Name)) {
errorMsg.AppendLine($"第{i+1}行：姓名不能為空");
e.IsExcelValidateOK = false;
}
}
// 2. 重複校驗（按姓名去重）
var nameGroups = list.Where(e => e.IsExcelValidateOK)
.GroupBy(e => e.Name)
.Where(g => g.Count() > 1);
foreach (var g in nameGroups) {
foreach (var e in g) {
e.IsExcelValidateOK = false;
errorMsg.AppendLine($"姓名「{g.Key}」存在重複記錄");
}
}
// 3. 通過校驗的入庫
var validList = list.Where(e => e.IsExcelValidateOK).ToList();
if (validList.Any() && errorMsg.Length == 0) {
// TODO: Repository.BulkInsert(validList);
}
}
public static string ExportExcel<T>(IList<T> data, Dictionary<string, string> cellHeader, string sheetName) {
IWorkbook workbook = new HSSFWorkbook(); // 或 new XSSFWorkbook() 生成 xlsx
ISheet sheet = workbook.CreateSheet(sheetName);
// 表頭行
IRow headerRow = sheet.CreateRow(0);
var keys = cellHeader.Keys.ToList();
for (int i = 0; i < keys.Count; i++) {
headerRow.CreateCell(i).SetCellValue(cellHeader[keys[i]]);
}
// 數據行
for (int rowIdx = 0; rowIdx < data.Count; rowIdx++) {
IRow row = sheet.CreateRow(rowIdx + 1);
T entity = data[rowIdx];
for (int colIdx = 0; colIdx < keys.Count; colIdx++) {
string propPath = keys[colIdx];
object val = GetPropertyValue(entity, propPath); // 反射取值，支持子對象
string cellVal = val?.ToString() ?? "";
// 特殊值處理：DateTime.MinValue 顯示為空
if (cellVal == "0001/1/1 0:00:00") cellVal = "";
row.CreateCell(colIdx).SetCellValue(cellVal);
}
}
// 自動調整列寬
for (int i = 0; i < keys.Count; i++) sheet.AutoSizeColumn(i);
// 保存文件
string fileName = $"{sheetName}_{DateTime.Now:yyyyMMddHHmmssfff}.xls";
string savePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "UpFiles", "ExcelFiles", fileName);
Directory.CreateDirectory(Path.GetDirectoryName(savePath));
using (var fs = new FileStream(savePath, FileMode.Create)) {
workbook.Write(fs);
}
return "/UpFiles/ExcelFiles/" + fileName; // 返回下載相對路徑
}
[HttpPost("import")]
public IActionResult Import(IFormFile file) {
if (file == null || file.Length == 0) return BadRequest("文件為空");
string tempPath = Path.GetTempFileName();
using (var fs = new FileStream(tempPath, FileMode.Create)) file.CopyTo(fs);
var header = new Dictionary<string, string> { /* ... */ };
var list = ExcelHelper.ImportExcel<UserEntity>(tempPath, header, out var errors);
ExcelHelper.ValidateAndSave(list, errors);
System.IO.File.Delete(tempPath);
return Ok(new { success = errors.Length == 0, msg = errors.ToString(), data = list });
}
[HttpGet("export")]
public IActionResult Export() {
var list = _repo.GetAllUsers(); // 獲取數據
var header = new Dictionary<string, string> { /* ... */ };
string url = ExcelHelper.ExportExcel(list, header, "用戶列表");
return Ok(new { url });
}
| 坑點 | 解決方案 |
|---|---|
| 大文件 OOM | 使用 SXSSFWorkbook (流式寫入) 或分批導出 |
| 日期格式混亂 | 單元格設置 CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd") |
| 數字存為文本 | 導入時 cell.SetCellType(CellType.String) 強制按文本讀取 |
| 中文列名亂碼 | 確保文件流讀寫編碼 UTF-8，NPOI 內部無編碼問題 |
| 合併儲存格導入 | 用 sheet.GetMergedRegions() 獲取合併區域，邏輯填充 |
NPOI 配合「屬性路徑映射字典 + 反射賦值/取值」模式，可構建通用、可配置、支持巢狀對象的 Excel 導入導出組件。核心在於：映射配置外置化、反射邏輯通用化、校驗邏輯業務化，三者分離，易維護、易擴展。
