qt5 使用qtxlsx 读写excel

太过爱你忘了你带给我的痛 2023-06-09 15:30 43阅读 0赞

（1）开源项目下载地址：[https://github.com/dbzhang800/QtXlsxWriter][https_github.com_dbzhang800_QtXlsxWriter]

（2）建立pro

TARGET = QtXlsx
    TEMPLATE = lib
    CONFIG += staticlib
    QT += core gui gui-private
     
    HEADERS += xlsxdocpropscore_p.h \
        xlsxdocpropsapp_p.h \
        xlsxrelationships_p.h \
        xlsxutility_p.h \
        xlsxsharedstrings_p.h \
        xlsxcontenttypes_p.h \
        xlsxtheme_p.h \
        xlsxformat.h \
        xlsxworkbook.h \
        xlsxstyles_p.h \
        xlsxabstractsheet.h \
        xlsxabstractsheet_p.h \
        xlsxworksheet.h \
        xlsxworksheet_p.h \
        xlsxchartsheet.h \
        xlsxchartsheet_p.h \
        xlsxzipwriter_p.h \
        xlsxworkbook_p.h \
        xlsxformat_p.h \
        xlsxglobal.h \
        xlsxdrawing_p.h \
        xlsxzipreader_p.h \
        xlsxdocument.h \
        xlsxdocument_p.h \
        xlsxcell.h \
        xlsxcell_p.h \
        xlsxdatavalidation.h \
        xlsxdatavalidation_p.h \
        xlsxcellreference.h \
        xlsxcellrange.h \
        xlsxrichstring_p.h \
        xlsxrichstring.h \
        xlsxconditionalformatting.h \
        xlsxconditionalformatting_p.h \
        xlsxcolor_p.h \
        xlsxnumformatparser_p.h \
        xlsxdrawinganchor_p.h \
        xlsxmediafile_p.h \
        xlsxabstractooxmlfile.h \
        xlsxabstractooxmlfile_p.h \
        xlsxchart.h \
        xlsxchart_p.h \
        xlsxsimpleooxmlfile_p.h \
        xlsxcellformula.h \
        xlsxcellformula_p.h
     
    SOURCES += xlsxdocpropscore.cpp \
        xlsxdocpropsapp.cpp \
        xlsxrelationships.cpp \
        xlsxutility.cpp \
        xlsxsharedstrings.cpp \
        xlsxcontenttypes.cpp \
        xlsxtheme.cpp \
        xlsxformat.cpp \
        xlsxstyles.cpp \
        xlsxworkbook.cpp \
        xlsxabstractsheet.cpp \
        xlsxworksheet.cpp \
        xlsxchartsheet.cpp \
        xlsxzipwriter.cpp \
        xlsxdrawing.cpp \
        xlsxzipreader.cpp \
        xlsxdocument.cpp \
        xlsxcell.cpp \
        xlsxdatavalidation.cpp \
        xlsxcellreference.cpp \
        xlsxcellrange.cpp \
        xlsxrichstring.cpp \
        xlsxconditionalformatting.cpp \
        xlsxcolor.cpp \
        xlsxnumformatparser.cpp \
        xlsxdrawinganchor.cpp \
        xlsxmediafile.cpp \
        xlsxabstractooxmlfile.cpp \
        xlsxchart.cpp \
        xlsxsimpleooxmlfile.cpp \
        xlsxcellformula.cpp

（3）修改源码 xlsxglobal.h 文件

/*
    #if !defined(QT_STATIC) && !defined(XLSX_NO_LIB)
    #  if defined(QT_BUILD_XLSX_LIB)
    #    define Q_XLSX_EXPORT Q_DECL_EXPORT
    #  else
    #    define Q_XLSX_EXPORT Q_DECL_IMPORT
    #  endif
    #else
    #  define Q_XLSX_EXPORT
    #endif*/
    #  define Q_XLSX_EXPORT

（4）使用

INCLUDEPATH += $$PWD/../../libs/xlsx/
    LIBS += -L$$PWD/../../libs/xlsx/release/ -lQtXlsx
    LIBS += -L$$PWD/../../libs/xlsx/debug/ -lQtXlsx

#include "xlsxdocument.h"
     
    QXlsx::Document xlsx;
    xlsx.write("A1", "Hello Qt!");
    xlsx.saveAs("E:/Test.xlsx");
    return 0;

（5）qt使用`QAxObject`快速读取excel

# 很多人搜如何读写excel都会看到用`QAxObject`来进行操作，很多人试了之后都会发现一个问题，就是慢，非常缓慢！因此很多人得出结论是`QAxObject`读写excel方法不可取，效率低。 #

后来我曾试过用ODBC等数据库类型的接口进行读写，遇到中文嗝屁不说，超大的excel还是会读取速度慢。 最后，看了一些开源的代码后发现，Windows下读取excel，还是用`QAxObject`最快！没错，就是用`QAxObject`读写最快！！！（读取10万单元格229ms） 大家以后读取excel时（win下），不用考虑别的方法，用`QAxObject`就行，速度杠杠的，慢是你操作有误！下面就说说如何能提高其读取效率。

# 读取excel慢的原因 #

这里不说如何打开或生成excel，着重说说如何快速读取excel。  
网上搜到用Qt操作excel的方法，读取都是使用类似下面这种方法进行：

1.  `QVariant ExcelBase::read(int row, int col)`
2.  `{`
3.  `QVariant ret;`
4.  `if (this->sheet != NULL && ! this->sheet->isNull())`
5.  `{`
6.  `QAxObject* range = this->sheet->querySubObject("Cells(int, int)", row, col);`
7.  `//ret = range->property("Value");`
8.  `ret = range->dynamicCall("Value()");`
9.  `delete range;`
10. `}`
11. `return ret;`
12. `}`

读取慢的根源就在于`sheet->querySubObject("Cells(int, int)", row, col)`

试想有10000个单元就得调用10000次`querySubObject`，网络上90%的教程都没说这个`querySubObject`产生的`QAxObject*`最好进行手动删除，虽然在它的父级`QAxObject`会管理它的内存，但父级不析构，子对象也不会析构，若调用10000次，就会产生10000个`QAxObject`对象 得益于[QT快速读取数据量很大的Excel文件][QT_Excel]此文，下面总结如何快速读写excel

# 快速读取excel文件 #

原则是一次调用`querySubObject`把所有数据读取到内存中  
VBA中可以使用`UsedRange`把所有用到的单元格范围返回，并使用属性`Value`把这些单元格的所有值获取。

这时，获取到的值是一个table，但Qt把它变为一个变量QVariant来储存，其实实际是一个`QList<QList<QVariant> >`，此时要操作里面的内容，需要把这个`QVariant`转换为`QList<QList<QVariant> >`

先看看获取整个单元格的函数示意(这里ExcelBase是一个读写excel的类封装)：

1.  `QVariant ExcelBase::readAll()`
2.  `{`
3.  `QVariant var;`
4.  `if (this->sheet != NULL && ! this->sheet->isNull())`
5.  `{`
6.  `QAxObject *usedRange = this->sheet->querySubObject("UsedRange");`
7.  `if(NULL == usedRange || usedRange->isNull())`
8.  `{`
9.  `return var;`
10. `}`
11. `var = usedRange->dynamicCall("Value");`
12. `delete usedRange;`
13. `}`
14. `return var;`
15. `}`

代码中`this->sheet`是已经打开的一个sheet，再获取内容时使用`this->sheet->querySubObject("UsedRange");`即可把所有范围都获取。

下面这个castVariant2ListListVariant函数把`QVariant`转换为`QList<QList<QVariant> >`

1.  `///`
2.  `/// \brief 把QVariant转为QList<QList<QVariant> >`
3.  `/// \param var`
4.  `/// \param res`
5.  `///`
6.  `void ExcelBase::castVariant2ListListVariant(const QVariant &var, QList<QList<QVariant> > &res)`
7.  `{`
8.  `QVariantList varRows = var.toList();`
9.  `if(varRows.isEmpty())`
10. `{`
11. `return;`
12. `}`
13. `const int rowCount = varRows.size();`
14. `QVariantList rowData;`
15. `for(int i=0;i<rowCount;++i)`
16. `{`
17. `rowData = varRows[i].toList();`
18. `res.push_back(rowData);`
19. `}`
20. `}`

这样excel的所有内容都转换为`QList<QList<QVariant>>`保存，其中`QList<QList<QVariant> >`中`QList<QVariant>`为每行的内容，行按顺序放入最外围的QList中。

对于如下如的excel：

![这里写图片描述][20160804175451390]

读取后的`QList<QList<QVariant> >`结构如下所示：

![这里写图片描述][20160804174250619]

继续展开

![这里写图片描述][20160804174259744]

下面看看此excel的读取速度有多高  
这里有个excel，有1000行，100列，共计十万单元格，打开使用了一些时间，读取10万单元格耗时229毫秒，  
读取的代码如下：(完整源代码见后面)

1.  `void MainWindow::on_action_open_triggered()`
2.  `{`
3.  `QString xlsFile = QFileDialog::getOpenFileName(this,QString(),QString(),"excel(*.xls *.xlsx)");`
4.  `if(xlsFile.isEmpty())`
5.  `return;`
6.  `QElapsedTimer timer;`
7.  `timer.start();`
8.  `if(m_xls.isNull())`
9.  `m_xls.reset(new ExcelBase);`
10. `m_xls->open(xlsFile);`
11. `qDebug()<<"open cost:"<<timer.elapsed()<<"ms";timer.restart();`
12. `m_xls->setCurrentSheet(1);`
13. `m_xls->readAll(m_datas);`
14. `qDebug()<<"read data cost:"<<timer.elapsed()<<"ms";timer.restart();`
15. `QVariantListListModel* md = qobject_cast<QVariantListListModel*>(ui->tableView->model());`
16. `if(md)`
17. `{`
18. `md->updateData();`
19. `}`
20. `qDebug()<<"show data cost:"<<timer.elapsed()<<"ms";timer.restart();`
21. `}`

上面的m\_xls和m\_datas是成员变量：

1.  `QScopedPointer<ExcelBase> m_xls;`
2.  `QList< QList<QVariant> > m_datas;`

读取的耗时：

1.  `"D:\czy_blog\czyBlog\04_fastReadExcel\src\fastReadExcelInWindows\excelRWByCztr1988.xls"`
2.  `open cost: 1183 ms`
3.  `read data cost: 229 ms`
4.  `show data cost: 14 ms`

10万个也就0.2秒而已

# 快速写入excel文件 #

同理，能通过`QAxObject *usedRange = this->sheet->querySubObject("UsedRange");`实现快速读取，也可以实现快速写入

快速写入前需要些获取写入单元格的范围：`Range(const QString&)`  
如excel的A1为第一行第一列，那么A1:B2就是从第一行第一列到第二行第二列的范围。

要写入这个范围，同样也是通过一个与之对应的`QList<QList<QVariant> >`，具体见下面代码：

1.  `///`
2.  `/// \brief 写入一个表格内容`
3.  `/// \param cells`
4.  `/// \return 成功写入返回true`
5.  `/// \see readAllSheet`
6.  `///`
7.  `bool ExcelBase::writeCurrentSheet(const QList<QList<QVariant> > &cells)`
8.  `{`
9.  `if(cells.size() <= 0)`
10. `return false;`
11. `if(NULL == this->sheet || this->sheet->isNull())`
12. `return false;`
13. `int row = cells.size();`
14. `int col = cells.at(0).size();`
15. `QString rangStr;`
16. `convertToColName(col,rangStr);`
17. `rangStr += QString::number(row);`
18. `rangStr = "A1:" + rangStr;`
19. `qDebug()<<rangStr;`
20. `QAxObject *range = this->sheet->querySubObject("Range(const QString&)",rangStr);`
21. `if(NULL == range || range->isNull())`
22. `{`
23. `return false;`
24. `}`
25. `bool succ = false;`
26. `QVariant var;`
27. `castListListVariant2Variant(cells,var);`
28. `succ = range->setProperty("Value", var);`
29. `delete range;`
30. `return succ;`
31. `}`

此函数是把数据从A1开始写

函数中的`convertToColName`为把列数，转换为excel中用字母表示的列数，这个函数是用递归来实现的：

1.  `///`
2.  `/// \brief 把列数转换为excel的字母列号`
3.  `/// \param data 大于0的数`
4.  `/// \return 字母列号，如1->A 26->Z 27 AA`
5.  `///`
6.  `void ExcelBase::convertToColName(int data, QString &res)`
7.  `{`
8.  `Q_ASSERT(data>0 && data<65535);`
9.  `int tempData = data / 26;`
10. `if(tempData > 0)`
11. `{`
12. `int mode = data % 26;`
13. `convertToColName(mode,res);`
14. `convertToColName(tempData,res);`
15. `}`
16. `else`
17. `{`
18. `res=(to26AlphabetString(data)+res);`
19. `}`
20. `}`
21. `///`
22. `/// \brief 数字转换为26字母`
23. `///`
24. `/// 1->A 26->Z`
25. `/// \param data`
26. `/// \return`
27. `///`
28. `QString ExcelBase::to26AlphabetString(int data)`
29. `{`
30. `QChar ch = data + 0x40;//A对应0x41`
31. `return QString(ch);`
32. `}`

看看写excel的耗时：

1.  `void MainWindow::on_action_write_triggered()`
2.  `{`
3.  `QString xlsFile = QFileDialog::getExistingDirectory(this);`
4.  `if(xlsFile.isEmpty())`
5.  `return;`
6.  `xlsFile += "/excelRWByCztr1988.xls";`
7.  `QElapsedTimer timer;`
8.  `timer.start();`
9.  `if(m_xls.isNull())`
10. `m_xls.reset(new ExcelBase);`
11. `m_xls->create(xlsFile);`
12. `qDebug()<<"create cost:"<<timer.elapsed()<<"ms";timer.restart();`
13. `QList< QList<QVariant> > m_datas;`
14. `for(int i=0;i<1000;++i)`
15. `{`
16. `QList<QVariant> rows;`
17. `for(int j=0;j<100;++j)`
18. `{`
19. `rows.append(i*j);`
20. `}`
21. `m_datas.append(rows);`
22. `}`
23. `m_xls->setCurrentSheet(1);`
24. `timer.restart();`
25. `m_xls->writeCurrentSheet(m_datas);`
26. `qDebug()<<"write cost:"<<timer.elapsed()<<"ms";timer.restart();`
27. `m_xls->save();`
28. `}`

输出：

1.  `create cost: 814 ms`
2.  `"A1:CV1000"`
3.  `write cost: 262 ms`

写10万个数据耗时262ms，有木有感觉很快，很强大

# 结论 #

*  Qt在windows下读写excel最快速的方法还是使用QAxObject
 *  不要使用类似`sheet->querySubObject("Cells(int, int)", row, col);`的方式读写excel，这是导致低效的更本原因
    
        void CMainWindow::openExcel(QString fileName)
        {
            QAxObject excel("Excel.Application");
            excel.setProperty("Visible", false);
            QAxObject *work_books = excel.querySubObject("WorkBooks");
            work_books->dynamicCall("Open(const QString&)", fileName);
        
            QAxObject *work_book = excel.querySubObject("ActiveWorkBook");
            QAxObject *work_sheets = work_book->querySubObject("Sheets");  //Sheets也可换用WorkSheets
        
            int sheet_count = work_sheets->property("Count").toInt();  //获取工作表数目
            if (sheet_count > 0)
            {
                QAxObject *work_sheet = work_book->querySubObject("Sheets(int)", 1);
        
                ui.label->setText("文件数据读取中...");
                QVariant var = readAll(work_sheet);
                castVariant2ListListVariant(var);
            }
        
            work_book->dynamicCall("Close(Boolean)", false);  //关闭文件
            excel.dynamicCall("Quit(void)");  //退出
        }
        
        QVariant CMainWindow::readAll(QAxObject *sheet)
        {
            QVariant var;
            if (sheet != NULL && !sheet->isNull())
            {
                QAxObject *usedRange = sheet->querySubObject("UsedRange");
                if (NULL == usedRange || usedRange->isNull())
                {
                    return var;
                }
                var = usedRange->dynamicCall("Value");
                delete usedRange;
            }
            return var;
        }
        
        void CMainWindow::castVariant2ListListVariant(const QVariant &var)
        {
            QVariantList varRows = var.toList();
            if (varRows.isEmpty())
            {
                return;
            }
            const int rowCount = varRows.size();
            QVariantList rowData;
            for (int i = 0; i < rowCount; ++i)
            {
                rowData = varRows[i].toList();
        
                if (i == 0)
                {
                    QStringList headers;
                    for each (auto item in rowData)
                    {
                        QString value = item.toString();
                        headers.append(value);
                    }
                    ui.tableWidget->setColumnCount(headers.size()); //设置列数
                    ui.tableWidget->setHorizontalHeaderLabels(headers);
                }
                else
                {
                    int row = ui.tableWidget->rowCount();
                    ui.tableWidget->setRowCount(row + 1);
                    for (int j = 0; j < rowData.size(); j++)
                    {
                        QString value = rowData[j].toString();
                        QTableWidgetItem *item = new QTableWidgetItem(value);
                        ui.tableWidget->setItem(row, j, item);
                    }
                }
            }
        }
    
    （6）[https://www.cnblogs.com/wangjian8888/p/9176662.html][https_www.cnblogs.com_wangjian8888_p_9176662.html]

[https_github.com_dbzhang800_QtXlsxWriter]: https://github.com/dbzhang800/QtXlsxWriter
[QT_Excel]: http://blog.csdn.net/a1069962325/article/details/49514377
[20160804175451390]: /images/20230531/aa919de015be4343994598c673883d67.png
[20160804174250619]: /images/20230531/0f8ae5fb379b4bbf85eea1a9aed22cd9.png
[20160804174259744]: /images/20230531/df7aa221b07e48639aba3d527e6b1d2c.png
[https_www.cnblogs.com_wangjian8888_p_9176662.html]: https://www.cnblogs.com/wangjian8888/p/9176662.html