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

    }

 

posted @ 2023-07-25 10:17  贾咩咩  Views(46)  Comments(0Edit  收藏  举报