epplus导出
调用方法
ExcelExporter.ToExcel<Output>( Path,//路径 //文件名 增加guid防止扫文件 $"报表_{Name}{DateTime.Now.ToString("yyyy-MM-dd")}_{Guid.NewGuid().ToString()}.xlsx", "报表",//标题 res.OrderBy(o => o.Name).ThenBy(o => o.No).ToList(), new string[] { "标题","标题","标题",... }, new Func<Output, object>[] { l => l.Name,//部门 l => l.TypeName,//型号 ... } );
辅助类
/// <summary>
/// 导出 /// </summary> public static class ExcelExporter { /// <summary> /// 导出根目录 /// </summary> public const string RootFile = "Report"; #region epplus /// <summary> /// 导出Excel文件 EPPLUS /// </summary> /// <returns></returns> public static string GetFileResponse<T>(string fiord, string newFile, string sheetName, IList<T> dtoList, string[] header, Func<T, object>[] propertySelectors) { try { return ExportExcelStream<T>(fiord, newFile, sheetName, dtoList, header, propertySelectors); } catch (Exception ex) { throw new UserFriendlyException(ResourceCode.State_500, ex.Message); } } private static string ExportExcelStream<T>(string fiord, string newFile, string sheetName, IList<T> dtoList, string[] header, Func<T, object>[] propertySelectors) { return CreateExcelStream(fiord, newFile, excelPackage => { var sheet = excelPackage.Workbook.Worksheets.Add(sheetName); sheet.OutLineApplyStyle = true; AddHeader(sheet, header); AddObjects(sheet, 2, dtoList, propertySelectors); }); } private static string CreateExcelStream(string fiord, string newFile, Action<ExcelPackage> creator) { if (!Directory.Exists(fiord)) { Directory.CreateDirectory(fiord); } var filePath = Path.Combine(fiord, newFile); var file = new FileInfo(filePath); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (var excelPackage = new ExcelPackage(file)) { creator(excelPackage); excelPackage.Save(); } return newFile; } private static void AddHeader(ExcelWorksheet sheet, params string[] headerTexts) { if (headerTexts.IsNullOrEmpty()) { return; } for (var i = 0; i < headerTexts.Length; i++) { AddHeader(sheet, i + 1, headerTexts[i]); } } private static void AddHeader(ExcelWorksheet sheet, int columnIndex, string headerText) { sheet.Cells[1, columnIndex].Value = headerText; sheet.Cells[1, columnIndex].Style.Font.Bold = true;
sheet.View.FreezePanes(2, 1);//锁定第一行
} private static void AddObjects<T>(ExcelWorksheet sheet, int startRowIndex, IList<T> items, params Func<T, object>[] propertySelectors) { if (items.IsNullOrEmpty() || propertySelectors.IsNullOrEmpty()) { return; } for (var i = 0; i < items.Count; i++) { sheet.Row(i + startRowIndex).CustomHeight = true; for (var j = 0; j < propertySelectors.Length; j++) { sheet.Cells[i + startRowIndex, j + 1].Value = propertySelectors[j](items[i]); //sheet.Column(j + 1).AutoFit(); } }
//自动列宽
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
} #endregion epplus /// <summary> /// 下载 /// </summary> /// <returns></returns> public static string ToExcel<T>(string rootPath, string fileName, string sheelName, List<T> list, string[] header, params Func<T, object>[] func) { var dt = DateTime.Now.ToString("yyyyMMdd"); var path = Path.Combine( Path.Combine(rootPath, RootFile), dt ); var filePath = GetFileResponse(path, fileName, sheelName, list, header, func); return Path.Combine(RootFile, dt, filePath); } }
咩咩咩