/// <summary> /// Add a new pivottable /// </summary> /// <param name="sheet">The worksheet</param> /// <param name="address">the address of the pivottable</param> /// <param name="sourceAddress">The address of the Source data</param> /// <param name="name"></param> /// <param name="tblId"></param> internal ExcelPivotTable(ExcelWorksheet sheet, ExcelAddressBase address, ExcelRangeBase sourceAddress, string name, int tblId) : base(sheet.NameSpaceManager) { WorkSheet = sheet; Address = address; var pck = sheet._package.Package; PivotTableXml = new XmlDocument(); PivotTableXml.LoadXml(GetStartXml(name, tblId, address, sourceAddress)); TopNode = PivotTableXml.DocumentElement; PivotTableUri = GetNewUri(pck, "/xl/pivotTables/pivotTable{0}.xml", tblId); init(); Part = pck.CreatePart(PivotTableUri, ExcelPackage.schemaPivotTable); PivotTableXml.Save(Part.GetStream()); //Worksheet-Pivottable relationship Relationship = sheet.Part.CreateRelationship(PackUriHelper.ResolvePartUri(sheet.WorksheetUri, PivotTableUri), TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotTable"); _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this, sourceAddress, tblId); _cacheDefinition.Relationship = Part.CreateRelationship(PackUriHelper.ResolvePartUri(PivotTableUri, _cacheDefinition.CacheDefinitionUri), TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotCacheDefinition"); sheet.Workbook.AddPivotTable(CacheID.ToString(), _cacheDefinition.CacheDefinitionUri); LoadFields(); using (var r = sheet.Cells[address.Address]) { r.Clear(); } }
/// <summary> /// Add a new pivottable /// </summary> /// <param name="sheet">The worksheet</param> /// <param name="address">the address of the pivottable</param> /// <param name="sourceAddress">The address of the Source data</param> /// <param name="name"></param> /// <param name="tblId"></param> internal ExcelPivotTable(ExcelWorksheet sheet, ExcelAddressBase address, ExcelRangeBase sourceAddress, string name, int tblId) : base(sheet.NameSpaceManager) { CreatePivotTable(sheet, address, sourceAddress._toCol - sourceAddress._fromCol + 1, name, tblId); CacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this, sourceAddress); CacheId = CacheDefinition._cacheReference.CacheId; LoadFields(); }
/// <summary> /// Add a new pivottable /// </summary> /// <param name="sheet">The worksheet</param> /// <param name="address">the address of the pivottable</param> /// <param name="pivotTableCache">The pivot table cache</param> /// <param name="name"></param> /// <param name="tblId"></param> internal ExcelPivotTable(ExcelWorksheet sheet, ExcelAddressBase address, PivotTableCacheInternal pivotTableCache, string name, int tblId) : base(sheet.NameSpaceManager) { CreatePivotTable(sheet, address, pivotTableCache.Fields.Count, name, tblId); CacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this, pivotTableCache); CacheId = pivotTableCache.CacheId; LoadFields(); }
/// <summary> /// Constructs a <see cref="PivotTableItemsMatcher"/>. /// </summary> /// <param name="pivotFields">The <see cref="ExcelPivotTableFieldCollection"/>.</param> /// <param name="pageFields">The <see cref="ExcelPageFieldCollection"/>.</param> /// <param name="cacheDefinition">The <see cref="ExcelPivotCacheDefinition"/>.</param> /// <param name="filters">The <see cref="ExcelPivotFieldFiltersCollection"/>.</param> public PivotTableItemsMatcher(ExcelPivotTableFieldCollection pivotFields, ExcelPageFieldCollection pageFields, ExcelPivotCacheDefinition cacheDefinition, ExcelPivotFieldFiltersCollection filters) { myCacheDefinition = cacheDefinition; myFields = pivotFields; myFilters = filters; if (pageFields != null) { foreach (var pageField in pageFields) { if (pageField.Item != null) { if (!this.PageFieldsItemsToInclude.ContainsKey(pageField.Field)) { this.PageFieldsItemsToInclude.Add(pageField.Field, new List <int> { }); } var pivotFieldItem = pivotFields[pageField.Field].Items[pageField.Item.Value]; this.PageFieldsItemsToInclude[pageField.Field].Add(pivotFieldItem.X); } else { var pageFieldItems = pivotFields[pageField.Field].Items; if (pageFieldItems != null) { foreach (var item in pageFieldItems.Where(i => !i.Hidden)) { if (!this.PageFieldsItemsToInclude.ContainsKey(pageField.Field)) { this.PageFieldsItemsToInclude.Add(pageField.Field, new List <int> { }); } this.PageFieldsItemsToInclude[pageField.Field].Add(item.X); } } } } } foreach (var field in pivotFields.Where(f => f.HasItems && (f.IsRowField || f.IsColumnField || f.IsSlicerField))) { var values = new List <int>(); foreach (var fieldItem in field.Items) { if (fieldItem.Hidden && fieldItem.X >= 0) { values.Add(fieldItem.X); } } this.HiddenFieldItems.Add(field.Index, values); } }
/// <summary> /// Update the existing <see cref="CacheRecordNode"/>. /// </summary> /// <param name="row">The row of data from the source table.</param> /// <param name="cacheDefinition">The cacheDefinition.</param> public void Update(IEnumerable <object> row, ExcelPivotCacheDefinition cacheDefinition) { if (row == null) { throw new ArgumentNullException(nameof(row)); } if (cacheDefinition == null) { throw new ArgumentNullException(nameof(cacheDefinition)); } if (row.Count() != this.Items.Count) { throw new InvalidOperationException("An attempt was made to update a CacheRecordNode with a different number of fields."); } int col = 0; foreach (var value in row) { var type = CacheItem.GetObjectType(value); var currentItem = myItems[col]; var cacheField = cacheDefinition.CacheFields[col]; if (cacheField.HasSharedItems) { // If shared items contains value, update this.Value to index // otherwise, create and add new sharedItem, update this.Value to new index currentItem.Value = this.GetCacheFieldSharedItemIndexString(cacheField, type, value); } else { // If only the value changed, update it. If the type changed, // replace the node with one of the correct type and value. string stringValue = ConvertUtil.ConvertObjectToXmlAttributeString(value); if (currentItem.Type == type && currentItem.Value != stringValue) { currentItem.Value = stringValue; } else if (currentItem.Type != type) { currentItem.ReplaceNode(type, stringValue, this.Node); } } col++; } }
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); } } }
/// <summary> /// Add a new pivottable /// </summary> /// <param name="sheet">The worksheet</param> /// <param name="address">the address of the pivottable</param> /// <param name="sourceAddress">The address of the Source data</param> /// <param name="name"></param> /// <param name="tblId"></param> internal ExcelPivotTable(ExcelWorksheet sheet, ExcelAddressBase address,ExcelRangeBase sourceAddress, string name, int tblId) : base(sheet.NameSpaceManager) { WorkSheet = sheet; Address = address; var pck = sheet._package.Package; PivotTableXml = new XmlDocument(); LoadXmlSafe(PivotTableXml, GetStartXml(name, tblId, address, sourceAddress), Encoding.UTF8); TopNode = PivotTableXml.DocumentElement; PivotTableUri = GetNewUri(pck, "/xl/pivotTables/pivotTable{0}.xml", ref tblId); init(); Part = pck.CreatePart(PivotTableUri, ExcelPackage.schemaPivotTable); PivotTableXml.Save(Part.GetStream()); //Worksheet-Pivottable relationship Relationship = sheet.Part.CreateRelationship(UriHelper.ResolvePartUri(sheet.WorksheetUri, PivotTableUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotTable"); _cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this, sourceAddress, tblId); _cacheDefinition.Relationship=Part.CreateRelationship(UriHelper.ResolvePartUri(PivotTableUri, _cacheDefinition.CacheDefinitionUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotCacheDefinition"); sheet.Workbook.AddPivotTable(CacheID.ToString(), _cacheDefinition.CacheDefinitionUri); LoadFields(); using (var r=sheet.Cells[address.Address]) { r.Clear(); } }
/// <summary> /// Creates an instance of a <see cref="ExcelPivotCacheRecords"/>. /// </summary> /// <param name="ns">The namespace of the worksheet.</param> /// <param name="package">The <see cref="Packaging.ZipPackage"/> of the Excel package.</param> /// <param name="tableId">The <see cref="ExcelPivotTable"/>'s ID.</param> /// <param name="cacheDefinition">The cache definition of the pivot table.</param> public ExcelPivotCacheRecords(XmlNamespaceManager ns, Packaging.ZipPackage package, ref int tableId, ExcelPivotCacheDefinition cacheDefinition) : base(ns, null) { if (ns == null) { throw new ArgumentNullException(nameof(ns)); } if (package == null) { throw new ArgumentNullException(nameof(package)); } if (cacheDefinition == null) { throw new ArgumentNullException(nameof(cacheDefinition)); } if (tableId < 1) { throw new ArgumentOutOfRangeException(nameof(tableId)); } // CacheRecord. Create an empty one. this.Uri = XmlHelper.GetNewUri(package, $"/xl/pivotCache/{ExcelPivotCacheRecords.Name}{{0}}.xml", ref tableId); var cacheRecord = new XmlDocument(); cacheRecord.LoadXml($"<{ExcelPivotCacheRecords.Name} xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" count=\"0\" />"); this.Part = package.CreatePart(this.Uri, ExcelPackage.schemaPivotCacheRecords); this.CacheRecordsXml = cacheRecord; cacheRecord.Save(this.Part.GetStream()); base.TopNode = cacheRecord.FirstChild; this.CacheDefinition = cacheDefinition; }
/// <summary> /// Creates an instance of an existing <see cref="ExcelPivotCacheRecords"/>. /// </summary> /// <param name="ns">The namespace of the worksheet.</param> /// <param name="package">The Excel package.</param> /// <param name="cacheRecordsXml">The <see cref="ExcelPivotCacheRecords"/> xml document.</param> /// <param name="targetUri">The <see cref="ExcelPivotCacheRecords"/> target uri.</param> /// <param name="cacheDefinition">The cache definition of the pivot table.</param> public ExcelPivotCacheRecords(XmlNamespaceManager ns, ExcelPackage package, XmlDocument cacheRecordsXml, Uri targetUri, ExcelPivotCacheDefinition cacheDefinition) : base(ns, null) { if (ns == null) { throw new ArgumentNullException(nameof(ns)); } if (cacheRecordsXml == null) { throw new ArgumentNullException(nameof(cacheRecordsXml)); } if (targetUri == null) { throw new ArgumentNullException(nameof(targetUri)); } if (cacheDefinition == null) { throw new ArgumentNullException(nameof(cacheDefinition)); } this.CacheRecordsXml = cacheRecordsXml; base.TopNode = cacheRecordsXml.SelectSingleNode($"d:{ExcelPivotCacheRecords.Name}", ns); this.Uri = targetUri; this.Part = package.Package.GetPart(this.Uri); this.CacheDefinition = cacheDefinition; var cacheRecordNodes = this.TopNode.SelectNodes("d:r", base.NameSpaceManager); foreach (XmlNode record in cacheRecordNodes) { this.Records.Add(new CacheRecordNode(base.NameSpaceManager, record)); } }
/// <summary> /// Create a pivottable on the supplied range /// </summary> /// <param name="Range">The range address including header and total row</param> /// <param name="PivotCacheDefinition">A pivot table cache shared with another pivot table</param> /// <param name="Name">The name of the pivottable. Must be unique </param> /// <returns>The pivottable object</returns> public ExcelPivotTable Add(ExcelAddressBase Range, ExcelPivotCacheDefinition PivotCacheDefinition, string Name) { return(Add(new ExcelPivotTable(_ws, Range, PivotCacheDefinition._cacheReference, Name, _ws.Workbook._nextPivotTableID++))); }
/// <summary> /// Creates a new <see cref="CacheRecordNode"/> and items as specified by the <paramref name="row"/> values. /// Adds the resulting <see cref="CacheRecordNode"/> to the specified <paramref name="parentNode"/>. /// </summary> /// <param name="namespaceManager">The namespace manager.</param> /// <param name="parentNode">The parent <see cref="ExcelPivotCacheRecords"/> <see cref="XmlNode"/>.</param> /// <param name="row">A list of object values that this node represents.</param> /// <param name="cacheDefinition">The parent <see cref="ExcelPivotCacheDefinition"/>.</param> public CacheRecordNode(XmlNamespaceManager namespaceManager, XmlNode parentNode, IEnumerable <object> row, ExcelPivotCacheDefinition cacheDefinition) { if (parentNode == null) { throw new ArgumentNullException(nameof(parentNode)); } if (namespaceManager == null) { throw new ArgumentNullException(nameof(namespaceManager)); } if (row == null) { throw new ArgumentNullException(nameof(row)); } if (cacheDefinition == null) { throw new ArgumentNullException(nameof(cacheDefinition)); } if (row.Count() != cacheDefinition.CacheFields.Count) { throw new InvalidOperationException("An attempt was made to create a CacheRecord node with an invalid number of fields."); } this.NameSpaceManager = namespaceManager; var recordNode = parentNode.OwnerDocument.CreateElement("d:r"); int col = 0; foreach (var value in row) { var type = CacheItem.GetObjectType(value); var cacheField = cacheDefinition.CacheFields[col]; if (cacheField.HasSharedItems) { // The corresponding cacheField has shared items; map the new cacheRecord entry // into shared items if a matching entry exists, otherwise create a new sharedItem entry and map accordingly. var indexStringValue = this.GetCacheFieldSharedItemIndexString(cacheField, type, value); var item = new CacheItem(namespaceManager, recordNode, PivotCacheRecordType.x, indexStringValue); item.AddSelf(recordNode); myItems.Add(item); } else { // If no SharedItems exist, simply create a record item entry. var stringValue = ConvertUtil.ConvertObjectToXmlAttributeString(value); var item = new CacheItem(namespaceManager, recordNode, type, stringValue); item.AddSelf(recordNode); myItems.Add(item); } col++; } parentNode.AppendChild(recordNode); }