diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index a0423df..c6542ee 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -153,6 +153,7 @@ public XLDataType DataType; public Boolean MixedDataType; public IEnumerable DistinctValues; + public Boolean IsTotallyBlankField; } internal struct PivotTableInfo diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 59619b7..2341def 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2084,7 +2084,10 @@ var sharedItems = new SharedItems(); - var ptfi = new PivotTableFieldInfo(); + var ptfi = new PivotTableFieldInfo + { + IsTotallyBlankField = false + }; var fieldValueCells = source.CellsUsed(cell => cell.Address.ColumnNumber == columnNumber && cell.Address.RowNumber > source.FirstRow().RowNumber()); @@ -2093,74 +2096,82 @@ && cell.Address.RowNumber > source.FirstRow().RowNumber() && cell.IsEmpty()).Any(); - if (types.Length == 1 && types.Single() == XLDataType.Number) + // For a totally blank column, we need to check that all cells in column are unused + if (!fieldValueCells.Any()) { - sharedItems.ContainsSemiMixedTypes = false; - sharedItems.ContainsString = false; - sharedItems.ContainsNumber = true; + ptfi.IsTotallyBlankField = true; + containsBlank = true; + } - ptfi.DataType = XLDataType.Number; - ptfi.MixedDataType = false; - ptfi.DistinctValues = fieldValueCells - .Select(cell => cell.GetDouble()) + if (types.Any()) + { + if (types.Length == 1 && types.Single() == XLDataType.Number) + { + sharedItems.ContainsSemiMixedTypes = false; + sharedItems.ContainsString = false; + sharedItems.ContainsNumber = true; + + ptfi.DataType = XLDataType.Number; + ptfi.MixedDataType = false; + ptfi.DistinctValues = fieldValueCells + .Select(cell => cell.GetDouble()) + .Distinct() + .Cast() + .ToArray(); + + int val; + var allInteger = ptfi.DistinctValues.All(v => int.TryParse(v.ToString(), out val)); + if (allInteger) sharedItems.ContainsInteger = true; + + pti.Fields.Add(xlpf.SourceName, ptfi); + + // Output items only for row / column / filter fields + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) + { + foreach (var value in ptfi.DistinctValues) + sharedItems.AppendChild(new NumberItem { Val = (double)value }); + + if (containsBlank) sharedItems.AppendChild(new MissingItem()); + } + + sharedItems.MinValue = (double)ptfi.DistinctValues.Min(); + sharedItems.MaxValue = (double)ptfi.DistinctValues.Max(); + } + else if (types.Length == 1 && types.Single() == XLDataType.DateTime) + { + sharedItems.ContainsSemiMixedTypes = false; + sharedItems.ContainsNonDate = false; + sharedItems.ContainsString = false; + sharedItems.ContainsDate = true; + + ptfi.DataType = XLDataType.DateTime; + ptfi.MixedDataType = false; + ptfi.DistinctValues = fieldValueCells + .Select(cell => cell.GetDateTime()) .Distinct() .Cast() .ToArray(); - int val; - var allInteger = ptfi.DistinctValues.All(v => int.TryParse(v.ToString(), out val)); - if (allInteger) sharedItems.ContainsInteger = true; + pti.Fields.Add(xlpf.SourceName, ptfi); - pti.Fields.Add(xlpf.SourceName, ptfi); + // Output items only for row / column / filter fields + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) + { + foreach (var value in ptfi.DistinctValues) + sharedItems.AppendChild(new DateTimeItem { Val = (DateTime)value }); - // Output items only for row / column / filter fields - if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName) - || pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName) - || pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) - { - foreach (var value in ptfi.DistinctValues) - sharedItems.AppendChild(new NumberItem { Val = (double)value }); + if (containsBlank) sharedItems.AppendChild(new MissingItem()); + } - if (containsBlank) sharedItems.AppendChild(new MissingItem()); + sharedItems.MinDate = (DateTime)ptfi.DistinctValues.Min(); + sharedItems.MaxDate = (DateTime)ptfi.DistinctValues.Max(); } + else - sharedItems.MinValue = (double)ptfi.DistinctValues.Min(); - sharedItems.MaxValue = (double)ptfi.DistinctValues.Max(); - } - else if (types.Length == 1 && types.Single() == XLDataType.DateTime) - { - sharedItems.ContainsSemiMixedTypes = false; - sharedItems.ContainsNonDate = false; - sharedItems.ContainsString = false; - sharedItems.ContainsDate = true; - - ptfi.DataType = XLDataType.DateTime; - ptfi.MixedDataType = false; - ptfi.DistinctValues = fieldValueCells - .Select(cell => cell.GetDateTime()) - .Distinct() - .Cast() - .ToArray(); - - pti.Fields.Add(xlpf.SourceName, ptfi); - - // Output items only for row / column / filter fields - if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName) - || pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName) - || pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) - { - foreach (var value in ptfi.DistinctValues) - sharedItems.AppendChild(new DateTimeItem { Val = (DateTime)value }); - - if (containsBlank) sharedItems.AppendChild(new MissingItem()); - } - - sharedItems.MinDate = (DateTime)ptfi.DistinctValues.Min(); - sharedItems.MaxDate = (DateTime)ptfi.DistinctValues.Max(); - } - else - { - if (types.Any()) { ptfi.DataType = types.First(); ptfi.MixedDataType = types.Length > 1; @@ -2187,7 +2198,10 @@ } if (containsBlank) sharedItems.ContainsBlank = true; - if (ptfi.DistinctValues.Any()) + + if (ptfi.IsTotallyBlankField) + pti.Fields.Add(xlpf.SourceName, ptfi); + else if (ptfi.DistinctValues?.Any() ?? false) sharedItems.Count = Convert.ToUInt32(ptfi.DistinctValues.Count()); var cacheField = new CacheField { Name = xlpf.SourceName }; @@ -2317,11 +2331,16 @@ var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; rowFields.AppendChild(f); - for (var i = 0; i < ptfi.DistinctValues.Count(); i++) + if (ptfi.IsTotallyBlankField) + rowItems.AppendChild(new RowItem()); + else { - var rowItem = new RowItem(); - rowItem.AppendChild(new MemberPropertyIndex { Val = i }); - rowItems.AppendChild(rowItem); + for (var i = 0; i < ptfi.DistinctValues.Count(); i++) + { + var rowItem = new RowItem(); + rowItem.AppendChild(new MemberPropertyIndex { Val = i }); + rowItems.AppendChild(rowItem); + } } var rowItemTotal = new RowItem { ItemType = ItemValues.Grand }; @@ -2333,11 +2352,16 @@ var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; columnFields.AppendChild(f); - for (var i = 0; i < ptfi.DistinctValues.Count(); i++) + if (ptfi.IsTotallyBlankField) + columnItems.AppendChild(new RowItem()); + else { - var rowItem = new RowItem(); - rowItem.AppendChild(new MemberPropertyIndex { Val = i }); - columnItems.AppendChild(rowItem); + for (var i = 0; i < ptfi.DistinctValues.Count(); i++) + { + var rowItem = new RowItem(); + rowItem.AppendChild(new MemberPropertyIndex { Val = i }); + columnItems.AppendChild(rowItem); + } } var rowItemTotal = new RowItem { ItemType = ItemValues.Grand }; @@ -2482,7 +2506,8 @@ var fieldItems = new Items(); // Output items only for row / column / filter fields - if (ptfi.DistinctValues.Any() + if (!ptfi.IsTotallyBlankField && + ptfi.DistinctValues.Any() && (pt.RowLabels.Contains(xlpf.SourceName) || pt.ColumnLabels.Contains(xlpf.SourceName) || pt.ReportFilters.Contains(xlpf.SourceName))) diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs index 2cb1332..c6348c3 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -1,5 +1,7 @@ using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Collections.Generic; using System.IO; namespace ClosedXML_Tests @@ -167,8 +169,7 @@ pt.ColumnLabels.Add("Month"); pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); - //wb.SaveAs(".\\pivot.xlsx.zip", true); - + using (var ms = new MemoryStream()) { wb.SaveAs(ms, true); @@ -189,6 +190,106 @@ } } + private class Pastry + { + public Pastry(string name, int? code, int numberOfOrders, double quality, string month, DateTime? bakeDate) + { + Name = name; + Code = code; + NumberOfOrders = numberOfOrders; + Quality = quality; + Month = month; + BakeDate = bakeDate; + } + + public string Name { get; set; } + public int? Code { get; } + public int NumberOfOrders { get; set; } + public double Quality { get; set; } + public string Month { get; set; } + public DateTime? BakeDate { get; set; } + } + + [Test] + public void BlankPivotTableField() + { + using (var ms = new MemoryStream()) + { + TestHelper.CreateAndCompare(() => + { + // Based on .\ClosedXML\ClosedXML_Examples\PivotTables\PivotTables.cs + // But with empty column for Month + var pastries = new List + { + new Pastry("Croissant", 101, 150, 60.2, "", new DateTime(2016, 04, 21)), + new Pastry("Croissant", 101, 250, 50.42, "", new DateTime(2016, 05, 03)), + new Pastry("Croissant", 101, 134, 22.12, "", new DateTime(2016, 06, 24)), + new Pastry("Doughnut", 102, 250, 89.99, "", new DateTime(2017, 04, 23)), + new Pastry("Doughnut", 102, 225, 70, "", new DateTime(2016, 05, 24)), + new Pastry("Doughnut", 102, 210, 75.33, "", new DateTime(2016, 06, 02)), + new Pastry("Bearclaw", 103, 134, 10.24, "", new DateTime(2016, 04, 27)), + new Pastry("Bearclaw", 103, 184, 33.33, "", new DateTime(2016, 05, 20)), + new Pastry("Bearclaw", 103, 124, 25, "", new DateTime(2017, 06, 05)), + new Pastry("Danish", 104, 394, -20.24, "", new DateTime(2017, 04, 24)), + new Pastry("Danish", 104, 190, 60, "", new DateTime(2017, 05, 08)), + new Pastry("Danish", 104, 221, 24.76, "", new DateTime(2016, 06, 21)), + + // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. + new Pastry("Scone", 105, 135, 0, "", new DateTime(2017, 04, 22)), + new Pastry("SconE", 105, 122, 5.19, "", new DateTime(2017, 05, 03)), + new Pastry("SCONE", 105, 243, 44.2, "", new DateTime(2017, 06, 14)), + + // For ContainsBlank and integer rows/columns test + new Pastry("Scone", null, 255, 18.4, "", null), + }; + + var wb = new XLWorkbook(); + + var sheet = wb.Worksheets.Add("PastrySalesData"); + // Insert our list of pastry data into the "PastrySalesData" sheet at cell 1,1 + var source = sheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); + sheet.Columns().AdjustToContents(); + + // Create a range that includes our table, including the header row + var range = source.DataRange; + var header = sheet.Range(1, 1, 1, 3); + var dataRange = sheet.Range(header.FirstCell(), range.LastCell()); + + IXLWorksheet ptSheet; + IXLPivotTable pt; + + for (var i = 1; i <= 4; i++) + { + // Add a new sheet for our pivot table + ptSheet = wb.Worksheets.Add("pvt" + i); + + // Create the pivot table, using the data from the "PastrySalesData" table + pt = ptSheet.PivotTables.AddNew("pvt" + i, ptSheet.Cell(1, 1), dataRange); + + if (i == 1 || i == 4) + pt.ColumnLabels.Add("Name"); + else if (i == 2 || i == 3) + pt.RowLabels.Add("Name"); + + if (i == 1 || i == 3) + pt.RowLabels.Add("Month"); + else if (i == 2 || i == 4) + pt.ColumnLabels.Add("Month"); + + // The values in our table will come from the "NumberOfOrders" field + // The default calculation setting is a total of each row/column + pt.Values.Add("NumberOfOrders", "NumberOfOrdersPercentageOfBearclaw") + .ShowAsPercentageFrom("Name").And("Bearclaw") + .NumberFormat.Format = "0%"; + + ptSheet.Columns().AdjustToContents(); + } + + return wb; + }, @"PivotTableReferenceFiles\BlankPivotTableField\BlankPivotTableField.xlsx"); + } + } + private static void SetFieldOptions(IXLPivotField field, bool withDefaults) { field.SubtotalsAtTop = !withDefaults; diff --git a/ClosedXML_Tests/Resource/PivotTableReferenceFiles/BlankPivotTableField/BlankPivotTableField.xlsx b/ClosedXML_Tests/Resource/PivotTableReferenceFiles/BlankPivotTableField/BlankPivotTableField.xlsx new file mode 100644 index 0000000..7166532 --- /dev/null +++ b/ClosedXML_Tests/Resource/PivotTableReferenceFiles/BlankPivotTableField/BlankPivotTableField.xlsx Binary files differ