Qt通过QAxObject调用Excel[解决在非主线程无法调用的问题]
2014-01-01 00:03 chanrom 阅读(3810) 评论(0) 编辑 收藏 举报各大论坛有非常多的关于如何Qt调用Excel的方法,也有源码可以直接拿来用,改一改完全可以用,但有时也会遇到一些特有的问题,和我们自己做的项目需求和设计有关。
本博文主要讲两个方面的内容:
- VS2010内开发一个简单的通过QAxObject调用excel的程序。
- 如何解决非主线程无法调用excel的问题。
本博文目的是分享和记录自己的编程路上的点点滴滴,有浅尝辄止之嫌,欢迎批评指点。
一、VS2010内开发一个简单的通过QAxObject调用excel的程序
开发环境VS2010+qt4.8.3。
- 在VS2010中创建【Qt Application】工程,之后的操作都可以默认。有一个地方需要注意,因为是QAxObject调用,需要在Qt4项目创建向导中勾选【ActiveQt container library】:
- 通过QAxObject调用的方式也很简单,这里提供两个相关的源代码,原作者不详(但是这里感谢他的分享),有一些些的改动。 借助Qt的QAxObject技术,以下几句可以访问到Excel的工作簿:
如果想了解Excel有哪些函数(上图中的Add()函数)可以调用,参考Microsoft Excel Visual Basic,谷歌搜索VBAXL10.CHM
主类中调用方式为:
QExcel *excel = new QExcel; excel->selectSheet(1); excel->setCellVariant(1, 1, "excel"); QString fileName = "F:/Desktop/test.xlsx"; excel->saveAs(fileName); excel->close();
QExcel.h
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#ifndef QEXCEL_H #define QEXCEL_H #include <QString> #include <QVariant> #include <QDir> #include <Qdebug> class QAxObject; class QExcel { public: QExcel(); ~QExcel(); public: QAxObject *getWorkBooks(); QAxObject *getWorkBook(); QAxObject *getWorkSheets(); QAxObject *getWorkSheet(); public: /**************************************************************************/ /* 工作表 */ /**************************************************************************/ void selectSheet(const QString& sheetName); //sheetIndex 起始于 1 void selectSheet(int sheetIndex); void deleteSheet(const QString& sheetName); void deleteSheet(int sheetIndex); void insertSheet(QString sheetName); int getSheetsCount(); //在 selectSheet() 之后才可调用 QString getSheetName(); QString getSheetName(int sheetIndex); /**************************************************************************/ /* 单元格 */ /**************************************************************************/ void setCellVariant(int row, int column, const QVariant& value); //cell 例如 "A7" void setCellVariant(const QString& cell, const QVariant& value); //range 例如 "A5:C7" void mergeCells(const QString& range); void mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn); QVariant getCellValue(int row, int column); void clearCell(int row, int column); void clearCell(const QString& cell); /**************************************************************************/ /* 布局格式 */ /**************************************************************************/ void getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn); void setColumnWidth(int column, int width); void setRowHeight(int row, int height); void setCellTextCenter(int row, int column); void setCellTextCenter(const QString& cell); void setCellTextWrap(int row, int column, bool isWrap); void setCellTextWrap(const QString& cell, bool isWrap); void setAutoFitRow(int row); void mergeSerialSameCellsInAColumn(int column, int topRow); int getUsedRowsCount(); void setCellFontBold(int row, int column, bool isBold); void setCellFontBold(const QString& cell, bool isBold); void setCellFontSize(int row, int column, int size); void setCellFontSize(const QString& cell, int size); /**************************************************************************/ /* 透视图表 */ /**************************************************************************/ void QExcel::setPivotTable(const QString& rowField, const QString& dataField); //注:rowField,选择某个字段,设置为xlRowField(行字段); // dataField,选择某个字段,设置为xlDataField(行字段); //以上参考VBA orientation属性,有xlRowField(行字段)xlColumnField(列字段)xlDataField(数值字段)等。 /**************************************************************************/ /* 文件 */ /**************************************************************************/ void saveAs(const QString& fileName); void close(); private: QAxObject *excel; QAxObject *workBooks; QAxObject *workBook; QAxObject *sheets; QAxObject *sheet; }; #endif
QExcel.cpp
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#include <QAxObject> #include <QFile> #include <QStringList> #include <QDebug> #include "Qexcel.h" QExcel::QExcel() { excel = 0; workBooks = 0; workBook = 0; sheets = 0; sheet = 0; excel = new QAxObject("Excel.Application"); workBooks = excel->querySubObject("Workbooks"); workBooks->dynamicCall("Add()"); workBook = excel->querySubObject("ActiveWorkBook"); sheets = workBook->querySubObject("WorkSheets"); } QExcel::~QExcel() { close(); } void QExcel::close() { excel->dynamicCall("Quit()"); delete sheet; delete sheets; delete workBook; delete workBooks; delete excel; excel = 0; workBooks = 0; workBook = 0; sheets = 0; sheet = 0; } void QExcel::clearCell(const QString& cell) { QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->dynamicCall("ClearContents()"); } void QExcel::deleteSheet(const QString& sheetName) { QAxObject * a = sheets->querySubObject("Item(const QString&)", sheetName); a->dynamicCall("delete"); } void QExcel::deleteSheet(int sheetIndex) { QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex); a->dynamicCall("delete"); } QAxObject *QExcel::getWorkBooks() { return workBooks; } QAxObject *QExcel::getWorkBook() { return workBook; } QAxObject *QExcel::getWorkSheets() { return sheets; } QAxObject *QExcel::getWorkSheet() { return sheet; } QVariant QExcel::getCellValue(int row, int column) { QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column); return range->property("Value"); } int QExcel::getSheetsCount() { return sheets->property("Count").toInt(); } QString QExcel::getSheetName() { return sheet->property("Name").toString(); } QString QExcel::getSheetName(int sheetIndex) { QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex); return a->property("Name").toString(); } void QExcel::getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn) { QAxObject *usedRange = sheet->querySubObject("UsedRange"); *topLeftRow = usedRange->property("Row").toInt(); *topLeftColumn = usedRange->property("Column").toInt(); QAxObject *rows = usedRange->querySubObject("Rows"); *bottomRightRow = *topLeftRow + rows->property("Count").toInt() - 1; QAxObject *columns = usedRange->querySubObject("Columns"); *bottomRightColumn = *topLeftColumn + columns->property("Count").toInt() - 1; } void QExcel::insertSheet(QString sheetName) { sheets->querySubObject("Add()"); QAxObject * a = sheets->querySubObject("Item(int)", 1); a->setProperty("Name", sheetName); } void QExcel::mergeCells(const QString& cell) { QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("VerticalAlignment", -4108);//xlCenter range->setProperty("WrapText", true); range->setProperty("MergeCells", true); } void QExcel::mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn) { QString cell; cell.append(QChar(topLeftColumn - 1 + 'A')); cell.append(QString::number(topLeftRow)); cell.append(":"); cell.append(QChar(bottomRightColumn - 1 + 'A')); cell.append(QString::number(bottomRightRow)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("VerticalAlignment", -4108);//xlCenter range->setProperty("WrapText", true); range->setProperty("MergeCells", true); } void QExcel::mergeSerialSameCellsInAColumn(int column, int topRow) { int a,b,c,rowsCount; getUsedRange(&a, &b, &rowsCount, &c); int aMergeStart = topRow, aMergeEnd = topRow + 1; QString value; while(aMergeEnd <= rowsCount) { value = getCellValue(aMergeStart, column).toString(); while(value == getCellValue(aMergeEnd, column).toString()) { clearCell(aMergeEnd, column); aMergeEnd++; } aMergeEnd--; mergeCells(aMergeStart, column, aMergeEnd, column); aMergeStart = aMergeEnd + 1; aMergeEnd = aMergeStart + 1; } } void QExcel::setPivotTable(const QString& rowField, const QString& dataField) { sheet->dynamicCall("PivotTableWizard()"); sheet = sheets->querySubObject("Item(const QString&)", "Sheet4"); QAxObject *pvtTables = sheet->querySubObject("PivotTables"); //int pvtTableCount = pvtTables->property("Count").toInt(); //qDebug() << pvtTableCount; QAxObject *pvtTable = sheet->querySubObject("PivotTables(int)", 1); QAxObject *pvtRowField = pvtTable->querySubObject("PivotFields(const QString&)", rowField); pvtRowField->setProperty("Orientation", "xlRowField"); QAxObject *pvtDataField = pvtTable->querySubObject("PivotFields(const QString&)", dataField); pvtDataField->setProperty("Orientation", "xlDataField"); //嵌入图表 QAxObject *chObjects = sheet->querySubObject("ChartObjects()"); chObjects->dynamicCall("Add(int, int, int, int)", 150, 20, 550, 320); int chartCount = chObjects->property("Count").toInt(); //QAxObject *chObject = sheet->querySubObject("ActiveChartObject"); } void QExcel::selectSheet(const QString& sheetName) { sheet = sheets->querySubObject("Item(const QString&)", sheetName); } void QExcel::selectSheet(int sheetIndex) { sheet = sheets->querySubObject("Item(int)", sheetIndex); } void QExcel::setCellVariant(int row, int column, const QVariant& value) { QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column); range->dynamicCall("SetValue(const QVariant&)", value); } void QExcel::setCellFontBold(int row, int column, bool isBold) { QString cell; cell.append(QChar(column - 1 + 'A')); cell.append(QString::number(row)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range = range->querySubObject("Font"); range->setProperty("Bold", isBold); } void QExcel::setCellFontSize(int row, int column, int size) { QString cell; cell.append(QChar(column - 1 + 'A')); cell.append(QString::number(row)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range = range->querySubObject("Font"); range->setProperty("Size", size); } void QExcel::saveAs(const QString& fileName) { workBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(fileName)); } void QExcel::setColumnWidth(int column, int width) { QString columnName; columnName.append(QChar(column - 1 + 'A')); columnName.append(":"); columnName.append(QChar(column - 1 + 'A')); QAxObject * col = sheet->querySubObject("Columns(const QString&)", columnName); col->setProperty("ColumnWidth", width); } void QExcel::setCellTextCenter(int row, int column) { QString cell; cell.append(QChar(column - 1 + 'A')); cell.append(QString::number(row)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("HorizontalAlignment", -4108);//xlCenter } void QExcel::setCellTextWrap(int row, int column, bool isWrap) { QString cell; cell.append(QChar(column - 1 + 'A')); cell.append(QString::number(row)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("WrapText", isWrap); } void QExcel::setAutoFitRow(int row) { QString rowsName; rowsName.append(QString::number(row)); rowsName.append(":"); rowsName.append(QString::number(row)); QAxObject * rows = sheet->querySubObject("Rows(const QString &)", rowsName); rows->dynamicCall("AutoFit()"); } void QExcel::clearCell(int row, int column) { QString cell; cell.append(QChar(column - 1 + 'A')); cell.append(QString::number(row)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->dynamicCall("ClearContents()"); } int QExcel::getUsedRowsCount() { QAxObject *usedRange = sheet->querySubObject("UsedRange"); int topRow = usedRange->property("Row").toInt(); QAxObject *rows = usedRange->querySubObject("Rows"); int bottomRow = topRow + rows->property("Count").toInt() - 1; return bottomRow; } void QExcel::setCellVariant(const QString& cell, const QVariant& value) { QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->dynamicCall("SetValue(const QVariant&)", value); } void QExcel::setCellFontSize(const QString &cell, int size) { QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range = range->querySubObject("Font"); range->setProperty("Size", size); } void QExcel::setCellTextCenter(const QString &cell) { QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("HorizontalAlignment", -4108);//xlCenter } void QExcel::setCellFontBold(const QString &cell, bool isBold) { QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range = range->querySubObject("Font"); range->setProperty("Bold", isBold); } void QExcel::setCellTextWrap(const QString &cell, bool isWrap) { QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("WrapText", isWrap); } void QExcel::setRowHeight(int row, int height) { QString rowsName; rowsName.append(QString::number(row)); rowsName.append(":"); rowsName.append(QString::number(row)); QAxObject * r = sheet->querySubObject("Rows(const QString &)", rowsName); r->setProperty("RowHeight", height); }
二、解决非主线程无法调用excel的问题
问题情境:这段代码可能要执行很长时间,那么需要单独开一个线程完成这项工作。那么我们可能会继承QThread类重写run()方法:
void Thread::run(){ QExcel *excel = new QExcel; excel->selectSheet(1); excel->setCellVariant(1, 1, "excel"); QString fileName = "F:/Desktop/test.xlsx"; excel->saveAs(fileName); excel->close(); }
然后调用:
Thread* thread = new Thread; thread->start();
但问题出现了,
workBooks是空指针,单步调试发现excel.exe根本没有启动。
解决方法:需要调用一个Windows API函数初始化COM Library,注意包含头文件“windows.h”:
CoInitializeEx(NULL, COINIT_MULTITHREADED);
此方面更多信息参考:http://msdn.microsoft.com/en-us/library/ms695279(v=vs.85).aspx
添加这一句后,之前的代码变为:
void Thread::run(){ CoInitializeEx(NULL, COINIT_MULTITHREADED); QExcel *excel = new QExcel; excel->selectSheet(1); excel->setCellVariant(1, 1, "excel"); QString fileName = "F:/Desktop/test2.xlsx"; excel->saveAs(fileName); excel->close(); }
附一些可以参考的文章并再次感谢他们的分享:
[SOLVED] MS Excel and QThread | Qt Project forums | Qt Project
在Qt中用QAxObject来操作Excel - 潇的专栏 - 博客频道 - CSDN.NET