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


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

C#使用EPPlus.dll动态库在一般处理程序中实现将datatable导出到excel

一、.如何安装EPPLus 1.在Visual Studio的解决方案的引用上右键选择管理NeGet程序包 2.在管理NuGet程序包中搜索EPPlus,点击进行安装,安装后便直接添加到引用了...
  • CGS_______
  • CGS_______
  • 2017年05月23日 19:36
  • 1631

1.ASP.NET MVC使用EPPlus,导出数据到Excel中

好久没写博客了,今天特地来更新一下,今天我们要学习的是如何导出数据到Excel文件中,这里我使用的是免费开源的Epplus组件。 源代码下载:https://github.com/caofangsh...
  • dz45693
  • dz45693
  • 2016年12月10日 13:30
  • 1135

NPOI vs EPPlus 导出Excel简单对比

1.什么是NPOI  ----(来自百度百科) NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。 (一)传统操作Excel...
  • zr_wb
  • zr_wb
  • 2016年12月12日 11:13
  • 2336

学习笔记——通过EPPlus导出Excel文件

小弟刚刚走上工作岗位不久,工作中常常遇到要导出Excel的情况,但感觉两种常见的导出方式(一种是通过GridView,另一种是通过微软的COM组件)都不是很理想,前者导出的灵活度太低,后者又常常出现进...
  • gaotongzhao
  • gaotongzhao
  • 2011年09月19日 23:56
  • 3097

EPPLUS 操作Excel2007 入门实例

目前正在进行excel com访问方式改造,涉及到要操作excel并且不需要在服务器上安装office。经过长时间在网上一通搜索,找到两种评价不错的第三方程序: 1、操作Excel2003 首选...
  • gbyzzj
  • gbyzzj
  • 2013年05月18日 18:52
  • 930

EpPlus读取生成Excel帮助类+读取csv帮助类+Aspose.Cells生成Excel帮助类

大部分功能逻辑都在,少量自定义异常类和扩展方法 ,可用类似代码自己替换//EpPlus读取生成Excel帮助类+读取csv帮助类,epplus只支持开放的Excel文件格式:xlsx,不支持 xls格...
  • pukuimin1226
  • pukuimin1226
  • 2016年08月24日 14:54
  • 4554

C# EPPlus和NPOI组件导出Excel学习总结

使用EPPlus 和NPOI 导出Excel
  • Bear_861110453
  • Bear_861110453
  • 2016年10月27日 14:29
  • 1437

使用EPPLUS快速转换Excel

EPPlus 是使用Open Office XML格式(xlsx)读写Excel 2007 / 2010文件的.net开发库。 Program.cs代码: class Program { ...
  • WuLex
  • WuLex
  • 2017年06月14日 13:35
  • 702

使用EPPlus(C#)读写Excel

EPPlus 是使用Open Office XML格式(xlsx)读写Excel 2007 / 2010文件的.net开发库。EPPlus 支持:单元格范围、单元格样式(边框,颜色,填充,字体,数字,...
  • alanzyy
  • alanzyy
  • 2016年01月27日 21:04
  • 12027

在.NET中不安装Office使用EPPlus生成带图表(Chart)的Excel报表

在开发.NET应用中可能会遇到需要生成带图表(Chart)的Excel报表的需求,特别是在一些ASP.NET网站中,有时候我们并不能保证Web服务器上一定安装了Office组件,所以使用微软的Offi...
  • zhoufoxcn
  • zhoufoxcn
  • 2013年11月04日 01:03
  • 8160
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Epplus组件导出Excel数据透视表
举报原因:
原因补充:

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