diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs index b855923..b7249d9 100644 --- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs +++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs @@ -8,6 +8,14 @@ internal class XLPivotValues: IXLPivotValues { private readonly Dictionary _pivotValues = new Dictionary(); + + private readonly IXLPivotTable _pivotTable; + + internal XLPivotValues(IXLPivotTable pivotTable) + { + this._pivotTable = pivotTable; + } + public IEnumerator GetEnumerator() { return _pivotValues.Values.GetEnumerator(); @@ -26,6 +34,10 @@ { var pivotValue = new XLPivotValue(sourceName) { CustomName = customName }; _pivotValues.Add(sourceName, pivotValue); + + if (_pivotValues.Count > 1 && !this._pivotTable.ColumnLabels.Any(cl => cl.SourceName == XLConstants.PivotTableValuesSentinalLabel) && !this._pivotTable.RowLabels.Any(rl => rl.SourceName == XLConstants.PivotTableValuesSentinalLabel)) + _pivotTable.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + return pivotValue; } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index dc2b6ee..b6020cd 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -7,13 +7,14 @@ { internal class XLPivotTable: IXLPivotTable { + public XLPivotTable() { Fields = new XLPivotFields(); ReportFilters = new XLPivotFields(); ColumnLabels=new XLPivotFields(); RowLabels = new XLPivotFields(); - Values = new XLPivotValues(); + Values = new XLPivotValues(this); Theme = XLPivotTableTheme.PivotStyleLight16; SetExcelDefaults(); diff --git a/ClosedXML/Excel/XLConstants.cs b/ClosedXML/Excel/XLConstants.cs index f01f06e..d1cbe58 100644 --- a/ClosedXML/Excel/XLConstants.cs +++ b/ClosedXML/Excel/XLConstants.cs @@ -1,9 +1,10 @@ - -namespace ClosedXML.Excel +namespace ClosedXML.Excel { //Use the class to store magic strings or variables. - internal static class XLConstants + public static class XLConstants { + public const string PivotTableValuesSentinalLabel = "{{Values}}"; + internal static class Comment { internal const string ShapeTypeId = "#_x0000_t202"; diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 8f63530..db2dde7 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -1914,7 +1914,7 @@ } // Generates content of pivotTablePart - private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart1, IXLPivotTable pt, uint cacheId) + private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart, IXLPivotTable pt, uint cacheId) { var pivotTableDefinition = new PivotTableDefinition { @@ -1981,7 +1981,7 @@ var columnFields = new ColumnFields(); var rowItems = new RowItems(); var columnItems = new ColumnItems(); - var pageFields = new PageFields {Count = (uint)pt.ReportFilters.Count()}; + var pageFields = new PageFields { Count = (uint)pt.ReportFilters.Count() }; var pivotFields = new PivotFields {Count = Convert.ToUInt32(pt.SourceRange.ColumnCount())}; foreach (var xlpf in pt.Fields.OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) ? pt.RowLabels.IndexOf(f) : Int32.MaxValue )) @@ -2004,7 +2004,7 @@ } else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName)) { - var f = new Field {Index = pt.Fields.IndexOf(xlpf)}; + var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; columnFields.AppendChild(f); for (var i = 0; i < xlpf.SharedStrings.Count; i++) @@ -2020,6 +2020,16 @@ } } + // -2 is the sentinal value for "Values" + if (pt.ColumnLabels.Any(cl => cl.SourceName == XLConstants.PivotTableValuesSentinalLabel)) + columnFields.AppendChild(new Field { Index = -2 }); + + if (pt.RowLabels.Any(rl => rl.SourceName == XLConstants.PivotTableValuesSentinalLabel)) + { + pivotTableDefinition.DataOnRows = true; + rowFields.AppendChild(new Field { Index = -2 }); + } + foreach (var xlpf in pt.Fields) { var pf = new PivotField {ShowAll = false, Name = xlpf.CustomName}; @@ -2114,6 +2124,7 @@ fieldItems.AppendChild(new Item {ItemType = ItemValues.Default}); } + fieldItems.Count = Convert.ToUInt32(fieldItems.Count()); pf.AppendChild(fieldItems); pivotFields.AppendChild(pf); } @@ -2123,27 +2134,35 @@ if (pt.RowLabels.Any()) { + rowFields.Count = Convert.ToUInt32(rowFields.Count()); pivotTableDefinition.AppendChild(rowFields); } else { rowItems.AppendChild(new RowItem()); } + + rowItems.Count = Convert.ToUInt32(rowItems.Count()); pivotTableDefinition.AppendChild(rowItems); if (!pt.ColumnLabels.Any()) { columnItems.AppendChild(new RowItem()); + columnItems.Count = Convert.ToUInt32(columnItems.Count()); pivotTableDefinition.AppendChild(columnItems); } else { + columnFields.Count = Convert.ToUInt32(columnFields.Count()); pivotTableDefinition.AppendChild(columnFields); + + columnItems.Count = Convert.ToUInt32(columnItems.Count()); pivotTableDefinition.AppendChild(columnItems); } if (pt.ReportFilters.Any()) { + pageFields.Count = Convert.ToUInt32(pageFields.Count()); pivotTableDefinition.AppendChild(pageFields); } @@ -2186,6 +2205,8 @@ dataFields.AppendChild(df); } + + dataFields.Count = Convert.ToUInt32(dataFields.Count()); pivotTableDefinition.AppendChild(dataFields); pivotTableDefinition.AppendChild(new PivotTableStyle @@ -2217,7 +2238,7 @@ #endregion - pivotTablePart1.PivotTableDefinition = pivotTableDefinition; + pivotTablePart.PivotTableDefinition = pivotTableDefinition; } diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index 6a8116c..3c367c0 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -55,7 +55,7 @@ var header = sheet.Range(1, 1, 1, 3); var dataRange = sheet.Range(header.FirstCell(), range.LastCell()); - for (int i = 1; i <= 3; i++) + for (int i = 1; i <= 1; i++) { // Add a new sheet for our pivot table var ptSheet = wb.Worksheets.Add("PivotTable" + i); @@ -65,13 +65,20 @@ // The rows in our pivot table will be the names of the pastries pt.RowLabels.Add("Name"); + pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); // The columns will be the months pt.ColumnLabels.Add("Month"); + //pt.ColumnLabels.Add("{{Values}}"); // 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"); + pt.Values.Add("NumberOfOrders", "NumberOfOrdersPercentageOfDoughnut") + .ShowAsPctFrom("Name").And("Doughnut") + .NumberFormat.Format = "0%"; + + pt.Values.Add("Quality", "Sum of Quality"); + //pt.Values.Add("NumberOfOrders", "Sum of NumberOfOrders"); } wb.SaveAs(filePath); diff --git a/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj b/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj index 3581c95..81bf383 100644 --- a/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj +++ b/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj @@ -63,6 +63,9 @@ + + PreserveNewest + diff --git a/ClosedXML_Sandbox/Program.cs b/ClosedXML_Sandbox/Program.cs index 0cb47d1..ed0202a 100644 --- a/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML_Sandbox/Program.cs @@ -1,4 +1,6 @@ -using System; +using ClosedXML.Excel; +using System.Linq; +using System; namespace ClosedXML_Sandbox { @@ -6,10 +8,26 @@ { private static void Main(string[] args) { - PerformanceRunner.TimeAction(PerformanceRunner.RunInsertTable); + var path = "tmp.xlsx"; + using (var workbook = new XLWorkbook(path)) + { + var ws1 = workbook.Worksheets.Last(); + + int rowCount = 20; //example + //first row headers + for (int i = 1; i < rowCount; i++) + { + var row = ws1.Row(i); + var values = row.Cells().Select(c => c.Value).ToArray(); + IXLCell cell = row.Cell("E"); //.Cell(5); + bool isEmpty = cell.IsEmpty(); //always empty on this column, but in original have 6 lines + var val = cell.Value; //cell. + //vals += val + Environment.NewLine; + } + } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } } -} \ No newline at end of file +}