查找与外部数据连接相关的查询表和列表对象

查找与外部数据连接相关的查询表和列表对象

有时，您需要找出与某些外部数据连接相关的查询表和列表对象。查询表与具有连接 ID 的外部数据连接对象相关，而列表对象与查询表相关。

以下示例代码说明了如何查找与外部数据连接相关的查询表和列表对象。该代码使用示例 excel 文件您可以从提供的链接下载。您还可以在本文底部看到此示例代码的输出。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
public static void main(String[] args) throws Exception {
	// The path to the documents directory
	String dataDir = Utils.getSharedDataDir(FindReferenceCellsFromExternalConnection.class) + "tables/";

	// Load workbook object
	Workbook workbook = new Workbook(dataDir + "sample.xlsm");

	// Check all the connections inside the workbook
	for (int i = 0; i < workbook.getDataConnections().getCount(); i++) {
		ExternalConnection externalConnection = workbook.getDataConnections().get(i);
		System.out.println("connection: " + externalConnection.getName());
		PrintTables(workbook, externalConnection);
		System.out.println();
	}
}

public static void PrintTables(Workbook workbook, ExternalConnection ec) {
	// Iterate all the worksheets
	for (int j = 0; j < workbook.getWorksheets().getCount(); j++) {
		Worksheet worksheet = workbook.getWorksheets().get(j);

		// Check all the query tables in a worksheet
		for (int k = 0; k < worksheet.getQueryTables().getCount(); k++) {
			QueryTable qt = worksheet.getQueryTables().get(k);

			// Check if query table is related to this external connection
			if (ec.getId() == qt.getConnectionId() && qt.getConnectionId() >= 0) {
				// Print the query table name and print its "Refers To"
				// range
				System.out.println("querytable " + qt.getName());
				String n = qt.getName().replace('+', '_').replace('=', '_');
				Name name = workbook.getWorksheets().getNames().get("'" + worksheet.getName() + "'!" + n);
				if (name != null) {
					Range range = name.getRange();
					if (range != null) {
						System.out.println("Refers To: " + range.getRefersTo());
					}
				}
			}
		}

		// Iterate all the list objects in this worksheet
		for (int k = 0; k < worksheet.getListObjects().getCount(); k++) {
			ListObject table = worksheet.getListObjects().get(k);

			// Check the data source type if it is query table
			if (table.getDataSourceType() == TableDataSourceType.QUERY_TABLE) {
				// Access the query table related to list object
				QueryTable qt = table.getQueryTable();

				// Check if query table is related to this external
				// connection
				if (ec.getId() == qt.getConnectionId() && qt.getConnectionId() >= 0) {
					// Print the query table name and print its refersto
					// range
					System.out.println("querytable " + qt.getName());
					System.out.println("Table " + table.getDisplayName());
					System.out.println("refersto: " + worksheet.getName() + "!"
							+ CellsHelper.cellIndexToName(table.getStartRow(), table.getStartColumn()) + ":"
							+ CellsHelper.cellIndexToName(table.getEndRow(), table.getEndColumn()));
				}
			}
		}
	}
}// end-PrintTables

控制台输出

这是使用此示例代码的上述示例代码的控制台输出示例 excel 文件.

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