public static SourceField GetInstance(CacheField cacheField, int index) { IList <PivotTableGroup> pivotGroupings = new List <PivotTableGroup>(); PivotTableGroup pivotTableGroup = PivotTableGroup.GetInstance(cacheField, index); if (pivotTableGroup != null) { pivotGroupings.Add(pivotTableGroup); } return(new SourceField() { Index = index, Name = cacheField.Name, NumberFmtId = cacheField.NumberFormatId ?? 0, // ECMA-376 Appendix B.2 Line 1284 shows that default value is "false" Items = SharedItems.GetInstance(cacheField), PivotGrouping = pivotGroupings }); }
// 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; }
internal void FromCacheField(CacheField cf) { this.SetAllNull(); if (cf.Name != null) { this.Name = cf.Name.Value; } if (cf.Caption != null) { this.Caption = cf.Caption.Value; } if (cf.PropertyName != null) { this.PropertyName = cf.PropertyName.Value; } if (cf.ServerField != null) { this.ServerField = cf.ServerField.Value; } if (cf.UniqueList != null) { this.UniqueList = cf.UniqueList.Value; } if (cf.NumberFormatId != null) { this.NumberFormatId = cf.NumberFormatId.Value; } if (cf.Formula != null) { this.Formula = cf.Formula.Value; } if (cf.SqlType != null) { this.SqlType = cf.SqlType.Value; } if (cf.Hierarchy != null) { this.Hierarchy = cf.Hierarchy.Value; } if (cf.Level != null) { this.Level = cf.Level.Value; } if (cf.DatabaseField != null) { this.DatabaseField = cf.DatabaseField.Value; } if (cf.MappingCount != null) { this.MappingCount = cf.MappingCount.Value; } if (cf.MemberPropertyField != null) { this.MemberPropertyField = cf.MemberPropertyField.Value; } if (cf.SharedItems != null) { this.SharedItems.FromSharedItems(cf.SharedItems); this.HasSharedItems = true; } if (cf.FieldGroup != null) { this.FieldGroup.FromFieldGroup(cf.FieldGroup); this.HasFieldGroup = true; } MemberPropertiesMap mpm; using (OpenXmlReader oxr = OpenXmlReader.Create(cf)) { while (oxr.Read()) { if (oxr.ElementType == typeof(MemberPropertiesMap)) { mpm = (MemberPropertiesMap)oxr.LoadCurrentElement(); if (mpm.Val != null) { this.MemberPropertiesMaps.Add(mpm.Val.Value); } else { this.MemberPropertiesMaps.Add(0); } } } } }
internal CacheField ToCacheField() { CacheField cf = new CacheField(); cf.Name = this.Name; if (this.Caption != null && this.Caption.Length > 0) { cf.Caption = this.Caption; } if (this.PropertyName != null & this.PropertyName.Length > 0) { cf.PropertyName = this.PropertyName; } if (this.ServerField != false) { cf.ServerField = this.ServerField; } if (this.UniqueList != true) { cf.UniqueList = this.UniqueList; } if (this.NumberFormatId != null) { cf.NumberFormatId = this.NumberFormatId.Value; } if (this.Formula != null && this.Formula.Length > 0) { cf.Formula = this.Formula; } if (this.SqlType != 0) { cf.SqlType = this.SqlType; } if (this.Hierarchy != 0) { cf.Hierarchy = this.Hierarchy; } if (this.Level != 0) { cf.Level = this.Level; } if (this.DatabaseField != true) { cf.DatabaseField = this.DatabaseField; } if (this.MappingCount != null) { cf.MappingCount = this.MappingCount.Value; } if (this.MemberPropertyField != false) { cf.MemberPropertyField = this.MemberPropertyField; } if (this.HasSharedItems) { cf.SharedItems = this.SharedItems.ToSharedItems(); } if (this.HasFieldGroup) { cf.FieldGroup = this.FieldGroup.ToFieldGroup(); } foreach (int i in this.MemberPropertiesMaps) { if (i != 0) { cf.Append(new MemberPropertiesMap() { Val = i }); } else { cf.Append(new MemberPropertiesMap()); } } return(cf); }
// 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 CacheField ToCacheField() { CacheField cf = new CacheField(); cf.Name = this.Name; if (this.Caption != null && this.Caption.Length > 0) cf.Caption = this.Caption; if (this.PropertyName != null & this.PropertyName.Length > 0) cf.PropertyName = this.PropertyName; if (this.ServerField != false) cf.ServerField = this.ServerField; if (this.UniqueList != true) cf.UniqueList = this.UniqueList; if (this.NumberFormatId != null) cf.NumberFormatId = this.NumberFormatId.Value; if (this.Formula != null && this.Formula.Length > 0) cf.Formula = this.Formula; if (this.SqlType != 0) cf.SqlType = this.SqlType; if (this.Hierarchy != 0) cf.Hierarchy = this.Hierarchy; if (this.Level != 0) cf.Level = this.Level; if (this.DatabaseField != true) cf.DatabaseField = this.DatabaseField; if (this.MappingCount != null) cf.MappingCount = this.MappingCount.Value; if (this.MemberPropertyField != false) cf.MemberPropertyField = this.MemberPropertyField; if (this.HasSharedItems) { cf.SharedItems = this.SharedItems.ToSharedItems(); } if (this.HasFieldGroup) { cf.FieldGroup = this.FieldGroup.ToFieldGroup(); } foreach (int i in this.MemberPropertiesMaps) { if (i != 0) cf.Append(new MemberPropertiesMap() { Val = i }); else cf.Append(new MemberPropertiesMap()); } return cf; }
internal void FromCacheField(CacheField cf) { this.SetAllNull(); if (cf.Name != null) this.Name = cf.Name.Value; if (cf.Caption != null) this.Caption = cf.Caption.Value; if (cf.PropertyName != null) this.PropertyName = cf.PropertyName.Value; if (cf.ServerField != null) this.ServerField = cf.ServerField.Value; if (cf.UniqueList != null) this.UniqueList = cf.UniqueList.Value; if (cf.NumberFormatId != null) this.NumberFormatId = cf.NumberFormatId.Value; if (cf.Formula != null) this.Formula = cf.Formula.Value; if (cf.SqlType != null) this.SqlType = cf.SqlType.Value; if (cf.Hierarchy != null) this.Hierarchy = cf.Hierarchy.Value; if (cf.Level != null) this.Level = cf.Level.Value; if (cf.DatabaseField != null) this.DatabaseField = cf.DatabaseField.Value; if (cf.MappingCount != null) this.MappingCount = cf.MappingCount.Value; if (cf.MemberPropertyField != null) this.MemberPropertyField = cf.MemberPropertyField.Value; if (cf.SharedItems != null) { this.SharedItems.FromSharedItems(cf.SharedItems); this.HasSharedItems = true; } if (cf.FieldGroup != null) { this.FieldGroup.FromFieldGroup(cf.FieldGroup); this.HasFieldGroup = true; } MemberPropertiesMap mpm; using (OpenXmlReader oxr = OpenXmlReader.Create(cf)) { while (oxr.Read()) { if (oxr.ElementType == typeof(MemberPropertiesMap)) { mpm = (MemberPropertiesMap)oxr.LoadCurrentElement(); if (mpm.Val != null) this.MemberPropertiesMaps.Add(mpm.Val.Value); else this.MemberPropertiesMaps.Add(0); } } } }
internal CacheField ToCacheField() { var cf = new CacheField(); cf.Name = Name; if ((Caption != null) && (Caption.Length > 0)) { cf.Caption = Caption; } if ((PropertyName != null) & (PropertyName.Length > 0)) { cf.PropertyName = PropertyName; } if (ServerField) { cf.ServerField = ServerField; } if (UniqueList != true) { cf.UniqueList = UniqueList; } if (NumberFormatId != null) { cf.NumberFormatId = NumberFormatId.Value; } if ((Formula != null) && (Formula.Length > 0)) { cf.Formula = Formula; } if (SqlType != 0) { cf.SqlType = SqlType; } if (Hierarchy != 0) { cf.Hierarchy = Hierarchy; } if (Level != 0) { cf.Level = Level; } if (DatabaseField != true) { cf.DatabaseField = DatabaseField; } if (MappingCount != null) { cf.MappingCount = MappingCount.Value; } if (MemberPropertyField) { cf.MemberPropertyField = MemberPropertyField; } if (HasSharedItems) { cf.SharedItems = SharedItems.ToSharedItems(); } if (HasFieldGroup) { cf.FieldGroup = FieldGroup.ToFieldGroup(); } foreach (var i in MemberPropertiesMaps) { if (i != 0) { cf.Append(new MemberPropertiesMap { Val = i }); } else { cf.Append(new MemberPropertiesMap()); } } return(cf); }