epplus导出

调用方法

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

    }