DotNET6 导出PDF和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;

}

/// <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);

/// <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;

}

/// <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);

// PDF（pdf）

application/pdf

// Excel（xlsx）

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

// Word（docx）

application/vnd.openxmlformats-officedocument.wordprocessingml.document