How can i read an Excel worksheet from Qt application?

Eduardo12l

I read the Qt documentation about it but it does not specify so much.
I know that ActiveX and QODBC are some ways.
But i read that ActiveX can be gotten in commercial Qt and QODBC ,according to Qt link (http://wiki.qt.io/Handling_Microsoft_Excel_file_format), has some bugs or problems.
Is there any other way?

hskoglund

Hi, I wrote an app before that reads Excel files, but it requires Excel (or Microsoft Office) to be installed.

Do you need to read Excel worksheets from a Qt application on a PC without Microsoft Office or Excel installed? If not, then it's pretty easy, you just "remote-control" Excel using ActiveQt,

Eduardo12l

Hi, @hskoglund many thank for replying. How can i remote-control an Excel using ActiveQt?

hskoglund

Hi try something like this:

create a simple Excel test file, say C:\Temp\Test.xslx" which contains prime numbers in column A, eg. "3" "5" "7" "11" etc. (one in each row).

Then create an empty Widgets app, in the .pro file add "axcontainer":
QT += core gui axcontainer
add #include "qaxobject.h" and #include "qdebug.h" in mainwindow.cpp
and then add this beautiful code in mainwindow.cpp after "ui->setupUi(this)";

auto excel     = new QAxObject("Excel.Application");
auto workbooks = excel->querySubObject("Workbooks");
auto workbook  = workbooks->querySubObject("Open(const QString&)","c:\\temp\\test.xlsx");
auto sheets    = workbook->querySubObject("Worksheets");
auto sheet     = sheets->querySubObject("Item(int)", 1);

// read the first cells in row 1..5
for (int r = 1; (r <= 5); ++r)
{
    auto cCell = sheet->querySubObject("Cells(int,int)",r,1);
    qDebug() << cCell->dynamicCall("Value()").toInt();
}

If all goes well you should see this in Qt Creator:

Starting C:\Projects\build-ExcelTest-Desktop_Qt_5_9_0_MSVC2015_32bit-Release\release\ExcelTest.exe...
3
5
7
11
13

Good luck!

VRonin

If you can't require Excel installed see https://github.com/VSRonin/QtXlsxWriter

Eduardo12l

Hi @hskoglund your way works almost Perfect!! Thx, but i have only one problem: I have to search an specific word in my Excel worksheet that have 700 Rows and 10 Columns and it takes around 30 seconds in process all the worksheet ¿do you know what can i do? And sometimes i dont know why it opens a window where says : "file available" its ready to be written and read or something like it but once it opened 16 windows with that phrase

Eduardo12l

Hi @VRonin thx you. Your way looks very very complex Im novice in C++ and Qt

hskoglund

@Eduardo12l about that message "file available" and Excel opening lots of windows in the background, that's most likely because my example above was sloppy and didn't close Excel when done, so that Excel became an zombie in the background for you :-(

I've made a new example, it closes Excel at the end, should cause less zombie activity:
To search for a word in your Excel worksheet, you can use the "Find" call, I've modified my Excel test.xlsx file like this, filling 700 rows and 10 columns with words:
Test file

and this is the new code:

auto excel     = new QAxObject("Excel.Application");
auto workbooks = excel->querySubObject("Workbooks");
auto workbook  = workbooks->querySubObject("Open(const QString&)","c:\\temp\\test.xlsx");
auto sheets    = workbook->querySubObject("Worksheets");
auto sheet     = sheets->querySubObject("Item(int)", 1);    // use first worksheet

// setup a range of 700 rows and 10 columns
auto range     = sheet->querySubObject("Range(A1,J700)");

// find a word
QString word = "engaged";
auto find = range->querySubObject("Find(const QString&)",word);

// if successful, show the address (first cell found)
if (nullptr != find)
    qDebug() << find->dynamicCall("Address");

// don't forget to quit Excel
excel->dynamicCall("Quit()");
delete excel;

Using the "Find" call is pretty fast, should only take a few seconds, this is what Qt Creator should show:

Starting C:\Projects\build-ExcelTest-Desktop_Qt_5_9_0_MSVC2015_32bit-Release\release\ExcelTest.exe...
QVariant(QString, "$E$4")```

Eduardo12l

Hello @hskoglund definitely it works very well but it takes 17 seconds ¿do you know what can i do to reduce that time it takes ?

hskoglund

@Eduardo12l Hmm 17 seconds... For one Find() call? If you start Excel, and do a Find inside it (ctrl-F) does it also take 17 seconds?

Eduardo12l

@hskoglund I reduced the size of my excel to 2 rows and it takes 5 seconds i think my Qt application has problems when tries to open Excel.