// Generates content of pivotTableCacheDefinitionPart
private static void GeneratePivotTableCacheDefinitionPartContent(
PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt)
{
var source = pt.SourceRange;
var pivotCacheDefinition = new PivotCacheDefinition
{
Id = "rId1",
SaveData = pt.SaveSourceData,
RefreshOnLoad = true //pt.RefreshDataOnOpen
};
if (pt.ItemsToRetainPerField == XLItemsToRetain.None)
pivotCacheDefinition.MissingItemsLimit = 0U;
else if (pt.ItemsToRetainPerField == XLItemsToRetain.Max)
pivotCacheDefinition.MissingItemsLimit = XLHelper.MaxRowNumber;
pivotCacheDefinition.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
var cacheSource = new CacheSource {Type = SourceValues.Worksheet};
cacheSource.AppendChild(new WorksheetSource {Name = source.ToString()});
var cacheFields = new CacheFields();
foreach (var c in source.Columns())
{
var columnNumber = c.ColumnNumber();
var columnName = c.FirstCell().Value.ToString();
var xlpf = pt.Fields.Add(columnName);
var field =
pt.RowLabels.Union(pt.ColumnLabels).Union(pt.ReportFilters).FirstOrDefault(f => f.SourceName == columnName);
if (field != null)
{
xlpf.CustomName = field.CustomName;
xlpf.Subtotals.AddRange(field.Subtotals);
}
var sharedItems = new SharedItems();
var onlyNumbers =
!source.Cells().Any(
cell =>
cell.Address.ColumnNumber == columnNumber &&
cell.Address.RowNumber > source.FirstRow().RowNumber() && cell.DataType != XLCellValues.Number);
if (onlyNumbers)
{
sharedItems = new SharedItems
{ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true};
}
else
{
foreach (var cellValue in source.Cells().Where(cell =>
cell.Address.ColumnNumber == columnNumber &&
cell.Address.RowNumber >
source.FirstRow().RowNumber()).Select(
cell => cell.Value.ToString())
.Where(cellValue => !xlpf.SharedStrings.Contains(cellValue)))
{
xlpf.SharedStrings.Add(cellValue);
}
foreach (var li in xlpf.SharedStrings)
{
sharedItems.AppendChild(new StringItem {Val = li});
}
}
var cacheField = new CacheField {Name = xlpf.SourceName};
cacheField.AppendChild(sharedItems);
cacheFields.AppendChild(cacheField);
}
pivotCacheDefinition.AppendChild(cacheSource);
pivotCacheDefinition.AppendChild(cacheFields);
pivotTableCacheDefinitionPart.PivotCacheDefinition = pivotCacheDefinition;
var pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.AddNewPart<PivotTableCacheRecordsPart>("rId1");
var pivotCacheRecords = new PivotCacheRecords();
pivotCacheRecords.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
pivotTableCacheRecordsPart.PivotCacheRecords = pivotCacheRecords;
}
// Generates content of pivotTableCacheRecordsPart2.
private void GeneratePivotTableCacheRecordsPart2Content(PivotTableCacheRecordsPart pivotTableCacheRecordsPart2)
{
PivotCacheRecords pivotCacheRecords2 = new PivotCacheRecords(){ Count = (UInt32Value)5U };
pivotCacheRecords2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
PivotCacheRecord pivotCacheRecord5 = new PivotCacheRecord();
FieldItem fieldItem26 = new FieldItem(){ Val = (UInt32Value)0U };
FieldItem fieldItem27 = new FieldItem(){ Val = (UInt32Value)0U };
NumberItem numberItem9 = new NumberItem(){ Val = 13D };
NumberItem numberItem10 = new NumberItem(){ Val = 1287D };
DateTimeItem dateTimeItem14 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-02T04:31:50Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) };
StringItem stringItem44 = new StringItem(){ Val = "sales staff H" };
pivotCacheRecord5.Append(fieldItem26);
pivotCacheRecord5.Append(fieldItem27);
pivotCacheRecord5.Append(numberItem9);
pivotCacheRecord5.Append(numberItem10);
pivotCacheRecord5.Append(dateTimeItem14);
pivotCacheRecord5.Append(stringItem44);
PivotCacheRecord pivotCacheRecord6 = new PivotCacheRecord();
FieldItem fieldItem28 = new FieldItem(){ Val = (UInt32Value)1U };
FieldItem fieldItem29 = new FieldItem(){ Val = (UInt32Value)1U };
NumberItem numberItem11 = new NumberItem(){ Val = 27D };
NumberItem numberItem12 = new NumberItem(){ Val = 11529D };
DateTimeItem dateTimeItem15 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-04T03:14:24Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) };
StringItem stringItem45 = new StringItem(){ Val = "sales staff I" };
pivotCacheRecord6.Append(fieldItem28);
pivotCacheRecord6.Append(fieldItem29);
pivotCacheRecord6.Append(numberItem11);
pivotCacheRecord6.Append(numberItem12);
pivotCacheRecord6.Append(dateTimeItem15);
pivotCacheRecord6.Append(stringItem45);
PivotCacheRecord pivotCacheRecord7 = new PivotCacheRecord();
FieldItem fieldItem30 = new FieldItem(){ Val = (UInt32Value)2U };
FieldItem fieldItem31 = new FieldItem(){ Val = (UInt32Value)0U };
NumberItem numberItem13 = new NumberItem(){ Val = 19D };
NumberItem numberItem14 = new NumberItem(){ Val = 1881D };
DateTimeItem dateTimeItem16 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-03T11:10:19Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) };
StringItem stringItem46 = new StringItem(){ Val = "sales staff J" };
pivotCacheRecord7.Append(fieldItem30);
pivotCacheRecord7.Append(fieldItem31);
pivotCacheRecord7.Append(numberItem13);
pivotCacheRecord7.Append(numberItem14);
pivotCacheRecord7.Append(dateTimeItem16);
pivotCacheRecord7.Append(stringItem46);
PivotCacheRecord pivotCacheRecord8 = new PivotCacheRecord();
FieldItem fieldItem32 = new FieldItem(){ Val = (UInt32Value)3U };
FieldItem fieldItem33 = new FieldItem(){ Val = (UInt32Value)2U };
NumberItem numberItem15 = new NumberItem(){ Val = 25D };
NumberItem numberItem16 = new NumberItem(){ Val = 1250D };
DateTimeItem dateTimeItem17 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-02T06:54:09Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) };
StringItem stringItem47 = new StringItem(){ Val = "sales staff B" };
pivotCacheRecord8.Append(fieldItem32);
pivotCacheRecord8.Append(fieldItem33);
pivotCacheRecord8.Append(numberItem15);
pivotCacheRecord8.Append(numberItem16);
pivotCacheRecord8.Append(dateTimeItem17);
pivotCacheRecord8.Append(stringItem47);
PivotCacheRecord pivotCacheRecord9 = new PivotCacheRecord();
FieldItem fieldItem34 = new FieldItem(){ Val = (UInt32Value)4U };
FieldItem fieldItem35 = new FieldItem(){ Val = (UInt32Value)2U };
NumberItem numberItem17 = new NumberItem(){ Val = 16D };
NumberItem numberItem18 = new NumberItem(){ Val = 800D };
DateTimeItem dateTimeItem18 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2003-01-01T04:01:28Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) };
StringItem stringItem48 = new StringItem(){ Val = "sales staff F" };
pivotCacheRecord9.Append(fieldItem34);
pivotCacheRecord9.Append(fieldItem35);
pivotCacheRecord9.Append(numberItem17);
pivotCacheRecord9.Append(numberItem18);
pivotCacheRecord9.Append(dateTimeItem18);
pivotCacheRecord9.Append(stringItem48);
pivotCacheRecords2.Append(pivotCacheRecord5);
pivotCacheRecords2.Append(pivotCacheRecord6);
pivotCacheRecords2.Append(pivotCacheRecord7);
pivotCacheRecords2.Append(pivotCacheRecord8);
pivotCacheRecords2.Append(pivotCacheRecord9);
pivotTableCacheRecordsPart2.PivotCacheRecords = pivotCacheRecords2;
}
// Generates content of pivotTableCacheRecordsPart1.
private void GeneratePivotTableCacheRecordsPart1Content(PivotTableCacheRecordsPart pivotTableCacheRecordsPart1)
{
PivotCacheRecords pivotCacheRecords1 = new PivotCacheRecords() { Count = (UInt32Value)3U };
PivotCacheRecord pivotCacheRecord1 = new PivotCacheRecord();
FieldItem fieldItem1 = new FieldItem() { Val = (UInt32Value)0U };
NumberItem numberItem4 = new NumberItem() { Val = 100D };
pivotCacheRecord1.Append(fieldItem1);
pivotCacheRecord1.Append(numberItem4);
PivotCacheRecord pivotCacheRecord2 = new PivotCacheRecord();
FieldItem fieldItem2 = new FieldItem() { Val = (UInt32Value)1U };
NumberItem numberItem5 = new NumberItem() { Val = 120D };
pivotCacheRecord2.Append(fieldItem2);
pivotCacheRecord2.Append(numberItem5);
PivotCacheRecord pivotCacheRecord3 = new PivotCacheRecord();
FieldItem fieldItem3 = new FieldItem() { Val = (UInt32Value)2U };
NumberItem numberItem6 = new NumberItem() { Val = 132D };
pivotCacheRecord3.Append(fieldItem3);
pivotCacheRecord3.Append(numberItem6);
pivotCacheRecords1.Append(pivotCacheRecord1);
pivotCacheRecords1.Append(pivotCacheRecord2);
pivotCacheRecords1.Append(pivotCacheRecord3);
pivotTableCacheRecordsPart1.PivotCacheRecords = pivotCacheRecords1;
}
// Generates content of pivotTableCacheRecordsPart1.
private void GeneratePivotTableCacheRecordsPart1Content(PivotTableCacheRecordsPart pivotTableCacheRecordsPart1)
{
PivotCacheRecords pivotCacheRecords1 = new PivotCacheRecords(){ Count = (UInt32Value)4U };
pivotCacheRecords1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
PivotCacheRecord pivotCacheRecord1 = new PivotCacheRecord();
FieldItem fieldItem12 = new FieldItem(){ Val = (UInt32Value)0U };
FieldItem fieldItem13 = new FieldItem(){ Val = (UInt32Value)0U };
NumberItem numberItem1 = new NumberItem(){ Val = 19D };
NumberItem numberItem2 = new NumberItem(){ Val = 2375D };
FieldItem fieldItem14 = new FieldItem(){ Val = (UInt32Value)0U };
StringItem stringItem30 = new StringItem(){ Val = "sales staff G" };
pivotCacheRecord1.Append(fieldItem12);
pivotCacheRecord1.Append(fieldItem13);
pivotCacheRecord1.Append(numberItem1);
pivotCacheRecord1.Append(numberItem2);
pivotCacheRecord1.Append(fieldItem14);
pivotCacheRecord1.Append(stringItem30);
PivotCacheRecord pivotCacheRecord2 = new PivotCacheRecord();
FieldItem fieldItem15 = new FieldItem(){ Val = (UInt32Value)1U };
FieldItem fieldItem16 = new FieldItem(){ Val = (UInt32Value)1U };
NumberItem numberItem3 = new NumberItem(){ Val = 13D };
NumberItem numberItem4 = new NumberItem(){ Val = 15990D };
FieldItem fieldItem17 = new FieldItem(){ Val = (UInt32Value)1U };
StringItem stringItem31 = new StringItem(){ Val = "sales staff B" };
pivotCacheRecord2.Append(fieldItem15);
pivotCacheRecord2.Append(fieldItem16);
pivotCacheRecord2.Append(numberItem3);
pivotCacheRecord2.Append(numberItem4);
pivotCacheRecord2.Append(fieldItem17);
pivotCacheRecord2.Append(stringItem31);
PivotCacheRecord pivotCacheRecord3 = new PivotCacheRecord();
FieldItem fieldItem18 = new FieldItem(){ Val = (UInt32Value)2U };
FieldItem fieldItem19 = new FieldItem(){ Val = (UInt32Value)2U };
NumberItem numberItem5 = new NumberItem(){ Val = 4D };
NumberItem numberItem6 = new NumberItem(){ Val = 312D };
FieldItem fieldItem20 = new FieldItem(){ Val = (UInt32Value)2U };
StringItem stringItem32 = new StringItem(){ Val = "sales staff E" };
pivotCacheRecord3.Append(fieldItem18);
pivotCacheRecord3.Append(fieldItem19);
pivotCacheRecord3.Append(numberItem5);
pivotCacheRecord3.Append(numberItem6);
pivotCacheRecord3.Append(fieldItem20);
pivotCacheRecord3.Append(stringItem32);
PivotCacheRecord pivotCacheRecord4 = new PivotCacheRecord();
FieldItem fieldItem21 = new FieldItem(){ Val = (UInt32Value)3U };
FieldItem fieldItem22 = new FieldItem(){ Val = (UInt32Value)2U };
NumberItem numberItem7 = new NumberItem(){ Val = 29D };
NumberItem numberItem8 = new NumberItem(){ Val = 2262D };
FieldItem fieldItem23 = new FieldItem(){ Val = (UInt32Value)3U };
StringItem stringItem33 = new StringItem(){ Val = "sales staff E" };
pivotCacheRecord4.Append(fieldItem21);
pivotCacheRecord4.Append(fieldItem22);
pivotCacheRecord4.Append(numberItem7);
pivotCacheRecord4.Append(numberItem8);
pivotCacheRecord4.Append(fieldItem23);
pivotCacheRecord4.Append(stringItem33);
pivotCacheRecords1.Append(pivotCacheRecord1);
pivotCacheRecords1.Append(pivotCacheRecord2);
pivotCacheRecords1.Append(pivotCacheRecord3);
pivotCacheRecords1.Append(pivotCacheRecord4);
pivotTableCacheRecordsPart1.PivotCacheRecords = pivotCacheRecords1;
}