以前的代码能跑就行,回头看自己都懵圈
这次依旧优化古董级代码
以下是Excel导出代码优化
| |
| |
| |
| |
| |
| |
| public static MemoryStream ExportExcel<T>(ExcelConfig<T> excelConfig) |
| { |
| var stream = new MemoryStream(); |
| |
| ExcelPackage.LicenseContext = LicenseContext.NonCommercial; |
| using (ExcelPackage package = new ExcelPackage(stream)) |
| { |
| |
| ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(excelConfig.SheetName); |
| |
| |
| 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]; |
| } |
| |
| |
| |
| |
| |
| |
| |
| int cellCount = 0; |
| |
| 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配置项
| |
| |
| |
| |
| public class ExcelConfig<T> |
| { |
| |
| |
| |
| public string SheetName { get; set; } |
| |
| |
| |
| |
| public List<string> ColumnNames { get; set; } |
| |
| |
| |
| |
| public List<int> ColumnWidths { get; set; } |
| |
| |
| |
| |
| 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导出代码优化
| |
| |
| |
| |
| |
| |
| public static byte[] ExportPDF<T>(PDFConfig<T> pdfConfig) |
| { |
| |
| Document document = new Document(); |
| |
| string fileName = "测试.pdf"; |
| FileStream fileStream = new FileStream(fileName, FileMode.Create); |
| |
| PdfWriter.GetInstance(document, fileStream); |
| document.Open(); |
| |
| 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); |
| |
| |
| |
| |
| |
| document.Add(placeholder); |
| document.Add(para); |
| document.Add(placeholder); |
| document.Add(tableRow_2); |
| |
| |
| 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); |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| 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配置项
| |
| |
| |
| |
| public class PDFConfig<T> |
| { |
| |
| |
| |
| public string SiteName { get; set; } |
| |
| |
| |
| |
| public string TaskName { get; set; } |
| |
| |
| |
| |
| public List<string> ColumnNames { get; set; } |
| |
| |
| |
| |
| public float[] ColumnWidths { get; set; } |
| |
| |
| |
| |
| 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的文件流处理都大同小异
| |
| application/pdf |
| |
| application/vnd.openxmlformats-officedocument.spreadsheetml.sheet |
| |
| application/vnd.openxmlformats-officedocument.wordprocessingml.document |