Count rows for merged cells using QAxObject

Question

I need to read an Excel file with many merged cells. Suppose the first column contains the 'Category' value and the second column contains the 'sub_category' value. The problem is that each category cell might be a merged cell with multiple sub_categories in the second column. I need to know the number of rows witch this merged cell occupies. My chart is something like this:

Similar questions have already been asked but in different languages:

QString category = "";
QString sub_category = "";    

auto excel     = new QAxObject("Excel.Application");
auto workbooks = excel->querySubObject("Workbooks");
auto workbook  = workbooks->querySubObject("Open(const QString&)" ,"C:\\Users\\Orkideh\\Documents\\build-MyApp-Desktop_Qt_5_12_9_MSVC2017_64bit-Debug\\Book1.xlsx");
auto sheets    = workbook->querySubObject("Worksheets");
auto sheet     = sheets->querySubObject("Item(int)", 1);
   
// Read the rows 2..150
for (int r = 2; (r <= 150); ++r)
{
    auto cCell = sheet->querySubObject("Cells(int,int)",r,1);
    category = cCell->dynamicCall("Value()").toString().simplified();       
    
    // if each row in the excel file had only 1 category and 1 sub-category,
    // I could use the below code to get sub_category:
    
    // cCell = sheet->querySubObject("Cells(int,int)",r, 2);
    // sub_category = cCell->dynamicCall("Value()").toString().simplified();
    
    // But in fact, the cell with category value might be a merged cell with
    // unknown number of rows.
    // I need to know the number of rows that this merged cell occupies to connect 
    // each sub_category to its own category.
}

ArashV ArashV · Accepted Answer · 2021-02-08T16:35:48

I finally got my answer from here: https://blog.csdn.net/u18004660201/article/details/80350164

It's possible to get the number of the first line of the target cell and the first line of its next cell. It's doable by getting the cell's 'range' and 'MergeArea'.

    for (int row = 2; row <= 150; ++row)
{
    QAxObject *range = sheet->querySubObject("Cells(int,int)", row, 1);
    range =  range->querySubObject("MergeArea");
    const int nRowStart = range->property("Row").toInt();
    const int nRowEnd = range->property("Column").toInt();
    range =  sheet->querySubObject("Cells(int,int)", nRowStart, nRowEnd);

    cout << "Value: " << range->property("Value").toString() << endl
                    << "The first line of this cell: " << nRowStart << endl << endl;
    // Do some other tasks...
}

This code reads all lines but no longer prints null as the value of merged cells in the first column. nRowStart holds the number of the first line. Now, I can set a counter and increase it until the value of nRowStart gets new which means I have reached the next cell. The value of that counter is what I needed.

Count rows for merged cells using QAxObject

1 Answers