Trova Tabelle di Query e Oggetti Elenco relativi a Connessioni Dati Esterne

Trova query tabelle e oggetti elenco relativi alle connessioni esterne dei dati

I seguenti codici di esempio con file Excel di esempio spiegano come trovare tabelle di query e oggetti elenco correlati a una connessione dati esterne.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Load workbook object
Workbook workbook = new Workbook(dataDir + "sample.xlsm");
// Check all the connections inside the workbook
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();
}
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
public static void PrintTables(Workbook workbook, Aspose.Cells.ExternalConnections.ExternalConnection ec)
{
// Iterate all the worksheets
for (int j = 0; j < workbook.Worksheets.Count; j++)
{
Worksheet worksheet = workbook.Worksheets[j];
// Check all the query tables in a worksheet
for (int k = 0; k < worksheet.QueryTables.Count; k++)
{
Aspose.Cells.QueryTable qt = worksheet.QueryTables[k];
// Check if query table is related to this external connection
if (ec.Id == qt.ConnectionId
&& qt.ConnectionId >= 0)
{
// Print the query table name and print its refersto range
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);
}
}
}
}
// Iterate all the list objects in this worksheet
for (int k = 0; k < worksheet.ListObjects.Count; k++)
{
ListObject table = worksheet.ListObjects[k];
// Check the data source type if it is query table
if (table.DataSourceType == Aspose.Cells.Tables.TableDataSourceType.QueryTable)
{
// Access the query table related to list object
QueryTable qt = table.QueryTable;
// Check if query table is related to this external connection
if (ec.Id == qt.ConnectionId
&& qt.ConnectionId >= 0)
{
// Print the query table name and print its refersto range
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));
}
}
}
}
}

Il seguente è l’output della console dell’esecuzione dei codici di esempio precedenti con questo file Excel di esempio.

 connection: AAPL Connection

querytable hp?s=AAPL+Historical+Prices

refersto: =Sheet1!$Q$1:$W$69

connection: BOSL066360W7_SQLEXPRESS Test

querytable BOSL066360W7_SQLEXPRESS Test

Table Table_BOSL066360W7_SQLEXPRESS_Test

refersto: Sheet1!A1:B3

connection: BOSL066360W7_SQLEXPRESS Test1

querytable BOSL066360W7_SQLEXPRESS Test_1

Table Table_BOSL066360W7_SQLEXPRESS_Test_1

refersto: Sheet1!D1:E2

connection: UWTI Connection

querytable hp?s=UWTI+Historical+Prices

refersto: =Sheet1!$H$1:$N$69