创建数据透视表，滤镜EPPLUS [英] Create Pivot Table Filters With EPPLUS
本文介绍了创建数据透视表，滤镜EPPLUS的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
EPPLUS 擅长库。你有一个想法如何创建透视表报表过滤器，行标签和值？一些简单的例子，将是极好的。
I am using EPPLUS excel library. Do you have an idea how to create pivot table with report filters, row label and values ? Some simple example will be excellent.
推荐答案
下面是一个简单的例子。需要注意的是EEPlus没有做网页过滤器的能力，所以你必须使用（按难度排序）VBA时，Excel互操作，或XML操作（我用XML下文）。你可以把这个代码放到EPPlus源码下载，简单的测试的单元测试：
Here is a simple example. Note that EEPlus does not have the ability to do page filters so you will have to use (in order of difficulty) VBA, Excel Interop, or XML manipulation (I used XML below). You can put this code into a unit test of the EPPlus source download for easy testing:
const string FORMATCURRENCY = "#,###;[Red](#,###)";
var file = new FileInfo(@"c:\temp\temp.xlsx");
if (file.Exists)
file.Delete();
var pck = new ExcelPackage(file);
var workbook = pck.Workbook;
var worksheet = workbook.Worksheets.Add("newsheet");
//The data
worksheet.Cells["A20"].Value = "Col1";
worksheet.Cells["A21"].Value = "sdf";
worksheet.Cells["A22"].Value = "wer";
worksheet.Cells["A23"].Value = "ghgh";
worksheet.Cells["A24"].Value = "sdf";
worksheet.Cells["A25"].Value = "wer";
worksheet.Cells["A26"].Value = "ghgh";
worksheet.Cells["A27"].Value = "sdf";
worksheet.Cells["A28"].Value = "wer";
worksheet.Cells["A29"].Value = "ghgh";
worksheet.Cells["B20"].Value = "Col2";
worksheet.Cells["B21"].Value = "Group A";
worksheet.Cells["B22"].Value = "Group B";
worksheet.Cells["B23"].Value = "Group A";
worksheet.Cells["B24"].Value = "Group C";
worksheet.Cells["B25"].Value = "Group A";
worksheet.Cells["B26"].Value = "Group B";
worksheet.Cells["B27"].Value = "Group C";
worksheet.Cells["B28"].Value = "Group C";
worksheet.Cells["B29"].Value = "Group A";
worksheet.Cells["C20"].Value = "Col3";
worksheet.Cells["C21"].Value = 453;
worksheet.Cells["C22"].Value = 634;
worksheet.Cells["C23"].Value = 274;
worksheet.Cells["C24"].Value = 453;
worksheet.Cells["C25"].Value = 634;
worksheet.Cells["C26"].Value = 274;
worksheet.Cells["C27"].Value = 453;
worksheet.Cells["C28"].Value = 634;
worksheet.Cells["C29"].Value = 274;
worksheet.Cells["D20"].Value = "Col4";
worksheet.Cells["D21"].Value = 686468;
worksheet.Cells["D22"].Value = 996440;
worksheet.Cells["D23"].Value = 185780;
worksheet.Cells["D24"].Value = 686468;
worksheet.Cells["D25"].Value = 996440;
worksheet.Cells["D26"].Value = 185780;
worksheet.Cells["D27"].Value = 686468;
worksheet.Cells["D28"].Value = 996440;
worksheet.Cells["D29"].Value = 185780;
//The pivot table
var pivotTable = worksheet.PivotTables.Add(worksheet.Cells["A4"], worksheet.Cells["A20:D29"], "test");
//The label row field
pivotTable.RowFields.Add(pivotTable.Fields["Col1"]);
pivotTable.DataOnRows = false;
//The data fields
var field = pivotTable.DataFields.Add(pivotTable.Fields["Col3"]);
field.Name = "Sum of Col2";
field.Function = DataFieldFunctions.Sum;
field.Format = FORMATCURRENCY;
field = pivotTable.DataFields.Add(pivotTable.Fields["Col4"]);
field.Name = "Sum of Col3";
field.Function = DataFieldFunctions.Sum;
field.Format = FORMATCURRENCY;
//The page field
pivotTable.PageFields.Add(pivotTable.Fields["Col2"]);
var xdCacheDefinition = pivotTable.CacheDefinition.CacheDefinitionXml;
var xeCacheFields = xdCacheDefinition.FirstChild["cacheFields"];
if (xeCacheFields == null)
return;
//To filter, add items to the Cache Definition via XML
var count = 0;
var assetfieldidx = -1;
foreach (XmlElement cField in xeCacheFields)
{
var att = cField.Attributes["name"];
if (att != null && att.Value == "Col2" )
{
assetfieldidx = count;
var sharedItems = cField.GetElementsByTagName("sharedItems")[0] as XmlElement;
if(sharedItems == null)
continue;
//set the collection attributes
sharedItems.RemoveAllAttributes();
att = xdCacheDefinition.CreateAttribute("count");
att.Value = "3";
sharedItems.Attributes.Append(att);
//create and add the item
var item = xdCacheDefinition.CreateElement("s", sharedItems.NamespaceURI);
att = xdCacheDefinition.CreateAttribute("v");
att.Value = "Group A";
item.Attributes.Append(att);
sharedItems.AppendChild(item);
item = xdCacheDefinition.CreateElement("s", sharedItems.NamespaceURI);
att = xdCacheDefinition.CreateAttribute("v");
att.Value = "Group B";
item.Attributes.Append(att);
sharedItems.AppendChild(item);
item = xdCacheDefinition.CreateElement("s", sharedItems.NamespaceURI);
att = xdCacheDefinition.CreateAttribute("v");
att.Value = "Group C";
item.Attributes.Append(att);
sharedItems.AppendChild(item);
break;
}
count++;
}
//Now go back to the main pivot table xml and add the cross references to complete filtering
var xdPivotTable = pivotTable.PivotTableXml;
var xdPivotFields = xdPivotTable.FirstChild["pivotFields"];
if (xdPivotFields == null)
return;
count = 0;
foreach (XmlElement pField in xdPivotFields)
{
//Find the asset type field
if (count == assetfieldidx)
{
var att = xdPivotTable.CreateAttribute("multipleItemSelectionAllowed");
att.Value = "1";
pField.Attributes.Append(att);
var items = pField.GetElementsByTagName("items")[0] as XmlElement;
items.RemoveAll();
att = xdPivotTable.CreateAttribute("count");
att.Value = "4";
items.Attributes.Append(att);
pField.AppendChild(items);
//Add the classes to the fields item collection
for (var i = 0; i < 3; i++)
{
var item = xdPivotTable.CreateElement("item", items.NamespaceURI);
att = xdPivotTable.CreateAttribute("x");
att.Value = i.ToString(CultureInfo.InvariantCulture);
item.Attributes.Append(att);
//Turn of the Cash class in the fielder
if (i == 1)
{
att = xdPivotTable.CreateAttribute("h");
att.Value = "1";
item.Attributes.Append(att);
}
items.AppendChild(item);
}
//Add the default
var defaultitem = xdPivotTable.CreateElement("item", items.NamespaceURI);
att = xdPivotTable.CreateAttribute("t");
att.Value = "default";
defaultitem.Attributes.Append(att);
items.AppendChild(defaultitem);
break;
}
count++;
}
pck.Save();
对不起，所有的编辑，但我一直在努力在这一会儿的时候我无意中发现了这个问题。我创建只是应用过滤器的扩展方法。给它的字段名（它假定有一个标题行contining列名），你要应用的过滤器，以及包含数据的工作表，否则将只是数据透视表的工作表，如果没有数据工作表中过去了。它有做基本的测试，所以你应该QA：
Sorry for all the edit but I have been working on this for a little while when I stumbled on this question. I created an extension method just for applying a filter. Give it the field name (it assumes there is a header line contining the column names), the filters you want to apply, and the worksheet containing the data or it will just the Pivot Table worksheet if no data worksheet is passed in. It have done basic testing so you should QA:
public static bool FilterField(this ExcelPivotTable pivotTable, string pageFieldName, IEnumerable<object> filters, ExcelWorksheet dataWorksheet = null)
{
//set the worksheet
var ws = dataWorksheet ?? pivotTable.WorkSheet;
//Set the cache definitions and cache fields
var xdCacheDefinition = pivotTable.CacheDefinition.CacheDefinitionXml;
var xeCacheFields = xdCacheDefinition.FirstChild["cacheFields"];
if (xeCacheFields == null)
return false;
//Go the field list in the definitions, note the field idx and valuesfor
var count = 0;
var fieldIndex = -1;
List<object> fieldValues = null;
foreach (XmlElement cField in xeCacheFields)
{
var att = cField.Attributes["name"];
if (att != null && att.Value.Equals(pageFieldName, StringComparison.OrdinalIgnoreCase))
{
//store the field data
fieldIndex = count;
var dataddress = new ExcelAddress(pivotTable.CacheDefinition.SourceRange.Address);
var valueHeader = ws
.Cells[dataddress.Start.Row, dataddress.Start.Column, dataddress.Start.Row, dataddress.End.Column]
.FirstOrDefault(cell => cell.Value.ToString().Equals(pageFieldName, StringComparison.OrdinalIgnoreCase));
if (valueHeader == null)
return false;
//Get the range minus the header row
var valueObject = valueHeader.Offset(1, 0, dataddress.End.Row - dataddress.Start.Row, 1).Value;
var values = (object[,])valueObject;
fieldValues = values
.Cast<object>()
.Distinct()
.ToList();
//kick back if the types are mixed
if (fieldValues.FirstOrDefault(v => v is string) != null && fieldValues.FirstOrDefault(v => !(v is string)) != null)
throw new NotImplementedException("Filter function does not (yet) support mixed parameter types");
//fill in the shared items for the field
var sharedItems = cField.GetElementsByTagName("sharedItems")[0] as XmlElement;
if (sharedItems == null)
continue;
//Reset the collection attributes
sharedItems.RemoveAllAttributes();
//Handle numerics - assume all or nothing
var isNumeric = fieldValues.FirstOrDefault(v => v is string) == null;
if (isNumeric)
{
att = xdCacheDefinition.CreateAttribute("containsSemiMixedTypes");
att.Value = "0";
sharedItems.Attributes.Append(att);
att = xdCacheDefinition.CreateAttribute("containsString");
att.Value = "0";
sharedItems.Attributes.Append(att);
att = xdCacheDefinition.CreateAttribute("containsNumber");
att.Value = "1";
sharedItems.Attributes.Append(att);
att = xdCacheDefinition.CreateAttribute("containsInteger");
att.Value = fieldValues.Any(v => !(v is int || v is long)) ? "0" : "1";
sharedItems.Attributes.Append(att);
}
//add the count
att = xdCacheDefinition.CreateAttribute("count");
att.Value = fieldValues.Count.ToString(CultureInfo.InvariantCulture);
sharedItems.Attributes.Append(att);
//create and add the item
foreach (var fieldvalue in fieldValues)
{
var item = xdCacheDefinition.CreateElement(isNumeric ? "n" : "s", sharedItems.NamespaceURI);
att = xdCacheDefinition.CreateAttribute("v");
att.Value = fieldvalue.ToString();
item.Attributes.Append(att);
sharedItems.AppendChild(item);
}
break;
}
count++;
}
if (fieldIndex == -1 || fieldValues == null)
return false;
//Now go back to the main pivot table xml and add the cross references to complete filtering
var xdPivotTable = pivotTable.PivotTableXml;
var xdPivotFields = xdPivotTable.FirstChild["pivotFields"];
if (xdPivotFields == null)
return false;
var filtervalues = filters.ToList();
count = 0;
foreach (XmlElement pField in xdPivotFields)
{
//Find the asset type field
if (count == fieldIndex)
{
var att = xdPivotTable.CreateAttribute("multipleItemSelectionAllowed");
att.Value = "1";
pField.Attributes.Append(att);
var items = pField.GetElementsByTagName("items")[0] as XmlElement;
if (items == null)
return false;
items.RemoveAll();
att = xdPivotTable.CreateAttribute("count");
att.Value = (fieldValues.Count + 1).ToString(CultureInfo.InvariantCulture);
items.Attributes.Append(att);
pField.AppendChild(items);
//Add the classes to the fields item collection
for (var i = 0; i < fieldValues.Count; i++)
{
var item = xdPivotTable.CreateElement("item", items.NamespaceURI);
att = xdPivotTable.CreateAttribute("x");
att.Value = i.ToString(CultureInfo.InvariantCulture);
item.Attributes.Append(att);
if (filtervalues.Contains(fieldValues[i]))
{
att = xdPivotTable.CreateAttribute("h");
att.Value = "1";
item.Attributes.Append(att);
}
items.AppendChild(item);
}
//Add the default
var defaultitem = xdPivotTable.CreateElement("item", items.NamespaceURI);
att = xdPivotTable.CreateAttribute("t");
att.Value = "default";
defaultitem.Attributes.Append(att);
items.AppendChild(defaultitem);
break;
}
count++;
}
return true;
}
要在上面的例子中使用它，你会做什么像这样的：
To use it in the above example, you would do something like this:
pivotTable.FilterField("Col2", new List<string> { "Group B" });
这篇关于创建数据透视表，滤镜EPPLUS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
