// ExStart:PrintTables 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)); } } } } }
public static void Run() { //Source directory string sourceDir = RunExamples.Get_SourceDirectory(); // Load workbook object Workbook workbook = new Workbook(sourceDir + "sampleFindQueryTablesAndListObjectsOfExternalDataConnections.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("FindQueryTablesAndListObjectsOfExternalDataConnections executed successfully.\r\n"); }
public static void Run() { // ExStart:FindQueryTablesAndListObjectsOfExternalDataConnections // 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(); // ExEnd:FindQueryTablesAndListObjectsOfExternalDataConnections }