/// <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);
}