【第22期】观点:IT 行业加班,到底有没有价值?

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


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

相关文章推荐

Excel开源组件介绍

Excel开源组件介绍 目前比较流行的读写Excel开源组件主要包括两种,分别为NPOI和EPPLUS,相较于后者前者流行时间比较早,而后者在程序设计上更人性化。 1组件简介 1.1 NPOI介...

[置顶]这些.NET开源项目你知道吗?.NET平台开源文档与报表处理组件集合(三)

在前2篇文章这些.NET开源项目你知道吗?让.NET开源来得更加猛烈些吧 和这些.NET开源项目你知道吗?让.NET开源来得更加猛烈些吧!(第二辑)中,大伙热情高涨。再次拿出自己的私货,在.NET平台...

程序员升职加薪指南!还缺一个“证”!

CSDN出品,立即查看!

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

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

Excel 技巧百例:数据透视表的简单使用-行列转换

Excel的数据透视表不仅可以进行分类汇总,而且可以轻松快捷的进行行列转换 例如: 按照上篇文章中的方法我们可以插入数据透视表,将姓名放到行中,将课程放到列中,将分数放到值中: 从上面的结果中...

Excel在统计分析中的应用—第四章—数据库统计函数与数据透视表-Part2-(数据库统计函数)

数据库统计函数包括: 1. 计数函数 2. 求和、乘积函数 3. 最值函数 4. 均值、方差、标准差函数 5. 其他数据库函数 详细说明: Category ...
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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