Read/Write with Excel in Qt

[alizadeh91]

Hi all...

In Qt5 what are the best and easiest ways to read/write with excel?

Note: I've seen the doc about it and something about ActiveX and QAxContainer and this:

http://www.qtcentre.org/threads/42143-read-and-write-data-in-excel-files

I am quit sure about QAxObject and its friends that will work for me :) But i don't know if is there any better ways for it or not? And if this modules

can work with spreadsheets in unix??? I just want to know if someone have experienced with it can guide me :)

Thanks :)

[Ashkan_s]

I haven't tried myself but I think you can use ODBC and QSqlTableModel,
I don't know whether it works in unix but at least it should work in windows.
The following may help to find the appropriate connection string:
http://www.connectionstrings.com/excel-2007

[d_stranz]

can work with spreadsheets in unix???

No, unless you can find some portable library for working with Excel files. ActiveX and QAxContainer are Windows-only, as are any ODBC or any other drivers supplied by Microsoft.

It is not at all clear whether these drivers will work with a "free-form" Excel spreadsheet, or whether they require the spreadsheet to be structured (in other words, a table of rows and columns like a database table).

[wysota]

http://libxls.sourceforge.net/

[alizadeh91]

Thanks a lot wysota and ashkan :) got it ;)

[iraytrace]

I've seen Qt (activeQt) code samples that try to count the cells in an Excel ActiveX object. I wanted to post an example that uses the "UsedRange" query in the hopes that folks will find this. Simpler than iterating over the data trying to guess which cells are used. This is a simple class derived from QTableWidget that can import an Excel sheet.


#include <QTableWidget>
#include <QAxObject>
#include <QTableWidgetItem>

class ExcelTable : public QTableWidget
{
Q_OBJECT
public:
explicit ExcelTable(QWidget *parent = 0) : QTableWidget(parent) {}
~ExcelTable() {}
public slots:
void import(QString fileName, int sheetNumber=1) {
QAxObject* excel = new QAxObject( "Excel.Application", 0 );
QAxObject* workbooks = excel->querySubObject( "Workbooks" );
QAxObject* workbook = workbooks->querySubObject( "Open(const QString&)", fileName );
QAxObject* sheets = workbook->querySubObject( "Worksheets" );
int sheetCount = sheets->dynamicCall("Count()").toInt(); //worksheets count
QAxObject* sheet = sheets->querySubObject( "Item( int )", sheetNumber );

// Find the cells that actually have content
QAxObject* usedrange = sheet->querySubObject( "UsedRange");
QAxObject * rows = usedrange->querySubObject("Rows");
QAxObject * columns = usedrange->querySubObject("Columns");
int intRowStart = usedrange->property("Row").toInt();
int intColStart = usedrange->property("Column").toInt();
int intCols = columns->property("Count").toInt();
int intRows = rows->property("Count").toInt();

// replicate the Excel content in the QTableWidget
this->setColumnCount(intColStart+intCols);
this->setRowCount(intRowStart+intRows);
for (int row=intRowStart ; row < intRowStart+intRows ; row++) {
for (int col=intColStart ; col < intColStart+intCols ; col++) {
QAxObject* cell = sheet->querySubObject( "Cells( int, int )", row, col );
QVariant value = cell->dynamicCall( "Value()" );
if (value.toString().isEmpty())
continue;

QTableWidgetItem * twi = new QTableWidgetItem(value.toString());
this->setItem(row-1, col-1, twi);
}
}

// clean up and close up
workbook->dynamicCall("Close()");
excel->dynamicCall("Quit()");
}
};

[d_stranz]

Thanks for a good example. Even more general would be to import into a QAbstractItemModel or QAbstractTableModel so you could have multiple views of the same data if desired.