Epplus组件导出Excel数据透视表

原创 2016年08月30日 14:46:36

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


版权声明:本文为博主原创文章,未经博主允许不得转载。

Excel2010数据透视表---如何将日期分为年和月进行统计

面对这样的问题例如日期是这样 我们要统计成如下图的样式 具体操作如下: 我们建立好数据透视表后,单击数据透视表日期的部分,右击,点击组合项:   ...

Excel Home 数据透视表初级班(10118班)小结第三课时

论坛链接:club.excelhome.net 培训中心链接:t.excelhome.net Excel Home 数据透视表初级班(10118班)小结 第三课时 打造精美的数据透视...

Excel 技巧百例:数据透视表的排序

数据透视表怎么进行排序呢?和正常数据的排序有什么不同吗? 如上图,我们将成绩单按学生进行汇总总分,点击总分后发现 “排序和筛选” 是制灰的,也就是无法使用的,那么我们应该如果对汇总后的成绩进行排序...

excel数据透视表

数据预处理 数据格式:首先打开工作簿,注意把文本格式的数字,转化为数值型,可以大范围选择(ctrl+单击或拖动),点击前面出现的叹号,转化为数值型。将数字转化为文字: 方法一 选中该列—-...

【峰回路转】Excel技巧百例 12.经典数据透视表布局

【峰回路转】Excel技巧百例 12.经典数据透视表布局

Excel 技巧百例:经典数据透视表布局

上文中我们得到了如下的数据透视表: 1.我们将学号也拖拽到行中,放在姓名的上方 虽然既显示和学号也显示了姓名,但是他们不在同一行 2.点击行标签,右键,选择“数据透视表选项” 3.点击“显...

Excel -- 数据透视表

一. 创建数据透视表,及布局 在数据表中任意选中某个单元格 [插入]-->[数据透视表] 选择数据区域后,确定 如图所示字段面板,数据透视表有:行标签、列标签、数值...

Excel Home 数据透视表初级班(10118班)小结第四课时

论坛链接:club.excelhome.net 培训中心链接:t.excelhome.net 第一章 开篇 这么快就到了最后一课了,学这课最大的感觉是,终于明白,为什么说,数据透视表结合VBA,...

Excel 中使用数据透视表 Pivot Table

http://www.cnblogs.com/waitrabbit/archive/2010/06/29/1767702.html  "数据透视表"  英文是 Pivot Table ,直接翻译是轴...

【峰回路转】Excel技巧百例 10.数据透视表的简单使用-分类汇总

【峰回路转】Excel技巧百例 10.数据透视表的简单使用-分类汇总
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Epplus组件导出Excel数据透视表
举报原因:
原因补充:

(最多只允许输入30个字)