Reading and Writing Query Table of Worksheet with C++

Aspose.Cells provides the Worksheet.QueryTables collection, which returns an 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

#include <iostream>
#include "Aspose.Cells.h"

using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Create workbook from source excel file
    Workbook workbook(srcDir + u"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
    std::cout << "Adjust Column Width: " << qt.GetAdjustColumnWidth() << std::endl;
    std::cout << "Preserve Formatting: " << qt.GetPreserveFormatting() << std::endl;

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

    // Save the workbook
    workbook.Save(outDir + u"Output_out.xlsx");

    std::cout << "Query Table properties updated and workbook saved successfully!" << std::endl;

    Aspose::Cells::Cleanup();
}

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 an option to read the address (i.e., result range of cells) for a query table. The following code demonstrates this feature by reading the address of the result range for a query table. The sample file can be downloaded here.

#include <iostream>
#include <locale>
#include <codecvt>
#include "Aspose.Cells.h"

using namespace Aspose::Cells;

std::string convert_u16_to_string(const char16_t* data) {
    std::wstring_convert<std::codecvt_utf8_utf16<char16_t>, char16_t> converter;
    return converter.to_bytes(data);
}

int main()
{
    Aspose::Cells::Startup();

    Workbook wb(u"Query TXT.xlsx");
    std::cout << convert_u16_to_string(wb.GetWorksheets().Get(0).GetQueryTables().Get(0).GetResultRange().GetAddress().GetData()) << std::endl;

    Aspose::Cells::Cleanup();
}