admin管理员组文章数量:1609530
1.前言:
学习qt表格控制,写博文记录:
【QT表格-1】QStandardItem的堆内存释放需要单独delete,还是随QStandardItemModel的remove或clear自动销毁?
【QT表格-2】QTableWidget单元格结束编辑操作endEditting_qtablewidget 单元格编辑事件
【QT表格-3】QTableWidget导入/导出excel通用代码,不需要安装office,不依赖任何多余环境,甚至不依赖编程语言
【QT表格-4】由QTableView/QTableWidget显示进度条和按钮,理解qt代理delegate用法
【QT表格-5】QTableView用代码设置选中状态
【QT表格-6】QTableWidget的currentCellChanged实现中途撤销
导出excel已经不是新鲜事,但有时候很方便,例如有些软件需要定制打印,有时候可以不用单独做打印,只要导出excel并打开就行了,比自己做打印更方便。
这次记录的实现方式,其实多年前我们在项目中就用过。如今玩qt,原本想从网上找个好用的就算了,最开始写这个博客时,找出来大部分都是AXObject方式,看名字就知道特别像activeX,真用一下就知道,跟早些年的OLE差不多,因为它要依赖环境。亦即,需要本机安装office。这就不爽!
所以又重拾以前的思路,重做了一版。但这种方法不是完美的,有兴趣可以看看openxml相关。现在有qxlsx,比我这个方法好多了,当初早知道有这个我肯定不费这劲。所以,这里只是记录一种思路。
2.原理:
这次的方法,实现原理很简单。随便打开一个excel,用office还是wps无所谓,另存为html,聪明的你看到这里应该不用我说了。因为这并不是什么高水平应用,这只是一个投机取巧,但是实用的做法。
但最大的好处,就是本机不用安装office或者wps,因为它是文本级别的操作,如果excel可以跨平台,这种实现方式可以应用到随便任何一种平台上。只要电子表格支持html格式,它就能用。
下面只是个记录。
3.实现步骤:
3.1.excel的html格式:
我使用wps随便新建一个电子表格文件,录入一些内容,设置一下边框。其它看需要你随便玩就行。
excel文件另存为html以后,用记事本或者其它网页编辑器,或者随便什么文本编辑器,打开它。看到如下类似代码:
<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3/TR/REC-html40">
<head>
省略16393字...
</head>
<body link="blue" vlink="purple">
<table width="287" border="0" cellpadding="0" cellspacing="0" style='width:215.25pt;border-collapse:collapse;table-layout:fixed;'>
<col width="72" style='width:54.00pt;'/>
<col width="88" style='mso-width-source:userset;mso-width-alt:2816;'/>
<col width="127" style='mso-width-source:userset;mso-width-alt:4064;'/>
<tr height="18" style='height:13.50pt;'>
<td class="xl65" height="18" width="72" style='height:13.50pt;width:54.00pt;' x:str>表头1</td>
<td class="xl65" width="88" style='width:66.00pt;' x:str>表头2</td>
<td class="xl65" width="127" style='width:95.25pt;' x:str>表头3</td>
</tr>
<tr height="18" style='height:13.50pt;'>
<td class="xl66" height="18" align="right" style='height:13.50pt;' x:num>123</td>
<td class="xl66" align="right" x:num>345</td>
<td class="xl66" align="right" x:num>4567</td>
</tr>
<tr height="18" style='height:13.50pt;'>
<td class="xl66" height="18" style='height:13.50pt;' x:str>qwe</td>
<td class="xl66" x:str>zxf</td>
<td class="xl66" x:str>tyi</td>
</tr>
<tr height="18" style='height:13.50pt;'>
<td class="xl66" height="18" style='height:13.50pt;' x:str>56h</td>
<td class="xl66" x:str>mi7</td>
<td class="xl67" x:str>45rv</td>
</tr>
<![if supportMisalignedColumns]>
<tr width="0" style='display:none;'>
<td width="88" style='width:66;'></td>
<td width="127" style='width:95;'></td>
</tr>
<![endif]>
</table>
</body>
</html>
其实看到这里,只要是会写程序的,一看就知道后面怎么玩了。无非就是标签文本替换而已。
3.2.简化html脚本:
上面随便简单的电子表格,生成了太多html脚本,为了浏览方便,上面只是head部分我就省略了不到两万字符。如果保留相对有用的部分,简化如下:
<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="ProgId" content="Excel.Sheet">
<meta name="Generator" content="WPS Office ET">
<style>
<!--
td
{border:.5pt solid windowtext;}
--> </style>
<!--[if gte mso 9]>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>270</x:DefaultRowHeight>
<x:Selected/>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveCol>0</x:ActiveCol>
<x:ActiveRow>0</x:ActiveRow>
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
<x:PageBreakZoom>100</x:PageBreakZoom>
<x:Print>
<x:PaperSizeIndex>9</x:PaperSizeIndex>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
<x:WindowHeight>12420</x:WindowHeight>
<x:WindowWidth>28125</x:WindowWidth>
</x:ExcelWorkbook>
</xml>
<![endif]-->
</head>
<body link="blue" vlink="purple">
<table>
<col width="80" align="center" />
<col width="50" align="right" />
<col width="100" />
<col width="200" />
<tr>
<td>f1</td>
<td>f2</td>
<td>f3</td>
<td>f4</td>
</tr>
<tr>
<td>1</td>
<td>2</td>
<td>3</td>
<td>4</td>
</tr>
<tr>
<td>a</td>
<td>b</td>
<td>c</td>
<td>d</td>
</tr>
<tr>
<td>cwd</td>
<td>qwe</td>
<td>xcz</td>
<td>excz</td>
</tr>
</table>
</body>
</html>
熟悉html或者xml的,一看便知。其中几个关键部分:
样式那里,定义一个表格单元格的边框样式。
xml那里,定制一些excel属性,比如Sheet1的名称,开始默认选中范围等。
再往下就是table,tr,td。记着从二十年前第一次接触html,我看了一眼就知道怎么玩了,所以我相信,对于各位同行,这个一眼就会。
这里table中多了一个col项,只是以列为单位统一设置而已。
3.3.模板:
上面这个简化过的html,我把它重命名为excel_html.md,所谓md就是model的意思,以后它就是个模板。
以后需要导出excel的时候,程序里面可以先读取模板文件,当然也可以不使用模板文件,反正都是字符串,也可以存入数据库。利用正则表达式替换其中的关键部分,以实现设置。
比如说,可以通过col项设置列宽和列对齐,通过<x:Name>Sheet1</x:Name>这里设置Sheet1的名字等。
3.4.代码:
下面是我写了一个例子当中的代码节选。核心功能就俩函数。主要是针对<table>标签的<tr>和<td>处理。其中我用正则表达式过滤的一些无用标签。
代码是经过修改的,尽量通用化,理论上复制下来就能用。宏QTHQ_BASESHARED_EXPORT 删掉即可,原本是用于制作成dll的选项的,具体可以参考我的其它文章。
Qt制作dll(带ui)并调用,兼容32位和64位_redeclared without dllimport_大橘的博客-CSDN博客
hq_base_db_grid_excel.h:
/**************************************************************************************************
** File name: hq_base_db_grid_excel.h (HQ_Base_DB_Grid_Excel)
** Created by: Henrick.Nie at 2023-7-10
** Used for: Import/export the grid data from/to the excel file.
**************************************************************************************************/
#ifndef HQ_BASE_DB_GRID_EXCEL_H
#define HQ_BASE_DB_GRID_EXCEL_H
#include "qt-hq_base_global.h"
#include <QFile>
#include <QFileDialog>
#include <QDesktopServices>
#include <QRegularExpression>
#include <QDomDocument>
#include <QDomElement>
#include <QMessageBox>
#include "hq_base_db_grid.h"
class QTHQ_BASESHARED_EXPORT HQ_Base_DB_Grid_Excel
{
public:
static bool f_SaveToExcel(QTableWidget *oGrid,
const bool &bAskBeforeDoing, const bool &bAskBeforeOpenning, QString *sFilePath = nullptr,
QString *sErr = nullptr, const QString &sSheetName = QString());
static bool f_SaveToExcel(const QString &sExcelHtmlModel, QTableWidget *oGrid,
const bool &bAskBeforeDoing, const bool &bAskBeforeOpenning, QString *sFilePath = nullptr,
QString *sErr = nullptr, const QString &sSheetName = QString());
static bool f_GetFromExcel_AsXML(QString &sXML, QString *sErr = nullptr);
static bool f_GetFromExcel(const QString &sModel, QJsonArray &oArrRows, const bool &bFirstRowIsHeader = false, QString *sErr = nullptr);
static bool f_GetFromExcel(QTableWidget *oGrid, const bool &bFirstRowIsHeader = false, QString *sErr = nullptr);
static bool f_SetFirstRowToHeader(QTableWidget *oGrid, const bool &bSetHeader, QString *sErr = nullptr);
private:
static bool f_Grid_GetDataAsHtml(QTableWidget *oGrid, QString &sTableHtml, QString *sErr = nullptr);
static QString f_Grid_GetAlignByEnum(const int &iAlign);
static bool f_Excel_GetDataAsHtml(QString &sTableHtml, QString *sErr = nullptr);
};
#endif // HQ_BASE_DB_GRID_EXCEL_H
hq_base_db_grid_excel.cpp:
#include "hq_base_db_grid_excel.h"
/**================================================================================================
** Converts the grid data as the html table string, output it in the parameter 'sTableHtml'.
** Returns true on successed, otherwise returns false.
** ---------------------------------
** Format:
**
** <table>
** <col width="80" align="center" />
** <col width="50" align="right" />
** <col width="100" />
** <col width="200" />
** <tr>
** <td >f1</td>
** <td >f2</td>
** <td >f3</td>
** <td >f4</td>
** </tr>
** <tr>
** <td >1</td>
** <td >2</td>
** <td >3</td>
** <td >4</td>
** </tr>
** <tr>
** <td >a</td>
** <td >b</td>
** <td >c</td>
** <td >d</td>
** </tr>
** <tr>
** <td >cwd</td>
** <td >qwe</td>
** <td >xcz</td>
** <td >excz</td>
** </tr>
** </table>
**
**===============================================================================================*/
bool HQ_Base_DB_Grid_Excel::f_Grid_GetDataAsHtml(QTableWidget *oGrid, QString &sTableHtml, QString *sErr)
{
sTableHtml.clear();
if (nullptr == oGrid)
{
HQ_Base::f_Pointer_SetValue(sErr, QString("The grid is nullptr."));
return false;
}
//Indent
QString sTable_col, sTable_Rows_tr, sIndent_table = " ", sIndent_col = " ", sIndent_tr = " ", sIndent_td = " ";
//Headers and col info(<col/>)
sTable_Rows_tr.append(sIndent_tr + "<tr>\n");
QString sHeader_td;
for (int iCol = 0; iCol < oGrid->columnCount(); ++iCol)
{
//width
QString sCol_Width = QString::number(oGrid->columnWidth(iCol));
//align
QTableWidgetItem *item = oGrid->horizontalHeaderItem(iCol);
int iAlign = (nullptr == item) ? 0 : item->textAlignment();
QString sCol_Align = f_Grid_GetAlignByEnum(iAlign);
sTable_col.append(sIndent_col + "<col width=\"" + sCol_Width + "\" align=\"" + sCol_Align + "\" />\n");
//header row
sHeader_td.append(sIndent_td + "<td align=\"center\">" + oGrid->horizontalHeaderItem(iCol)->text() + "</td>\n");
}
sTable_Rows_tr.append(sHeader_td);
sTable_Rows_tr.append(sIndent_tr + "</tr>\n");
//data(<tr/><td/>)
for (int iRow = 0; iRow < oGrid->rowCount(); ++iRow)
{
sTable_Rows_tr.append(sIndent_tr + "<tr>\n");
QString sRows_td;
for (int iCol = 0; iCol < oGrid->columnCount(); ++iCol)
{
QTableWidgetItem *item = oGrid->item(iRow, iCol);
QString sText;
if (nullptr != item)
{
sText = item->text();
}
sRows_td.append(sIndent_td + "<td>" + sText + "</td>\n");
}
sTable_Rows_tr.append(sRows_td);
sTable_Rows_tr.append(sIndent_tr + "</tr>\n");
}
//table
sTableHtml.append(sIndent_table + "<table>\n");
sTableHtml.append(sTable_col);
sTableHtml.append(sTable_Rows_tr);
sTableHtml.append(sIndent_table + "</table>\n");
HQ_Base::f_Pointer_SetValue(sErr, QString());
return true;
}
QString HQ_Base_DB_Grid_Excel::f_Grid_GetAlignByEnum(const int &iAlign)
{
quint16 iHAlign = iAlign | 0x000F;
QString sAlign;
switch (iHAlign) {
case 1:
sAlign = "left";
break;
case 2:
sAlign = "right";
break;
case 4:
sAlign = "center";
break;
default:
break;
}
return sAlign;
}
/**================================================================================================
** Save the grid data to excel.
**===============================================================================================*/
bool HQ_Base_DB_Grid_Excel::f_SaveToExcel(QTableWidget *oGrid,
const bool &bAskBeforeDoing, const bool &bAskBeforeOpenning,
QString *sFilePath, QString *sErr, const QString &sSheetName)
{
try
{
//Get the model.
QString sFile = "excel_html.md";
QFile f(sFile);
if (!f.exists())
{
throw QString("Can not find the model file.");
}
if (!f.open(QIODevice::ReadOnly|QIODevice::Text))
{
if (f.isOpen())
{
f.close();
}
throw QString("The model file openning failed.");
}
QString sExcelHtml = f.readAll();
if (f.isOpen())
{
f.close();
}
return f_SaveToExcel(sExcelHtml, oGrid, bAskBeforeDoing, bAskBeforeOpenning, sFilePath, sErr, sSheetName);
}
catch (QString sExceptionMsg)
{
HQ_Base::f_Pointer_SetValue(sErr, sExceptionMsg);
return false;
}
}
bool HQ_Base_DB_Grid_Excel::f_SaveToExcel(const QString &sExcelHtmlModel, QTableWidget *oGrid,
const bool &bAskBeforeDoing, const bool &bAskBeforeOpenning,
QString *sFilePath, QString *sErr, const QString &sSheetName)
{
try
{
HQ_Base::f_Pointer_SetValue(sFilePath, QString());
HQ_Base::f_Pointer_SetValue(sErr, QString());
QString sExcelHtml = sExcelHtmlModel;
if (sExcelHtml.isEmpty())
{
throw QString("The excel model lost.");
}
if (nullptr == oGrid)
{
throw QString("The grid does not exist.");
}
QString sMsg;
//Ask user before doing.
if (bAskBeforeDoing)
{
sMsg = "Export the grid data to the excel. Continue?";
if(QMessageBox::question(nullptr, "", sMsg, QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes) != QMessageBox::Yes)
{
return false;
}
}
//Select the saving path.
QString sFile_Saved = QFileDialog::getSaveFileName(nullptr, "Select the saving path.",
QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation),
"Excel (*.xlsx);;Excel 97-2003 (*.xls)");
if (sFile_Saved.isEmpty())
{
return false;//If the user canceled, do nothing.
}
//If the target file is not writable.
QFile f_Save(sFile_Saved);
if (f_Save.isOpen() || !f_Save.open(QIODevice::WriteOnly|QIODevice::Text))
{
if (f_Save.isOpen())
{
f_Save.close();
}
throw QString("The target file is using. Perhaps it is already openned by an other program.");
}
//Sheet name.
if (!sSheetName.isEmpty())
{
sExcelHtml.replace(QRegularExpression("(?<=<x:Name>)[\\s\\S]*(?=</x:Name>)"), sSheetName);
}
//Html_table.
QString sTableHtml;
if (!f_Grid_GetDataAsHtml(oGrid, sTableHtml, sErr))
{
return false;
}
sExcelHtml.replace(QRegularExpression("<table>[\\s\\S]*</table>"), sTableHtml);
//Save to file.
qint64 i = f_Save.write(sExcelHtml.toStdString().c_str());
if (f_Save.isOpen())
{
f_Save.close();
}
if (i < 0)
{
throw QString("The file writting failed.");
}
//Ask before openning.
if (bAskBeforeOpenning)
{
sMsg = "The excel file exporting successed, open it?\n";
if(QMessageBox::question(nullptr, "", sMsg + sFile_Saved, QMessageBox::Yes|QMessageBox::No) == QMessageBox::Yes)
{
//Open the file.
QDesktopServices::openUrl(QUrl("file:///" + QDir::toNativeSeparators(sFile_Saved)));
}
}
HQ_Base::f_Pointer_SetValue(sFilePath, sFile_Saved);
HQ_Base::f_Pointer_SetValue(sErr, QString());
return true;
}
catch (QString sExceptionMsg)
{
HQ_Base::f_Pointer_SetValue(sFilePath, QString());
HQ_Base::f_Pointer_SetValue(sErr, sExceptionMsg);
return false;
}
}
/**================================================================================================
** Import the excel data to the html string, and output into the parameter 'sTableHtml'.
**===============================================================================================*/
bool HQ_Base_DB_Grid_Excel::f_Excel_GetDataAsHtml(QString &sTableHtml, QString *sErr)
{
try
{
sTableHtml.clear();
HQ_Base::f_Pointer_SetValue(sErr, QString());
QString sFileName = QFileDialog::getOpenFileName(nullptr, "Select the saving path.",
QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation),
"Excel (*.xlsx);;Excel 97-2003 (*.xls)");
if (sFileName.isEmpty())
{
return false;//If the user canceled, do nothing.
}
QFile f(sFileName);
if (!f.open(QIODevice::ReadOnly|QIODevice::Text))
{
if (f.isOpen())
{
f.close();
}
throw QString("The excel file openning failed.");
}
QString sExcelHtml = f.readAll();
if (f.isOpen())
{
f.close();
}
QRegularExpressionMatch match = QRegularExpression("<table[^>]*>[\\s\\S]*</table>").match(sExcelHtml);
if (!match.hasMatch())
{
throw QString("The excel file format is error.");
}
sTableHtml = match.captured();
sTableHtml.replace(QRegularExpression("\\bx:[^ <>]+\\b"), "");//remove 'x:str' or 'x:num', 'x:...'.
sTableHtml.replace(QRegularExpression("<col[^>]*/>"), "");//remove '<col...>'.
sTableHtml.replace(QRegularExpression("<!\\[if[^>]*\\]>[\\S\\s]*<!\\[endif\\]>"), "");//remove '<![if]>...<![endif]>'.
HQ_Base::f_Pointer_SetValue(sErr, QString());
return true;
}
catch (QString sExceptionMsg)
{
HQ_Base::f_Pointer_SetValue(sErr, sExceptionMsg);
return false;
}
}
/**================================================================================================
** Import the excel data to the xml string, and output into the parameter 'sXML'.
**===============================================================================================*/
bool HQ_Base_DB_Grid_Excel::f_GetFromExcel_AsXML(QString &sXML, QString *sErr)
{
try
{
sXML.clear();
//Import the excel data into the variable 'sTableHtml' as html.
QString sTableHtml;
if (!f_Excel_GetDataAsHtml(sTableHtml, sErr))
{
return false;
}
QDomDocument doc;
QString sErrMsg;
int iRow, iCol;
if (!doc.setContent(sTableHtml, &sErrMsg, &iRow, &iCol))
{
throw sErrMsg + ", the position in excel file is: row = " + QString::number(iRow)
+ ", col = " + QString::number(iCol) + ".";
}
//Get '<table>...</table>', and remove the nodes that is not '<tr/>'.
QDomNode node_Table = doc.firstChild();
int i = 0;
while (i < node_Table.childNodes().size())
{
QDomNode node = node_Table.childNodes().at(i);
if (node.nodeName() == "tr")
{
++i;
}
else
{
node_Table.removeChild(node);
}
}
doc.replaceChild(node_Table, doc.firstChild());
sXML = doc.toString();
HQ_Base::f_Pointer_SetValue(sErr, QString());
return true;
}
catch (QString sExceptionMsg)
{
HQ_Base::f_Pointer_SetValue(sErr, sExceptionMsg);
return false;
}
}
/**================================================================================================
** Import the data from the excel file to the json array by the given model.
**===============================================================================================*/
bool HQ_Base_DB_Grid_Excel::f_GetFromExcel(const QString &sModel, QJsonArray &oArrRows, const bool &bFirstRowIsHeader, QString *sErr)
{
try
{
oArrRows = QJsonArray();
//Import the excel data into the variable 'sXML' as the xml string.
QString sXML;
if (!f_GetFromExcel_AsXML(sXML, sErr))
{
return false;
}
QDomDocument doc;
QString sErrMsg;
int iRow, iCol;
if (!doc.setContent(sXML, &sErrMsg, &iRow, &iCol))
{
throw sErrMsg + ", the position in excel file is: row = " + QString::number(iRow)
+ ", col = " + QString::number(iCol) + ".";
}
QDomNode node_Table = doc.firstChild();
//Get the cols info from the model.
QJsonObject oJsonModel = HQ_Base::f_Json_StringToJsonObj(sModel);
if (oJsonModel.isEmpty())
{
throw QString("The given model string can not convert to json object.");
}
QStringList sColList = oJsonModel.value("Cols_Visible").toString().split("|", QString::SkipEmptyParts);
//If it has the header, remove the first node.
if (bFirstRowIsHeader)
{
node_Table.removeChild(node_Table.firstChild());
}
//tr (rows)
int iTrCount = node_Table.childNodes().size();
for (int i_tr = 0; i_tr < iTrCount; ++i_tr)
{
QDomNode node_tr = node_Table.childNodes().at(i_tr);
QJsonObject oJsonRow;
//td (cells)
for (int iCol = 0; iCol < sColList.size(); ++iCol)
{
QString sColName = sColList.at(iCol);
QString sValue;
if (iCol < node_tr.childNodes().size())
{
QDomElement el_td = node_tr.childNodes().at(iCol).toElement();
sValue = el_td.text();
}
oJsonRow.insert(sColName, sValue);
}
oArrRows.append(oJsonRow);
}
HQ_Base::f_Pointer_SetValue(sErr, QString());
return true;
}
catch (QString sExceptionMsg)
{
HQ_Base::f_Pointer_SetValue(sErr, sExceptionMsg);
return false;
}
}
/**================================================================================================
** Import the data from the excel file to the grid.
**===============================================================================================*/
bool HQ_Base_DB_Grid_Excel::f_GetFromExcel(QTableWidget *oGrid, const bool &bFirstRowIsHeader, QString *sErr)
{
try
{
//Import the excel data into the variable 'sXML' as the xml string.
QString sXML;
if (!f_GetFromExcel_AsXML(sXML, sErr))
{
return false;
}
QDomDocument doc;
QString sErrMsg;
int iRow, iCol;
if (!doc.setContent(sXML, &sErrMsg, &iRow, &iCol))
{
throw sErrMsg + ", the position in excel file is: row = " + QString::number(iRow)
+ ", col = " + QString::number(iCol) + ".";
}
QDomNode node_Table = doc.firstChild();
//tr
int iTrCount = node_Table.childNodes().size();
int iRowCount = bFirstRowIsHeader ? iTrCount - 1 : iTrCount;
oGrid->setRowCount(iRowCount);
for (int i_tr = 0; i_tr < iTrCount; ++i_tr)
{
QDomNode node_tr = node_Table.childNodes().at(i_tr);
//td
if (oGrid->columnCount() < node_tr.childNodes().size())
{
oGrid->setColumnCount(node_tr.childNodes().size());
}
for (int i_td = 0; i_td < node_tr.childNodes().size(); ++i_td)
{
QDomElement el_td = node_tr.childNodes().at(i_td).toElement();
QTableWidgetItem *item = new QTableWidgetItem(el_td.text());
if (bFirstRowIsHeader)
{
if (i_tr == 0)
{
oGrid->setHorizontalHeaderItem(i_td, item);
}
else
{
oGrid->setItem(i_tr - 1, i_td, item);
}
}
else
{
oGrid->setItem(i_tr, i_td, item);
}
}
}
HQ_Base::f_Pointer_SetValue(sErr, QString());
return true;
}
catch (QString sExceptionMsg)
{
HQ_Base::f_Pointer_SetValue(sErr, sExceptionMsg);
return false;
}
}
bool HQ_Base_DB_Grid_Excel::f_SetFirstRowToHeader(QTableWidget *oGrid, const bool &bSetHeader, QString *sErr)
{
try
{
//made header
if (bSetHeader)
{
if (oGrid->rowCount() < 1)
{
throw QString("Operation canceled. For the grid row count is lesser than 1.");
}
for (int iCol = 0; iCol < oGrid->columnCount(); ++iCol)
{
QTableWidgetItem *item = oGrid->horizontalHeaderItem(iCol);
if (nullptr == item)
{
item = new QTableWidgetItem;
}
QString sFirstRowCellText;
if (nullptr != oGrid->item(0, iCol))
{
sFirstRowCellText = oGrid->item(0, iCol)->text();
}
item->setText(sFirstRowCellText);
oGrid->setHorizontalHeaderItem(iCol, item);
delete oGrid->item(0, iCol);
}
oGrid->removeRow(0);
}
//remove header
else
{
oGrid->insertRow(0);
for (int iCol = 0; iCol < oGrid->columnCount(); ++iCol)
{
QTableWidgetItem *item = oGrid->horizontalHeaderItem(iCol);
if (nullptr == item)
{
item = new QTableWidgetItem;
}
QString sText = item->text();
item->setText(QString::number(iCol));
oGrid->setHorizontalHeaderItem(iCol, item);
item = new QTableWidgetItem(sText);
oGrid->setItem(0, iCol, item);
}
}
HQ_Base::f_Pointer_SetValue(sErr, QString());
return true;
}
catch (QString sExceptionMsg)
{
HQ_Base::f_Pointer_SetValue(sErr, sExceptionMsg);
return false;
}
}
代码是我又改了一版通用的。因为之前我的电子表格QTabWidget是自己包装过的,可以根据json模板自动加载表头和数据,并处理对齐,用起来更方便。
而我想把excel功能改得让它更通用,不依赖于我包装了类库。其它就没什么了。下面是我的程序运行情况。
4.运行测试:
首先在界面上添加一个按钮,在它的槽函数中写:
//导出excel:
QString sErr;
if (!HQ_Base_DB_Grid_Excel::f_SaveToExcel(ui->oGridMain, false, true, nullptr, &sErr, "SingleGrid"))
{
if (!sErr.isEmpty())
{
QMessageBox::warning(this, "Exporting failed", sErr, QMessageBox::Ok, QMessageBox::NoButton);
}
}
/* 这里oGridMain是我自己命名的,就是一个QTableWidget。
* 后面参数作用:执行前不询问,执行后询问是否要打开exce文件,
* excel文件路径传入nullptr不获取到外部程序,sheet1表单显示名称设置为“SingleGrid”。
*/
//导入excel:
QString sErr;
QString sModel = ui->oGridMain->f_GetModel(&sErr);
QJsonArray oArrRows;
if (!HQ_Base_DB_Grid_Excel::f_GetFromExcel(sModel, oArrRows, false, &sErr))
{
if (!sErr.isEmpty())
{
QMessageBox::warning(this, "Importing failed", sErr, QMessageBox::Ok, QMessageBox::NoButton);
}
return;
}
ui->oGridMain->addRow(oArrRows);
/* 这里oGridMain是用了我自己做的一个表格类。可以自动根据模板加载表头和数据。
* 如果读者想直接用通用的QTableWidget,可以挑选f_GetFromExcel的其它重载。
*/
很简单,主要就调用f_GetFromExcel函数这一行。然后效果就出来了。
代码中又尝试做了excel导入功能,道理类似。就不再贴图了。当然要注意,这种方式既然是基于文本替换实现的,那导入时对文件也有要求,把excel另存为html,或者粘贴到一个html格式的excel文件中。这样才可以被代码解析。
这里我做了取舍,不想依赖office或wps环境,导入时就多了这一步。除非像开发一个wps一样,能够彻底独立操作excel。
上述方法仅仅是一个思路。网上axobject和openxml的方式都有代码,就是不喜欢ax模式,openxml的代码,qt在处理zip时用到了私有库,想去掉警告还挺麻烦的。因为我想实现一个真正没有任何强依赖,又好用的东西。当然目前我做的还差得远。
5.总结:
开头说过,这种方法不完美,但不仅仅是因为上述原因。亲测,wps支持较好,office不行,毕竟这种方法太粗暴。网上看过openxml相关代码,貌似都在vs环境下使用。目前还没想到更好方法,也不想浪费过多时间。
各位高手可以讨论一下,如果有更好的方式,记得交流一下。
本文完。
版权声明:本文标题:【QT表格-3】QTableWidget导入导出excel通用代码,不需要安装office,不依赖任何多余环境,甚至不依赖编程语言 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/dongtai/1728572301a1164343.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论