Epplus组件导出Excel数据透视表

EPPlus-Create advanced Excel spreadsheets on the server
这是官网地址。有需要可以去看看。

利用Epplus组件，导出数据到Excel文件中，并生成透视表。

引用

using OfficeOpenXml;
using OfficeOpenXml.Table.PivotTable;

先定义好报表中各种字段的名称，之后直接使用。

//Sheet名
private const string Sheet1 = "Data Report";
private const string ReportSheet = "Pivot Report";

//列名  如果要很据此数据设计数据透视表，列名要不同。
private const string C1 = "C1";
private const string C2 = "C2";
private const string C3 = "C3";
private const string C4 = "C4";
private const string C5 = "C5";
private const string C6 = "C6";

获取数据源，先导出sheet1。 modelList为数据

private ExcelPackage _excelPackage;
ExcelWorksheet sheet1 = _excelPackage.Workbook.Worksheets.Add(Sheet1);

/*在Sheet1中第一行添加列名*/
sheet1.Cells[1, 1].Value = C1;
sheet1.Cells[1, 2].Value = C2;
sheet1.Cells[1, 3].Value = C3;
sheet1.Cells[1, 4].Value = C4;
sheet1.Cells[1, 5].Value = C5;
sheet1.Cells[1, 6].Value = C6;

/*插入数据*/
for (int i = 0; i < modelList.Count(); i++)
{
    sheet1.Cells[2 + i, 1].Value = modelList[i].C1;
    sheet1.Cells[2 + i, 2].Value = modelList[i].C2;
    sheet1.Column(3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
    sheet1.Column(3).Style.Numberformat.Format = "#,##0.00"; 
    /*数据字段的格式，若要在透视表中参与计算，不能以字符串格式显示*/
    sheet1.Cells[2 + i, 3].Value = modelList[i].C3;
    sheet1.Column(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
    sheet1.Column(4).Style.Numberformat.Format = "#,##0.00";
    sheet1.Cells[2 + i, 4].Value = modelList[i].C4;
    sheet1.Cells[2 + i, 5].Value = modelList[i].C5;
    sheet1.Cells[2 + i, 6].Value = modelList[i].C6;
}

此时，源数据已导出，再生成透视表。

ExcelWorksheet pivotSheet = _excelPackage.Workbook.Worksheets.Add(ReportSheet );

if (sheet1.Dimension != null)
{ 
    var dataRange = sheet1.Cells[sheet1.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[1]);   //添加行字段
    pt.RowFields.Add(pt.Fields[2]);
    pt.RowFields.Add(pt.Fields[5]);
    pt.RowFields.Add(pt.Fields[6]);
    foreach (var field in pt.RowFields)
    {
        field.SubTotalFunctions = eSubTotalFunctions.None;  //不允许行分类汇总
    }
    var dateField1 = pt.DataFields.Add(pt.Fields[3]);  
    dateField1.Function = DataFieldFunctions.Sum;
    dateField1.Format = "#,##0.00";
    var dateField2 = pt.DataFields.Add(pt.Fields[4]);  
    dateField2.Function = DataFieldFunctions.Sum;
    dateField2.Format = "#,##0.00";
    /*使数据透视表的报表形式为表格形式*/
    foreach (var field in pt.Fields)
    { 
        field.Outline = false;
        field.Compact = false;
        field.ShowAll = false;
        field.SubtotalTop = false;
    }
}

在设计透视表时，筛选器、行、列、数值字段可按实际要求设计.对于这种字段较少的表，

var pt =pivotSheet.PivotTables.Add(pivotSheet.Cells["A3"], dataSheet.Cells["A1:F" + rows + ""], "PivotTable1");

其中row为sheet1 中的行数。

//可隐藏数据源表

if (_excelPackage.Workbook.Worksheets[Sheet1 ] != null)
{
     _excelPackage.Workbook.Worksheets[Sheet1 ].Hidden = eWorkSheetHidden.VeryHidden;
}

//在透视表中写入VBA

_excelPackage.Workbook.CreateVBAProject();
var sb = new StringBuilder();
sb.AppendLine("Private Sub Workbook_Open()");
sb.AppendLine("ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow");
sb.AppendLine("End Sub");
_excelPackage.Workbook.CodeModule.Code = sb.ToString();

其他

public ExcelExport(FileInfo fileInfo)
{
    _excelPackage = new ExcelPackage(fileInfo);
    while(_excelPackage.Workbook.Worksheets.Count > 0)
    {
        _excelPackage.Workbook.Worksheets.Delete(1);
    }
}

public void Dispose()
{
    _excelPackage.Save();
    _excelPackage.Dispose();
}

sheet1.Cells.Style.Font.Name = "Arial";
sheet1.Cells.Style.Font.Size = 9;
for (int j = 0; j < 6; j++)
{
    sheet1.Column(j + 1).Width = 15;
}

sheet1.Row(row).Style.Font.Bold = true;

pt.GrandTotalCaption = "總計";

_excelPackage.Workbook.Worksheets.MoveBefore(Sheet1, Sheet2);

---