【QT】使用Qxlsx读取Excel单元格中函数表达式的结果值
【QT】使用Qxlsx读取Excel单元格中函数表达式的结果值
零、起因
是这样的，目前朋友托我写一款模板生成软件，任务是先把他写的程序文件复制一份出来，然后再根据Excel中对应位置的单元格的值，修改程序文件副本中的某些文件。对于读Excel的需求，经过测试，最终选择Qxlsx这款开源QT组件来读取Excel中的值，但是Excel中有些单元格用的是Excel函数，使用Document::read函数读出来的是Excel函数表达式，而不是Excel函数表达式函数的结果。故开启本文……
壹、调查
网络调查
通过询问AI、搜索等方式，尝试寻求使用Qxlsx读取Excel单元格中函数表达式结果值的方法，可惜的是我没有找到相应的方法。
不过在与AI对话的过程中，我得到了一个重要的线索：
在AI列举的这些库中，大多都是不支持Excel函数表达式值的读取的，想一想也正常，毕竟写一套解析引擎代码比写一套读取内容代码复杂多了，后面是想参考一下其他能读Excel函数表达式值的库的做法，问下来发现openpyxl支持读Excel函数表达式值，而且原理这里也提到了，是因为Excel文件中的函数表达式的结果值会被保存在单元格中，而openpyxl并不是经过计算得出的，而是直接读出保存在单元格中的结果值的。而且从最后一句话中，我们应该能得出这样的结论：Excel文件保存了什么就只能读出什么，没有保存的是读不出的。
既然得到这样的线索，而且我们也能拿到Qxlsx的源码，那在读取阶段只需要读一下结果值存在Cell对象中不就可以了？
XLSX文件结构调查
众所周知，.xlsx文件本质上是一个压缩包，于是我先创建了一个包含有函数表达式的Excel文件并保存为excel.xlsx。
把excel.xlsx文件使用7z解压，得到如下目录结构：
Microsoft Windows [版本 10.0.22631.4460]
(c) Microsoft Corporation。保留所有权利。
D:\y17mm\Desktop\xlsx文件分析>tree /f
卷 井中月 的文件夹 PATH 列表
卷序列号为 FE8F-1AB3
D:.
│ excel.xlsx
│
└─excel
│ [Content_Types].xml
│
├─docProps
│ app.xml
│ core.xml
│ custom.xml
│
├─xl
│ │ sharedStrings.xml
│ │ styles.xml
│ │ workbook.xml
│ │
│ ├─theme
│ │ theme1.xml
│ │
│ ├─worksheets
│ │ sheet1.xml
│ │
│ └─_rels
│ workbook.xml.rels
│
└─_rels
.rels
D:\y17mm\Desktop\xlsx文件分析>
简单观察文件后发现，表格文件里Sheet1的数据就存在excel/xl/worksheets/sheet1.xml中，汉字不存具体的值（但这个不需要我们关心，是从excel/xl/sharedStrings.xml中映射过来的），使用标签v与其他xml文档做映射，而函数表达式会存下来，标签是f，省略了等于号，函数表达式的值也会存下来，就与公式在同一个c标签中，用与保存其他文字相同的标签v来保存……
excel/xl/worksheets/sheet1.xml的内容：
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"
xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:etc="http://www.wps.cn/officeDocument/2017/etCustomData">
<sheetPr/>
<dimension ref="A1:B2"/>
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0">
<selection activeCell="B2" sqref="B2"/>
</sheetView>
</sheetViews>
<sheetFormatPr defaultColWidth="8.88888888888889" defaultRowHeight="14.4" outlineLevelRow="1" outlineLevelCol="1"/>
<cols>
<col min="1" max="1" width="9.11111111111111" customWidth="1"/>
</cols>
<sheetData>
<row r="1" spans="1:2">
<c r="A1" t="s">
<v>0</v>
</c>
<c r="B1" t="s">
<v>1</v>
</c>
</row>
<row r="2" spans="1:2">
<c r="A2" s="1" t="s">
<v>2</v>
</c>
<c r="B2" t="str">
<f>LEFT(B1,1)</f>
<v>钟</v>
</c>
</row>
</sheetData>
<pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5"/>
<headerFooter/>
</worksheet>
excel/xl/sharedStrings.xml的内容：
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="3" uniqueCount="3">
<si>
<t>名字</t>
</si>
<si>
<t>钟离</t>
</si>
<si>
<t>姓氏</t>
</si>
</sst>
真的有保存函数表达式的结果值，那Qxlsx实现读取Excel单元格中函数表达式的结果值就具有了可行性！
源码追踪调查
按理说Qxlsx的Document::read函数读出来的应该就是c标签中v标签的内容呀，可为啥读到的是c标签中f标签呢？
通过跟踪Qxlsx的读取单元格值的源码，不难发现这样一个函数：
发现在读取值时会先判断是否有公式，如果有，则返回等于号加公式文本，否则不是公式也不是日期时间，则使用cell->value()返回cell中的值。到这里，应该能想到解决方案了，我们只需要调用这个cellAt函数，并且不需要管是否有函数公式，直接调用返回value()的结果就好了。
贰、解决
基于以上结论，不难写出这样一个函数用于读取Excel中单元格函数表达式的结果值：
#include "xlsxdocument.h"
#include "xlsxcellformula.h"
#include "xlsxcell.h"
#include <QDebug>
bool isDebug = true;
QVariant readCellValue(QXlsx::Document& xlsx, const int& row, const int& col, const bool readVal=true){
if(readVal) {
auto* ws = xlsx.currentWorksheet();
auto *cell = ws->cellAt(row, col);
if(cell != nullptr && cell->hasFormula()) { // 存在公式
if(isDebug) qDebug()<<"公式";
if(isDebug) qDebug()<<"cell->formula().isValid()"<<cell->formula().isValid();
if(isDebug) qDebug()<<"cell->formula().formulaText()"<<cell->formula().formulaText();
if(isDebug) qDebug()<<"cell->formula().formulaType()"<<cell->formula().formulaType();
if(isDebug) qDebug()<<"cell->value()"<<cell->value();
QVariant v = cell->value();
QVariant emptyVariant;
if(v.toString().compare(emptyVariant.toString()) == 0) { // 如果值为空
return "=" + cell->formula().formulaText(); // 返回公式
} else {
return cell->value(); // 返回计算值
}
}
}
// cell->d_func()->value
return xlsx.read(row, col);
}
要读取值时判断是否存在函数表达式，如果存在，则尝试获取函数表达式的值，如果函数表达式有有效值则返回值，否则按照默认方式返回函数表达式。
经过调整，我们成功地拿到了Excel单元格中函数表达式的结果值：
叁、总结
Excel表格单元格中函数表达式的结果值一般会存在表格文件中的，理论上只要能读取xlsx表格的单元格的值的代码经过稍加改造都可以读到Excel单元格中函数表达式的结果值。本次Qxlsx读取Excel单元格中函数表达式的结果值代码总结如下：
int row = 2; // 行
int col = 2; // 列
QString xlsxFile = "D:\\y17mm\\Desktop\\xlsx文件分析\\excel.xlsx"; // 文件路径
QXlsx::Document xlsx(xlsxFile);
auto* ws = xlsx.currentWorksheet();
auto *cell = ws->cellAt(row, col);
auto ret = cell->value();
qDebug()<<"读取结果："<<ret;
浙公网安备 33010602011771号