The PivotTable field name is invalid for 18.2 dll version

Hi,

I have an issue with PivotTable field.

When I have nothing as data on an excel sheet and calling “RefreshData” method its give “The PivotTable field name is invalid” this type of error.

I have changed sample file to remove data rows and tried with sample code given at below URL.

https://github.com/aspose-cells/Aspose.Cells-for-.NET/blob/master/Examples/CSharp/PivotTable/RefreshAndCalculateItems.cs

@TomP,

Thanks for providing us details.

Please provide us your sample file and paste your sample code (runnable) to reproduce the issue, we will check it soon.

By the way, if you have nothing to be set as data source for PivotTable, you might get the exception when refreshing the PivotTable or calculating the data because Aspose.Cells would require valid source data so it could refresh PivotTable accordingly.

we are providing a data source but the exception is occurring when the datatable behind the datasource has 0 rows. How can we handle the use case of no rows?

@TomP

To create PivotTable, you must have at least 2 rows. And one of them must be header row. Otherwise, Microsoft Excel will not let you create Pivot Table.

Please see the screenshot and its prompt message asking for at least 2 rows for your reference.

Screenshot:

thanks. can you suggest a workaround to handle the condition where there are no data rows coming back from the database?

@TomP

If there are no data rows, your pivot table will be lost, So you should handle this condition as a special case. You should either retain your existing pivot table or lose it and next time when there are data rows, you may create it from scratch. You may also keep at least 1 data row when there is no data rows and fill that rows with some dummy values so that your pivot table and its structure does not get lost.

Hi, we are getting this message “The PivotTable field name is invalid” exception with version 18.2 we are using.
We are facing an issue with aspose cells. We were unable to reproduce the same behavior with sample code hence sending you details. Hope you could figure out and tell us what the issue is.

Some Background:

If you open demo.xlsx (in attached zip file), you will find a sheet called as “Limits Export”. The data in that sheet is added on fly from feed.csv (in attached zip file). Following code is run on the merged workbook. When we tried to replicate the behavior by saving the merged workbook our disk and putting that as input to our sample code, it did not give us that exception. Hence, it looks like the issue only happens when it is done on the fly.

Note: When same feed data is copied into the excel manually and refreshed using excel option manually there is no error / exception thrown by excel hence we think the issue with Aspose DLL which may not be handling a specific scenario.

Code Fragment
Workbook _workbook = new Workbook(@“demo.xlsx”);
_workbook.Settings.CreateCalcChain = false;

        _workbook.CalculateFormula(false);


        foreach (var ws in _workbook.Worksheets)
        {
            foreach (var pt in ws.PivotTables)
            {
                try
                {
                    var tmp = pt.RefreshDataFlag;
                    pt.RefreshDataFlag = true;
                    pt.RefreshData();
                    pt.CalculateData();
                    pt.CalculateRange();
                    pt.RefreshDataFlag = tmp;
                }
                catch (Exception e)
                {

                }
            }
        }

Please also refer to attached image files for some more details which may help you to understand the issue.
ExceptionScreenshot.png (13.9 KB)
StackTrace.png (2.6 KB)
IssueInOriginalCode.png (34.2 KB)
NoIssueInSampleCode.png (26.3 KB)
ExcelFiles.zip (164.7 KB)

@rajendrak,

We are sorry that we could not re-produce this issue using the provided information. First you may please test this issue using latest version Aspose.Cells for .NET 18.8.5 by downloading from the following link:

If issue is not resolved, please provide some working project which can be used to reproduce this issue or provide complete steps to reproduce this issue here.