DotNET6 导出PDF和Excel（续）

书接上文

以前的代码能跑就行，回头看自己都懵圈

这次依旧优化古董级代码

以下是Excel导出代码优化

/// <summary>
/// Excel导出
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="excelConfig">配置项</param>
/// <returns></returns>
public static MemoryStream ExportExcel<T>(ExcelConfig<T> excelConfig)
{
    var stream = new MemoryStream();
    // 非商业用途
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    using (ExcelPackage package = new ExcelPackage(stream))
    {
        // 添加worksheet
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(excelConfig.SheetName);
        // 单元格自动适应大小（好像没用还得手动设置列宽）
        // worksheet.Cells.Style.ShrinkToFit = true;
        for (int i = 0; i < excelConfig.ColumnNames.Count; i++)
        {
            // 设置列名
            worksheet.Cells[1, i + 1].Value = excelConfig.ColumnNames[i];
            // 设置列宽
            worksheet.Column(i + 1).Width = excelConfig.ColumnWidths[i];
        }
        // 金额格式
        // worksheet.Column(2).Style.Numberformat.Format = "￥#,##0.00";
        // 从第二行第三列到第一万行第三列被设置为下拉框
        // var unitmeasure = worksheet.DataValidations.AddListValidation(worksheet.Cells[2, 3, 10000, 3].Address);
        // unitmeasure.Formula.Values.Add("现金");
        // unitmeasure.Formula.Values.Add("刷卡");
        // 列下标
        int cellCount = 0;
        // 获得该类的Type
        Type t = excelConfig.DataList.FirstOrDefault().GetType();
        for (int i = 0; i < excelConfig.DataList.Count; i++)
        {
            // 遍历属性值
            foreach (PropertyInfo pi in t.GetProperties())
            {
                // 从第二行开始插入数据，因为第一行是列名
                var value = pi.GetValue(excelConfig.DataList[i]).ToString();
                worksheet.Cells[i + 2, ++cellCount].Value = value;
            }
            // 重置
            cellCount = 0;
        }
        // 设置是否进行锁定
        worksheet.Protection.IsProtected = true;
        // 设置密码
        worksheet.Protection.SetPassword("123456");
        // 下面是一些锁定时权限的设置
        worksheet.Protection.AllowAutoFilter = false;
        worksheet.Protection.AllowDeleteColumns = false;
        worksheet.Protection.AllowDeleteRows = false;
        worksheet.Protection.AllowEditScenarios = false;
        worksheet.Protection.AllowEditObject = false;
        worksheet.Protection.AllowFormatCells = false;
        worksheet.Protection.AllowFormatColumns = false;
        worksheet.Protection.AllowFormatRows = false;
        worksheet.Protection.AllowInsertColumns = false;
        worksheet.Protection.AllowInsertHyperlinks = false;
        worksheet.Protection.AllowInsertRows = false;
        worksheet.Protection.AllowPivotTables = false;
        worksheet.Protection.AllowSelectLockedCells = false;
        worksheet.Protection.AllowSelectUnlockedCells = false;
        worksheet.Protection.AllowSort = false;
        package.Save();
    }
    stream.Position = 0;
    return stream;
}

对于一些列的下拉框设置，后续还有优化空间

关于锁定和设置密码以及其他设置可以自行百度

Excel配置项

/// <summary>
/// Excel配置项
/// </summary>
/// <typeparam name="T"></typeparam>
public class ExcelConfig<T>
{
    /// <summary>
    /// Sheet名称
    /// </summary>
    public string SheetName { get; set; }

    /// <summary>
    /// 列名集合
    /// </summary>
    public List<string> ColumnNames { get; set; }

    /// <summary>
    /// 列宽集合
    /// </summary>
    public List<int> ColumnWidths { get; set; }

    /// <summary>
    /// 数据集
    /// </summary>
    public List<T> DataList { get; set; }
}

模拟调用

ExcelConfig<Student> studentList = new ExcelConfig<Student>();
studentList.SheetName = "学生报表";
// 正常情况从数据库取
studentList.DataList = new List<Student>();
studentList.ColumnNames = new List<string>
{
    "Id",
    "编号",
    "姓名",
    "等级",
    "专业",
    "地址",
};
// 列名集合与列宽集合数量须保持一直，为对应列的宽度
studentList.ColumnWidths = new List<int> { 10, 25, 15, 30, 15, 25 };
// 获取文件流后自行处理返回值
var steam = ExcelHelper.ExportExcel(studentList);

通过简单配置以及调用即可获取文件流

拿到文件流后即可返回给前端下载

以下是PDF导出代码优化

/// <summary>
/// PDF导出
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="pdfConfig">配置项</param>
/// <returns></returns>
public static byte[] ExportPDF<T>(PDFConfig<T> pdfConfig)
{
    // 创建一个文档对象
    Document document = new Document();
    // 创建一个 PDF 文件流
    string fileName = "测试.pdf";
    FileStream fileStream = new FileStream(fileName, FileMode.Create);
    // 实例化一个 PDF写入器
    PdfWriter.GetInstance(document, fileStream);
    document.Open();
    // 指定字体文件，IDENTITY_H：支持中文
    string fontpath = @"C:\Windows\Fonts\SIMHEI.TTF";
    BaseFont customfont = BaseFont.CreateFont(fontpath, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
    // 设置字体颜色样式
    var baseFont = new Font(customfont)
    {
        // 字体颜色
        Color = new BaseColor(0, 0, 0),
        // 字体大小
        Size = 8
    };

    // 定义字体样式
    var headerStyle = new Font(customfont)
    {
        Color = new BaseColor(0, 0, 0),
        Size = 18,
    };

    // 设置标题名称样式以及居中
    var head = new Paragraph(pdfConfig.SiteName, headerStyle);
    head.SetAlignment("Center");

    var headerStyle2 = new Font(customfont)
    {
        Color = new BaseColor(0, 0, 0),
        Size = 10,
    };
    var para = new Paragraph(string.Format("{0}", pdfConfig.TaskName), headerStyle2)
    {
        // 和报表对齐需要减少的左边距
        IndentationLeft = -30f
    };

    // 上方文字与表格相隔间距（空格不能为空，不然间距无效果）
    var placeholder = new Paragraph("   ", headerStyle2);

    PdfPTable tableRow_2 = new PdfPTable(8);
    tableRow_2.TotalWidth = 580f;
    tableRow_2.LockedWidth = true;
    tableRow_2.DefaultCell.Border = Rectangle.NO_BORDER;
    tableRow_2.WidthPercentage = 100;
    tableRow_2.DefaultCell.MinimumHeight = 80f;
    tableRow_2.SetWidths(pdfConfig.ColumnWidths);

    for (int i = 0; i < pdfConfig.ColumnNames.Count; i++)
    {
        var Row_2_Cell_i = new PdfPCell(new Paragraph(pdfConfig.ColumnNames[i], baseFont));
        Row_2_Cell_i.HorizontalAlignment = Element.ALIGN_CENTER;// 文字居中
        Row_2_Cell_i.BackgroundColor = BaseColor.LightGray;
        tableRow_2.AddCell(Row_2_Cell_i);
    }

    document.Add(head);

    // 添加一张图片到文档
    // Image image = Image.GetInstance("cute.gif");
    // document.Add(image);

    document.Add(placeholder);
    document.Add(para);
    document.Add(placeholder);
    document.Add(tableRow_2);

    // 获得该类的Type
    Type t = pdfConfig.DataList.FirstOrDefault().GetType();

    // 填充数据
    for (int i = 0; i < pdfConfig.DataList.Count; i++)
    {
        PdfPTable tableRow_3 = new PdfPTable(8);
        tableRow_3.TotalWidth = 580f;
        tableRow_3.LockedWidth = true;
        tableRow_3.DefaultCell.Border = Rectangle.NO_BORDER;
        tableRow_3.WidthPercentage = 100;
        tableRow_3.DefaultCell.MinimumHeight = 80f;
        tableRow_3.SetWidths(pdfConfig.ColumnWidths);
        // 遍历属性值
        foreach (PropertyInfo pi in t.GetProperties())
        {
            var value = pi.GetValue(pdfConfig.DataList[i]).ToString();
            var txt = new Paragraph(value, baseFont);
            var cell = new PdfPCell(txt);
            tableRow_3.AddCell(cell);
        }
        document.Add(tableRow_3);
    }

    document.Close();
    fileStream.Close();
    fileStream.Dispose();
    return GetFileStream(fileName);
}

/// <summary>
/// 文件转成字节
/// 并删除文件
/// 返回字节
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static byte[] GetFileStream(string filePath)
{
    var byteBuffer = File.ReadAllBytes(filePath);
    if (byteBuffer.Length > 0)
    {
        if (File.Exists(filePath))
        {
            File.Delete(filePath);
        }
        return byteBuffer;
    }
    return null;
}

对比之前针对数据写的代码，现在是完全抽离出来一个公共方法

页脚配置代码暂时也去除了，后续可能会添加进来

PDF配置项

/// <summary>
/// PDF配置项
/// </summary>
/// <typeparam name="T"></typeparam>
public class PDFConfig<T>
{
    /// <summary>
    /// 标题名称
    /// </summary>
    public string SiteName { get; set; }

    /// <summary>
    /// 任务名称
    /// </summary>
    public string TaskName { get; set; }

    /// <summary>
    /// 列名集合
    /// </summary>
    public List<string> ColumnNames { get; set; }

    /// <summary>
    /// 列宽集合
    /// </summary>
    public float[] ColumnWidths { get; set; }

    /// <summary>
    /// 数据集
    /// </summary>
    public List<T> DataList { get; set; }
}

模拟调用

PDFConfig<Student> studentList = new PDFConfig<Student>();
studentList.SiteName = "学生报表";
studentList.TaskName = string.Format("报表日期：{0}",DateTime.UtcNow.ToShortDateString());
// 正常情况从数据库取
studentList.DataList = new List<Student>();
studentList.ColumnNames = new List<string>
{
    "Id",
    "编号",
    "姓名",
    "等级",
    "专业",
    "地址",
};
studentList.ColumnWidths = new float[] { 50f, 120f, 60f, 150f, 95f, 95f };
byte[] file = PDFHelper.ExportPDF(studentList);
var stream = new MemoryStream(file);

通过配置项获取字节，然后通过字节生成文件流

最后Excel和PDF的文件流处理都大同小异

// PDF（pdf）
application/pdf
// Excel（xlsx）
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
// Word（docx）
application/vnd.openxmlformats-officedocument.wordprocessingml.document