Invalid value for external reference in DataSource field of Pivot Table

Aspose.Cells Product Family
1

Steps to reproduce:

  1. Go to a folder, for example C:\Users\Willem-Jan\Desktop.
  2. Create a new file Data.xlsx and add some data.
  3. Create a new subfolder, for example TEST.
  4. Create a new file PivotTable.xlsx in that folder.
  5. Make a new Pivot Table in this file with data source the data in Data.xlsx.

Now there is a problem with the DataSource field returned by Aspose. Consider the following code:

var workbook = new Workbook("PivotTable.xlsx");
var pivotTable = workbook.Worksheets["Sheet1"].PivotTables[0];
var dataSource = pivotTable.DataSource[0];

The value returned by Aspose.Cells is: '\Users\Willem-Jan\Desktop\[Data.xlsx]Sheet1'!B2:C12
However, the expected value is: 'C:\Users\Willem-Jan\Desktop\[Data.xlsx]Sheet1'!B2:C12

We’re using Aspose.Cells v21.12.0. Here is a ZIP with the test files: PivotTables.zip (15.5 KB)

2

@perfectxl,

I noticed the issue using Aspose.Cells APIs using your template file. But when I checked the data source manually in MS Excel, I got the same results. I downloaded your attached zipped archive. I created a folder “TestFolder” on e drive. Now I copied “PivotTables.zip” archive to it. I extracted the zipped archive into the folder. I then opened the “PivotTable.xlsx” file into MS Excel. I clicked on the pivot table and check its data source. I got the same results as PivotTable.DataSource attribute is giving. See the screenshots for your reference.
sc_shot1.png (34.5 KB)
sc_shot2.png (77.8 KB)

3

Thank you for your response. I tried it again, downloaded “PivotTable.zip” and extracted it to a new location (D: drive). Now I’m able to open “PivotTables.xlsx” and MS Excel shows the full path for the Pivot Table data source (even when that file doesn’t exist), including the drive letter. See the screenshots below.

Screenshot 1.png (7.3 KB)
Screenshot 2.png (33.8 KB)

I don’t know how MS Excel determines this location, but it is the original location I selected when creating the Pivot Table. Maybe Excel can determine the path correctly on my system, but not on yours? Or maybe this is a bug in Excel and something Aspose.Cells cannot fix?

Just for reference, I dived in the XML of “PivotTables.xlsx” and I found two locations where the location is stored. See the screenshots below.

Screenshot 3.png (50.6 KB)
Screenshot 4.png (83.9 KB)

Can you reproduce the issue if you create the PivotTables.xlsx file yourself? See the steps in my first post.

4

@perfectxl,

Thanks for sharing further details and screenshots.

We have logged a ticket with an id “CELLSNET-50301” for your issue. We will thoroughly analyze and investigate your issue and if we found it is an issue with the APIs, we will surely sort it out soon.

Once we have an update on it, we will let you know here.

5

@perfectxl,

This is to inform you that your issue has been resolved. This fix will be included in the next regular release of Aspose.Cells for .NET v22.2. The release is scheduled for the second week of February 2022. We will notify you once the next version is published.

6

The issues you have found earlier (filed as CELLSNET-50301) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi