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>
{
Q_OBJECT
public:
~ExcelTable() {}
public slots:
void import
(QString fileName,
int sheetNumber
=1) { 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;
this->setItem(row-1, col-1, twi);
}
}
// clean up and close up
workbook->dynamicCall("Close()");
excel->dynamicCall("Quit()");
}
};
#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()");
}
};
To copy to clipboard, switch view to plain text mode
Bookmarks