internal ExcelPivotCacheDefinition(XmlNamespaceManager ns, ExcelPivotTable pivotTable) :
base(ns, null)
{
foreach (var r in pivotTable.Part.GetRelationshipsByType(ExcelPackage.schemaRelationships + "/pivotCacheDefinition"))
{
Relationship = r;
}
CacheDefinitionUri = UriHelper.ResolvePartUri(Relationship.SourceUri, Relationship.TargetUri);
var pck = pivotTable.WorkSheet._package.Package;
Part = pck.GetPart(CacheDefinitionUri);
CacheDefinitionXml = new XmlDocument();
LoadXmlSafe(CacheDefinitionXml, Part.GetStream());
TopNode = CacheDefinitionXml.DocumentElement;
PivotTable = pivotTable;
if (CacheSource == eSourceType.Worksheet)
{
var worksheetName = GetXmlNodeString(_sourceWorksheetPath);
if (pivotTable.WorkSheet.Workbook.Worksheets.Any(t => t.Name == worksheetName))
{
_sourceRange = pivotTable.WorkSheet.Workbook.Worksheets[worksheetName].Cells[GetXmlNodeString(_sourceAddressPath)];
}
}
}
//internal ExcelBarChart(ExcelDrawings drawings, XmlNode node) :
// base(drawings, node/*, 1*/)
//{
// SetChartNodeText("");
//}
//internal ExcelBarChart(ExcelDrawings drawings, XmlNode node, eChartType type) :
// base(drawings, node, type)
//{
// SetChartNodeText("");
// SetTypeProperties(drawings, type);
//}
internal ExcelBarChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource)
: base(drawings, node, type, topChart, PivotTableSource)
{
SetChartNodeText("");
SetTypeProperties(drawings, type);
}
internal ExcelBubbleChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource)
: base(drawings, node, type, topChart, PivotTableSource)
{
ShowNegativeBubbles = false;
BubbleScale = 100;
_chartSeries = new ExcelBubbleChartSeries(this, drawings.NameSpaceManager, _chartNode, PivotTableSource!=null);
//SetTypeProperties();
}
internal ExcelPieChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
}
private List <Tuple <int, int> > ConvertGroupingTuples(List <Tuple <int, int> > tupleList, ExcelPivotTable pivotTable)
{
if (tupleList == null || pivotTable == null)
{
return(tupleList);
}
var convertedList = new List <Tuple <int, int> >();
foreach (var tuple in tupleList)
{
CacheFieldNode cacheField = tuple.Item1 == -2 ? null : pivotTable.CacheDefinition.CacheFields[tuple.Item1];
if (cacheField != null && cacheField.IsDateGrouping)
{
int pivotFieldItemValue = pivotTable.Fields[tuple.Item1].Items[tuple.Item2].X;
convertedList.Add(new Tuple <int, int>(tuple.Item1, pivotFieldItemValue));
}
else
{
convertedList.Add(tuple);
}
}
return(convertedList);
}
internal ExcelPivotTableField(XmlNamespaceManager ns, XmlNode topNode,ExcelPivotTable table, int index, int baseIndex)
: base(ns, topNode)
{
Index = index;
BaseIndex = baseIndex;
_table = table;
}
internal ExcelChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource)
: base(drawings, node, "xdr:graphicFrame/xdr:nvGraphicFramePr/xdr:cNvPr/@name")
{
ChartType = type;
CreateNewChart(drawings, type, topChart);
Init(drawings, _chartNode);
_chartSeries = new ExcelChartSeries(this, drawings.NameSpaceManager, _chartNode, PivotTableSource!=null);
if (PivotTableSource != null) SetPivotSource(PivotTableSource);
SetTypeProperties();
if (topChart == null)
LoadAxis();
else
{
_axis = topChart.Axis;
if (_axis.Length > 0)
{
XAxis = _axis[0];
YAxis = _axis[1];
}
}
}
internal ExcelPivotCacheDefinition(XmlNamespaceManager ns, ExcelPivotTable pivotTable, ExcelRangeBase sourceAddress, int tblId)
: base(ns, null)
{
PivotTable = pivotTable;
var pck = pivotTable.WorkSheet.xlPackage.Package;
//CacheDefinition
CacheDefinitionXml = new XmlDocument();
CacheDefinitionXml.LoadXml(GetStartXml(sourceAddress));
CacheDefinitionUri=new Uri(string.Format("/xl/pivotCache/pivotCacheDefinition{0}.xml", tblId), UriKind.Relative);
Part = pck.CreatePart(CacheDefinitionUri, ExcelPackage.schemaPivotCacheDefinition);
TopNode = CacheDefinitionXml.DocumentElement;
//CacheRecord. Create an empty one.
CacheRecordUri = new Uri(string.Format("/xl/pivotCache/pivotCacheRecords{0}.xml", tblId), UriKind.Relative);
var cacheRecord = new XmlDocument();
cacheRecord.LoadXml("<pivotCacheRecords xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" count=\"0\" />");
var recPart = pck.CreatePart(CacheRecordUri, ExcelPackage.schemaPivotCacheRecords);
cacheRecord.Save(recPart.GetStream());
RecordRelationship = Part.CreateRelationship(PackUriHelper.ResolvePartUri(CacheDefinitionUri, CacheRecordUri), TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotCacheRecords");
RecordRelationshipID = RecordRelationship.Id;
CacheDefinitionXml.Save(Part.GetStream());
}
internal ExcelScatterChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource)
: base(drawings, node, type, topChart, PivotTableSource)
{
SetTypeProperties();
}
internal ExcelPivotTableDataFieldCollection(ExcelPivotTable table) :
base(table)
{
}
internal ExcelPivotTableRowColumnFieldCollection(ExcelPivotTable table, string topNode) :
base(table)
{
_topNode = topNode;
}
/// <summary>
/// Creates an instance of a <see cref="ExcelPageFieldCollection"/>.
/// </summary>
/// <param name="namespaceManager">The namespace manager.</param>
/// <param name="node">The top xml node.</param>
/// <param name="table">The existing pivot table.</param>
public ExcelPageFieldCollection(XmlNamespaceManager namespaceManager, XmlNode node, ExcelPivotTable table)
: base(namespaceManager, node, table)
{
}
/// <summary>
/// Creates an instance of a <see cref="ExcelPivotTableFieldCollection"/>.
/// </summary>
/// <param name="namespaceManager">The namespace manager.</param>
/// <param name="topNode">The xml node.</param>
/// <param name="table">The existing pivot table.</param>
/// <param name="field">The <see cref="ExcelPivotTableField"/> of this collection.</param>
public ExcelPivotTableFieldItemCollection(XmlNamespaceManager namespaceManager, XmlNode topNode, ExcelPivotTable table, ExcelPivotTableField field) : base(namespaceManager, topNode, table)
{
if (field == null)
{
throw new ArgumentNullException(nameof(field));
}
this.Field = field;
}
private bool FindGroupingRecordValueAndTupleMatch(CacheFieldNode cacheField, CacheRecordNode record, Tuple <int, int> tuple, ExcelPivotTable pivotTable)
{
if (cacheField.IsDateGrouping)
{
// Find record indices for date groupings fields.
var recordIndices = this.DateGroupingRecordValueTupleMatch(cacheField, tuple.Item2);
// If the record value is in the list, then the record value and tuple are a match.
int index = tuple.Item1 < record.Items.Count ? tuple.Item1 : cacheField.FieldGroup.BaseField;
var itemValue = record.Items[index].Value;
if (recordIndices.All(i => i != int.Parse(itemValue)))
{
return(false);
}
}
else
{
// Use discrete grouping property collection to determine match.
int baseIndex = cacheField.FieldGroup.BaseField;
// Get the pivot field item's x value and the record item's v value.
int pivotFieldValue = pivotTable.Fields[tuple.Item1].Items[tuple.Item2].X;
var recordValue = int.Parse(record.Items[baseIndex].Value);
var fieldGroup = cacheField.FieldGroup;
// Get the shared item string the pivot field item and record item is pointing to.
var pivotFieldPtrValue = fieldGroup.GroupItems[pivotFieldValue].Value;
var recordDiscretePtrValue = int.Parse(fieldGroup.DiscreteGroupingProperties[recordValue].Value);
var recordPtrValue = fieldGroup.GroupItems[recordDiscretePtrValue].Value;
// Check if the pivot field item and record item is pointing to the same shared string.
if (!pivotFieldPtrValue.IsEquivalentTo(recordPtrValue))
{
return(false);
}
}
return(true);
}
private bool FindCacheRecordValueAndTupleValueMatch(List <Tuple <int, int> > list, CacheRecordNode record, ExcelPivotTable pivotTable)
{
foreach (var tuple in list)
{
// Ignore data field tuples, group pivot field tuples and custom field subtotal settings.
if (tuple.Item1 == -2 || tuple.Item2 == 1048832)
{
continue;
}
var cacheField = this.CacheDefinition.CacheFields[tuple.Item1];
if (cacheField.IsGroupField)
{
bool groupMatch = this.FindGroupingRecordValueAndTupleMatch(cacheField, record, tuple, pivotTable);
if (!groupMatch)
{
return(false);
}
}
else
{
var sharedItems = this.CacheDefinition.CacheFields[tuple.Item1].SharedItems;
int recordValue = int.Parse(record.Items[tuple.Item1].Value);
int pivotFieldValue = pivotTable.Fields[tuple.Item1].Items[tuple.Item2].X;
if (pivotTable.HasFilters)
{
foreach (var filter in pivotTable.Filters)
{
int filterFieldIndex = filter.Field;
int recordReferenceIndex = int.Parse(record.Items[filterFieldIndex].Value);
string recordReferenceString = this.CacheDefinition.CacheFields[filterFieldIndex].SharedItems[recordReferenceIndex].Value;
bool isNumeric = this.CacheDefinition.CacheFields[filterFieldIndex].SharedItems[recordReferenceIndex].Type == PivotCacheRecordType.n;
bool isMatch = filter.MatchesFilterCriteriaResult(recordReferenceString, isNumeric);
if (!isMatch)
{
return(false);
}
}
}
if (!sharedItems[recordValue].Value.IsEquivalentTo(sharedItems[pivotFieldValue].Value))
{
return(false);
}
}
}
return(true);
}
internal void AddPivotSerie(ExcelPivotTable pivotTableSource)
{
var r=pivotTableSource.WorkSheet.Cells[pivotTableSource.Address.Address];
_isPivot = true;
AddSeries(r.Offset(0, 1, r._toRow - r._fromRow + 1, 1).FullAddressAbsolute, r.Offset(0, 0, r._toRow - r._fromRow + 1, 1).FullAddressAbsolute);
}
internal ExcelPivotTableFieldCollectionBase(ExcelPivotTable table)
{
_table = table;
}
internal ExcelDoughnutChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource) :
base(drawings, node, type, topChart, PivotTableSource)
{
//SetPaths();
}
internal ExcelPivotTableFieldCollection(ExcelPivotTable table, string topNode) :
base(table)
{
}
internal void SetPivotSource(ExcelPivotTable pivotTableSource)
{
PivotTableSource = pivotTableSource;
XmlElement chart = ChartXml.SelectSingleNode("c:chartSpace/c:chart", NameSpaceManager) as XmlElement;
var pivotSource = ChartXml.CreateElement("pivotSource", ExcelPackage.schemaChart);
chart.ParentNode.InsertBefore(pivotSource, chart);
pivotSource.InnerXml = string.Format("<c:name>[]{0}!{1}</c:name><c:fmtId val=\"0\"/>", PivotTableSource.WorkSheet.Name, pivotTableSource.Name);
var fmts = ChartXml.CreateElement("pivotFmts", ExcelPackage.schemaChart);
chart.PrependChild(fmts);
fmts.InnerXml = "<c:pivotFmt><c:idx val=\"0\"/><c:marker><c:symbol val=\"none\"/></c:marker></c:pivotFmt>";
Series.AddPivotSerie(pivotTableSource);
}
/// <summary>
/// Add a calculated field to EPPlus pivot table.
/// </summary>
/// <param name="pivotTable"></param>
/// <param name="name">Name for the created data field</param>
/// <param name="formula">Formula</param>
/// <param name="numFmtId">Id for number format
///
/// Build in ID's
///
/// 0 General
/// 1 0
/// 2 0.00
/// 3 #,##0
/// 4 #,##0.00
/// 9 0%
/// 10 0.00%
/// 11 0.00E+00
/// 12 # ?/?
/// 13 # ??/??
/// 14 mm-dd-yy
/// 15 d-mmm-yy
/// 16 d-mmm
/// 17 mmm-yy
/// 18 h:mm AM/PM
/// 19 h:mm:ss AM/PM
/// 20 h:mm
/// 21 h:mm:ss
/// 22 m/d/yy h:mm
/// 37 #,##0 ;(#,##0)
/// 38 #,##0 ;[Red](#,##0)
/// 39 #,##0.00;(#,##0.00)
/// 40 #,##0.00;[Red](#,##0.00)
/// 45 mm:ss
/// 46 [h]:mm:ss
/// 47 mmss.0
/// 48 ##0.0E+0
/// 49 @
/// </param>
/// <param name="index">Position of dataField. Zero based.</param>
/// <param name="caption">Caption if different from name.</param>
public static void AddCalculatedField(this OfficeOpenXml.Table.PivotTable.ExcelPivotTable pivotTable, string name, string formula, int numFmtId = 10, int?index = 0, string caption = null)
{
if (!pivotTable.WorkSheet.Workbook.Styles.NumberFormats.Where(nf => nf.NumFmtId == numFmtId).Any())
{
throw new ArgumentOutOfRangeException("Not a valid numFmtId.");
}
//First, add the calculated field cacheFields element as a childr of the cacheFields element in the pivotCacheDefinition1.xml
XmlElement cacheFieldsElement = pivotTable.CacheDefinition.CacheDefinitionXml.GetElementsByTagName("cacheFields")[0] as XmlElement;
//Add the cacheField element and take note of the index
XmlAttribute cacheFieldsCountAttribute = cacheFieldsElement.Attributes["count"];
int count = Convert.ToInt32(cacheFieldsCountAttribute.Value);
cacheFieldsElement.InnerXml += String.Format("<cacheField name=\"{0}\" numFmtId=\"0\" formula=\"{1}\" databaseField=\"0\"/>\n", name, formula);
int cacheFieldIndex = ++count;
//update cachefields count attribute
cacheFieldsCountAttribute.Value = count.ToString();
//Next, update pivotTable1.xml and insert pivotField element as a child of the pivotFields element
XmlElement pivotFieldsElement = pivotTable.PivotTableXml.GetElementsByTagName("pivotFields")[0] as XmlElement;
XmlAttribute pivotFieldsCountAttribute = pivotFieldsElement.Attributes["count"];
pivotFieldsElement.InnerXml += "<pivotField dataField=\"1\" compact=\"0\" outline=\"0\" subtotalTop=\"0\" dragToRow=\"0\" dragToCol=\"0\" dragToPage=\"0\" showAll=\"0\" includeNewItemsInFilter=\"1\" defaultSubtotal=\"0\"/> \n";
//update pivotFields count attribute
pivotFieldsCountAttribute.Value = (int.Parse(pivotFieldsCountAttribute.Value) + 1).ToString();
//Also in pivotTable1.xml, insert the <dataField> to the correct position, the fld here points to cacheField index
XmlElement dataFields = pivotTable.PivotTableXml.GetElementsByTagName("dataFields")[0] as XmlElement;
//Create the dataField element with the attributes
XmlElement dataField = pivotTable.PivotTableXml.CreateElement("dataField", pivotTable.PivotTableXml.DocumentElement.NamespaceURI);
dataField.RemoveAllAttributes();
XmlAttribute nameAttrib = pivotTable.PivotTableXml.CreateAttribute("name");
//cacheField cannot have same name attribute as dataField
if (caption == null || caption == name)
{
nameAttrib.Value = " " + name;
}
else
{
nameAttrib.Value = caption;
}
dataField.Attributes.Append(nameAttrib);
XmlAttribute fldAttrib = pivotTable.PivotTableXml.CreateAttribute("fld");
fldAttrib.Value = (cacheFieldIndex - 1).ToString();
dataField.Attributes.Append(fldAttrib);
XmlAttribute baseFieldAttrib = pivotTable.PivotTableXml.CreateAttribute("baseField");
baseFieldAttrib.Value = "0";
dataField.Attributes.Append(baseFieldAttrib);
XmlAttribute baseItemAttrib = pivotTable.PivotTableXml.CreateAttribute("baseItem");
baseItemAttrib.Value = "0";
dataField.Attributes.Append(baseItemAttrib);
XmlAttribute numFmtIdAttrib = pivotTable.PivotTableXml.CreateAttribute("numFmtId");
numFmtIdAttrib.Value = numFmtId.ToString();
dataField.Attributes.Append(numFmtIdAttrib);
//Insert dataField element to the correct position.
if (index <= 0)
{
dataFields.PrependChild(dataField);
}
else if (index >= dataFields.ChildNodes.Count)
{
dataFields.AppendChild(dataField);
}
else
{
XmlNode insertBeforeThis = dataFields.ChildNodes.Item(index.Value);
if (insertBeforeThis != null)
{
dataFields.InsertBefore(dataField, insertBeforeThis);
}
else
{
dataFields.AppendChild(dataField);
}
}
}
internal static ExcelChart GetNewChart(ExcelDrawings drawings, XmlNode drawNode, eChartType chartType, ExcelChart topChart, ExcelPivotTable PivotTableSource)
{
switch(chartType)
{
case eChartType.Pie:
case eChartType.PieExploded:
case eChartType.Pie3D:
case eChartType.PieExploded3D:
return new ExcelPieChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.BarOfPie:
case eChartType.PieOfPie:
return new ExcelOfPieChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.Doughnut:
case eChartType.DoughnutExploded:
return new ExcelDoughnutChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.BarClustered:
case eChartType.BarStacked:
case eChartType.BarStacked100:
case eChartType.BarClustered3D:
case eChartType.BarStacked3D:
case eChartType.BarStacked1003D:
case eChartType.ConeBarClustered:
case eChartType.ConeBarStacked:
case eChartType.ConeBarStacked100:
case eChartType.CylinderBarClustered:
case eChartType.CylinderBarStacked:
case eChartType.CylinderBarStacked100:
case eChartType.PyramidBarClustered:
case eChartType.PyramidBarStacked:
case eChartType.PyramidBarStacked100:
case eChartType.ColumnClustered:
case eChartType.ColumnStacked:
case eChartType.ColumnStacked100:
case eChartType.Column3D:
case eChartType.ColumnClustered3D:
case eChartType.ColumnStacked3D:
case eChartType.ColumnStacked1003D:
case eChartType.ConeCol:
case eChartType.ConeColClustered:
case eChartType.ConeColStacked:
case eChartType.ConeColStacked100:
case eChartType.CylinderCol:
case eChartType.CylinderColClustered:
case eChartType.CylinderColStacked:
case eChartType.CylinderColStacked100:
case eChartType.PyramidCol:
case eChartType.PyramidColClustered:
case eChartType.PyramidColStacked:
case eChartType.PyramidColStacked100:
return new ExcelBarChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.XYScatter:
case eChartType.XYScatterLines:
case eChartType.XYScatterLinesNoMarkers:
case eChartType.XYScatterSmooth:
case eChartType.XYScatterSmoothNoMarkers:
return new ExcelScatterChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.Line:
case eChartType.Line3D:
case eChartType.LineMarkers:
case eChartType.LineMarkersStacked:
case eChartType.LineMarkersStacked100:
case eChartType.LineStacked:
case eChartType.LineStacked100:
return new ExcelLineChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.Bubble:
case eChartType.Bubble3DEffect:
return new ExcelBubbleChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.Radar:
case eChartType.RadarFilled:
case eChartType.RadarMarkers:
return new ExcelRadarChart(drawings, drawNode, chartType, topChart, PivotTableSource);
case eChartType.Surface:
case eChartType.SurfaceTopView:
case eChartType.SurfaceTopViewWireframe:
case eChartType.SurfaceWireframe:
return new ExcelSurfaceChart(drawings, drawNode, chartType, topChart, PivotTableSource);
default:
return new ExcelChart(drawings, drawNode, chartType, topChart, PivotTableSource);
}
}
public int getFieldNum(ExcelPivotTable table, string toFind)
{
for (int i = 0; i < table.DataFields.Count; i++)
{
if (table.DataFields[i].Name.Contains(toFind)) return i + 2;
}
return -1;
}
internal ExcelLineChart(ExcelDrawings drawings, XmlNode node, eChartType type, ExcelChart topChart, ExcelPivotTable PivotTableSource)
: base(drawings, node, type, topChart, PivotTableSource)
{
//_chartTopPath = string.Format(_chartTopPath, GetChartNodeText());
}
/// <summary>
/// Calculate the values for each cell in the pivot table by de-referencing the tuple using the cache definition if a pivot table is given.
/// Otherwise, calculate the values for each cell in the pivot table for GetPivotData.
/// </summary>
/// <param name="rowTuples">The list of rowItem indices.</param>
/// <param name="columnTuples">The list of columnItem indices.</param>
/// <param name="filterIndices">A dictionary of page field (filter) indices. Maps a cache field to a list of selected filter item indices.</param>
/// <param name="dataFieldIndex">The index of the data field.</param>
/// <param name="pivotTable">The pivot table (optional).</param>
/// <param name="includeHiddenValues">A value indicating whether or not to include hidden values in calculations.</param>
/// <returns>The subtotal value or null if no values are found.</returns>
public List <object> FindMatchingValues(List <Tuple <int, int> > rowTuples, List <Tuple <int, int> > columnTuples,
Dictionary <int, List <int> > filterIndices, int dataFieldIndex, ExcelPivotTable pivotTable = null, bool includeHiddenValues = false)
{
// Convert tuple values if the tuple is a group field.
rowTuples = this.ConvertGroupingTuples(rowTuples, pivotTable);
columnTuples = this.ConvertGroupingTuples(columnTuples, pivotTable);
var matchingValues = new List <object>();
foreach (var record in this.Records)
{
bool match = false;
if (rowTuples != null)
{
match = pivotTable == null?this.FindCacheRecordIndexAndTupleIndexMatch(rowTuples, record) : this.FindCacheRecordValueAndTupleValueMatch(rowTuples, record, pivotTable);
}
if ((match && columnTuples != null) || rowTuples == null)
{
match = pivotTable == null?this.FindCacheRecordIndexAndTupleIndexMatch(columnTuples, record) : this.FindCacheRecordValueAndTupleValueMatch(columnTuples, record, pivotTable);
}
if (match && filterIndices != null)
{
match = this.FindCacheRecordValueAndPageFieldTupleValueMatch(filterIndices, record);
}
if (match)
{
this.AddToList(record, dataFieldIndex, matchingValues);
}
}
return(matchingValues);
}
