// Generates content of pivotTableCacheDefinitionPart1. private void GeneratePivotTableCacheDefinitionPart1Content(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1) { PivotCacheDefinition pivotCacheDefinition1 = new PivotCacheDefinition() { Id = "rId1", RefreshedBy = "Author", RefreshedDate = 40905.65532824074D, CreatedVersion = 4, RefreshedVersion = 4, MinRefreshableVersion = 3, RecordCount = (UInt32Value)3U }; CacheSource cacheSource1 = new CacheSource() { Type = SourceValues.Worksheet }; WorksheetSource worksheetSource1 = new WorksheetSource() { Reference = "A1:B4", Sheet = "Sheet2" }; cacheSource1.Append(worksheetSource1); CacheFields cacheFields1 = new CacheFields() { Count = (UInt32Value)2U }; CacheField cacheField1 = new CacheField() { Name = "id", NumberFormatId = (UInt32Value)0U }; SharedItems sharedItems1 = new SharedItems() { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true, ContainsInteger = true, MinValue = 1D, MaxValue = 3D, Count = (UInt32Value)3U }; NumberItem numberItem1 = new NumberItem() { Val = 1D }; NumberItem numberItem2 = new NumberItem() { Val = 2D }; NumberItem numberItem3 = new NumberItem() { Val = 3D }; sharedItems1.Append(numberItem1); sharedItems1.Append(numberItem2); sharedItems1.Append(numberItem3); cacheField1.Append(sharedItems1); CacheField cacheField2 = new CacheField() { Name = "score", NumberFormatId = (UInt32Value)0U }; SharedItems sharedItems2 = new SharedItems() { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true, ContainsInteger = true, MinValue = 100D, MaxValue = 132D }; cacheField2.Append(sharedItems2); cacheFields1.Append(cacheField1); cacheFields1.Append(cacheField2); PivotCacheDefinitionExtensionList pivotCacheDefinitionExtensionList1 = new PivotCacheDefinitionExtensionList(); pivotCacheDefinition1.Append(cacheSource1); pivotCacheDefinition1.Append(cacheFields1); pivotCacheDefinition1.Append(pivotCacheDefinitionExtensionList1); pivotTableCacheDefinitionPart1.PivotCacheDefinition = pivotCacheDefinition1; }
// 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; }
internal void FromPivotCacheDefinition(PivotCacheDefinition pcd) { SetAllNull(); if (pcd.Id != null) { Id = pcd.Id.Value; } if (pcd.Invalid != null) { Invalid = pcd.Invalid.Value; } if (pcd.SaveData != null) { SaveData = pcd.SaveData.Value; } if (pcd.RefreshOnLoad != null) { RefreshOnLoad = pcd.RefreshOnLoad.Value; } if (pcd.OptimizeMemory != null) { OptimizeMemory = pcd.OptimizeMemory.Value; } if (pcd.EnableRefresh != null) { EnableRefresh = pcd.EnableRefresh.Value; } if (pcd.RefreshedBy != null) { RefreshedBy = pcd.RefreshedBy.Value; } if (pcd.RefreshedDate != null) { RefreshedDate = pcd.RefreshedDate.Value; } if (pcd.BackgroundQuery != null) { BackgroundQuery = pcd.BackgroundQuery.Value; } if (pcd.MissingItemsLimit != null) { MissingItemsLimit = pcd.MissingItemsLimit.Value; } if (pcd.CreatedVersion != null) { CreatedVersion = pcd.CreatedVersion.Value; } if (pcd.RefreshedVersion != null) { RefreshedVersion = pcd.RefreshedVersion.Value; } if (pcd.MinRefreshableVersion != null) { MinRefreshableVersion = pcd.MinRefreshableVersion.Value; } if (pcd.RecordCount != null) { RecordCount = pcd.RecordCount.Value; } if (pcd.UpgradeOnRefresh != null) { UpgradeOnRefresh = pcd.UpgradeOnRefresh.Value; } if (pcd.IsTupleCache != null) { IsTupleCache = pcd.IsTupleCache.Value; } if (pcd.SupportSubquery != null) { SupportSubquery = pcd.SupportSubquery.Value; } if (pcd.SupportAdvancedDrill != null) { SupportAdvancedDrill = pcd.SupportAdvancedDrill.Value; } if (pcd.CacheSource != null) { CacheSource.FromCacheSource(pcd.CacheSource); } // doing one by one because it's bloody hindering awkward complicated. if (pcd.CacheFields != null) { SLCacheField cf; using (var oxr = OpenXmlReader.Create(pcd.CacheFields)) { while (oxr.Read()) { if (oxr.ElementType == typeof(CacheField)) { cf = new SLCacheField(); cf.FromCacheField((CacheField)oxr.LoadCurrentElement()); CacheFields.Add(cf); } } } } if (pcd.CacheHierarchies != null) { SLCacheHierarchy ch; using (var oxr = OpenXmlReader.Create(pcd.CacheHierarchies)) { while (oxr.Read()) { if (oxr.ElementType == typeof(CacheHierarchy)) { ch = new SLCacheHierarchy(); ch.FromCacheHierarchy((CacheHierarchy)oxr.LoadCurrentElement()); CacheHierarchies.Add(ch); } } } } if (pcd.Kpis != null) { SLKpi k; using (var oxr = OpenXmlReader.Create(pcd.Kpis)) { while (oxr.Read()) { if (oxr.ElementType == typeof(Kpi)) { k = new SLKpi(); k.FromKpi((Kpi)oxr.LoadCurrentElement()); Kpis.Add(k); } } } } if (pcd.TupleCache != null) { TupleCache.FromTupleCache(pcd.TupleCache); HasTupleCache = true; } if (pcd.CalculatedItems != null) { SLCalculatedItem ci; using (var oxr = OpenXmlReader.Create(pcd.CalculatedItems)) { while (oxr.Read()) { if (oxr.ElementType == typeof(CalculatedItem)) { ci = new SLCalculatedItem(); ci.FromCalculatedItem((CalculatedItem)oxr.LoadCurrentElement()); CalculatedItems.Add(ci); } } } } if (pcd.CalculatedMembers != null) { SLCalculatedMember cm; using (var oxr = OpenXmlReader.Create(pcd.CalculatedMembers)) { while (oxr.Read()) { if (oxr.ElementType == typeof(CalculatedMember)) { cm = new SLCalculatedMember(); cm.FromCalculatedMember((CalculatedMember)oxr.LoadCurrentElement()); CalculatedMembers.Add(cm); } } } } if (pcd.Dimensions != null) { SLDimension d; using (var oxr = OpenXmlReader.Create(pcd.Dimensions)) { while (oxr.Read()) { if (oxr.ElementType == typeof(Dimension)) { d = new SLDimension(); d.FromDimension((Dimension)oxr.LoadCurrentElement()); Dimensions.Add(d); } } } } if (pcd.MeasureGroups != null) { SLMeasureGroup mg; using (var oxr = OpenXmlReader.Create(pcd.MeasureGroups)) { while (oxr.Read()) { if (oxr.ElementType == typeof(MeasureGroup)) { mg = new SLMeasureGroup(); mg.FromMeasureGroup((MeasureGroup)oxr.LoadCurrentElement()); MeasureGroups.Add(mg); } } } } if (pcd.Maps != null) { SLMeasureDimensionMap mdm; using (var oxr = OpenXmlReader.Create(pcd.Maps)) { while (oxr.Read()) { if (oxr.ElementType == typeof(MeasureDimensionMap)) { mdm = new SLMeasureDimensionMap(); mdm.FromMeasureDimensionMap((MeasureDimensionMap)oxr.LoadCurrentElement()); Maps.Add(mdm); } } } } }
// Generates content of pivotTableCacheDefinitionPart2. private void GeneratePivotTableCacheDefinitionPart2Content(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart2) { PivotCacheDefinition pivotCacheDefinition3 = new PivotCacheDefinition(){ Id = "rId1", RefreshedBy = "Masaki Tamura (Pasona Tech)", RefreshedDate = 40946.730963773145D, CreatedVersion = 4, RefreshedVersion = 5, MinRefreshableVersion = 3, RecordCount = (UInt32Value)5U }; pivotCacheDefinition3.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); CacheSource cacheSource2 = new CacheSource(){ Type = SourceValues.Worksheet }; WorksheetSource worksheetSource2 = new WorksheetSource(){ Name = "DataTable2" }; cacheSource2.Append(worksheetSource2); CacheFields cacheFields2 = new CacheFields(){ Count = (UInt32Value)6U }; CacheField cacheField8 = new CacheField(){ Name = "Date", NumberFormatId = (UInt32Value)14U }; SharedItems sharedItems7 = new SharedItems(){ ContainsSemiMixedTypes = false, ContainsNonDate = false, ContainsDate = true, ContainsString = false, MinDate = System.Xml.XmlConvert.ToDateTime("2000-01-01T13:54:32Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind), MaxDate = System.Xml.XmlConvert.ToDateTime("2002-12-30T22:26:24Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind), Count = (UInt32Value)5U }; DateTimeItem dateTimeItem9 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-01T13:54:32Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; DateTimeItem dateTimeItem10 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-01T15:32:03Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; DateTimeItem dateTimeItem11 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-01T16:56:19Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; DateTimeItem dateTimeItem12 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-01T18:04:48Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; DateTimeItem dateTimeItem13 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2002-12-30T22:26:24Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; sharedItems7.Append(dateTimeItem9); sharedItems7.Append(dateTimeItem10); sharedItems7.Append(dateTimeItem11); sharedItems7.Append(dateTimeItem12); sharedItems7.Append(dateTimeItem13); cacheField8.Append(sharedItems7); CacheField cacheField9 = new CacheField(){ Name = "Product", NumberFormatId = (UInt32Value)0U }; SharedItems sharedItems8 = new SharedItems(){ Count = (UInt32Value)10U }; StringItem stringItem34 = new StringItem(){ Val = "product_F" }; StringItem stringItem35 = new StringItem(){ Val = "product_G" }; StringItem stringItem36 = new StringItem(){ Val = "product_C" }; StringItem stringItem37 = new StringItem(){ Val = "product_E", Unused = true }; StringItem stringItem38 = new StringItem(){ Val = "product_D", Unused = true }; StringItem stringItem39 = new StringItem(){ Val = "product_B", Unused = true }; StringItem stringItem40 = new StringItem(){ Val = "product_A", Unused = true }; StringItem stringItem41 = new StringItem(){ Val = "product_J", Unused = true }; StringItem stringItem42 = new StringItem(){ Val = "product_I", Unused = true }; StringItem stringItem43 = new StringItem(){ Val = "product_H", Unused = true }; sharedItems8.Append(stringItem34); sharedItems8.Append(stringItem35); sharedItems8.Append(stringItem36); sharedItems8.Append(stringItem37); sharedItems8.Append(stringItem38); sharedItems8.Append(stringItem39); sharedItems8.Append(stringItem40); sharedItems8.Append(stringItem41); sharedItems8.Append(stringItem42); sharedItems8.Append(stringItem43); cacheField9.Append(sharedItems8); CacheField cacheField10 = new CacheField(){ Name = "Quantity", NumberFormatId = (UInt32Value)0U }; SharedItems sharedItems9 = new SharedItems(){ ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true, ContainsInteger = true, MinValue = 13D, MaxValue = 27D }; cacheField10.Append(sharedItems9); CacheField cacheField11 = new CacheField(){ Name = "Price", NumberFormatId = (UInt32Value)0U }; SharedItems sharedItems10 = new SharedItems(){ ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true, ContainsInteger = true, MinValue = 800D, MaxValue = 11529D }; cacheField11.Append(sharedItems10); CacheField cacheField12 = new CacheField(){ Name = "DeliveryDate", NumberFormatId = (UInt32Value)14U }; SharedItems sharedItems11 = new SharedItems(){ ContainsSemiMixedTypes = false, ContainsNonDate = false, ContainsDate = true, ContainsString = false, MinDate = System.Xml.XmlConvert.ToDateTime("2000-01-02T04:31:50Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind), MaxDate = System.Xml.XmlConvert.ToDateTime("2003-01-01T04:01:28Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; cacheField12.Append(sharedItems11); CacheField cacheField13 = new CacheField(){ Name = "Employee", NumberFormatId = (UInt32Value)0U }; SharedItems sharedItems12 = new SharedItems(); cacheField13.Append(sharedItems12); cacheFields2.Append(cacheField8); cacheFields2.Append(cacheField9); cacheFields2.Append(cacheField10); cacheFields2.Append(cacheField11); cacheFields2.Append(cacheField12); cacheFields2.Append(cacheField13); PivotCacheDefinitionExtensionList pivotCacheDefinitionExtensionList2 = new PivotCacheDefinitionExtensionList(); PivotCacheDefinitionExtension pivotCacheDefinitionExtension2 = new PivotCacheDefinitionExtension(){ Uri = "{725AE2AE-9491-48be-B2B4-4EB974FC3084}" }; pivotCacheDefinitionExtension2.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); X14.PivotCacheDefinition pivotCacheDefinition4 = new X14.PivotCacheDefinition(){ PivotCacheId = (UInt32Value)1U }; pivotCacheDefinitionExtension2.Append(pivotCacheDefinition4); pivotCacheDefinitionExtensionList2.Append(pivotCacheDefinitionExtension2); pivotCacheDefinition3.Append(cacheSource2); pivotCacheDefinition3.Append(cacheFields2); pivotCacheDefinition3.Append(pivotCacheDefinitionExtensionList2); pivotTableCacheDefinitionPart2.PivotCacheDefinition = pivotCacheDefinition3; }
// Generates content of pivotTableCacheDefinitionPart1. private void GeneratePivotTableCacheDefinitionPart1Content(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1) { PivotCacheDefinition pivotCacheDefinition1 = new PivotCacheDefinition(){ Id = "rId1", RefreshedBy = "Masaki Tamura (Pasona Tech)", RefreshedDate = 40946.731165509256D, CreatedVersion = 4, RefreshedVersion = 5, MinRefreshableVersion = 3, RecordCount = (UInt32Value)4U }; pivotCacheDefinition1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); CacheSource cacheSource1 = new CacheSource(){ Type = SourceValues.Worksheet }; WorksheetSource worksheetSource1 = new WorksheetSource(){ Name = "DataTable1" }; cacheSource1.Append(worksheetSource1); CacheFields cacheFields1 = new CacheFields(){ Count = (UInt32Value)7U }; CacheField cacheField1 = new CacheField(){ Name = "Date", NumberFormatId = (UInt32Value)14U }; SharedItems sharedItems1 = new SharedItems(){ ContainsSemiMixedTypes = false, ContainsNonDate = false, ContainsDate = true, ContainsString = false, MinDate = System.Xml.XmlConvert.ToDateTime("2000-01-01T15:40:41Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind), MaxDate = System.Xml.XmlConvert.ToDateTime("2002-12-30T20:50:43Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind), Count = (UInt32Value)4U }; DateTimeItem dateTimeItem1 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-01T15:40:41Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; DateTimeItem dateTimeItem2 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-02T03:35:07Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; DateTimeItem dateTimeItem3 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2002-12-30T06:43:15Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; DateTimeItem dateTimeItem4 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2002-12-30T20:50:43Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; sharedItems1.Append(dateTimeItem1); sharedItems1.Append(dateTimeItem2); sharedItems1.Append(dateTimeItem3); sharedItems1.Append(dateTimeItem4); FieldGroup fieldGroup1 = new FieldGroup(){ ParentId = (UInt32Value)6U, Base = (UInt32Value)0U }; RangeProperties rangeProperties1 = new RangeProperties(){ GroupBy = GroupByValues.Months, StartDate = System.Xml.XmlConvert.ToDateTime("2000-01-01T15:40:41Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind), EndDate = System.Xml.XmlConvert.ToDateTime("2002-12-30T20:50:43Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; GroupItems groupItems1 = new GroupItems(){ Count = (UInt32Value)14U }; StringItem stringItem1 = new StringItem(){ Val = "<2000/01/01" }; StringItem stringItem2 = new StringItem(){ Val = "1月" }; StringItem stringItem3 = new StringItem(){ Val = "2月" }; StringItem stringItem4 = new StringItem(){ Val = "3月" }; StringItem stringItem5 = new StringItem(){ Val = "4月" }; StringItem stringItem6 = new StringItem(){ Val = "5月" }; StringItem stringItem7 = new StringItem(){ Val = "6月" }; StringItem stringItem8 = new StringItem(){ Val = "7月" }; StringItem stringItem9 = new StringItem(){ Val = "8月" }; StringItem stringItem10 = new StringItem(){ Val = "9月" }; StringItem stringItem11 = new StringItem(){ Val = "10月" }; StringItem stringItem12 = new StringItem(){ Val = "11月" }; StringItem stringItem13 = new StringItem(){ Val = "12月" }; StringItem stringItem14 = new StringItem(){ Val = ">2002/12/30" }; groupItems1.Append(stringItem1); groupItems1.Append(stringItem2); groupItems1.Append(stringItem3); groupItems1.Append(stringItem4); groupItems1.Append(stringItem5); groupItems1.Append(stringItem6); groupItems1.Append(stringItem7); groupItems1.Append(stringItem8); groupItems1.Append(stringItem9); groupItems1.Append(stringItem10); groupItems1.Append(stringItem11); groupItems1.Append(stringItem12); groupItems1.Append(stringItem13); groupItems1.Append(stringItem14); fieldGroup1.Append(rangeProperties1); fieldGroup1.Append(groupItems1); cacheField1.Append(sharedItems1); cacheField1.Append(fieldGroup1); CacheField cacheField2 = new CacheField(){ Name = "Product", NumberFormatId = (UInt32Value)0U }; SharedItems sharedItems2 = new SharedItems(){ Count = (UInt32Value)10U }; StringItem stringItem15 = new StringItem(){ Val = "product_A" }; StringItem stringItem16 = new StringItem(){ Val = "product_D" }; StringItem stringItem17 = new StringItem(){ Val = "product_E" }; StringItem stringItem18 = new StringItem(){ Val = "product_C", Unused = true }; StringItem stringItem19 = new StringItem(){ Val = "product_B", Unused = true }; StringItem stringItem20 = new StringItem(){ Val = "product_J", Unused = true }; StringItem stringItem21 = new StringItem(){ Val = "product_I", Unused = true }; StringItem stringItem22 = new StringItem(){ Val = "product_H", Unused = true }; StringItem stringItem23 = new StringItem(){ Val = "product_G", Unused = true }; StringItem stringItem24 = new StringItem(){ Val = "product_F", Unused = true }; sharedItems2.Append(stringItem15); sharedItems2.Append(stringItem16); sharedItems2.Append(stringItem17); sharedItems2.Append(stringItem18); sharedItems2.Append(stringItem19); sharedItems2.Append(stringItem20); sharedItems2.Append(stringItem21); sharedItems2.Append(stringItem22); sharedItems2.Append(stringItem23); sharedItems2.Append(stringItem24); cacheField2.Append(sharedItems2); CacheField cacheField3 = new CacheField(){ Name = "Quantity", NumberFormatId = (UInt32Value)0U }; SharedItems sharedItems3 = new SharedItems(){ ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true, ContainsInteger = true, MinValue = 4D, MaxValue = 29D }; cacheField3.Append(sharedItems3); CacheField cacheField4 = new CacheField(){ Name = "Price", NumberFormatId = (UInt32Value)0U }; SharedItems sharedItems4 = new SharedItems(){ ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true, ContainsInteger = true, MinValue = 312D, MaxValue = 15990D }; cacheField4.Append(sharedItems4); CacheField cacheField5 = new CacheField(){ Name = "DeliveryDate", NumberFormatId = (UInt32Value)14U }; SharedItems sharedItems5 = new SharedItems(){ ContainsSemiMixedTypes = false, ContainsNonDate = false, ContainsDate = true, ContainsString = false, MinDate = System.Xml.XmlConvert.ToDateTime("2000-01-06T15:40:41Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind), MaxDate = System.Xml.XmlConvert.ToDateTime("2003-01-03T06:43:15Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind), Count = (UInt32Value)4U }; DateTimeItem dateTimeItem5 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-06T15:40:41Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; DateTimeItem dateTimeItem6 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2000-01-07T03:35:07Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; DateTimeItem dateTimeItem7 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2003-01-03T06:43:15Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; DateTimeItem dateTimeItem8 = new DateTimeItem(){ Val = System.Xml.XmlConvert.ToDateTime("2002-12-31T20:50:43Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; sharedItems5.Append(dateTimeItem5); sharedItems5.Append(dateTimeItem6); sharedItems5.Append(dateTimeItem7); sharedItems5.Append(dateTimeItem8); cacheField5.Append(sharedItems5); CacheField cacheField6 = new CacheField(){ Name = "Employee", NumberFormatId = (UInt32Value)0U }; SharedItems sharedItems6 = new SharedItems(); cacheField6.Append(sharedItems6); CacheField cacheField7 = new CacheField(){ Name = "Years", NumberFormatId = (UInt32Value)0U, DatabaseField = false }; FieldGroup fieldGroup2 = new FieldGroup(){ Base = (UInt32Value)0U }; RangeProperties rangeProperties2 = new RangeProperties(){ GroupBy = GroupByValues.Years, StartDate = System.Xml.XmlConvert.ToDateTime("2000-01-01T15:40:41Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind), EndDate = System.Xml.XmlConvert.ToDateTime("2002-12-30T20:50:43Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind) }; GroupItems groupItems2 = new GroupItems(){ Count = (UInt32Value)5U }; StringItem stringItem25 = new StringItem(){ Val = "<2000/01/01" }; StringItem stringItem26 = new StringItem(){ Val = "2000年" }; StringItem stringItem27 = new StringItem(){ Val = "2001年" }; StringItem stringItem28 = new StringItem(){ Val = "2002年" }; StringItem stringItem29 = new StringItem(){ Val = ">2002/12/30" }; groupItems2.Append(stringItem25); groupItems2.Append(stringItem26); groupItems2.Append(stringItem27); groupItems2.Append(stringItem28); groupItems2.Append(stringItem29); fieldGroup2.Append(rangeProperties2); fieldGroup2.Append(groupItems2); cacheField7.Append(fieldGroup2); cacheFields1.Append(cacheField1); cacheFields1.Append(cacheField2); cacheFields1.Append(cacheField3); cacheFields1.Append(cacheField4); cacheFields1.Append(cacheField5); cacheFields1.Append(cacheField6); cacheFields1.Append(cacheField7); PivotCacheDefinitionExtensionList pivotCacheDefinitionExtensionList1 = new PivotCacheDefinitionExtensionList(); PivotCacheDefinitionExtension pivotCacheDefinitionExtension1 = new PivotCacheDefinitionExtension(){ Uri = "{725AE2AE-9491-48be-B2B4-4EB974FC3084}" }; pivotCacheDefinitionExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); X14.PivotCacheDefinition pivotCacheDefinition2 = new X14.PivotCacheDefinition(){ PivotCacheId = (UInt32Value)2U }; pivotCacheDefinitionExtension1.Append(pivotCacheDefinition2); pivotCacheDefinitionExtensionList1.Append(pivotCacheDefinitionExtension1); pivotCacheDefinition1.Append(cacheSource1); pivotCacheDefinition1.Append(cacheFields1); pivotCacheDefinition1.Append(pivotCacheDefinitionExtensionList1); pivotTableCacheDefinitionPart1.PivotCacheDefinition = pivotCacheDefinition1; }
// Generates content of pivotTableCacheDefinitionPart1. private void GeneratePivotTableCacheDefinitionPart1Content(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1) { PivotCacheDefinition pivotCacheDefinition1 = new PivotCacheDefinition(){ SaveData = false, RefreshedBy = "Paul Berruti", RefreshedDate = 40855.575896064816D, BackgroundQuery = true, CreatedVersion = 5, RefreshedVersion = 5, MinRefreshableVersion = 3, RecordCount = (UInt32Value)0U, SupportSubquery = true, SupportAdvancedDrill = true }; pivotCacheDefinition1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); CacheSource cacheSource1 = new CacheSource(){ Type = SourceValues.External, ConnectionId = (UInt32Value)3U }; CacheFields cacheFields1 = new CacheFields(){ Count = (UInt32Value)0U }; CacheHierarchies cacheHierarchies1 = new CacheHierarchies(){ Count = (UInt32Value)11U }; CacheHierarchy cacheHierarchy1 = new CacheHierarchy(){ UniqueName = "[Query].[account_id]", Caption = "account_id", Attribute = true, DefaultMemberUniqueName = "[Query].[account_id].[All]", AllUniqueName = "[Query].[account_id].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false }; CacheHierarchy cacheHierarchy2 = new CacheHierarchy(){ UniqueName = "[Query].[account_parent]", Caption = "account_parent", Attribute = true, DefaultMemberUniqueName = "[Query].[account_parent].[All]", AllUniqueName = "[Query].[account_parent].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false }; CacheHierarchy cacheHierarchy3 = new CacheHierarchy(){ UniqueName = "[Query].[account_description]", Caption = "account_description", Attribute = true, DefaultMemberUniqueName = "[Query].[account_description].[All]", AllUniqueName = "[Query].[account_description].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false }; CacheHierarchy cacheHierarchy4 = new CacheHierarchy(){ UniqueName = "[Query].[account_type]", Caption = "account_type", Attribute = true, DefaultMemberUniqueName = "[Query].[account_type].[All]", AllUniqueName = "[Query].[account_type].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false }; CacheHierarchy cacheHierarchy5 = new CacheHierarchy(){ UniqueName = "[Query].[account_rollup]", Caption = "account_rollup", Attribute = true, DefaultMemberUniqueName = "[Query].[account_rollup].[All]", AllUniqueName = "[Query].[account_rollup].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false }; CacheHierarchy cacheHierarchy6 = new CacheHierarchy(){ UniqueName = "[Query].[Custom Members]", Caption = "Custom Members", Attribute = true, DefaultMemberUniqueName = "[Query].[Custom Members].[All]", AllUniqueName = "[Query].[Custom Members].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false }; CacheHierarchy cacheHierarchy7 = new CacheHierarchy(){ UniqueName = "[Query].[CurrentUser]", Caption = "CurrentUser", Attribute = true, DefaultMemberUniqueName = "[Query].[CurrentUser].[All]", AllUniqueName = "[Query].[CurrentUser].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false }; CacheHierarchy cacheHierarchy8 = new CacheHierarchy(){ UniqueName = "[Query].[RefreshTime]", Caption = "RefreshTime", Attribute = true, DefaultMemberUniqueName = "[Query].[RefreshTime].[All]", AllUniqueName = "[Query].[RefreshTime].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false }; CacheHierarchy cacheHierarchy9 = new CacheHierarchy(){ UniqueName = "[Query].[RowNumber]", Caption = "RowNumber", Attribute = true, KeyAttribute = true, DefaultMemberUniqueName = "[Query].[RowNumber].[All]", AllUniqueName = "[Query].[RowNumber].[All]", DimensionUniqueName = "[Query]", DisplayFolder = "", Count = (UInt32Value)0U, Unbalanced = false, Hidden = true }; CacheHierarchy cacheHierarchy10 = new CacheHierarchy(){ UniqueName = "[Measures].[__XL_Count Query]", Caption = "__XL_Count Query", Measure = true, DisplayFolder = "", MeasureGroup = "Query", Count = (UInt32Value)0U, Hidden = true }; CacheHierarchy cacheHierarchy11 = new CacheHierarchy(){ UniqueName = "[Measures].[__XL_Count of Models]", Caption = "__XL_Count of Models", Measure = true, DisplayFolder = "", Count = (UInt32Value)0U, Hidden = true }; cacheHierarchies1.Append(cacheHierarchy1); cacheHierarchies1.Append(cacheHierarchy2); cacheHierarchies1.Append(cacheHierarchy3); cacheHierarchies1.Append(cacheHierarchy4); cacheHierarchies1.Append(cacheHierarchy5); cacheHierarchies1.Append(cacheHierarchy6); cacheHierarchies1.Append(cacheHierarchy7); cacheHierarchies1.Append(cacheHierarchy8); cacheHierarchies1.Append(cacheHierarchy9); cacheHierarchies1.Append(cacheHierarchy10); cacheHierarchies1.Append(cacheHierarchy11); Kpis kpis1 = new Kpis(){ Count = (UInt32Value)0U }; Dimensions dimensions1 = new Dimensions(){ Count = (UInt32Value)2U }; Dimension dimension1 = new Dimension(){ Measure = true, Name = "Measures", UniqueName = "[Measures]", Caption = "Measures" }; Dimension dimension2 = new Dimension(){ Name = "Query", UniqueName = "[Query]", Caption = "Query" }; dimensions1.Append(dimension1); dimensions1.Append(dimension2); MeasureGroups measureGroups1 = new MeasureGroups(){ Count = (UInt32Value)1U }; MeasureGroup measureGroup1 = new MeasureGroup(){ Name = "Query", Caption = "Query" }; measureGroups1.Append(measureGroup1); Maps maps1 = new Maps(){ Count = (UInt32Value)1U }; MeasureDimensionMap measureDimensionMap1 = new MeasureDimensionMap(){ MeasureGroup = (UInt32Value)0U, Dimension = (UInt32Value)1U }; maps1.Append(measureDimensionMap1); PivotCacheDefinitionExtensionList pivotCacheDefinitionExtensionList1 = new PivotCacheDefinitionExtensionList(); PivotCacheDefinitionExtension pivotCacheDefinitionExtension1 = new PivotCacheDefinitionExtension(){ Uri = "{725AE2AE-9491-48be-B2B4-4EB974FC3084}" }; pivotCacheDefinitionExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); X14.PivotCacheDefinition pivotCacheDefinition2 = new X14.PivotCacheDefinition(){ SupportSubqueryNonVisual = true, SupportSubqueryCalcMem = true, SupportAddCalcMems = true }; pivotCacheDefinitionExtension1.Append(pivotCacheDefinition2); pivotCacheDefinitionExtensionList1.Append(pivotCacheDefinitionExtension1); pivotCacheDefinition1.Append(cacheSource1); pivotCacheDefinition1.Append(cacheFields1); pivotCacheDefinition1.Append(cacheHierarchies1); pivotCacheDefinition1.Append(kpis1); pivotCacheDefinition1.Append(dimensions1); pivotCacheDefinition1.Append(measureGroups1); pivotCacheDefinition1.Append(maps1); pivotCacheDefinition1.Append(pivotCacheDefinitionExtensionList1); pivotTableCacheDefinitionPart1.PivotCacheDefinition = pivotCacheDefinition1; }