public static void ShowValueAs(this ExcelPivotTable pivotTable, ExcelPivotTableDataField dataField, PivotShowAsValues pivotShowAs, ExcelPivotTableField baseField = null) { var xdoc = pivotTable.PivotTableXml; var nsm = new XmlNamespaceManager(xdoc.NameTable); // "http://schemas.openxmlformats.org/spreadsheetml/2006/main" var schemaMain = xdoc.DocumentElement.NamespaceURI; if (nsm.HasNamespace("x") == false) { nsm.AddNamespace("x", schemaMain); } var schemaMainX14 = "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"; if (nsm.HasNamespace("x14") == false) { nsm.AddNamespace("x14", schemaMainX14); } // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.pivottabledefinition.aspx var pivotTableDefinition = xdoc.SelectSingleNode("/x:pivotTableDefinition", nsm); pivotTableDefinition.AppendAttribute("updatedVersion", "5"); // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.datafield.aspx var dataFieldNode = xdoc.SelectSingleNode("/x:pivotTableDefinition/x:dataFields/x:dataField[@name='" + dataField.Name + "']", nsm); if (baseField != null) { dataFieldNode.AppendAttribute("baseField", baseField.Index.ToString()); } // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.datafieldextensionlist.aspx var extLst = dataFieldNode.AppendElement(schemaMain, "x:extLst"); // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.datafieldextension.aspx var ext = extLst.AppendElement(schemaMain, "x:ext"); // https://msdn.microsoft.com/en-us/library/dd950685.aspx ext.AppendAttribute("uri", "{E15A36E0-9728-4e99-A89B-3F7291B0FE68}"); // https://msdn.microsoft.com/en-us/library/dd949774.aspx var x14DataField = ext.AppendElement(schemaMainX14, "x14:dataField"); x14DataField.AppendAttribute("pivotShowAs", pivotShowAs.StringValue()); }
public static void ShowValueAs(this ExcelPivotTable pivotTable, ExcelPivotTableDataField dataField, ShowDataAsValues showDataAs, ExcelPivotTableField baseField = null) { var xdoc = pivotTable.PivotTableXml; var nsm = new XmlNamespaceManager(xdoc.NameTable); // "http://schemas.openxmlformats.org/spreadsheetml/2006/main" var schemaMain = xdoc.DocumentElement.NamespaceURI; if (nsm.HasNamespace("x") == false) { nsm.AddNamespace("x", schemaMain); } // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.datafield.aspx var dataFieldNode = xdoc.SelectSingleNode("/x:pivotTableDefinition/x:dataFields/x:dataField[@name='" + dataField.Name + "']", nsm); dataFieldNode.AppendAttribute("showDataAs", showDataAs.StringValue()); if (baseField != null) { dataFieldNode.AppendAttribute("baseField", baseField.Index.ToString()); } }
internal ExcelPivotTable(PackageRelationship rel, ExcelWorksheet sheet) : base(sheet.NameSpaceManager) { WorkSheet = sheet; PivotTableUri = PackUriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri); Relationship = rel; var pck = sheet._package.Package; Part = pck.GetPart(PivotTableUri); PivotTableXml = new XmlDocument(); PivotTableXml.Load(Part.GetStream()); init(); TopNode = PivotTableXml.DocumentElement; Address = new ExcelAddressBase(GetXmlNodeString("d:location/@ref")); _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this); LoadFields(); //int index=0; ////Add fields. //foreach (XmlElement fieldElem in TopNode.SelectNodes("d:pivotFields/d:pivotField", NameSpaceManager)) //{ // var fld=new ExcelPivotTableField(NameSpaceManager, fieldElem, this, index++); // Fields.AddInternal(fld); //} ////Add fields. //index = 0; //foreach (XmlElement fieldElem in _cacheDefinition.TopNode.SelectNodes("d:cacheFields/d:cacheField", NameSpaceManager)) //{ // var fld = Fields[index++]; // fld.SetCacheFieldNode(fieldElem); //} //Add row fields. foreach (XmlElement rowElem in TopNode.SelectNodes("d:rowFields/d:field", NameSpaceManager)) { int x; if (int.TryParse(rowElem.GetAttribute("x"), out x) && x >= 0) { RowFields.AddInternal(Fields[x]); } else { rowElem.ParentNode.RemoveChild(rowElem); } } ////Add column fields. foreach (XmlElement colElem in TopNode.SelectNodes("d:colFields/d:field", NameSpaceManager)) { int x; if (int.TryParse(colElem.GetAttribute("x"), out x) && x >= 0) { ColumnFields.AddInternal(Fields[x]); } else { colElem.ParentNode.RemoveChild(colElem); } } //Add Page elements //int index = 0; foreach (XmlElement pageElem in TopNode.SelectNodes("d:pageFields/d:pageField", NameSpaceManager)) { int fld; if (int.TryParse(pageElem.GetAttribute("fld"), out fld) && fld >= 0) { var field = Fields[fld]; field._pageFieldSettings = new ExcelPivotTablePageFieldSettings(NameSpaceManager, pageElem, field, fld); PageFields.AddInternal(field); } } //Add data elements //index = 0; foreach (XmlElement dataElem in TopNode.SelectNodes("d:dataFields/d:dataField", NameSpaceManager)) { int fld; if (int.TryParse(dataElem.GetAttribute("fld"), out fld) && fld >= 0) { var field = Fields[fld]; var dataField = new ExcelPivotTableDataField(NameSpaceManager, dataElem, field); DataFields.AddInternal(dataField); } } }
internal ExcelPivotTable(Packaging.ZipPackageRelationship rel, ExcelWorksheet sheet) : base(sheet.NameSpaceManager) { WorkSheet = sheet; PivotTableUri = UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri); Relationship = rel; var pck = sheet._package.Package; Part=pck.GetPart(PivotTableUri); PivotTableXml = new XmlDocument(); LoadXmlSafe(PivotTableXml, Part.GetStream()); init(); TopNode = PivotTableXml.DocumentElement; Address = new ExcelAddressBase(GetXmlNodeString("d:location/@ref")); _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this); LoadFields(); //Add row fields. foreach (XmlElement rowElem in TopNode.SelectNodes("d:rowFields/d:field", NameSpaceManager)) { int x; if (int.TryParse(rowElem.GetAttribute("x"), out x) && x >= 0) { RowFields.AddInternal(Fields[x]); } else { rowElem.ParentNode.RemoveChild(rowElem); } } ////Add column fields. foreach (XmlElement colElem in TopNode.SelectNodes("d:colFields/d:field", NameSpaceManager)) { int x; if(int.TryParse(colElem.GetAttribute("x"),out x) && x >= 0) { ColumnFields.AddInternal(Fields[x]); } else { colElem.ParentNode.RemoveChild(colElem); } } //Add Page elements //int index = 0; foreach (XmlElement pageElem in TopNode.SelectNodes("d:pageFields/d:pageField", NameSpaceManager)) { int fld; if (int.TryParse(pageElem.GetAttribute("fld"), out fld) && fld >= 0) { var field = Fields[fld]; field._pageFieldSettings = new ExcelPivotTablePageFieldSettings(NameSpaceManager, pageElem, field, fld); PageFields.AddInternal(field); } } //Add data elements //index = 0; foreach (XmlElement dataElem in TopNode.SelectNodes("d:dataFields/d:dataField", NameSpaceManager)) { int fld; if (int.TryParse(dataElem.GetAttribute("fld"), out fld) && fld >= 0) { var field = Fields[fld]; var dataField = new ExcelPivotTableDataField(NameSpaceManager, dataElem, field); DataFields.AddInternal(dataField); } } }
public static void Top10(this ExcelPivotTable pivotTable, ExcelPivotTableField field, ExcelPivotTableDataField dataField, int number = 10, bool bottom = false, bool percent = false) { var xdoc = pivotTable.PivotTableXml; var nsm = new XmlNamespaceManager(xdoc.NameTable); // "http://schemas.openxmlformats.org/spreadsheetml/2006/main" var schemaMain = xdoc.DocumentElement.NamespaceURI; if (nsm.HasNamespace("x") == false) { nsm.AddNamespace("x", schemaMain); } // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.pivotfilters.aspx var filters = xdoc.SelectSingleNode("/x:pivotTableDefinition/x:filters", nsm); int filtersCount = 0; if (filters == null) { var pivotTableDefinition = xdoc.SelectSingleNode("/x:pivotTableDefinition", nsm); filters = pivotTableDefinition.AppendElement(schemaMain, "x:filters"); filtersCount = 1; } else { XmlAttribute countAttr = filters.Attributes["count"]; int count = int.Parse(countAttr.Value); filtersCount = count + 1; } filters.AppendAttribute("count", filtersCount.ToString()); // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.pivotfilter.aspx var filter = filters.AppendElement(schemaMain, "x:filter"); filter.AppendAttribute("id", filtersCount.ToString()); filter.AppendAttribute("type", (percent ? "percent" : "count")); int fld = 0; foreach (ExcelPivotTableField pivotField in pivotTable.Fields) { if (pivotField == field) { filter.AppendAttribute("fld", fld.ToString()); break; } fld++; } int iMeasureFld = 0; foreach (ExcelPivotTableDataField pivotDataField in pivotTable.DataFields) { if (pivotDataField == dataField) { filter.AppendAttribute("iMeasureFld", iMeasureFld.ToString()); break; } iMeasureFld++; } // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.autofilter.aspx var autoFilter = filter.AppendElement(schemaMain, "x:autoFilter"); // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.filtercolumn.aspx var filterColumn = autoFilter.AppendElement(schemaMain, "x:filterColumn"); filterColumn.AppendAttribute("colId", "0"); // the first auto filter in the pivot table // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.top10.aspx var top10 = filterColumn.AppendElement(schemaMain, "x:top10"); top10.AppendAttribute("val", number.ToString()); top10.AppendAttribute("top", (bottom ? "0" : "1")); top10.AppendAttribute("percent", (percent ? "1" : "0")); }
public static void SortOnDataField(this ExcelPivotTable pivotTable, ExcelPivotTableField field, ExcelPivotTableDataField dataField, bool descending = false) { var xdoc = pivotTable.PivotTableXml; var nsm = new XmlNamespaceManager(xdoc.NameTable); // "http://schemas.openxmlformats.org/spreadsheetml/2006/main" var schemaMain = xdoc.DocumentElement.NamespaceURI; if (nsm.HasNamespace("x") == false) { nsm.AddNamespace("x", schemaMain); } // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.pivotfield.aspx var pivotField = xdoc.SelectSingleNode("/x:pivotTableDefinition/x:pivotFields/x:pivotField[position()=" + (field.Index + 1) + "]", nsm); pivotField.AppendAttribute("sortType", (descending ? "descending" : "ascending")); // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.autosortscope.aspx var autoSortScope = pivotField.AppendElement(schemaMain, "x:autoSortScope"); // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.pivotarea.aspx var pivotArea = autoSortScope.AppendElement(schemaMain, "x:pivotArea"); // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.pivotareareferences.aspx var references = pivotArea.AppendElement(schemaMain, "x:references"); references.AppendAttribute("count", "1"); // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.pivotareareference.aspx var reference = references.AppendElement(schemaMain, "x:reference"); // Specifies the index of the field to which this filter refers. A value of -2 indicates the 'data' field. // int -> uint: -2 -> ((2^32)-2) = 4294967294 reference.AppendAttribute("field", "4294967294"); // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.fielditem.aspx var x = reference.AppendElement(schemaMain, "x:x"); int v = 0; foreach (ExcelPivotTableDataField pivotDataField in pivotTable.DataFields) { if (pivotDataField == dataField) { x.AppendAttribute("v", v.ToString()); break; } v++; } }