因为这个问题困扰了我很久，所以在这里记录一下，顺便给可能会遇到类似问题的人提供一点帮助。
在qt中，如果用C++处理数据保存到excel，正常来说在pro文件中添加 axcontainer 然后就能够调用到excel，但是这只能适用于数量级没那么大时的需求。
我之前也只考虑用C++来完成需求，但是尝试过各种方法似乎都避免不了，主要是涉及到12h-24h的测试资料，大约几十万笔数据的保存，此时不管我用什么方法都会卡住，甚至挂掉，并且excel也是一个比较占内存的软件，所以我才说如果只用C++处理数据这一动作相对来说是很难实现的
那么我这里提供的思路就是引用python来进行excel的数据处理，所以我采用的方法是将数据分时间段保存为不同的excel，然后通过python来整合各个excel档，组合成一个完整的excel文件，这样后续方便测试人员的数据归纳。
# -*- coding: utf-8 -*-
import os
import sys
import openpyxl
from openpyxl import Workbook
def is_empty_worksheet(ws):
"""检查工作表是否为空"""
for row in ws.iter_rows():
for cell in row:
if cell.value is not None:
return False
return True
def merge_excel_files(output_file, input_directory):
try:
# 创建一个新的工作簿
wb = Workbook()
ws = wb.active
# 遍历输入目录中的所有 Excel 文件
for filename in os.listdir(input_directory):
if filename.endswith(".xlsx") or filename.endswith(".xls"):
file_path = os.path.join(input_directory, filename)
print(f"Processing file: {file_path}")
# 打开输入文件
input_wb = openpyxl.load_workbook(file_path, read_only=True)
# 遍历输入文件中的所有工作表
for sheet_name in input_wb.sheetnames:
input_ws = input_wb[sheet_name]
print(f"Processing sheet: {sheet_name}")
# 跳过空工作表
if is_empty_worksheet(input_ws):
print(f"Skipping empty sheet: {sheet_name}")
continue
# 如果目标工作簿中还没有该工作表，则创建
if sheet_name not in wb.sheetnames:
wb.create_sheet(title=sheet_name)
target_ws = wb[sheet_name]
# 找到目标工作表的最后一行非空行
last_row = target_ws.max_row
while last_row > 0 and all(cell.value is None for cell in target_ws[last_row]):
last_row -= 1
last_row += 1 # 下一行开始写入
# 如果是第一个文件，写入表头
if target_ws.max_row == 1 and all(cell.value is None for cell in target_ws[1]):
headers = [cell.value for cell in input_ws[1]]
target_ws.append(headers)
last_row += 1
# 复制数据
for row in input_ws.iter_rows(min_row=2, values_only=True):
if any(value is not None for value in row):
try:
target_ws.append(row)
last_row += 1
except Exception as e:
print(f"Error - Failed to append row to sheet '{sheet_name}': {e}")
continue
# 关闭输入文件
input_wb.close()
# 保存输出文件
wb.save(output_file)
print(f"Files have been merged and saved to: {output_file}")
except Exception as e:
print(f"Error - {e}")
sys.exit(1)
if __name__ == "__main__":
if len(sys.argv) != 3:
print("Usage: python pyxl.py <output_file> <input_directory>")
sys.exit(1)
output_file = sys.argv[1]
input_directory = sys.argv[2]
merge_excel_files(output_file, input_directory)
上面就是相关实现，这里主要是将excel进行组合，然后由于是分时间段保存的数据，所以在组合时要将空行给跳过，这样的话，组合在一起就是一个完整的测报了。
当然，要用到的python的openpyxl库：
pip install openpyxl那对于我来说，我是建立了一个按钮来触发：
void MainWindow::on_Merge_xl_btn_clicked()
{
// 选择输出文件路径
QString outputFilePath = QFileDialog::getSaveFileName(this, "Save Merged Excel File", "", "Excel Files (*.xlsx *.xls)");
if (outputFilePath.isEmpty()) {
return;
}
// 选择输入文件
QStringList inputFiles = QFileDialog::getOpenFileNames(this, "Select Excel Files to Merge", "", "Excel Files (*.xlsx *.xls)");
if (inputFiles.isEmpty()) {
return;
}
// 创建临时目录
QString tempDirPath = QDir::tempPath() + "/excel_merge_temp";
QDir().mkpath(tempDirPath);
// 将选中的文件复制到临时目录
for (const QString &inputFile : inputFiles) {
QString fileName = QFileInfo(inputFile).fileName();
QString tempFilePath = tempDirPath + "/" + fileName;
if (!QFile::exists(inputFile)) {
log_object->print_log("Source file does not exist: " + inputFile + "\n");
continue;
}
if (!QFile::copy(inputFile, tempFilePath)) {
log_object->print_log("Failed to copy file: " + inputFile + " to " + tempFilePath + "\n");
continue;
}
log_object->print_log("Copied file: " + inputFile + " to " + tempFilePath + "\n");
}
// 检查是否有文件成功复制
if (QDir(tempDirPath).entryList(QDir::Files).isEmpty()) {
log_object->print_log("No files were successfully copied to the temporary directory.\n");
QMessageBox::critical(this, "Error", "No files were successfully copied to the temporary directory.");
return;
}
// 构建 Python 脚本路径
QString pythonScriptPath = ":/pyt/pyxl.py"; // pyxl.py 已经嵌入到资源文件中
// 读取嵌入的 Python 脚本并写入临时文件
QTemporaryFile tempScriptFile;
if (!tempScriptFile.open()) {
log_object->print_log("Failed to create temporary Python script file\n");
return;
}
QFile resourceFile(pythonScriptPath);
if (!resourceFile.open(QIODevice::ReadOnly | QIODevice::Text)) {
log_object->print_log("Failed to open embedded Python script\n");
return;
}
QTextStream in(&resourceFile);
QString scriptContent = in.readAll();
resourceFile.close();
QTextStream out(&tempScriptFile);
out.setCodec("UTF-8"); // 设置输出流的编码为 UTF-8
out << scriptContent;
tempScriptFile.close();
// 启动 Python 脚本
QProcess process;
QStringList arguments;
arguments << tempScriptFile.fileName() << outputFilePath << tempDirPath;
process.start("python", arguments);
// 连接标准输出和标准错误输出信号
connect(&process, &QProcess::readyReadStandardOutput, [&]() {
QByteArray output = process.readAllStandardOutput();
log_object->print_log("Python script output: " + QString(output) + "\n");
});
connect(&process, &QProcess::readyReadStandardError, [&]() {
QByteArray error = process.readAllStandardError();
log_object->print_log("Python script error: " + QString(error) + "\n");
});
if (!process.waitForStarted()) {
log_object->print_log("Error - Failed to start Python script.\n");
QMessageBox::critical(this, "Error", "Failed to start Python script.");
return;
}
if (!process.waitForFinished()) {
log_object->print_log("Error - Python script execution failed.\n");
QMessageBox::critical(this, "Error", "Python script execution failed.");
return;
}
if (process.exitCode() == 0) {
log_object->print_log("Success - Files have been merged successfully.\n");
QMessageBox::information(this, "Success", "Files have been merged successfully!");
// 删除临时目录
QDir tempDir(tempDirPath);
tempDir.removeRecursively();
} else {
log_object->print_log("Error - Failed to merge files. Exit code: " + QString::number(process.exitCode()) + "\n");
QMessageBox::critical(this, "Error", "Failed to merge files. Exit code: " + QString::number(process.exitCode()));
// 删除临时目录
QDir tempDir(tempDirPath);
tempDir.removeRecursively();
}
}
这样的话，就完成了，然后如果想方便的话，可以将python文件放到资源文件里面，这样会避免不必要的麻烦，包括路径的处理之类的。
标签：存储,QT,excel,ws,log,file,print,input,row From： https://blog.csdn.net/BaiPiaoGhost/article/details/143630618