Epplus组件导出Excel数据透视表

(3条消息) Epplus组件导出Excel数据透视表_北山向阳的博客-CSDN博客

 

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EPPlus.Core.Extensions;
using OfficeOpenXml;
using OfficeOpenXml.Table.PivotTable;

namespace epplusDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            List<MacroDataModel> source = new List<MacroDataModel>();
            for (int i = 0; i < 30; i++)
            {
                source.Add(new MacroDataModel() { C1 = "C1" + i, C2 = "C2" + i, C3 = i, C4 = "C4" + i, C5 = "C5" + i, C6 = "C6" + i });
            }

            ExcelPackage excelPackage = source.ToWorksheet("test").WithConfiguration(configuration => configuration.WithColumnConfiguration(x => x.AutoFit()))
                             .WithColumn(x => x.C1, "C1")
                             .WithColumn(x => x.C2, "C2")
                             .WithColumn(x => x.C3, "C3")
                             .WithColumn(x => x.C4, "C4").WithColumn(x => x.C5, "C5")
                             .WithColumn(x => x.C6, "C6")
                             .ToExcelPackage();

            ExcelWorksheet sheetSource = excelPackage.Workbook.Worksheets["Test"];

            ExcelWorksheet pivotSheet = excelPackage.Workbook.Worksheets.Add("Pivot");

            if (sheetSource.Dimension != null)
            {
                var dataRange = sheetSource.Cells[sheetSource.Dimension.Address];
                var pt = pivotSheet.PivotTables.Add(pivotSheet.Cells["A3"], dataRange, "PivotTable1");
                /*A3是指在该sheet表中插入数据透视表的位置，"PivotTable1"是指透视表的名字*/
                pt.ColumGrandTotals = true;
                pt.RowGrandTotals = true;//允许行列汇总计算
                pt.DataOnRows = false;  //将值移至列
                //pt.PageFields.Add(pt.Fields[0]);  //添加筛选器字段C1在A1
                pt.RowFields.Add(pt.Fields[0]); //添加行字段
                pt.RowFields.Add(pt.Fields[1]);  
                //pt.RowFields.Add(pt.Fields[3]);
                pt.RowFields.Add(pt.Fields[4]);

                pt.ColumnFields.Add(pt.Fields[3]);

                foreach (var field in pt.RowFields)
                {
                    field.SubTotalFunctions = eSubTotalFunctions.None;  //不允许行分类汇总
                }

                var dateField1 = pt.DataFields.Add(pt.Fields[2]);
                dateField1.Function = DataFieldFunctions.Sum;
                dateField1.Format = "#,##0.00";
                /*使数据透视表的报表形式为表格形式*/
                foreach (var field in pt.Fields)
                {
                    field.Outline = false;
                    field.Compact = false;
                    field.ShowAll = false;
                    field.SubtotalTop = false;
                }
            }
            FileStream stream = new FileStream(@"C:\Desktop\toushi.xlsx", FileMode.CreateNew);
            excelPackage.SaveAs(stream);

        }

    }

    public class MacroDataModel
    {
        public string C1 { get; set; }
        public string C2 { get; set; }
        public decimal C3 { get; set; }
        public string C4 { get; set; }
        public string C5 { get; set; }
        public string C6 { get; set; }
    }
}