Incorrect external data connections

Aspose.Cells Product Family
,
1

We have the following file: Objects Demo.xlsm.zip (313.6 KB)
Aspose.Cells 20.9.0 is returning some incorrect external data connections with this file.

According to MS Excel, the file has 4 queries and 1 normal connection:

image.png (6.5 KB)
image.png (3.5 KB)

However, Aspose.Cells is giving 7 data connections, some with problems.
See this example code for the details:

var workbook = new Workbook(“Objects Demo.xlsm”);

var externalConnections = workbook.DataConnections;

var connection1 = externalConnections[0];
// Name: ModelConnection_ExternalData_3
// INCORRECT: not visible in MS Excel

var connection2 = externalConnections[1];
// Name: Query - qry_Externalref2_tblData1
// INCORRECT: PowerQueryFormula is missing

var connection3 = externalConnections[2];
// Name: Query - qry_tblData1
// INCORRECT: PowerQueryFormula is missing

var connection4 = externalConnections[3];
// Name: Query - qry_tblData2
// INCORRECT: PowerQueryFormula is missing

var connection5 = externalConnections[4];
// Name: Query - qryInternalAppend
// OK

var connection6 = externalConnections[5];
// Name: Query from MS Access Database
// OK

var connection7 = externalConnections[6];
var name7 = connection7.Name;
// Name: ThisWorkbookDataModel
// INCORRECT: not visible in MS Excel

2

@infotron,
This issue is reproduced here and logged in our database for further investigation and a fix. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-47644 - Different external connections fetched as compared to MS Excel

3

@infotron,

  1. Some connections are not visible in MS Excel. Aspose.Cells simply parsed xl/connections.xml(unzipped the file) and returned all connections in it. Also, there is not a property which indicates whether it’s visible in MS Excel. We only find the connection is visible when ExternalConnection.Type is ConnectionDataSourceType.ODBCBasedSource.

  2. PowerQueryFormula is missing. We will try to find PowerQueryFormula of Data Model Connection.

4
  1. Okay, that is clear. We can filter on the ExternalConnection.Type property if we want to show connections only visible in Excel.
  2. Let us know if there is a fix for the missing PowerQueryFormula for this type of external connection.
5

@infotron,

  1. Yes, you can do that.
  2. Please spare us little time to evaluate the issue. Sure, once we figure it out, we will let you know.
6

@infotron,
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

7

@infotron,

Please try our latest version/fix: Aspose.Cells for .NET v20.9.13 (attached)

We have fixed the issue where PowerQueryFormula was missing

Let us know your feedback.
Aspose.Cells20.9.13 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.9.13 For .Net4.0.Zip (5.4 MB)

8

I can confirm that the issue related to the missing PowerQueryFormula is fixed in Aspose.Cells 20.9.13. Thank you!

9

@infotron,

Thanks for your feedback.

Good to know that your issue (related to the missing PowerQueryFormula) is resolved by the new fix. In the event of further query or issue, feel free to write us back.

10

The issues you have found earlier (filed as CELLSNET-47644) have been fixed in Aspose.Cells for .NET v20.10. This message was posted using Bugs notification tool by Amjad_Sahi. You may also get the version @ Nuget repos. (NuGet Gallery | Aspose.Cells 20.10.0).