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.

from aspose.cells import CellsHelper, Workbook
from aspose.cells.tables import TableDataSourceType
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
# The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
# Load workbook object
workbook = Workbook(dataDir + "sample.xlsm")
# Check all the connections inside the workbook
for i in range(len(workbook.data_connections)):
externalConnection = workbook.data_connections[i]
print("connection: " + externalConnection.name)
# Iterate all the worksheets
for j in range(len(workbook.worksheets)):
worksheet = workbook.worksheets[j]
# Check all the query tables in a worksheet
for k in range(len(worksheet.query_tables)):
qt = worksheet.query_tables[k]
# Check if query table is related to this external connection
if externalConnection.id == qt.connection_id and qt.connection_id >= 0:
# Print the query table name and print its refersto range
print("querytable " + qt.name)
n = qt.name
n = n.replace("+", "_")
n = n.replace("=", "_")
name = workbook.worksheets.names.get("'" + worksheet.name + "'!" + n)
if name != None:
range = name.get_range()
if range != None:
print("refersto: " + range.refers_to)
# Iterate all the list objects in this worksheet
for k in range(len(worksheet.list_objects)):
table = worksheet.list_objects[k]
# Check the data source type if it is query table
if table.data_source_type == TableDataSourceType.QUERY_TABLE:
# Access the query table related to list object
qt = table.query_table
# Check if query table is related to this external connection
if externalConnection.id == qt.connection_id and qt.connection_id >= 0:
# Print the query table name and print its refersto range
print("querytable " + qt.name)
print("Table " + table.display_name)
print("refersto: " + worksheet.name + "!" + CellsHelper.cell_index_to_name(table.start_row, table.start_column) + ":" + CellsHelper.cell_index_to_name(table.end_row, table.end_column))
print()
print("Press any key to continue...")
input()

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