Reading and Writing Query Table of Worksheet

Aspose.Cells provides Worksheet.getQueryTables() collection which returns the QueryTableCollection. To get a specific QueryTable, use the QueryTableCollection.get() property and pass the index of the QueryTable. The QueryTable class has the following two properties for adjusting the QueryTable.

• QueryTable.getAdjustColumnWidth()
• QueryTable.getPreserveFormatting()

These are both boolean values. You can view them in Microsoft Excel via Data > Connections > Properties.

Reading and Writing Query Table of Worksheet

The following sample code reads the first QueryTable of the first worksheet and then prints both of the QueryTable properties. Then it sets the QueryTable.PreserveFormatting to true.

The following screenshot shows the source excel file used in the code and its properties showing both of the QueryTable values.

The following screenshot shows the output excel file generated by the code and its properties showing both of the QueryTable values. As you can see the Preserved Formatting checkbox is checked now.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
String dataDir = Utils.getDataDir(ReadingAndWritingQueryTable.class);
// Create workbook from source excel file
Workbook workbook = new Workbook(dataDir + "Sample.xlsx");

// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);

// Access first Query Table
QueryTable qt = worksheet.getQueryTables().get(0);

// Print Query Table Data
System.out.println("Adjust Column Width: " + qt.getAdjustColumnWidth());
System.out.println("Preserve Formatting: " + qt.getPreserveFormatting());

// Now set Preserve Formatting to true
qt.setPreserveFormatting(true);

// Save the workbook
workbook.save(dataDir + "Output.xlsx");

Console Output

Here is the console output of the above sample code

 Adjust Column Width: true

Preserve Formatting: false

Retrieve query table result range

Aspose.Cells provides the option to read the address i.e. result range of cells for a query table. Following code demonstrates this feature by reading the address of result range for a query table. The sample file can be downloaded here.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

// Create workbook from source excel file
Workbook wb = new Workbook("Query TXT.xlsx");

// Display the address(range) of result range of query table
System.out.println(wb.getWorksheets().get(0).getQueryTables().get(0).getResultRange().getAddress());