How-To Read Value from PivotTable ?

[APspireUser]

Hello,

I have been successful in opening workbook, then loading pivot table into a variable. I can apply necessary filters to pivot table and i can calculate the table without issues. However how do i read values from the refreshed pivottable?

Code: Select all

var pt = workbook.Worksheets[warConfig.SheetName].PivotTables["PivotTable1"] as XlsPivotTable;
var camFilter = new PivotReportFilter(warConfig.FilterField , true);
camFilter.IsMultipleSelect = true;     
camFilter.FilterItemStrings = new List<string> { warConfig.FilterValue };     
var valueTypeFilter = new PivotReportFilter("Value Type", true);
valueTypeFilter.IsMultipleSelect = false;
valueTypeFilter.FilterItemStrings = ["Hrs"];
pt.ReportFilters.Add(camFilter);
pt.ReportFilters.Add(valueTypeFilter);
pt.CalculateData();


I Tried pt.RowFields & pt.DataFields this just returns the model structure no values.

[APspireUser]

This is where i loose the data row and it becomes only 1 row in the PivotTabl.Location.Rows area.

Code: Select all

        var camFilter = new PivotReportFilter(warConfig.FilterField , true)
        {
            IsMultipleSelect = false,
            FieldString = warConfig.FilterValue,
            FieldName = warConfig.FilterField
        };
       
        pt?.ReportFilters.Add(camFilter); <-- HERE IS WHERE I LOOSE MY DATA
        pt?.CalculateData();                     <--THIS DOESNT UPDATE AND SHOW A DATA ROW, STILL ONLY A HEADER ROW AVAILABLE


[Doris.Liu]

Hi,

Thanks for your inquiry.
Please provide your excel test document for our investigation. Thanks in advance.

Sincerely,
Doris
E-iceblue support team

[APspireUser]

I cant upload the file but after further investigation what i found is that the PivotTable source range is only the header. So im guessing the data is only stored in the PivotCache which allows me to change filters manually in the workbook through the UI. but when i do it in code it somehow refreshes the pivotcache from the now blank source range area, thus im getting no rows. Is there a way to change filters without loosing the pivotcache?

[Doris.Liu]

Hello,

Thank you for your further feedback.
I have simulated a test with an Excel document containing a PivotTable, and I did not encounter any data loss when adding a new filter. I have attached a screenshot below for your reference. If you are not using our latest version of Spire.XLS 14.5.3, please download the latest version from the link and test it. And you can refer to the code snippet below to read the data from PivotTable.

Code: Select all

            IXLSRange loc = pt.Location;
            for (int i = loc.Row; i <= loc.LastRow; i++)
                for (int j = loc.Column; j <= loc.LastColumn; j++)
                {
                    IXLSRange cell = sheet.Range[i, j];
                    Debug.WriteLine(cell.Value);
                }

If I have misunderstood your issue, please provide us with the file and screenshots for further investigation. You can send the file to 'support@e-iceblue.com' with sensitive data redacted. We assure you that your file information will not be leaked or misused.
Thanks in advance for your assistance.

Sincerely,
Doris
E-iceblue support team