C# EpPlus导出Excel

下载包 <PackageReference Include="EPPlus" Version="5.6.0" />

1、公用方法

 public abstract class EpPlusExcelExporterBase : ITransientDependency
    {
        private readonly ITempFileCacheManager _tempFileCacheManager;

        protected EpPlusExcelExporterBase(ITempFileCacheManager tempFileCacheManager)
        {
            _tempFileCacheManager = tempFileCacheManager;
        }

        protected FileDto CreateExcelPackage(string fileName, Action<ExcelPackage> creator)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            var file = new FileDto(fileName, MimeTypeNames.ApplicationVndOpenxmlformatsOfficedocumentSpreadsheetmlSheet);

            using (var excelPackage = new ExcelPackage())
            {
                creator(excelPackage);
                Save(excelPackage, file);
            }

            return file;
        }

        //protected FileDto CreateDocPackage(string fileName, Action<OPCPackage> creator)
        //{
        //    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        //    var file = new FileDto(fileName, MimeTypeNames.ApplicationVndOpenxmlformatsOfficedocumentSpreadsheetmlSheet);

        //    using (var excelPackage = new OPCPackage())
        //    {
        //        creator(excelPackage);
        //        Save(excelPackage, file);
        //    }

        //    return file;
        //}

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

            }
        }

        protected void AddHeader(ExcelWorksheet sheet, int startRowIndex, params string[] headerTexts)
        {
            if (headerTexts.IsNullOrEmpty())
            {
                return;
            }

            for (var i = 0; i < headerTexts.Length; i++)
            {
                AddHeader(sheet, i + 1, headerTexts[i], startRowIndex);

            }
        }


        protected void AddHeader(ExcelWorksheet sheet, int columnIndex, string headerText)
        {
            sheet.Cells[1, columnIndex].Value = headerText;
            sheet.Cells[1, columnIndex].Style.Font.Bold = true;
        }

        protected void AddHeader(ExcelWorksheet sheet, int columnIndex, string headerText, int startRowIndex)
        {
            sheet.Cells[startRowIndex, columnIndex].Value = headerText;
            sheet.Cells[startRowIndex, columnIndex].Style.Font.Bold = true;
            sheet.Cells[startRowIndex, columnIndex].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
        }

        protected 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++)
            {
                for (var j = 0; j < propertySelectors.Length; j++)
                {
                    sheet.Cells[i + startRowIndex, j + 1].Value = propertySelectors[j](items[i]);
                    sheet.Cells[i + startRowIndex, j + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    sheet.Cells[i + startRowIndex, j + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                }
            }
        }

        protected void Save(ExcelPackage excelPackage, FileDto file)
        {
            //var excleFile = new FileInfo("D://dd.xlsx");
            //excelPackage.SaveAs(excleFile);

            _tempFileCacheManager.SetFile(file.FileToken, excelPackage.GetAsByteArray());
        }
    }
}

2.生成Excel

public class ManualFillingExportExcel : EpPlusExcelExporterBase
{
public ManualFillingExportExcel(
ITempFileCacheManager tempFileCacheManager)
: base(tempFileCacheManager)
{

}

        public FileDto HourlyTrafficVolumeEquivalentExportToFile(GetHourlyTrafficVolumeEquivalentOutput list, GetHourlyTrafficVolumeEquivalentInput input)
        {

            return CreateExcelPackage(
                $"HourlyTrafficVolumeEquivalentExport{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx",
                excelPackage =>
                {
                    var sheet = excelPackage.Workbook.Worksheets.Add("统计详情");

                    sheet.View.ShowGridLines = false;//去除边框


                    sheet.OutLineApplyStyle = true;
                    for (var i = 1; i < 26; i++)
                    {
                        sheet.Column(i).Width = 10;
                    }

                    //AddHeader(sheet)
                    //ExcelWorksheet sheet, int columnIndex, string headerText
                    AddHeader(
                        sheet,
                        6,
                        ("月份"),
                        ("1时"),
                        ("2时"),
                        ("3时"),
                        ("4时"),
                        ("5时"),
                        ("6时"),
                        ("7时"),
                        ("8时"),
                        ("9时"),
                        ("10时"),
                        ("11时"),
                        ("12时"),
                        ("13时"),
                        ("14时"),
                        ("15时"),
                        ("16时"),
                        ("17时"),
                        ("18时"),
                        ("19时"),
                        ("20时"),
                        ("21时"),
                        ("22时"),
                        ("23时"),
                        ("24时")

                    );

                    AddObjects(
                        sheet, 7, list.HourlyTrafficVolumeEquivalentDtoList,
                        _ => _.Month,
                        _ => _.One,
                        _ => _.Two,
                        _ => _.Three,
                         _ => _.Four,
                        _ => _.Five,
                        _ => _.Six,
                          _ => _.Seven,
                        _ => _.Eight,
                        _ => _.Nine,
                        _ => _.Ten,
                        _ => _.Eleven,
                          _ => _.Twelve,
                        _ => _.Thirteen,
                        _ => _.Fourteen,
                        _ => _.Fifteen,
                        _ => _.Sixteen,
                        _ => _.Seventeen,
                        _ => _.Eighteen,
                        _ => _.Nineteen,
                        _ => _.Thirteen,
                        _ => _.Twentyone,
                        _ => _.Twentytwo,
                        _ => _.Twentythree,
                        _ => _.Twentyfour
                        );


                });

        }

3.注意点

sheet.Cells[3, 1, 5].Merge = true;//合并单元格
sheet.Cells[3, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//水平居中
sheet.Cells[3, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中

 //为单元格设置字体颜色
var cell = sheet.Cells[1,1];
cell.Style.Font.Color.SetColor(Color.Red);

sheet.View.ShowGridLines = false;//去除边框
sheet.OutLineApplyStyle = true;

//表格边框设置
sheet.Cells[6, i].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));