diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs
index b6020cd..c762261 100644
--- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs
+++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs
@@ -91,6 +91,10 @@
public IXLPivotTable SetInsertBlankLines() { InsertBlankLines = true; return this; } public IXLPivotTable SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; }
+ internal String RelId { get; set; }
+ internal String CacheDefinitionRelId { get; set; }
+ internal String WorkbookCacheRelId { get; set; }
+
private void SetExcelDefaults()
{
EmptyCellReplacement = String.Empty;
diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs
index 3cfe9ef..8eaf483 100644
--- a/ClosedXML/Excel/XLWorkbook.cs
+++ b/ClosedXML/Excel/XLWorkbook.cs
@@ -406,6 +406,23 @@
return false;
}
+ public IXLRange RangeFromFullAddress(String rangeAddress, out IXLWorksheet ws)
+ {
+ ws = null;
+ if (!rangeAddress.Contains('!')) return null;
+
+ var split = rangeAddress.Split('!');
+ var first = split[0];
+ var wsName = first.StartsWith("'") ? first.Substring(1, first.Length - 2) : first;
+ var localRange = split[1];
+ if (TryGetWorksheet(wsName, out ws))
+ {
+ return ws.Range(localRange);
+ }
+ return null;
+ }
+
+
///
/// Saves the current workbook.
///
@@ -739,17 +756,23 @@
return Ranges(namedCells).Cells();
}
- public IXLRange Range(String namedRange)
+ public IXLRange Range(String range)
{
- var range = NamedRange(namedRange);
- if (range == null) return null;
- return range.Ranges.FirstOrDefault();
+ var namedRange = NamedRange(range);
+ if (namedRange != null)
+ return namedRange.Ranges.FirstOrDefault();
+ else
+ {
+ IXLWorksheet ws;
+ var r = RangeFromFullAddress(range, out ws);
+ return r;
+ }
}
- public IXLRanges Ranges(String namedRanges)
+ public IXLRanges Ranges(String ranges)
{
var retVal = new XLRanges();
- var rangePairs = namedRanges.Split(',');
+ var rangePairs = ranges.Split(',');
foreach (var range in rangePairs.Select(r => Range(r.Trim())).Where(range => range != null))
{
retVal.Add(range);
diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs
index 006fe16..51e606b 100644
--- a/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -401,6 +401,142 @@
}
}
LoadDefinedNames(workbook);
+
+ #region Pivot tables
+
+ // Delay loading of pivot tables until all sheets have been loaded
+ foreach (Sheet dSheet in sheets.OfType())
+ {
+ var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart;
+
+ if (wsPart != null)
+ {
+ var ws = (XLWorksheet)WorksheetsInternal.Worksheet(dSheet.Name);
+
+ foreach (var pivotTablePart in wsPart.PivotTableParts)
+ {
+ var pivotTableCacheDefinitionPart = pivotTablePart.PivotTableCacheDefinitionPart;
+ var pivotTableDefinition = pivotTablePart.PivotTableDefinition;
+
+ var target = ws.FirstCell();
+ if (pivotTableDefinition.Location != null && pivotTableDefinition.Location.Reference != null && pivotTableDefinition.Location.Reference.HasValue)
+ {
+ target = ws.Range(pivotTableDefinition.Location.Reference.Value).FirstCell();
+ }
+
+ IXLRange source = null;
+ if (pivotTableCacheDefinitionPart.PivotCacheDefinition != null
+ && pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource != null
+ && pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource != null)
+ {
+ // TODO: Implement other sources besides worksheetSource (e.g. Table source?)
+ source = ws.Workbook.Range(pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource.Name.Value);
+ }
+
+ if (target != null && source != null)
+ {
+ var pt = ws.PivotTables.AddNew(pivotTableDefinition.Name, target, source) as XLPivotTable;
+ pt.RelId = wsPart.GetIdOfPart(pivotTablePart);
+ pt.CacheDefinitionRelId = pivotTablePart.GetIdOfPart(pivotTableCacheDefinitionPart);
+ pt.WorkbookCacheRelId = dSpreadsheet.WorkbookPart.GetIdOfPart(pivotTableCacheDefinitionPart);
+
+ if (pivotTableDefinition.MergeItem != null) pt.MergeAndCenterWithLabels = pivotTableDefinition.MergeItem.Value;
+ if (pivotTableDefinition.Indent != null) pt.RowLabelIndent = (int)pivotTableDefinition.Indent.Value;
+ if (pivotTableDefinition.PageOverThenDown != null) pt.FilterAreaOrder = pivotTableDefinition.PageOverThenDown.Value ? XLFilterAreaOrder.OverThenDown : XLFilterAreaOrder.DownThenOver;
+ if (pivotTableDefinition.PageWrap != null) pt.FilterFieldsPageWrap = (int)pivotTableDefinition.PageWrap.Value;
+ if (pivotTableDefinition.UseAutoFormatting != null) pt.AutofitColumns = pivotTableDefinition.UseAutoFormatting.Value;
+ if (pivotTableDefinition.PreserveFormatting != null) pt.PreserveCellFormatting = pivotTableDefinition.PreserveFormatting.Value;
+ if (pivotTableDefinition.RowGrandTotals != null) pt.ShowGrandTotalsRows = pivotTableDefinition.RowGrandTotals.Value;
+ if (pivotTableDefinition.ColumnGrandTotals != null) pt.ShowGrandTotalsColumns = pivotTableDefinition.ColumnGrandTotals.Value;
+ if (pivotTableDefinition.SubtotalHiddenItems != null) pt.FilteredItemsInSubtotals = pivotTableDefinition.SubtotalHiddenItems.Value;
+ if (pivotTableDefinition.MultipleFieldFilters != null) pt.AllowMultipleFilters = pivotTableDefinition.MultipleFieldFilters.Value;
+ if (pivotTableDefinition.CustomListSort != null) pt.UseCustomListsForSorting = pivotTableDefinition.CustomListSort.Value;
+ if (pivotTableDefinition.ShowDrill != null) pt.ShowExpandCollapseButtons = pivotTableDefinition.ShowDrill.Value;
+ if (pivotTableDefinition.ShowDataTips != null) pt.ShowContextualTooltips = pivotTableDefinition.ShowDataTips.Value;
+ if (pivotTableDefinition.ShowMemberPropertyTips != null) pt.ShowPropertiesInTooltips = pivotTableDefinition.ShowMemberPropertyTips.Value;
+ if (pivotTableDefinition.ShowHeaders != null) pt.DisplayCaptionsAndDropdowns = pivotTableDefinition.ShowHeaders.Value;
+ if (pivotTableDefinition.GridDropZones != null) pt.ClassicPivotTableLayout = pivotTableDefinition.GridDropZones.Value;
+ if (pivotTableDefinition.ShowEmptyRow != null) pt.ShowEmptyItemsOnRows = pivotTableDefinition.ShowEmptyRow.Value;
+ if (pivotTableDefinition.ShowEmptyColumn != null) pt.ShowEmptyItemsOnColumns = pivotTableDefinition.ShowEmptyColumn.Value;
+ if (pivotTableDefinition.ShowItems != null) pt.DisplayItemLabels = pivotTableDefinition.ShowItems.Value;
+ if (pivotTableDefinition.FieldListSortAscending != null) pt.SortFieldsAtoZ = pivotTableDefinition.FieldListSortAscending.Value;
+ if (pivotTableDefinition.PrintDrill != null) pt.PrintExpandCollapsedButtons = pivotTableDefinition.PrintDrill.Value;
+ if (pivotTableDefinition.ItemPrintTitles != null) pt.RepeatRowLabels = pivotTableDefinition.ItemPrintTitles.Value;
+ if (pivotTableDefinition.FieldPrintTitles != null) pt.PrintTitles = pivotTableDefinition.FieldPrintTitles.Value;
+ if (pivotTableDefinition.EnableDrill != null) pt.EnableShowDetails = pivotTableDefinition.EnableDrill.Value;
+
+ if (pivotTableDefinition.ShowMissing != null && pivotTableDefinition.MissingCaption != null)
+ pt.EmptyCellReplacement = pivotTableDefinition.MissingCaption.Value;
+
+ if (pivotTableDefinition.ShowError != null && pivotTableDefinition.ErrorCaption != null)
+ pt.ErrorValueReplacement = pivotTableDefinition.ErrorCaption.Value;
+
+ // Row labels
+ foreach (var rf in pivotTableDefinition.RowFields.Cast())
+ {
+ if (rf.Index.Value == -2)
+ pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel);
+ else if (rf.Index < pivotTableDefinition.PivotFields.Count)
+ {
+ var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField;
+ if (pf != null && pf.Name != null) pt.RowLabels.Add(pf.Name.Value);
+ }
+ }
+
+ // Column labels
+ foreach (var cf in pivotTableDefinition.ColumnFields.Cast())
+ {
+ if (cf.Index.Value == -2)
+ pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel);
+
+ else if (cf.Index < pivotTableDefinition.PivotFields.Count)
+ {
+ var pf = pivotTableDefinition.PivotFields.ElementAt(cf.Index.Value) as PivotField;
+ if (pf != null && pf.Name != null) pt.ColumnLabels.Add(pf.Name.Value);
+ }
+ }
+
+ // Values
+ foreach (var df in pivotTableDefinition.DataFields.Cast())
+ {
+ if ((int)df.Field.Value == -2)
+ pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel);
+
+ else if (df.Field.Value < pivotTableDefinition.PivotFields.Count)
+ {
+ var pf = pivotTableDefinition.PivotFields.ElementAt((int)df.Field.Value) as PivotField;
+ if (pf != null && pf.Name != null)
+ {
+ var pv = pt.Values.Add(pf.Name.Value, df.Name.Value);
+ if (df.NumberFormatId != null) pv.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value);
+ if (df.Subtotal != null) pv = pv.SetSummaryFormula(df.Subtotal.Value.ToClosedXml());
+ if (df.ShowDataAs != null)
+ {
+ var calculation = pv.Calculation;
+ calculation = df.ShowDataAs.Value.ToClosedXml();
+ pv = pv.SetCalculation(calculation);
+ }
+ if (df.BaseField != null) {
+ var col = pt.SourceRange.Column(df.BaseField.Value + 1);
+
+ var items = col.CellsUsed()
+ .Select(c => c.Value)
+ .Skip(1) // Skip header column
+ .Distinct().ToList();
+
+ pv.BaseField = col.FirstCell().GetValue();
+ if (df.BaseItem != null) pv.BaseItem = items[(int)df.BaseItem.Value].ToString();
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+ }
+
+ #endregion
+
}
#region Comment Helpers
@@ -2037,4 +2173,4 @@
return false;
}
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs
index 3704e42..dc4ff47 100644
--- a/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -1835,23 +1835,40 @@
cacheId = pivotCaches.Cast().Max(pc => pc.CacheId.Value) + 1;
}
- foreach (var pt in xlWorksheet.PivotTables)
+ foreach (var pt in xlWorksheet.PivotTables.Cast())
{
// TODO: Avoid duplicate pivot caches of same source range
- var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook);
- var pivotTableCacheDefinitionPart = workbookPart.AddNewPart(ptCdp);
+ var workbookCacheRelId = pt.WorkbookCacheRelId;
+ PivotCache pivotCache;
+ PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart;
+ if (!XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId))
+ {
+ pivotCache = pivotCaches.Cast().Single(pc => pc.Id.Value == pt.WorkbookCacheRelId);
+ pivotTableCacheDefinitionPart = workbookPart.GetPartById(pt.WorkbookCacheRelId) as PivotTableCacheDefinitionPart;
+ }
+ else
+ {
+ workbookCacheRelId = context.RelIdGenerator.GetNext(RelType.Workbook);
+ pivotCache = new PivotCache { CacheId = cacheId++, Id = workbookCacheRelId };
+ pivotTableCacheDefinitionPart = workbookPart.AddNewPart(workbookCacheRelId);
+ }
+
GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt);
- var pivotCache = new PivotCache { CacheId = cacheId++, Id = ptCdp };
+ if (XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId))
+ pivotCaches.AppendChild(pivotCache);
- pivotCaches.AppendChild(pivotCache);
+ PivotTablePart pivotTablePart;
+ if (XLHelper.IsNullOrWhiteSpace(pt.RelId))
+ pivotTablePart = worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook));
+ else
+ pivotTablePart = worksheetPart.GetPartById(pt.RelId) as PivotTablePart;
- var pivotTablePart =
- worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook));
GeneratePivotTablePartContent(pivotTablePart, pt, pivotCache.CacheId, context);
- pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook));
+ if (XLHelper.IsNullOrWhiteSpace(pt.RelId))
+ pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook));
}
}
@@ -1932,7 +1949,9 @@
pivotTableCacheDefinitionPart.PivotCacheDefinition = pivotCacheDefinition;
- var pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.AddNewPart("rId1");
+ var pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.GetPartsOfType().Any() ?
+ pivotTableCacheDefinitionPart.GetPartsOfType().First() :
+ pivotTableCacheDefinitionPart.AddNewPart("rId1");
var pivotCacheRecords = new PivotCacheRecords();
pivotCacheRecords.AddNamespaceDeclaration("r",
diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj
index 0e23f5b..8a04744 100644
--- a/ClosedXML_Tests/ClosedXML_Tests.csproj
+++ b/ClosedXML_Tests/ClosedXML_Tests.csproj
@@ -252,6 +252,7 @@
+
diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs
index 628ce15..3571bd2 100644
--- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs
+++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs
@@ -1,10 +1,8 @@
-using System;
+using ClosedXML.Excel;
+using NUnit.Framework;
using System.Collections.Generic;
-using System.Data;
using System.IO;
using System.Linq;
-using ClosedXML.Excel;
-using NUnit.Framework;
namespace ClosedXML_Tests.Excel
{
@@ -20,7 +18,8 @@
var files = new List()
{
@"Misc\TableWithCustomTheme.xlsx",
- @"Misc\EmptyTable.xlsx"
+ @"Misc\EmptyTable.xlsx",
+ @"Misc\LoadPivotTables.xlsx"
};
foreach (var file in files)
@@ -56,5 +55,27 @@
}
}
}
+
+ [Test]
+ public void CanLoadBasicPivotTable()
+ {
+ using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LoadPivotTables.xlsx")))
+ using (var wb = new XLWorkbook(stream))
+ {
+ var ws = wb.Worksheet("PivotTable1");
+ var pt = ws.PivotTable("PivotTable1");
+ Assert.AreEqual("PivotTable1", pt.Name);
+
+ Assert.AreEqual(1, pt.RowLabels.Count());
+ Assert.AreEqual("Name", pt.RowLabels.Single().SourceName);
+
+ Assert.AreEqual(1, pt.ColumnLabels.Count());
+ Assert.AreEqual("Month", pt.ColumnLabels.Single().SourceName);
+
+ var pv = pt.Values.Single();
+ Assert.AreEqual("Sum of NumberOfOrders", pv.CustomName);
+ Assert.AreEqual("NumberOfOrders", pv.SourceName);
+ }
+ }
}
}
diff --git a/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx b/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx
new file mode 100644
index 0000000..6fad7fe
--- /dev/null
+++ b/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx
Binary files differ