1、安装NPOI
NuGet\Install-Package NPOI -Version 2.7.3
2、Excel操作相关类
ExcelDataResource.cs ——要导出到Excel中的数据源包装对象：
public class ExcelDataResource
{/// <summary>/// Sheet名称/// </summary>public string? SheetName { get; set; }/// <summary>/// 表头所在行/// </summary>public int HeaderIndex { get; set; }/// <summary>/// 要导出到Excel表的数据源/// </summary>public List<object>? SheetDataResource { get; set; }
}
HeaderAttribute.cs ——要导出到Excel的数据源的字段对应表头注解特性：
[AttributeUsage(AttributeTargets.Property)]
public class HeaderAttribute : Attribute
{/// <summary>/// 数据行对象的属性注解，定义导出到Excel中的列表头名/// </summary>public string HeaderName { get; set; }public HeaderAttribute(string headerName) => HeaderName = headerName;
}
ExcelOperationHelper.cs ——包含以下Excel操作主要方法：
- 根据数据源生成Excel文件流或字节流；
- Excel表格宽度自适应；
- Excel生成DataTable
public static class ExcelOperationHelper
{ /// <summary>/// 导出/// </summary>public static IWorkbook DataToHSSFWorkbook(List<ExcelDataResource>? dataResources){HSSFWorkbook _Workbook = new HSSFWorkbook();if (dataResources == null && dataResources?.Count == 0){return _Workbook;}foreach (var sheetResource in dataResources){if (sheetResource.SheetDataResource != null && sheetResource.SheetDataResource.Count == 0)break;var sheetName = string.IsNullOrWhiteSpace(sheetResource.SheetName) ? $"Sheet{dataResources.IndexOf(sheetResource) + 1}" : sheetResource.SheetName;ISheet sheet = _Workbook.CreateSheet(sheetName);object obj = sheetResource.SheetDataResource[0];Type type = obj.GetType();List<PropertyInfo> propList = [.. type.GetProperties()];ICellStyle style = _Workbook.CreateCellStyle();style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;style.FillPattern = FillPattern.SolidForeground;style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;style.Alignment = HorizontalAlignment.CenterSelection;style.VerticalAlignment = VerticalAlignment.Center;IFont font = _Workbook.CreateFont();font.IsBold = true;style.SetFont(font);IRow headerRow = sheet.CreateRow(0);headerRow.Height = 100 * 4;for (int i = 0; i < propList.Count(); i++){HeaderAttribute propertyAttribute = propList[i].GetCustomAttribute<HeaderAttribute>();ICell cell = headerRow.CreateCell(i);cell.SetCellValue(propertyAttribute?.HeaderName ?? propList[i].Name);cell.CellStyle = style;}for (int i = 0; i < sheetResource.SheetDataResource.Count(); i++){IRow row = sheet.CreateRow(sheetResource.HeaderIndex + i + 1);object objInstance = sheetResource.SheetDataResource[i];for (int j = 0; j < propList.Count; j++){ICell cell = row.CreateCell(j);cell.SetCellValue((propList[j].GetValue(objInstance) ?? "").ToString());}}sheet.AutoSizeSheetColumns();}return _Workbook;}/// <summary>/// Sheet表宽度自适应/// </summary>/// <param name="sheet"></param>private static void AutoSizeSheetColumns(this ISheet sheet){IRow header = sheet.GetRow(sheet.FirstRowNum);//获取第一行for (int i = header.FirstCellNum; i < header.LastCellNum; i++){//自动调整列的宽度(不支持中文)sheet.AutoSizeColumn(i);var columnWidth = sheet.GetColumnWidth(i) / 256;for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++){IRow currentRow = sheet.GetRow(rowNum);if (currentRow.GetCell(i) != null){ICell currentCell = currentRow.GetCell(i);//int length = Encoding.Default.GetBytes(currentCell.ToString()).Length * 256 + 200;int length = (CalculateTextWidth(currentCell.ToString()) + 2) * 256;length = Math.Min(length, 256 * 256);if (columnWidth < length){columnWidth = length;}}}sheet.SetColumnWidth(i, columnWidth);} }// 字符宽度计算函数private static int CalculateTextWidth(string text){if (string.IsNullOrEmpty(text)) return 0;var chars = text.ToArray();// 中文字符按2单位计算，英文按1单位int width = chars.Length + chars.Where(c => c > 0x4E00 && c < 0x9FA5).Count(); return width;}/// <summary>/// 生成Excel的内存流-MemoryStream/// </summary>/// <param name="dataResources"></param>/// <returns></returns>public static MemoryStream ToExcelMemoryStream(this List<ExcelDataResource> dataResources){IWorkbook _Workbook = DataToHSSFWorkbook(dataResources);using MemoryStream stream = new MemoryStream();_Workbook.Write(stream, true);return stream;}/// <summary>/// 通过数据生成Excel 然后转换成byte[]/// </summary>/// <param name="dataResources"></param>/// <returns></returns>public static byte[] ToExcelByteArray(this List<ExcelDataResource> dataResources){using (var stream = dataResources.ToExcelMemoryStream()){byte[] bt = stream.ToArray();//stream.Write(bt, 0, bt.Length);return bt;}}/// <summary>/// Excel转换成DataTable /// </summary>/// <param name="hSSFWorkbook"></param>/// <returns></returns>public static List<DataTable> ExcelToDateTable(this IWorkbook hSSFWorkbook){List<DataTable> datatableList = new List<DataTable>();for (int sheetIndex = 0; sheetIndex < hSSFWorkbook.NumberOfSheets; sheetIndex++){ISheet sheet = hSSFWorkbook.GetSheetAt(sheetIndex);//获取表头 FirstRowNum 第一行索引 0IRow header = sheet.GetRow(sheet.FirstRowNum);//获取第一行if (header == null) break;DataTable dtNpoi = new DataTable();for (int i = header.FirstCellNum; i < header.LastCellNum; i++){ICell cell = header.GetCell(i);string cellValue = cell != null ? $"{cell}" : $"Column{i + 1}";if (cellValue != null){DataColumn col = new DataColumn(cellValue);dtNpoi.Columns.Add(col);}}int startRow = sheet.FirstRowNum + 1; //数据的第一行索引//数据 LastRowNum 最后一行的索引(如第九行索引为8)for (int i = startRow; i <= sheet.LastRowNum; i++){IRow row = sheet.GetRow(i);//获取第i行if (row == null) continue;DataRow dr = dtNpoi.NewRow();//遍历每行的单元格for (int j = row.FirstCellNum; j < row.LastCellNum; j++){if (row.GetCell(j) != null)dr[j] = row.GetCell(j).ToString();}dtNpoi.Rows.Add(dr);}datatableList.Add(dtNpoi);}return datatableList;}/// <summary>/// Excel文件流生成DataTable/// </summary>/// <param name="stream"></param>/// <returns></returns>public static List<DataTable> ExcelStreamToDateTable(Stream stream){IWorkbook hSSFWorkbook = WorkbookFactory.Create(stream);return hSSFWorkbook.ExcelToDateTable();}
}
3、导出Excel操作（使用案例）
Excel的数据行对象的类定义：
internal class ExportData
{/// <summary>/// 公司名称/// </summary>[Header("公司名称")]public string CompanyName { get; set; }/// <summary>/// 订单金额(元)/// </summary>[Header("订单金额(元)")]public string OrderAmount { get; set; }/// <summary>/// 订单号/// </summary>[Header("订单号")]public string TradeNo { get; set; }/// <summary>/// 下单时间/// </summary>[Header("下单时间")]public string OrderTime { get; set; }………………
}
在控制器中的定义接口方法返回要导出的Excel文件：
/// <summary>
/// 查询数据并下载成Excel文件
/// </summary>
/// <returns></returns>
[HttpPost]
public async Task<FileResult> DownloadExcelFile(QueryDto input)
{var company= input.Company;var dateRange = input.OrderDateRange;DateTime? start = dateRange?.Start == null ? null : DateTime.Parse(dateRange.Start);DateTime? end = dateRange?.End == null ? null : DateTime.Parse(dateRange.End);var list = await _aaaRepository.Select.FromQuery<BBB_Entity, CCC_Entity>(_bbbRepository.Select, _cccRepository.Select).InnerJoin((a, b, c) => a.BBBId == b.Id).InnerJoin((a, b, c) => a.CCCId == c.Id).WhereIf(!company.IsNull(), (a, b, c) => a.Company.Contains(company, StringComparison.OrdinalIgnoreCase)) .WhereIf(dateRange != null && dateRange?.Start != null && dateRange?.End != null, (a, b, c) => b.CreatedTime.Value.Date >= start && b.CreatedTime.Value.Date <= end)//.WhereDynamicFilter(input.DynamicFilter).OrderByDescending((a, b, c) => b.CreatedTime).ToListAsync((a, b, c) => new ExportData{CompanyName = c.Name,OrderAmount = b.PayAmount.HasValue ? (Convert.ToSingle(b.PayAmount.Value) / 100.0D).ToString().TrimEnd('0') : null,TradeNo = b.TradeNo,OrderTime = b.CreatedTime.HasValue ? b.CreatedTime.Value.ToString("yyyy-MM-dd HH:mm:ss") : null,……，……，……});List<ExcelDataResource> dataSourceList = [new ExcelDataResource { SheetDataResource = list.ToList<object>() , SheetName = "Sheet名" }];var fileBytes = dataSourceList.ToExcelByteArray();return new FileContentResult(fileBytes, "application/vnd.ms-excel") { FileDownloadName="导出结果文件名.xlsx"};
}
前端调用下载文件：
const onDownload = async (data:EmptyObjectType) => {const res = await new SampleApi().downloadExcelFile(data, { format: 'blob', returnResponse: true });if (!!res) {var url = URL.createObjectURL(res.data as Blob);let link = document.createElement("a");link.setAttribute("href", url);link.setAttribute("download", 导出结果文件_" + dayjs().format('YYYYMMDDHHmmss') + ".xlsx");link.setAttribute("target", "_blank");link.setAttribute("display", "none;");document.body.appendChild(link);link.click();URL.revokeObjectURL(url)document.body.removeChild(link);}
}
