diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index cbadd34..c92a39d 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2068,7 +2068,9 @@ case XLPivotTableSourceType.Range: worksheetSource.Name = null; worksheetSource.Reference = source.RangeAddress.ToStringRelative(includeSheet: false); - worksheetSource.Sheet = source.RangeAddress.Worksheet.Name.EscapeSheetName(); + + // Do not quote worksheet name with whitespace here - issue #955 + worksheetSource.Sheet = source.RangeAddress.Worksheet.Name; break; case XLPivotTableSourceType.Table: diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs index 7d95222..51a89bb 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -278,6 +278,71 @@ } [Test] + public void SourceSheetWithWhitespace() + { + 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, "", null), + 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("Pastry Sales Data"); + // Insert our list of pastry data into the "PastrySalesData" sheet at cell 1,1 + var table = sheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); + sheet.Cell("F11").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + sheet.Columns().AdjustToContents(); + + IXLWorksheet ptSheet; + IXLPivotTable pt; + + // Add a new sheet for our pivot table + ptSheet = wb.Worksheets.Add("pvt"); + + // Create the pivot table, using the data from the "PastrySalesData" table + pt = ptSheet.PivotTables.Add("pvt", ptSheet.Cell(1, 1), table.AsRange()); + pt.ColumnLabels.Add("Name"); + pt.RowLabels.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; + }, @"Other\PivotTableReferenceFiles\SourceSheetWithWhitespace\outputfile.xlsx"); + } + } + + [Test] public void PivotTableWithNoneTheme() { using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Other\PivotTableReferenceFiles\PivotTableWithNoneTheme\inputfile.xlsx"))) diff --git a/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/SourceSheetWithWhitespace/outputfile.xlsx b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/SourceSheetWithWhitespace/outputfile.xlsx new file mode 100644 index 0000000..77bfc8d --- /dev/null +++ b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/SourceSheetWithWhitespace/outputfile.xlsx Binary files differ