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 第一行索引 0
IRow 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);
}
}