Reading and Writing Query Table of Worksheet

Aspose.Cells provides Worksheet.QueryTables collection which returns the object of type QueryTable by index. It has the following two properties

• QueryTable.AdjustColumnWidth
• QueryTable.PreserveFormatting

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.

You can download the source Excel file used in this code and the output Excel file generated by the code from the following links.

• Source Excel File
• Output Excel File

// 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);

// Create workbook from source excel file
Workbook workbook = new Workbook(dataDir + "Sample.xlsx");

// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Access first Query Table
QueryTable qt = worksheet.QueryTables[0];

// Print Query Table Data
Console.WriteLine("Adjust Column Width: " + qt.AdjustColumnWidth);
Console.WriteLine("Preserve Formatting: " + qt.PreserveFormatting);

// Now set Preserve Formatting to true
qt.PreserveFormatting = true;

// Save the workbook
workbook.Save(dataDir + "Output_out.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 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. Sample file can be downloaded here.

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

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

// Display the address(range) of result range of query table
Console.WriteLine(wb.Worksheets[0].QueryTables[0].ResultRange.Address);