admin 管理员组文章数量: 1184232
1.Qt原身库支持打开XLSX文件
QFileInfo fileInfo(strTempFilePath);
QString filePath = fileInfo.filePath();
if (fileInfo.suffix().compare("xlsx") == 0)
{
/// 根据路径获取xlsx文件对象
QXlsx::Document xlsx(filePath);
QXlsx::CellRange range = xlsx.dimension();
int lastRow = range.lastRow();
int lastColumn = range.lastColumn();
QStringList code_nums; code_nums.clear();
QStringList code_name; code_name.clear();
if (lastRow > lastColumn)//行大于列多半是行为字段列为字段值
{
for (int row = range.firstRow(); row <= range.lastRow(); row += 1)
{
QString strKey = xlsx.read(row, m_tColumnField).toString();
QString strValue = xlsx.read(row, m_tColumnField + 1).toString()；
}
}
else//列大于行多半是列为字段行为字段值
{
for (int column = range.firstColumn(); column <= range.lastColumn(); column += 1)
{
QString strKey = xlsx.read(m_tRowField, column).toString();
QString strValue = xlsx.read(m_tRowField + 1, column).toString();
}
}
}
2.对于XLS文件Qt源生的库不支持打开，可以通过QAxObject 基于COM系统组件通过本机安装的office或者WPS读取表格内容
if (fileInfo.suffix().compare("xls") == 0)
{
QAxObject* excel = new QAxObject(this);
QString filePath = fileInfo.filePath();//文件路径
int tColumnCount = m_tColumnField;
int tRowCount = m_tRowField;
if (excel->setControl("Excel.Application")) //office excel形式
{
}
else if(excel->setControl("ket.Application"))//wps格式
{
}
else
{
delete excel;
excel = nullptr;
return config;
}
excel->dynamicCall("SetVisible (bool Visible)", "false"); // 不显示窗体
excel->setProperty("DisplayAlerts", false);//不显示警告信息
QAxObject* workbooks = excel->querySubObject("WorkBooks");
workbooks->dynamicCall("Open (const QString&)", filePath);
QAxObject* work_book = excel->querySubObject("ActiveWorkBook");
QAxObject* work_sheets = work_book->querySubObject("Sheets");
QAxObject* work_sheet = work_book->querySubObject("Sheets(int)", 1);
QAxObject* used_range = work_sheet->querySubObject("UsedRange");
QAxObject* rows = used_range->querySubObject("Rows");
QAxObject* columns = used_range->querySubObject("Columns");
int lastRow = rows->property("Count").toInt(); //获取行数
int lastColumn = columns->property("Count").toInt(); //获取行数
int sheet_count = work_sheets->property("Count").toInt(); //获取工作表数目
if (sheet_count > 0)
{
if (lastRow > lastColumn)
{
for (int row = 1; row <= lastRow; row += 1)
{
QAxObject* cell = work_sheet->querySubObject("Cells(int, int)", row, tColumnCount);//获取单元格
QString strKey = cell->dynamicCall("Value2()").toString();
if (strKey.isNull())
{
continue;
}
QAxObject* cellValue = work_sheet->querySubObject("Cells(int, int)", row, tColumnCount+1);//获取单元格
QString strValue = cellValue->dynamicCall("Value2()").toString();
}
}
else
{
for (int column = 1; column <= lastColumn; column += 1)
{
QAxObject* cell = work_sheet->querySubObject("Cells(int, int)", tRowCount, column);//获取单元格
QString strKey = cell->dynamicCall("Value2()").toString();
if (strKey.isNull())
{
continue;
}
QAxObject* cellValue = work_sheet->querySubObject("Cells(int, int)", tRowCount +1, column);//获取单元格
QString strValue = cellValue->dynamicCall("Value2()").toString();
}
}
}
excel->dynamicCall("Quit()");//关闭对应程序
delete columns;
delete rows;
delete used_range;
delete work_sheet;
delete work_sheets;
delete work_book;
delete workbooks;
delete excel;
columns = nullptr;
rows = nullptr;
used_range = nullptr;
work_sheet = nullptr;
work_sheets = nullptr;
work_book = nullptr;
workbooks = nullptr;
excel = nullptr;
}
3.使用2进行XLS文件解析时需要重复打开关闭EXCEL或者WPS效率较低可以通过以下脚本将文件夹下XLS文件批量转换为XLSX文件再使用1解析效率显著提高
@echo off & title 批量转换 Xls 到 Xlsx 文件 基于 WPS 的 “批量转换 Xlsx 到 Csv 文件 ”修改
call :MakeVBS "%~0"
for /r %%a in (*.xls) do (
cls
echo 正在转换"%%~a"
Xls2Xlsx.vbs "%%~a"
)
cls&echo 转换完毕
del Xls2Xlsx.vbs
pause
exit
:MakeVBS
for /f "tokens=1 delims=[]" %%a in ('find /n "::Xls2Xlsx::" "%~1"') do set HH=%%~a
more +%HH% "%~1">Xls2Xlsx.vbs
goto :eof
::Xls2Xlsx::
const xlWorkbookDefault = 51
Set fso=CreateObject("Scripting.FileSystemObject")
XLS = WScript.Arguments(0)
XLSX = fso.GetFile(XLS).ParentFolder.Path & "\" & fso.GetBaseName(XLS) & ".xlsx"
Set objExcel = CreateObject("Excel.Application") //WPS为Ket.Application
Set objWorkbook = objExcel.Workbooks.Open(XLS)
objExcel.DisplayAlerts = FALSE
objExcel.Visible = TRUE
Set objWorksheet = objWorkbook.Worksheets("Sheet1")
objWorksheet.SaveAs XLSX, xlWorkbookDefault
objExcel.Quit
版权声明：本文标题：从零开始：使用Qt的QxWx模块解读XLS和XLSX表格文件的简单方法！ 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/b/1771534167a3545701.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论