Excel and Qt

[antialias]

So I've been browsing for how to read / write excel files with Qt...I only found this one example:

QAxObject* excel = new QAxObject( "Excel.Application", 0 );
//  excel->dynamicCall("SetVisible(bool)",true);
    QAxObject *workbooks = excel->querySubObject( "Workbooks" );
    QAxObject *workbook = workbooks->querySubObject( "Open(const QString&)", "e:/tmp/test.xls" );
    QAxObject *sheets = workbook->querySubObject( "Worksheets" );
	int count = sheets->dynamicCall("Count()").toInt();
	QStringList sheetsList;
	for (int i=1; i<=count; i++)
	{
		QAxObject* sheet = sheets->querySubObject( "Item( int )", i );
		QString name = sheet->dynamicCall("Name()").toString();
	    sheetsList.append(name);
	}
	workbook->dynamicCall("Close()");
    excel->dynamicCall( "Quit()");

QAxObject* excel = new QAxObject( "Excel.Application", 0 );
//  excel->dynamicCall("SetVisible(bool)",true);
    QAxObject *workbooks = excel->querySubObject( "Workbooks" );
    QAxObject *workbook = workbooks->querySubObject( "Open(const QString&)", "e:/tmp/test.xls" );
    QAxObject *sheets = workbook->querySubObject( "Worksheets" );
	int count = sheets->dynamicCall("Count()").toInt();
	QStringList sheetsList;
	for (int i=1; i<=count; i++)
	{
		QAxObject* sheet = sheets->querySubObject( "Item( int )", i );
		QString name = sheet->dynamicCall("Name()").toString();
	    sheetsList.append(name);
	}
	workbook->dynamicCall("Close()");
    excel->dynamicCall( "Quit()");

To copy to clipboard, switch view to plain text mode 

compiles fine, but it crashes on execution on the line

QAxObject *workbook = workbooks->querySubObject( "Open(const QString&)", "e:/tmp/test.xls" );

QAxObject *workbook = workbooks->querySubObject( "Open(const QString&)", "e:/tmp/test.xls" );

To copy to clipboard, switch view to plain text mode 

(yes, the excel file does exist and resides in that directory. Replacing the / with \\ doesn't solve the problem)

what am I doing wrong?

[giacomelli.fabio]

I'm only a noob but I think you have found only a piece of a bigger example......

I had the same problem in the past (write to an excel) and I didn't find anything free of cost so I did a very basic XML writer (excel 2003, fully compatible also with OpenOffice) starting from this :

http://www.qtforum.de/forum/viewtopi...d09c193368953b

You can do the same !!

[ChrisW67]

"Crashes" is a fairly broad statement. Is excel or workbooks equal to zero?

[antialias]

OK, found an example that works. Since I only need to read from an excel file I could throw out a lot of stuff. Only thing I'm not sure about is how to close the file without it asking me if I want to save (since I will need to process a lot of excel files in rapid succession automatically)

but that is only a minor inconvenience.

[marcvanriet]

Hello,

Could you post (al link to) the example ?

I need to do just the same.

Best regards,
Marc

[antialias]

Here's some code that can get you started. I must stress, that I don't fully understand it, since I copied and pasted it from somewhere else - and then started to screw around with it. So don't ask me to explain in depth. The comments in here are what I THINK happens.

I still need to create a QAxWidget for some reason, even though I only want to read and not display the excel file. If anyone knows how I can get around using a QApplication and a QAxWidget object and still have this work: please let me know.

#include <qaxobject>
#include <qaxwidget>
#include <qapplication>
 
int main(int argc, char *argv[])
{
QString strVal;
const char* cp = "Range(E11)";
 
QApplication a(argc, argv);
QAxWidget excel("Excel.Application");   
 
// because I want to read the file - not display it.
excel.setProperty("Visible", false);
 
QAxObject *workbooks = excel.querySubObject("WorkBooks");
workbooks->dynamicCall("Open (const QString&)", QString("e:/tmp/test3.xls"));
QAxObject *workbook = excel.querySubObject("ActiveWorkBook");
 
// I need data from the 2nd worksheet (worksheets are numbered starting from 1)
QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 2);
 
// Read fom the second worksheet the contents of cell E11
QAxObject *range = worksheet->querySubObject(cp);
strVal = range->property("Value").toString();
 
// close the excel file
excel.dynamicCall("Quit (void)");
 
return(0);
}

#include <qaxobject>
#include <qaxwidget>
#include <qapplication>

int main(int argc, char *argv[])
{
QString strVal;
const char* cp = "Range(E11)";

QApplication a(argc, argv);
QAxWidget excel("Excel.Application");   

// because I want to read the file - not display it.
excel.setProperty("Visible", false);

QAxObject *workbooks = excel.querySubObject("WorkBooks");
workbooks->dynamicCall("Open (const QString&)", QString("e:/tmp/test3.xls"));
QAxObject *workbook = excel.querySubObject("ActiveWorkBook");

// I need data from the 2nd worksheet (worksheets are numbered starting from 1)
QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 2);

// Read fom the second worksheet the contents of cell E11
QAxObject *range = worksheet->querySubObject(cp);
strVal = range->property("Value").toString();

// close the excel file
excel.dynamicCall("Quit (void)");

return(0);
}

To copy to clipboard, switch view to plain text mode 

[marcvanriet]

Hi antialias,

Thanks for the sample code. It worked almost right away. Just had to add "LIBS += qaxcontainer.lib" to my .pro file, and then use the full path name to my excel file.

For the QAxWidget : you can just use a QAxObject instead of QAxWidget.

For skipping QApplication, I don't know for sure. A 'console application' doesn't need QApplication. From "C++ Programming with Qt4' : "... since this is a console application, it has a slightly different .pro file from those we have seen for GUI applications. We only link against QtCore since we don't use any GUI fucntionallity. Then we specify that we want to enable console output on windows and that we don't want the application to live in a bundle on maxosx" and they give the following .pro file :
TEMPLATE = app
QT = core
CONFIG += console
CONFIG -= app_bundle
SOURCES = ....... your sources

By the way : if something goes wrong in the calls to the QAxObject (like specifying a wrong filename), you get detailed error information in the application output window of QtCreator. I hope you get this output too, because it helps a lot.

Best regards,
Marc

[marcvanriet]

Hi antialias,

Thanks for the sample code. It worked almost right away. Just had to add "LIBS += qaxcontainer.lib" to my .pro file, and then use the full path name to my excel file.

For the QAxWidget : you can just use a QAxObject instead of QAxWidget.

For skipping QApplication, I don't know for sure. A 'console application' doesn't need QApplication. From "C++ Programming with Qt4' : "... since this is a console application, it has a slightly different .pro file from those we have seen for GUI applications. We only link against QtCore since we don't use any GUI fucntionallity. Then we specify that we want to enable console output on windows and that we don't want the application to live in a bundle on maxosx" and they give the following .pro file :
TEMPLATE = app
QT = core
CONFIG += console
CONFIG -= app_bundle
SOURCES = ....... your sources

By the way : if something goes wrong in the calls to the QAxObject (like specifying a wrong filename), you get detailed error information in the application output window of QtCreator. I hope you get this output too, because it helps a lot.

Best regards,
Marc

[antialias]

For the QAxWidget : you can just use a QAxObject instead of QAxWidget.

When I try that it crashes in this line.

workbooks->dynamicCall("Open (const QString&)", QString("e:/tmp/test3.xls"));

workbooks->dynamicCall("Open (const QString&)", QString("e:/tmp/test3.xls"));

To copy to clipboard, switch view to plain text mode 

with a Null pointer exception.
If I leave out the QApplication it doesn't compile at all (even though my app is a console application)

I'm working with Visual Studio 2010 Express (just using Qt classes/libs but not making a GUI for this project) - so I don't have a pro-File. The debug information is somewhat limited, too :-(

But it works well enough so I'll move on to other things...

[Ginsengelf]

Hi, for console applications there is QCoreApplication.

Ginsengelf

[aneeshkumaru]

Make sure that your .pro contains the below lines..
LIBS += -lqaxserver \
-lqaxcontainer
My advice is to start a new class and do the code..You can connect the code to a pushbutton slot...

QAxObject *excel=new QAxObject("Excel.Application");
excel->setProperty("Visible",false);
QAxObject *workbooks=excel->querySubObject("WorkBooks");
workbooks->dynamicCall("Open (const QString&)",QString("C:\\Book.xls"));
QAxObject *workbook=excel->querySubObject("ActiveWorkBook");
QAxObject *worksheets=workbook->querySubObject("WorkSheets");

U can contact me at aneeshkumaru@yahoo.co.in ...i also got a lot of issue..bcz i am also a beginner. but i solved most of the issues...