diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 860f680..b45d927 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -1937,7 +1937,6 @@ // TODO: Avoid duplicate pivot caches of same source range - var workbookCacheRelId = pt.WorkbookCacheRelId; PivotCache pivotCache; PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart; if (!String.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) @@ -1947,7 +1946,7 @@ } else { - workbookCacheRelId = context.RelIdGenerator.GetNext(RelType.Workbook); + var workbookCacheRelId = context.RelIdGenerator.GetNext(RelType.Workbook); pivotCache = new PivotCache { CacheId = cacheId++, Id = workbookCacheRelId }; pivotTableCacheDefinitionPart = workbookPart.AddNewPart(workbookCacheRelId); } @@ -2030,10 +2029,13 @@ var fieldValueCells = source.CellsUsed(cell => cell.Address.ColumnNumber == columnNumber && cell.Address.RowNumber > source.FirstRow().RowNumber()); - var types = fieldValueCells.Select(cell => cell.DataType).Distinct(); + var types = fieldValueCells.Select(cell => cell.DataType).Distinct().ToArray(); + var containsBlank = source.CellsUsed(true, cell => cell.Address.ColumnNumber == columnNumber + && cell.Address.RowNumber > source.FirstRow().RowNumber() + && cell.IsEmpty()).Any(); - if (types.Count() == 1 && types.Single() == XLDataType.Number) + if (types.Length == 1 && types.Single() == XLDataType.Number) { sharedItems.ContainsSemiMixedTypes = false; sharedItems.ContainsString = false; @@ -2044,7 +2046,12 @@ ptfi.DistinctValues = fieldValueCells .Select(cell => cell.GetDouble()) .Distinct() - .Cast(); + .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); @@ -2055,24 +2062,27 @@ { 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.Count() == 1 && types.Single() == XLDataType.DateTime) + else if (types.Length == 1 && types.Single() == XLDataType.DateTime) { sharedItems.ContainsSemiMixedTypes = false; + sharedItems.ContainsNonDate = false; sharedItems.ContainsString = false; - sharedItems.ContainsNumber = false; sharedItems.ContainsDate = true; ptfi.DataType = XLDataType.DateTime; ptfi.MixedDataType = false; ptfi.DistinctValues = fieldValueCells - .Select(cell => cell.GetDateTime()) - .Distinct() - .Cast(); + .Select(cell => cell.GetDateTime()) + .Distinct() + .Cast() + .ToArray(); pti.Fields.Add(xlpf.SourceName, ptfi); @@ -2083,6 +2093,8 @@ { 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(); @@ -2093,26 +2105,30 @@ if (types.Any()) { ptfi.DataType = types.First(); - ptfi.MixedDataType = types.Count() > 1; + ptfi.MixedDataType = types.Length > 1; if (!ptfi.MixedDataType && ptfi.DataType == XLDataType.Text) ptfi.DistinctValues = fieldValueCells .Select(cell => cell.Value) .Cast() - .Distinct(StringComparer.OrdinalIgnoreCase); + .Distinct(StringComparer.OrdinalIgnoreCase) + .ToArray(); else ptfi.DistinctValues = fieldValueCells .Select(cell => cell.GetString()) - .Cast() - .Distinct(StringComparer.OrdinalIgnoreCase); + .Distinct(StringComparer.OrdinalIgnoreCase) + .ToArray(); pti.Fields.Add(xlpf.SourceName, ptfi); foreach (var value in ptfi.DistinctValues) sharedItems.AppendChild(new StringItem { Val = (string)value }); + + if (containsBlank) sharedItems.AppendChild(new MissingItem()); } } + if (containsBlank) sharedItems.ContainsBlank = true; if (ptfi.DistinctValues.Any()) sharedItems.Count = Convert.ToUInt32(ptfi.DistinctValues.Count()); diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index 0d92978..8f19d35 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -8,9 +8,10 @@ { private class Pastry { - public Pastry(string name, int numberOfOrders, double quality, string month, DateTime bakeDate) + public Pastry(string name, int? code, int numberOfOrders, double quality, string month, DateTime? bakeDate) { Name = name; + Code = code; NumberOfOrders = numberOfOrders; Quality = quality; Month = month; @@ -18,33 +19,37 @@ } 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; } + public DateTime? BakeDate { get; set; } } public void Create(String filePath) { var pastries = new List { - new Pastry("Croissant", 150, 60.2, "Apr", new DateTime(2016, 04, 21)), - new Pastry("Croissant", 250, 50.42, "May", new DateTime(2016, 05, 03)), - new Pastry("Croissant", 134, 22.12, "Jun", new DateTime(2016, 06, 24)), - new Pastry("Doughnut", 250, 89.99, "Apr", new DateTime(2017, 04, 23)), - new Pastry("Doughnut", 225, 70, "May", new DateTime(2016, 05, 24)), - new Pastry("Doughnut", 210, 75.33, "Jun", new DateTime(2016, 06, 02)), - new Pastry("Bearclaw", 134, 10.24, "Apr", new DateTime(2016, 04, 27)), - new Pastry("Bearclaw", 184, 33.33, "May", new DateTime(2016, 05, 20)), - new Pastry("Bearclaw", 124, 25, "Jun", new DateTime(2017, 06, 05)), - new Pastry("Danish", 394, -20.24, "Apr", new DateTime(2017, 04, 24)), - new Pastry("Danish", 190, 60, "May", new DateTime(2017, 05, 08)), - new Pastry("Danish", 221, 24.76, "Jun", new DateTime(2016, 06, 21)), + new Pastry("Croissant", 101, 150, 60.2, "Apr", new DateTime(2016, 04, 21)), + new Pastry("Croissant", 101, 250, 50.42, "May", new DateTime(2016, 05, 03)), + new Pastry("Croissant", 101, 134, 22.12, "Jun", new DateTime(2016, 06, 24)), + new Pastry("Doughnut", 102, 250, 89.99, "Apr", new DateTime(2017, 04, 23)), + new Pastry("Doughnut", 102, 225, 70, "May", new DateTime(2016, 05, 24)), + new Pastry("Doughnut", 102, 210, 75.33, "Jun", new DateTime(2016, 06, 02)), + new Pastry("Bearclaw", 103, 134, 10.24, "Apr", new DateTime(2016, 04, 27)), + new Pastry("Bearclaw", 103, 184, 33.33, "May", new DateTime(2016, 05, 20)), + new Pastry("Bearclaw", 103, 124, 25, "Jun", new DateTime(2017, 06, 05)), + new Pastry("Danish", 104, 394, -20.24, "Apr", new DateTime(2017, 04, 24)), + new Pastry("Danish", 104, 190, 60, "May", new DateTime(2017, 05, 08)), + new Pastry("Danish", 104, 221, 24.76, "Jun", new DateTime(2016, 06, 21)), // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. - new Pastry("Scone", 135, 0, "Apr", new DateTime(2017, 04, 22)), - new Pastry("SconE", 122, 5.19, "May", new DateTime(2017, 05, 03)), - new Pastry("SCONE", 243, 44.2, "Jun", new DateTime(2017, 06, 14)), + new Pastry("Scone", 105, 135, 0, "Apr", new DateTime(2017, 04, 22)), + new Pastry("SconE", 105, 122, 5.19, "May", new DateTime(2017, 05, 03)), + new Pastry("SCONE", 105, 243, 44.2, "Jun", new DateTime(2017, 06, 14)), + + // For ContainsBlank and integer rows/columns test + new Pastry("Scone", null, 255, 18.4, null, null), }; using (var wb = new XLWorkbook()) @@ -209,6 +214,23 @@ #endregion Different kind of pivot + #region Pivot Table with interger rows + + ptSheet = wb.Worksheets.Add("pvtInteger"); + + pt = ptSheet.PivotTables.AddNew("pvtInteger", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Name"); + pt.RowLabels.Add("Code"); + pt.RowLabels.Add("BakeDate"); + + pt.ColumnLabels.Add("Month"); + + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + + #endregion Pivot Table with filter + wb.SaveAs(filePath); } } diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index e4616db..4ad1e7f 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ