diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 172a843..849570c 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -288,6 +288,10 @@ } } + // Remove empty pivot cache part + if (workbookPart.Workbook.PivotCaches != null && !workbookPart.Workbook.PivotCaches.Any()) + workbookPart.Workbook.RemoveChild(workbookPart.Workbook.PivotCaches); + GenerateCalculationChainPartContent(workbookPart, context); if (workbookPart.ThemePart == null) @@ -1779,23 +1783,32 @@ XLWorksheet xlWorksheet, SaveContext context) { + PivotCaches pivotCaches; + uint cacheId = 0; + if (workbookPart.Workbook.PivotCaches == null) + pivotCaches = workbookPart.Workbook.AppendChild(new PivotCaches()); + else + { + pivotCaches = workbookPart.Workbook.PivotCaches; + if (pivotCaches.Any()) + cacheId = pivotCaches.Cast().Max(pc => pc.CacheId.Value) + 1; + } + foreach (var pt in xlWorksheet.PivotTables) { + // TODO: Avoid duplicate pivot caches of same source range var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook); var pivotTableCacheDefinitionPart = workbookPart.AddNewPart(ptCdp); GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt); - var pivotCaches = new PivotCaches(); - var pivotCache = new PivotCache {CacheId = 0U, Id = ptCdp}; + var pivotCache = new PivotCache { CacheId = cacheId++, Id = ptCdp }; pivotCaches.AppendChild(pivotCache); - workbookPart.Workbook.AppendChild(pivotCaches); - var pivotTablePart = worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); - GeneratePivotTablePartContent(pivotTablePart, pt); + GeneratePivotTablePartContent(pivotTablePart, pt, pivotCache.CacheId); pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); } @@ -1889,12 +1902,12 @@ } // Generates content of pivotTablePart - private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart1, IXLPivotTable pt) + private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart1, IXLPivotTable pt, uint cacheId) { var pivotTableDefinition = new PivotTableDefinition { Name = pt.Name, - CacheId = 0U, + CacheId = cacheId, DataCaption = "Values", MergeItem = GetBooleanValue(pt.MergeAndCenterWithLabels, true), Indent = Convert.ToUInt32(pt.RowLabelIndent), diff --git a/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML_Examples/Creating/CreateFiles.cs index 4a17302..067ed70 100644 --- a/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML_Examples/Creating/CreateFiles.cs @@ -88,6 +88,7 @@ new UsingPhonetics().Create(Path.Combine(path, "UsingPhonetics.xlsx")); new WalkingRanges().Create(Path.Combine(path, "CellMoves.xlsx")); new AddingComments().Create(Path.Combine(path, "AddingComments.xlsx")); + new PivotTables().Create(Path.Combine(path, "PivotTables.xlsx")); } } } diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index b36a956..4649a17 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -1,29 +1,35 @@ -using System; -using ClosedXML.Excel; +using ClosedXML.Excel; +using System; namespace ClosedXML_Examples { - public class PivotTables + public class PivotTables : IXLExample { public void Create(String filePath) { var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Pivot Table"); - - ws.Cell("A1").Value = "Category"; - ws.Cell("A2").Value = "A"; - ws.Cell("A3").Value = "B"; - ws.Cell("A4").Value = "B"; - ws.Cell("B1").Value = "Number"; - ws.Cell("B2").Value = 100; - ws.Cell("B3").Value = 150; - ws.Cell("B4").Value = 75; - //var pivotTable = ws.Range("A1:B4").CreatePivotTable(ws.Cell("D1")); - //pivotTable.RowLabels.Add("Category"); - //pivotTable.Values.Add("Number") - // .ShowAsPctFrom("Category").And("A") - // .NumberFormat.Format = "0%"; + var wsData = wb.Worksheets.Add("Data"); + wsData.Cell("A1").Value = "Category"; + wsData.Cell("A2").Value = "A"; + wsData.Cell("A3").Value = "B"; + wsData.Cell("A4").Value = "B"; + wsData.Cell("B1").Value = "Number"; + wsData.Cell("B2").Value = 100; + wsData.Cell("B3").Value = 150; + wsData.Cell("B4").Value = 75; + var source = wsData.Range("A1:B4"); + + for (int i = 1; i <= 3; i++) + { + var name = "PT" + i; + var wsPT = wb.Worksheets.Add(name); + var pt = wsPT.PivotTables.AddNew(name, wsPT.Cell("A1"), source); + pt.RowLabels.Add("Category"); + pt.Values.Add("Number") + .ShowAsPctFrom("Category").And("A") + .NumberFormat.Format = "0%"; + } wb.SaveAs(filePath); } diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index cd9b3c0..f60d23b 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -250,6 +250,7 @@ + diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs index dadb7f9..345daa6 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -1,16 +1,15 @@ -using NUnit.Framework; +using ClosedXML_Examples; +using NUnit.Framework; namespace ClosedXML_Tests { [TestFixture] public class XLPivotTableTests { - //[Test] - //public void CreateTable() - //{ - // var ws = new XLWorkbook().Worksheets.Add("Sheet1"); - - - //} + [Test] + public void PivotTables() + { + TestHelper.RunTestExample(@"PivotTables\PivotTables.xlsx"); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx new file mode 100644 index 0000000..43136a8 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ