Convert Excel Cell into list of Checkbox using QAxObject

[npatil15]

Hello,

I'm adding some data in excel (.xls) file and got successful.
Now I want to covert a cell into a list of a checkbox.

So for user, when he click on any option in checkbox that data will show in that column. And if all checkbox selected then the total of all items will be shown in the same column.

I need what query I need for this. Check the sample example below that I want to implement.

Thanks :)

[hskoglund]

Hi, you can use the OLEObjects object in Excel to create checkboxes.
I've made an example: create a simple vanilla widgets app, in the .pro file add "axcontainer" to the QT += line, and then replace mainwindow.cpp with this one:

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include "qaxobject.h"
#include "qdebug.h"
#include "qtimer.h"

MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    QStringList names =
    {"Alpha","Bravo","Charlie","Delta","Echo","Foxtrot","Golf","Hotel","India",
    "Juliet","Kilo","Lima","Mike","November","Oscar","Papa","Qt","Romeo",
    "Sierra","Tango","Uniform","Victor","Whiskey","X-Ray","Yankee","Zulu",};

    auto excel = new QAxObject("Excel.Application");
    excel->dynamicCall("SetVisible(bool)",true);
    excel->querySubObject("Workbooks")->querySubObject("Add");  // a new worksheet
    auto sheet = excel->querySubObject("Worksheets")->querySubObject("Item(int)", 1);

    auto firstRow       = 3;
    auto nameColumn     = 1;
    auto checkBoxColumn = 3;
    auto totalColumn    = 5;

    auto r = firstRow - 1;
    auto widthAndHeight = 16;   // increase cell size for better visibility
    sheet->querySubObject("Cells(Int,Int)",r,nameColumn)->setProperty("Value","List Of Names");
    sheet->querySubObject("Columns(Int)",nameColumn)->setProperty("ColumnWidth",widthAndHeight);

    sheet->querySubObject("Cells(Int,Int)",r,checkBoxColumn)->setProperty("Value","Click after Pickup Options");
    sheet->querySubObject("Columns(Int)",checkBoxColumn)->setProperty("ColumnWidth",widthAndHeight);

    for (; (r < names.count()); ++r)
        sheet->querySubObject("Rows(Int)",r)->setProperty("RowHeight",widthAndHeight);

    r = firstRow;
    for (auto n : names)
        if (rand() % 2) // use 50% of the names
            sheet->querySubObject("Cells(Int,Int)",r++,nameColumn)->setProperty("Value",n);

    r = firstRow;
    for (;;)
    {
    // any name in this row?
        auto n = sheet->querySubObject("Cells(Int,Int)",r,nameColumn)->dynamicCall("Value");
        if (!n.isValid())
            break;  // no that's it

     // create a checkbox in sheet 1
        sheet->querySubObject("OLEOBjects()")->dynamicCall("Add(Forms.CheckBox.1)");

     // find the checkbox we just inserted
        QString cbName = "Checkbox" + sheet->querySubObject("OLEOBjects()")->dynamicCall("Count").toString();
        auto cb  = sheet->querySubObject(QString("OLEOBjects(" + cbName + ")").toLatin1());
        auto cbo = cb->querySubObject("Object");

    // position it and insert the names from column 1
        cbo->setProperty("Left",sheet->querySubObject("Cells(Int,Int)",r,checkBoxColumn)->dynamicCall("Left").toDouble());
        cbo->setProperty("Top" ,sheet->querySubObject("Cells(Int,Int)",r,checkBoxColumn)->dynamicCall("Top").toDouble());
        cbo->setProperty("Caption",n);
        cbo->setProperty("Value",rand() % 2); // click some (half) of them

        r++;    // next row please
    }

// prepare the poller for showing the total
    auto timer = new QTimer(this);
    timer->start(100);  // 10 times/second
    connect(timer,&QTimer::timeout,[excel,firstRow,nameColumn,totalColumn]
    {
        auto sheet = excel->querySubObject("Worksheets")->querySubObject("Item(int)",1);

        auto r = firstRow;
        QString total;
        for (;;)
        {
        // any name in this row?
            auto n = sheet->querySubObject("Cells(Int,Int)",r,nameColumn)->dynamicCall("Value");
            if (!n.isValid())
                break;  // no that's it

        // get the checkbox and see if it's clicked (true)
            QString cbName = "Checkbox" + QString::number(1 + r - firstRow);
            auto cb  = sheet->querySubObject(QString("OLEOBjects(" + cbName + ")").toLatin1());
            auto cbo = cb->querySubObject("Object");
            if (cbo->dynamicCall("Value").toBool())
                total += n.toString(); // clicked, add to the total

            r++;    // next row please
        }

    // show the total
        sheet->querySubObject("Cells(Int,Int)",firstRow,totalColumn)->setProperty("Value",total);
    });
}

MainWindow::~MainWindow()
{
    delete ui;
}

Note: I think it's outside the realm of QAxObject to subscribe to a clicking event from the checkboxes, so I dd a simple polling function instead. But this is just a demo, because if you interact with Excel, say by trying to quit, the polling loop will be unhappy and crash :-(

[npatil15]

@hskoglund said in Convert Excel Cell into list of Checkbox using QAxObject:

connect(timer,&QTimer::timeout,[excel,firstRow,nameColumn,totalColumn]

Hello,

Thanks for this, that would be the solution for me, but somehow my application is crashing on using the connect lambda method. So didn't able to run the complete code.

One more question, I need the list of checkboxes would work as a drop-down list when a user clicks on it.
In short, I want to add filtering for a specific row, to show content based on filter selection.
Thanks again :)

[hskoglund]

Hi, I've tried but I had no luck in constructing a drop-down list and populate it with checkboxes through a QAxObject (it could be possible using VBA or an Excel macro).