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