.net5下使用EPPlus导出Excel(复杂表头)

本文主要是介绍.net5下使用EPPlus导出Excel(复杂表头)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using OfficeOpenXml;
using OfficeOpenXml.Style;namespace HZCC.Common
{public class ExcelHelper{/// <summary>/// List导出excel/// </summary>/// <typeparam name="T"></typeparam>/// <param name="dataList">数据</param>/// <param name="headers">表头</param>/// <param name="hasBorlder">单元格是否有边框</param>/// <returns></returns>public static string CreateExcelFromListEx<T>(List<T> dataList, string sheetName, List<ExcelHeader> headers, bool hasBorlder = false){if (headers == null){throw new Exception("表头信息不能为空！");}string sWebRootFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "wwwroot\\Download");//如果用浏览器url下载的方式  存放excel的文件夹一定要建在网站首页的同级目录下！！！if (!Directory.Exists(sWebRootFolder)){Directory.CreateDirectory(sWebRootFolder);}string sFileName = $@"tempExcel_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";var path = Path.Combine(sWebRootFolder, sFileName);FileInfo file = new FileInfo(path);if (file.Exists){file.Delete();file = new FileInfo(path);}ExcelPackage.LicenseContext = LicenseContext.NonCommercial;ExcelPackage package = new ExcelPackage(file);//创建sheetExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);var headerRowCount = 1;//标题占的行数headers.ForEach(p =>{var rowNum = int.Parse(Regex.Replace(p.Adress, "[a-z]", "", RegexOptions.IgnoreCase));if (rowNum > headerRowCount){headerRowCount = rowNum;}});if (hasBorlder)//边框{var dataRows = dataList.Count() + headerRowCount;string modelRange = "A1:" + Regex.Replace(headers.LastOrDefault().Adress, "[0-9]", "", RegexOptions.IgnoreCase) + dataRows.ToString();var modelTable = worksheet.Cells[modelRange];modelTable.Style.Border.Top.Style = ExcelBorderStyle.Thin;modelTable.Style.Border.Left.Style = ExcelBorderStyle.Thin;modelTable.Style.Border.Right.Style = ExcelBorderStyle.Thin;modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;}worksheet.Cells[headerRowCount, 1].LoadFromCollection(dataList, true);//表头字段for (int i = 0; i < headers.Count; i++){worksheet.Cells[headers[i].Adress].Value = headers[i].Value;if (!string.IsNullOrWhiteSpace(headers[i].MergeArea)){worksheet.Cells[headers[i].MergeArea].Merge = true;}worksheet.Cells[headers[i].Adress].Style.Font.Size = 12; //字体大小worksheet.Cells[headers[i].Adress].Style.Font.Bold = true; //设置粗体worksheet.Cells[headers[i].Adress].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中对齐worksheet.Cells[headers[i].Adress].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中对齐if (headers[i].Width > 0){worksheet.Column(ColumnIndex(headers[i].Adress)).Width = headers[i].Width;}}var objs = worksheet.Cells;for (int i = 1; i <= dataList.Count + 2; i++){for (int j = 1; j <= headers.Count + 1; j++){objs[i, j].Value = objs[i, j].Value == null ? "" : objs[i, j].Value.ToString(); //null值设置为""}}package.Save();return path;//返回文件路径}/// <summary>/// 根据单元格地址计算出单元格所在列的索引/// </summary>/// <param name="reference">单元格地址，示例A1,AB2</param>/// <returns></returns>private static int ColumnIndex(string reference){int ci = 0;reference = reference.ToUpper();for (int ix = 0; ix < reference.Length && reference[ix] >= 'A'; ix++)ci = (ci * 26) + ((int)reference[ix] - 64);return ci;}}public class ExcelHeader{/// <summary>/// 单元格地址A1,B2等/// </summary>public string Adress { get; set; }/// <summary>/// 单元格值/// </summary>public string Value { get; set; }/// <summary>/// 单元格合并区域,示例A1:B2，为空表示不合并/// </summary>public string MergeArea { get; set; }/// <summary>/// 列宽度，合并列不需要指定宽度，示例：/// | A |/// |B|C|/// 表头A是单元格B和单元格C合并而来，不需要指定宽度，只指定B和C宽度即可/// </summary>public int Width { get; set; }}
}

关键代码：

worksheet.Cells[headerRowCount, 1].LoadFromCollection(dataList, true);

指定List数据从Excel什么位置开始写入，worksheet.Cells默认从Excel左上角写入数据，使用worksheet.Cells[Row, Col]指定数据写入的定位锚点。

表头使用示例：

            var headers = new List<ExcelHeader>{new ExcelHeader{ Adress="A1", Value="工号", MergeArea="A1:A2" },new ExcelHeader{ Adress="B1", Value="姓名", MergeArea="B1:B2" },new ExcelHeader{ Adress="C1", Value="公司", MergeArea="C1:C2", Width=9 },new ExcelHeader{ Adress="D1", Value="部门", MergeArea="D1:D2", Width=15 },new ExcelHeader{ Adress="E1", Value="工作日出勤", MergeArea="E1:F1" },new ExcelHeader{ Adress="E2", Value="出勤天数", Width=11},new ExcelHeader{ Adress="F2", Value="出差天数", Width=11},};

导出效果：