526互联

epplus导出

发布时间 2023-07-25 10:24:40作者: 贾咩咩

调用方法

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

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

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


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

    }