public static void AddDataValueLabel(SpreadsheetDocument document, WorksheetPart sheet, PivotAxis axis) { PivotTablePart pivotTablePart = sheet.GetPartsOfType<PivotTablePart>().First(); XDocument pivotTable = pivotTablePart.GetXDocument(); switch (axis) { case PivotAxis.Column: // Add to colFields { XElement fields = pivotTable.Element(S.pivotTableDefinition).Element(S.colFields); if (fields == null) { fields = new XElement(S.colFields, new XAttribute(NoNamespace.count, 0)); XElement rowFields = pivotTable.Element(S.pivotTableDefinition).Element(S.rowFields); if (rowFields == null) pivotTable.Element(S.pivotTableDefinition).Element(S.pivotFields).AddAfterSelf(fields); else rowFields.AddAfterSelf(fields); } fields.Add(new XElement(S.field, new XAttribute(NoNamespace.x, -2))); fields.Attribute(NoNamespace.count).Value = fields.Elements(S.field).Count().ToString(); } break; case PivotAxis.Row: // Add to rowFields { XElement fields = pivotTable.Element(S.pivotTableDefinition).Element(S.rowFields); if (fields == null) { fields = new XElement(S.rowFields, new XAttribute(NoNamespace.count, 0)); pivotTable.Element(S.pivotTableDefinition).Element(S.pivotFields).AddAfterSelf(fields); } fields.Add(new XElement(S.field, new XAttribute(NoNamespace.x, -2))); fields.Attribute(NoNamespace.count).Value = fields.Elements(S.field).Count().ToString(); } break; case PivotAxis.Page: // Add to pageFields { XElement fields = pivotTable.Element(S.pivotTableDefinition).Element(S.pageFields); if (fields == null) { fields = new XElement(S.pageFields, new XAttribute(NoNamespace.count, 0)); XElement prev = pivotTable.Element(S.pivotTableDefinition).Element(S.colFields); if (prev == null) prev = pivotTable.Element(S.pivotTableDefinition).Element(S.rowFields); if (prev == null) pivotTable.Element(S.pivotTableDefinition).Element(S.pivotFields).AddAfterSelf(fields); else prev.AddAfterSelf(fields); } fields.Add(new XElement(S.pageField, new XAttribute(NoNamespace.fld, -2))); fields.Attribute(NoNamespace.count).Value = fields.Elements(S.field).Count().ToString(); } break; } pivotTablePart.PutXDocument(); PivotTableCacheDefinitionPart cacheDefPart = pivotTablePart.GetPartsOfType<PivotTableCacheDefinitionPart>().First(); ForcePivotRefresh(cacheDefPart); }
public static void AddDataValue(SpreadsheetDocument document, WorksheetPart sheet, string fieldName) { PivotTablePart pivotTablePart = sheet.GetPartsOfType<PivotTablePart>().First(); PivotTableCacheDefinitionPart cacheDefPart = pivotTablePart.GetPartsOfType<PivotTableCacheDefinitionPart>().First(); XDocument cacheDef = cacheDefPart.GetXDocument(); int index = Array.FindIndex(cacheDef.Descendants(S.cacheField).ToArray(), z => z.Attribute(NoNamespace.name).Value == fieldName); XDocument pivotTable = pivotTablePart.GetXDocument(); XElement pivotField = pivotTable.Descendants(S.pivotField).Skip(index).First(); pivotField.Add(new XAttribute(NoNamespace.dataField, "1")); XElement fields = pivotTable.Element(S.pivotTableDefinition).Element(S.dataFields); if (fields == null) { fields = new XElement(S.dataFields, new XAttribute(NoNamespace.count, 0)); XElement prev = pivotTable.Element(S.pivotTableDefinition).Element(S.pageFields); if (prev == null) prev = pivotTable.Element(S.pivotTableDefinition).Element(S.colFields); if (prev == null) prev = pivotTable.Element(S.pivotTableDefinition).Element(S.rowFields); if (prev == null) prev = pivotTable.Element(S.pivotTableDefinition).Element(S.pivotFields); prev.AddAfterSelf(fields); } fields.Add(new XElement(S.dataField, new XAttribute(NoNamespace.name, "Sum of " + fieldName), new XAttribute(NoNamespace.fld, index), new XAttribute(NoNamespace.baseField, 0), new XAttribute(NoNamespace.baseItem, 0))); int count = fields.Elements(S.dataField).Count(); fields.Attribute(NoNamespace.count).Value = count.ToString(); if (count == 2) { // Only when data field count goes from 1 to 2 do we add a special column to label the data fields AddDataValueLabel(document, sheet, PivotAxis.Column); } pivotTablePart.PutXDocument(); ForcePivotRefresh(cacheDefPart); }
public static void AddPivotAxis(SpreadsheetDocument document, WorksheetPart sheet, string fieldName, PivotAxis axis) { // Create indexed items in cache and definition PivotTablePart pivotTablePart = sheet.GetPartsOfType<PivotTablePart>().First(); PivotTableCacheDefinitionPart cacheDefPart = pivotTablePart.GetPartsOfType<PivotTableCacheDefinitionPart>().First(); PivotTableCacheRecordsPart recordsPart = cacheDefPart.GetPartsOfType<PivotTableCacheRecordsPart>().First(); XDocument cacheDef = cacheDefPart.GetXDocument(); int index = Array.FindIndex(cacheDef.Descendants(S.cacheField).ToArray(), z => z.Attribute(NoNamespace.name).Value == fieldName); XDocument records = recordsPart.GetXDocument(); List<XElement> values = new List<XElement>(); foreach (XElement rec in records.Descendants(S.r)) { XElement val = rec.Elements().Skip(index).First(); int x = Array.FindIndex(values.ToArray(), z => XElement.DeepEquals(z, val)); if (x == -1) { values.Add(val); x = values.Count() - 1; } val.ReplaceWith(new XElement(S.x, new XAttribute(NoNamespace.v, x))); } XElement sharedItems = cacheDef.Descendants(S.cacheField).Skip(index).First().Element(S.sharedItems); sharedItems.Add(new XAttribute(NoNamespace.count, values.Count()), values); recordsPart.PutXDocument(); cacheDefPart.PutXDocument(); // Add axis definition to pivot table field XDocument pivotTable = pivotTablePart.GetXDocument(); XElement pivotField = pivotTable.Descendants(S.pivotField).Skip(index).First(); XElement items = new XElement(S.items, new XAttribute(NoNamespace.count, values.Count() + 1), values.OrderBy(z => z.Attribute(NoNamespace.v).Value).Select(z => new XElement(S.item, new XAttribute(NoNamespace.x, Array.FindIndex(values.ToArray(), a => a.Attribute(NoNamespace.v).Value == z.Attribute(NoNamespace.v).Value))))); items.Add(new XElement(S.item, new XAttribute(NoNamespace.t, "default"))); switch (axis) { case PivotAxis.Column: pivotField.Add(new XAttribute(NoNamespace.axis, "axisCol"), items); // Add to colFields { XElement fields = pivotTable.Element(S.pivotTableDefinition).Element(S.colFields); if (fields == null) { fields = new XElement(S.colFields, new XAttribute(NoNamespace.count, 0)); XElement rowFields = pivotTable.Element(S.pivotTableDefinition).Element(S.rowFields); if (rowFields == null) pivotTable.Element(S.pivotTableDefinition).Element(S.pivotFields).AddAfterSelf(fields); else rowFields.AddAfterSelf(fields); } fields.Add(new XElement(S.field, new XAttribute(NoNamespace.x, index))); fields.Attribute(NoNamespace.count).Value = fields.Elements(S.field).Count().ToString(); } break; case PivotAxis.Row: pivotField.Add(new XAttribute(NoNamespace.axis, "axisRow"), items); // Add to rowFields { XElement fields = pivotTable.Element(S.pivotTableDefinition).Element(S.rowFields); if (fields == null) { fields = new XElement(S.rowFields, new XAttribute(NoNamespace.count, 0)); pivotTable.Element(S.pivotTableDefinition).Element(S.pivotFields).AddAfterSelf(fields); } fields.Add(new XElement(S.field, new XAttribute(NoNamespace.x, index))); fields.Attribute(NoNamespace.count).Value = fields.Elements(S.field).Count().ToString(); } break; case PivotAxis.Page: pivotField.Add(new XAttribute(NoNamespace.axis, "axisPage"), items); // Add to pageFields { XElement fields = pivotTable.Element(S.pivotTableDefinition).Element(S.pageFields); if (fields == null) { fields = new XElement(S.pageFields, new XAttribute(NoNamespace.count, 0)); XElement prev = pivotTable.Element(S.pivotTableDefinition).Element(S.colFields); if (prev == null) prev = pivotTable.Element(S.pivotTableDefinition).Element(S.rowFields); if (prev == null) pivotTable.Element(S.pivotTableDefinition).Element(S.pivotFields).AddAfterSelf(fields); else prev.AddAfterSelf(fields); } fields.Add(new XElement(S.pageField, new XAttribute(NoNamespace.fld, index))); fields.Attribute(NoNamespace.count).Value = fields.Elements(S.field).Count().ToString(); } break; } pivotTablePart.PutXDocument(); ForcePivotRefresh(cacheDefPart); }