We have the following test file: DataModelConnection.xlsx.zip (26.9 KB)
This spreadsheet contains one pivot table, with the data source added to data model. Therefore, this file also contains one data model connection. Now we have the following test code:
var workbook = new Workbook("DataModelConnection.xlsx");
var dataModelConnection = workbook.DataConnections.OfType<DataModelConnection>().First();
var command = dataModelConnection.Command; // expected: Sheet1!$A$1:$B$10, actual: 1
var pivotTable = workbook.Worksheets[0].PivotTables[0];
var dataSource = pivotTable.DataSource; // null
var dataModelSource = pivotTable.GetSourceDataConnections()[0].Command; // expected: Sheet1!$A$1:$B$10, actual: 1
The value of dataModelConnection.Command returned by Aspose.Cells is incorrect. In Excel, if you go to Data > Queries & Connections > tab Connections > right click WorksheetConnection_1 > Properties > tab Definition, you see the correct value under “Command text”.
The value of pivotTable.DataSource or pivotTable.GetSourceDataConnections()[0].Command returned by Aspose.Cells is also incorrect. In Excel, if you go to PivotTable Analyze > Change Data Source, you see the correct value in the “Table/Range” field.
We’re using Aspose.Cells 24.10.0 for .NET.
Hint: we found in the XML that the correct value can be found in a hidden defined name: image.png (2.5 KB)