Render pivot table values

[rvkstudent]

Hi, i, using ClosedXML for creating pivot tables. i need to create multiple pivot tables and then need to compare them. I create pivot tables via PivotTables.AddNew(). All works good - i see pivot tables in my files. But when im try to get values - there is null. Here is my code to create pivot:

 IXLPivotTable pt;

                    var source = ws.Range(3, 1, excelRow - 2, excelColumn).AsTable();
                    var range = source.DataRange;
                    var header = ws.Range(3,1, 3, excelColumn);
                    var dataRange = ws.Range(header.FirstCell(), range.LastCell());
                    var name = "Сводная по дебиторке";
                    var wsPT = wb.Worksheets.Add(name);
                    pt = wsPT.PivotTables.AddNew(name, wsPT.Cell(1, 1), source);
                    pt.RowLabels.Add("Ответственный");
                    pt.RowLabels.Add("Филиал отгрузки");             
                    pt.ColumnLabels.Add("Документы");
                    pt.Values.Add("Сальдо в рублях");

                    wb.Save();
                  
                    int excelRow2 = wsPT.RowsUsed().Count();

Here excelRow2 value = 0. Its wrong. When i try to evaluate wsPT.Cell(1,2) its empty, but in my file its not empty. I cant read results of pivot...
When i open XL file in Windows, then just save it throught Excel and restart my code excelRow2 value changed to right and wsPT.Cell(1,2) have right value. May be i am doing something wrong?

May be there is another way to get the values from result pivot table?

[igitur]

Pivot tables are still work in progress. And one big feature that's missing is the "rendering" of pivot tables. Currently the pivot tables are configured, but there is no engine to calculate the values of the cells. Only when you open it in Excel does the pivot table get rendered.

[jahav]

Thanks to #1238 and #2178 (plus others), it's feasible to render the values into the sheet, at least for some.

Prerequisite: a way to generalize summarization function. It is desirable to reuse the function used in formulas. The summary functions are still mostly in expression-based format, thus unsuitable. For SUM, I could just use an array as a parameter. It will require an allocation, but good enough for first version.

Required steps:

1. String values in pivot cache values should always go into the shared items and the record should reference them through index value. That makes operation of comparing two strings into operation of comparing two index values and makes distinct operation on strings far easier.
2. For column+row fields, perform a distinct operation on values of the fields in the column/row. The end result will be a list of distinct N-tuples (N = number of fields for axis)
3. Perform sorting on the N-tuples.
4. Calculate values from records values for each intersection. Use summarization function.
5. Prepare layout - total size of table and where to put what value

• different report layouts
• subtotal position (none/top/bottom) (can be multiple for each value field)
• grand totals (can be multiple for each value field)
• blank after each item
• there can be multiple value fields, not just one.

6. Write values to the sheet