Get target/output cell address for an ExternalConnection

Aspose.Cells Product Family
1

Hello,


Does anyone know how to get the list object and query table for a specific external connection? I’m trying to get the output range (cell address) for an ExternalConnection. It does not appear you can use object names to determine if you have the ListObject for an ExternalConnection, e.g.:
Aspose.Cells.Tables.ListObject.DisplayName != Aspose.Cells.ExternalConnections.ExternalConnection.Name

// For each external connection, get the target/output range.
for (int i = 0; i < workbook.DataConnections.Count; i++) {
Aspose.Cells.ExternalConnections.ExternalConnection externalConnection = workbook.DataConnections[i];
for (int j = 0; j < workbook.Worksheets.Count; j++) {
Worksheet worksheet = workbook.Worksheets[j];

for (int k = 0; k < worksheet.ListObjects.Count; k++) {
Aspose.Cells.Tables.ListObject listObject = worksheet.ListObjects[k];
}

for (int k = 0; k < worksheet.QueryTables.Count; k++) {
Aspose.Cells.QueryTable queryTable = worksheet.QueryTables[k];
}

}

}
2

Hi,

Thanks for your posting and using Aspose.Cells.

For list objects, please try ListObject.DisplayName property.

For query tables, please provide us your sample excel file and explain your requirements in detail with screenshots.

It will help us investigate your issue and we will update you with a sample code. In case, it is a New Feature, we will log a request for it in our database for its implementation. Thanks for your cooperation in this regard.

3

The goal is to get a connection’s target/output cell, which seems to be stored in a QueryTable object (in Excel). It does not appear that you can use object names to make links:


QueryTable.Name != ListObject.DisplayName != ExternalConnection.Name

I can get a connections target/output cell using VBA (see attached). Depending on the connection type, by iterating over each worksheet in the workbook, I can back out the WorkbookConnection by iterating over the Worksheet.ListObjects, or Worksheet.QueryTables. See the “PrintInfo” method in the “ThisWorkbook” module in the attachment.

“PrintInfo” output:
Target for workbook connection “AAPL Connection” is "[C:\Users\a500965\Desktop\Book1.xlsm]Sheet1!$Q$1"
Target for workbook connection “UWTI Connection” is "[C:\Users\a500965\Desktop\Book1.xlsm]Sheet1!$H$1"
Target for list object “Table_BOSL066360W7_SQLEXPRESS_Test” is “[C:\Users\a500965\Desktop\Book1.xlsm]Sheet1!$A$1”(connection name is “NULL”) – THIS CONNECTION WAS DELETED, SO CONNECTION NAME IS NULL, BUT THE LIST OBJECT STILL EXISTS
Target for list object “Table_BOSL066360W7_SQLEXPRESS_Test_1” is “[C:\Users\a500965\Desktop\Book1.xlsm]Sheet1!$D$1”(connection name is “NULL”) – THIS CONNECTION WAS DELETED, SO CONNECTION NAME IS NULL, BUT THE LIST OBJECT STILL EXISTS
Target for query table “hp?s=UWTI+Historical+Prices” is “[C:\Users\a500965\Desktop\Book1.xlsm]Sheet1!$H$1”(connection name is “UWTI Connection”)
Target for query table “hp?s=AAPL+Historical+Prices” is “[C:\Users\a500965\Desktop\Book1.xlsm]Sheet1!$Q$1”(connection name is “AAPL Connection”)

It doesn’t look like I can do the same via Aspose.Cells API. Can you confirm?

Thanks,




Also – and perhaps I should make another post for this issue – it appears that when you are accessing an .xls (97-2003) workbook, the Workbook.DataConnections collection is empty even though the workbook has data connections. Save the attachment as an .xls file, and try to iterate over the data connections using Aspose, and you won’t be able to.
4

Hi,

Thanks for all the details and considering Aspose.Cells.

We have looked into your requirements and it seems to be a new feature. But we need your more help. Could you please show all these things (as you have shown in textual output) visibly in screenshots just like I have shown Tables in a screenshot. We will then log a New Feature request for this issue with all the details you provided. Thanks for your cooperation in this regard.

5
I'm not sure what to show via screenshot.

I've attached an image of my worksheet, which shows the external connection data on my worksheet. Next, I've attached an image of the connections dialog, which shows two (remaining) external data connections in my workbook (I had two other connections which connected to a local instance of SQL Server, which I deleted, but the ListObjects remain in the worksheet). Programatically, I need to know where in the workbook the data for these two external connections is stored. I can do this using VBA (which is attached in my previous reply), but not via the Aspose API.

Also,the following is a bug, not a new feature, which I (of course ;)) need right away:
It appears that when you are accessing an .xls (97-2003) workbook, the Workbook.DataConnections collection is empty even though the workbook has data connections. You may reproduce saving the workbook attachment from my previous replay as an .xls file, and try to iterate over the data connections using Aspose. Unfortunately, I am unable to iterate the .xls connections.

using (FileStream fileStream = new FileStream(fileInfo.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
Workbook workbook = new Workbook(fileStream);
for (int i = 0; i < workbook.DataConnections.Count; i++) {
// .xls workbook (97-2003) does not show any data connections, even though data connections exist in the workbook
}
}

Thanks!


6

Hi,

Thanks for all the details and screenshots and using Aspose.Cells.

We have logged this issue in our database for investigation. We will look into it and implement your requirement if possible. In case of a bug, we will fix it. Once, there is some fix available for you or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44356 - Get target or output cell address for an ExternalConnection

7

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-44356 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

8

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.7.2.4.

Please see the following code.

C#

public void CELLSNET44356()
{
Workbook workbook = new Workbook(Constants.sourcePath + “CELLSNET44356.xlsm”);

for (int i = 0; i < workbook.DataConnections.Count; i++)
{
Aspose.Cells.ExternalConnections.ExternalConnection externalConnection = workbook.DataConnections[i];
Console.WriteLine(“connection: " + externalConnection.Name);
PrintTables(workbook, externalConnection);
Console.WriteLine();
}

workbook.Save(Constants.destPath + “CELLSNET44356.xlsm”);
}

public static void PrintTables(Workbook workbook, Aspose.Cells.ExternalConnections.ExternalConnection ec)
{
for (int j = 0; j < workbook.Worksheets.Count; j++)
{
Worksheet worksheet = workbook.Worksheets[j];

for (int k = 0; k < worksheet.QueryTables.Count; k++)
{
Aspose.Cells.QueryTable qt = worksheet.QueryTables[k];
if (ec.Id == qt.ConnectionId
&& qt.ConnectionId >= 0)
{
Console.WriteLine(“querytable " + qt.Name);
string n = qt.Name.Replace(‘+’, ‘‘).Replace(’=', '’);
Name name = workbook.Worksheets.Names[”'” + worksheet.Name + “'!” + n];
if (name != null)
{
Range range = name.GetRange();
if (range != null)
{
Console.WriteLine("refersto: " + range.RefersTo);
}
}
}
}

for (int k = 0; k < worksheet.ListObjects.Count; k++)
{
ListObject table = worksheet.ListObjects[k];
if (table.DataSourceType == Aspose.Cells.Tables.TableDataSourceType.QueryTable)
{

QueryTable qt = table.QueryTable;
if (ec.Id == qt.ConnectionId
&& qt.ConnectionId >= 0)
{
Console.WriteLine("querytable " + qt.Name);
Console.WriteLine("Table " + table.DisplayName);
Console.WriteLine("refersto: " + worksheet.Name + “!” + CellsHelper.CellIndexToName(table.StartRow, table.StartColumn) + “:” + CellsHelper.CellIndexToName(table.EndRow, table.EndColumn));
}
}
}
}
}

BTW, we could not find a direct relation of QueryTable and target address in the worksheet if the QueryTable belongs to the worksheet, but we found if we remove all defined names, the file will be corrupted, so we depend on name to get the target address. If you have good solution, please share it with us.

9

The issues you have found earlier (filed as CELLSNET-44356) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.