Consider the following example file: PivotTableReferences.xlsx.zip (40.1 KB)
This file contains two external connections of type DataModelConnection. In MS Excel, we can go to Data > Queries & Connections > Connections to view the connection properties. In the Definition tab, the source of the connections is visible (one defined name Data3 and one sheet range Sheet3!$B$2:$C$114). See the following screenshots:
DataModelConnection1 Excel.png (60.1 KB)
DataModelConnection2 Excel.png (60.3 KB)
However, in Aspose.Cells only the standard properties of ExternalConnection are available. There is no property where we can read the source of the DataModelConnection. See the following screenshots:
DataModelConnection1 Aspose.png (35.6 KB)
DataModelConnection2 Aspose.png (36.4 KB)
We would expect to be able to read the values Data3 and Sheet3!$B$2:$C$114 somehow.
Can you confirm that the property is missing, and can you add this property to DataModelConnection?
We’re using Aspose.Cells 24.8.0 for .NET.
@perfectxl,
Thanks for the template XLSX file and screenshots.
I tested using the following sample code with latest version and it works as expected.
e.g.,
Sample code:
Workbook workbook = new Workbook("e:\\test2\\PivotTableReferences.xlsx");
for (int i = 0; i < workbook.DataConnections.Count; i++)
{
Aspose.Cells.ExternalConnections.ExternalConnection externalConnection = workbook.DataConnections[i];
if (externalConnection is DBConnection)
{
DBConnection dbConn = externalConnection as DBConnection;
Console.WriteLine("Connection Name: " + dbConn.Name);
Console.WriteLine("Info: " + dbConn.ConnectionInfo);
Console.WriteLine("Command type: " + dbConn.CommandType);
Console.WriteLine("Command: " + dbConn.Command);
Console.WriteLine("Description: " + dbConn.ConnectionDescription);
Console.WriteLine("Id: " + dbConn.ConnectionId);
Console.WriteLine("Credentials: " + dbConn.CredentialsMethodType);
Console.WriteLine("Type: " + dbConn.Type);
Console.WriteLine("########################");
}
else if (externalConnection is DataModelConnection)
{
DataModelConnection dataModelConnection = externalConnection as DataModelConnection;
Console.WriteLine("Connection Name: " + dataModelConnection.Name);
Console.WriteLine("Description info: " + dataModelConnection.ConnectionDescription);
Console.WriteLine("Connection ID: " + dataModelConnection.ConnectionId);
Console.WriteLine("Credentials: " + dataModelConnection.CredentialsMethodType);
Console.WriteLine("Type: " + dataModelConnection.Type);
Console.WriteLine("########################");
}
else
{
Console.WriteLine("Connection Name: " + workbook.DataConnections[i].Name);
Console.WriteLine("Description info: " + workbook.DataConnections[i].ConnectionDescription);
Console.WriteLine("Connection ID: " + workbook.DataConnections[i].ConnectionId);
Console.WriteLine("Credentials: " + workbook.DataConnections[i].CredentialsMethodType);
Console.WriteLine("Type: " + workbook.DataConnections[i].Type);
Console.WriteLine("########################");
}
}
Console output:
Connection Name: ThisWorkbookDataModel
Info: Data Model Connection
Command type: CubeName
Command: Model
Description: Data Model
Id: 1
Credentials: Integrated
Type: OLEDBBasedSource
########################
Connection Name: WorksheetConnection_PivotTableReferences.xlsx!Data3
Description info:
Connection ID: 2
Credentials: Integrated
Type: WorksheetDataModel
########################
Connection Name: WorksheetConnection_Sheet3!$B$2:$C$114
Description info:
Connection ID: 3
Credentials: Integrated
Type: WorksheetDataModel
########################
Let us know which properties do you need to extract (which is available in MS Excel but not by Aspose.Cells). We will check your issue further.
Thanks for investigating the issue. The property that is missing is the value in the “Command text” box in Excel, indicated with red in the provided screenshots:
DataModelConnection1 Excel.png (60.1 KB)
DataModelConnection2 Excel.png (60.3 KB)
For Connection ID 2 we would like to read the value Data3 and for Connection ID 3 we would like to read value Sheet3!$B$2:$C$114.
Note: this is not the Connection Name. The name of the connection can be changed independently and therefore we would like to read the original source of the DataModelConnection, as shown in Excel.
@perfectxl
We have not supported such kind of property yet. It is complicated feature and we are afraid we cannot support it soon. Anyways, you will get updated here when we have some news for it.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-56781
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@perfectxl
We made some changes to the API. Please try as the following codes with the next version (when it is released):
public void CELLSNET56781()
{
Workbook workbook = new Workbook(Constants.PivotTableSourcePath + "CELLSNET56781.xlsx");
ExternalConnection conn = workbook.Worksheets[0].PivotTables[0].GetSourceDataConnections()[0];
Assert.AreEqual("WorksheetConnection_Sheet3!$B$2:$C$114", conn.Name);
Assert.AreEqual(ExternalConnectionClassType.DataModel, conn.ClassType);
Assert.AreEqual(ConnectionDataSourceType.WorksheetDataModel, conn.SourceType);
Assert.IsNull(conn.ConnectionFile);
Assert.IsNull(conn.ConnectionString);
Assert.AreEqual("Sheet3!$B$2:$C$114", conn.Command);
conn = workbook.Worksheets[1].PivotTables[0].GetSourceDataConnections()[0];
Assert.AreEqual("Data3",conn.Command);
workbook.Save(Constants.PivotTableDestPath + "CELLSNET56781.xlsx");
}
The issues you have found earlier (filed as CELLSNET-56781) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi