EPPlus数据透视表/图表 [英] EPPlus pivot tables/charts
问题描述
我已经将EPPlus for .net使用了一段时间，但仅用于简单的数据操作。
在如何使用它来创建数据透视表/图表方面是否有任何示例？
似乎支持它，因为我可以在智能感知中看到PivotTable，但不确定语法。
我只能找到饼形图/条形图在提供的示例中。
我从提姆的答案中得到了类似的解决方案。首先，我定义了一个简单的接口，用作导出方法的一部分：
公共接口IPivotTableCreator
{
void CreatePivotTable（
OfficeOpenXml.ExcelPackage pkg，//引用目标书
字符串tableName，//用于为相关项目生成名称的标签名称
字符串ivotRangeName）； //工作簿中的命名范围是指数据
}
然后我实现了一个简单的类包含变量值和过程代码的代码可以完成工作：
公共类SimplePivotTable：IPivotTableCreator
{
List< string> _GroupByColumns;
List< string> _SummaryColumns;
///< summary>
///构造函数
///< / summary>
public SimplePivotTable（string [] groupByColumns，string [] summaryColumns）
{
_GroupByColumns = new List< string>（groupByColumns）;
_SummaryColumns =新列表< string>（summaryColumns）;
}
///< summary>
///在Excel中构建简单的PivatTable的回调处理程序
/// http://stackoverflow.com/questions/11650080/epplus-pivot-tables-charts
/// < / summary>
public void CreatePivotTable（OfficeOpenXml.ExcelPackage pkg，字符串tableName，字符串ivotRangeName）
{
string pageName = Pivot- + tableName.Replace（，）;
var wsPivot = pkg.Workbook.Worksheets.Add（pageName）;
pkg.Workbook.Worksheets.MoveBefore（PageName，tableName）;
var dataRange = pkg.Workbook./*Worksheets[tableName].*/Names[pivotRangeName];
varivotTable = wsPivot.PivotTables.Add（wsPivot.Cells [ C3]，dataRange， Pivot_ + tableName.Replace（，）））；
ivotTable.ShowHeaders = true;
ivotTable.UseAutoFormatting = true;
ivotTable.ApplyWidthHeightFormats = true;
ivotTable.ShowDrill = true;
ivotTable.FirstHeaderRow = 1; //第一行的标题为
ivotTable.FirstDataCol = 1; //数据的第一个col
ivotTable.FirstDataRow = 2; // foreach的第一行数据
（_GroupByColumns中的字符串行）
{
var field = ivotTable.Fields [row];
ivotTable.RowFields.Add（field）;
field.Sort = eSortType.Ascending;
}
foreach（_SummaryColumns中的字符串列）
{
var field = ivotTable.Fields [column];
ExcelPivotTableDataField结果= ivotTable.DataFields.Add（字段）;
}
ivotTable.DataOnRows = false;
}
}
然后我创建<$ c $的实例c> SimplePivotTable 创建者类：
IPivotTableCreator ptCreator = new SimplePivotTable（
新字符串[ ] { OrganizationTitle， GroupingTitle， DetailTitle}，/ *可折叠行* /
新字符串[] { Baseline， Increase， Decrease， NetChange， CurrentCount} ）; / *摘要栏* /
我有第三类，目前介绍了大约六种不同的方法来取一个-或更多数据集（通常是List对象），然后将每个数据集转换为具有数据命名范围的数据工作表。现在，我正在调整这些导出方法，以允许我为任何/所有这些导出方法生成数据透视表。他们都这样做：
OfficeOpenXml.ExcelPackage pkg = new ExcelPackage（）;
ExportCollectionToExcel（pkg，tableName，数据集）; //创建填充有数据
的工作表//创建数据
的NamedRange ptCreator.CreatePivotTable（pkg，tableName，GetPivotRangeName（tableName））;
通过使用界面，我留出了更多机会（我认为）来生成例如用于多张纸的不同数据透视表。我的基本 SimplePivotTable 类仅用于具有特定假设的单个表，但是将配置数据放入以表名作为键的字典中并不难。 / p>
希望能帮助某人。
I've been using EPPlus for .net for a while now but only for simple data manipulation. Are there any examples somewhere on how to use it to create pivot tables/charts? It seems to support it as I can see PivotTable in the intellisense but just unsure on the syntax.
I could only find the likes of pie/bar charts in the samples provided.
I've produced a similar solution from Tim's Answer. First, I defined a simple interface that I use as part of my export methods:
public interface IPivotTableCreator
{
void CreatePivotTable(
OfficeOpenXml.ExcelPackage pkg, // reference to the destination book
string tableName, // "tab" name used to generate names for related items
string pivotRangeName); // Named range in the Workbook refers to data
}
Then I implemented a simple class that hold the variable values and procedural code to do the work:
public class SimplePivotTable : IPivotTableCreator
{
List<string> _GroupByColumns;
List<string> _SummaryColumns;
/// <summary>
/// Constructor
/// </summary>
public SimplePivotTable(string[] groupByColumns, string[] summaryColumns)
{
_GroupByColumns = new List<string>(groupByColumns);
_SummaryColumns = new List<string>(summaryColumns);
}
/// <summary>
/// Call-back handler that builds simple PivatTable in Excel
/// http://stackoverflow.com/questions/11650080/epplus-pivot-tables-charts
/// </summary>
public void CreatePivotTable(OfficeOpenXml.ExcelPackage pkg, string tableName, string pivotRangeName)
{
string pageName = "Pivot-" + tableName.Replace(" ", "");
var wsPivot = pkg.Workbook.Worksheets.Add(pageName);
pkg.Workbook.Worksheets.MoveBefore(PageName, tableName);
var dataRange = pkg.Workbook./*Worksheets[tableName].*/Names[pivotRangeName];
var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["C3"], dataRange, "Pivot_" + tableName.Replace(" ", ""));
pivotTable.ShowHeaders = true;
pivotTable.UseAutoFormatting = true;
pivotTable.ApplyWidthHeightFormats = true;
pivotTable.ShowDrill = true;
pivotTable.FirstHeaderRow = 1; // first row has headers
pivotTable.FirstDataCol = 1; // first col of data
pivotTable.FirstDataRow = 2; // first row of data
foreach (string row in _GroupByColumns)
{
var field = pivotTable.Fields[row];
pivotTable.RowFields.Add(field);
field.Sort = eSortType.Ascending;
}
foreach (string column in _SummaryColumns)
{
var field = pivotTable.Fields[column];
ExcelPivotTableDataField result = pivotTable.DataFields.Add(field);
}
pivotTable.DataOnRows = false;
}
}
Then I create an instance of my SimplePivotTable creator class:
IPivotTableCreator ptCreator = new SimplePivotTable(
new string[] { "OrganizationTitle", "GroupingTitle", "DetailTitle" }, /* collapsible rows */
new string[] { "Baseline", "Increase", "Decrease", "NetChange", "CurrentCount"}); /* summary columns */
I have a third class that currently exposes about six different methods to take one-or-more data sets (usually List objects) and turn each of the data sets into a worksheet of data with a named range for the data. Now, I'm adapting those export methods to allow me to generate Pivot Tables for any/all of those export methods. They all do something like this:
OfficeOpenXml.ExcelPackage pkg = new ExcelPackage();
ExportCollectionToExcel(pkg, tableName, dataset); // Create worksheet filled with data
// Creates a NamedRange of data
ptCreator.CreatePivotTable(pkg, tableName, GetPivotRangeName(tableName));
By using an interface, I leave open more opportunities (I think) to generate, for example, a different pivot table for multiple sheets. My basic SimplePivotTable class just used for a single table with some specific assumptions, but it wouldn't be hard to put the configuration data into a dictionary keyed to the Table names.
Hope that helps someone.
这篇关于EPPlus数据透视表/图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
