这篇博客介绍了如何在Qt中通过开启新线程来解决导出数据库到Excel时速度慢的问题。作者详细阐述了创建基于QObject的线程类、在界面中触发导出事件、接收并处理数据的步骤，并分享了在不同阶段可能出现的错误及其解决方案。尽管出现了一些错误，如1004错误和列数不匹配，但最终通过调整实现了成功导出。
Qt 表格文件 | 数据库文件 导出Excel
一、导出表格内数据
1.在界面添加导出按钮
2.点击按钮触发函数
/**
* @brief HistoryResult::on_save_history_result
* 保存数据
*/
void HistoryResult::on_save_history_result()
{
QString strAdd = mainWin->StrDir; //当前程序运行目录 StrDir = qApp->applicationDirPath();
QDir dir;
bool is;
QString dirPath1,dirPath2;
QString driverName = mainWin->driverName; //获取正在作业的驱动器名
QString dateStr = ui->line_date->text(); //转换成 年月日的格式
QString fName = driverName+"_"+dateStr; //文件名为 驱动器_日期.xls
dirPath1 = strAdd+"/result"; //目录 ...../Debug/result
dirPath2 = dirPath1+"/"+dateStr; //目录 ...../Debug/result/年-月-日
//上面的mkdir 只能创建单层目录 mkpath 如果父目录不在 也可以直接创建
is = dir.exists(dirPath2);
if(!is)
{
is = dir.mkpath(dirPath2);
}
QString title = fName+"_result";
QString fileName = QFileDialog::getSaveFileName(this, tr("保存"),tr("%1/%2").arg(dirPath2).arg(title),"Excel 文件(*.xls *.xlsx)");
if(fileName == "") return; //点击取消 fileName = "" 不保存
emit saveHistoryResult(fileName,dateStr);
QAxObject *excel = new QAxObject;
if (excel->setControl("Excel.Application")) //连接Excel控件
{
excel->dynamicCall("SetVisible (bool Visible)","false"); //不显示窗体
excel->setProperty("DisplayAlerts", false); //不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
QAxObject *workbooks = excel->querySubObject("WorkBooks"); //获取工作簿集合
workbooks->dynamicCall("Add"); //新建一个工作簿
QAxObject *workbook = excel->querySubObject("ActiveWorkBook"); //获取当前工作簿
QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);
int i,j;
//-******************************************后面需要导出的是数据库的数据 列数为字段数 行数为 总量
int colcount = ui->result_table->columnCount(); // 获取数据的列数
int rowscount=ui->result_table->rowCount(); // 获取数据的行数
if(rowscount >= 5)
{
int ret1 = QMessageBox::question(this,tr("提示"),tr("文化部过大，是否选择后台导出？"),QMessageBox::Yes,QMessageBox::No);
if(ret1 == QMessageBox::Yes)
{
this->setVisible(false);
}
}
QAxObject *cell,*col;
//标题行
cell=worksheet->querySubObject("Cells(int,int)", 1, 1);
cell->dynamicCall("SetValue(const QString&)", title);
cell->querySubObject("Font")->setProperty("Size", 18);
//调整行高
worksheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 30);
//合并标题行
QString cellTitle;
cellTitle.append("A1:");
cellTitle.append(QChar(colcount - 1 + 'A'));
cellTitle.append(QString::number(1));
QAxObject *range = worksheet->querySubObject("Range(const QString&)", cellTitle);
// range->setProperty("WrapText", true);
// range->setProperty("MergeCells", true);
// range->setProperty("HorizontalAlignment", -4108); //xlCenter
// range->setProperty("VerticalAlignment", -4108); //xlCenter
//列标题 PRAGMA table_info('DRIVER_PARAM') 查出所有字段信息
for(i=0;i<colcount;i++)
{
QString columnName;
columnName.append(QChar(i + 'A'));
columnName.append(":");
columnName.append(QChar(i + 'A'));
col = worksheet->querySubObject("Columns(const QString&)", columnName);
// col->setProperty("ColumnWidth", ui->result_table->columnWidth(i)/6);
cell=worksheet->querySubObject("Cells(int,int)", 2, i+1);
// //QTableWidget 获取表格头部文字信息
columnName=ui->result_table->horizontalHeaderItem(i)->text();
// //获取表格头部文字信息
cell->dynamicCall("SetValue(const QString&)", columnName);
cell->querySubObject("Font")->setProperty("Bold", true);
cell->querySubObject("Interior")->setProperty("Color",QColor(191, 191, 191));
}
//获取表格数据部分
for(i=0;i<rowscount;i++) //行数
{
for (j=0;j<colcount;j++)
{
worksheet->querySubObject("Cells(int,int)", i+3, j+1)->dynamicCall("SetValue(const QString&)", ui->result_table->item(i,j)?ui->result_table->item(i,j)->text():"");
}
float proBarValue = 10; //进度条初始10
proBarValue = 10+80*(i+1)/rowscount; //10~90 每存一条 走总条数分之一
int intBarValue1 = (int)proBarValue; //
// this->setAttribute(Qt::dontshowonscreen);
this->setAttribute(Qt::WA_DontShowOnScreen);
// ui->progressBar->setValue(intBarValue1); //修改进度条的值
}
//画框线
QString lrange;
lrange.append("A2:");
lrange.append(colcount - 1 + 'A');
lrange.append(QString::number(rowscount + 2));
range = worksheet->querySubObject("Range(const QString&)", lrange);
// range->querySubObject("Borders")->setProperty("LineStyle", QString::number(1));
// range->querySubObject("Borders")->setProperty("Color", QColor(255, 0, 0));
//调整数据区行高s
QString rowsName;
rowsName.append("2:");
rowsName.append(QString::number(rowscount + 2));
range = worksheet->querySubObject("Range(const QString&)", rowsName);
range->setProperty("RowHeight", 20);
workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(fileName));//保存至fileName
workbook->dynamicCall("Close()"); //关闭工作簿
excel->dynamicCall("Quit()"); //关闭excel
delete excel;
excel=NULL;
if (QMessageBox::question(NULL,"完成","文件已经导出，是否现在打开？",QMessageBox::Yes|QMessageBox::No)==QMessageBox::Yes)
{
QDesktopServices::openUrl(QUrl("file:///" + QDir::toNativeSeparators(fileName)));
}
}
}
二、开启线程 导出数据库数据
因为这种方法导出数据比较慢，快的我也不会，所以导出数据，再开起一个线程，这样就可以边导出边做其他事了。（不是非得导出数据库数据，原来表格数据也是可以的）
1.准备新的线程
①创建一个类 基于QObject即可
class HistorySave : public QObject
{
Q_OBJECT
public:
explicit HistorySave(QObject *parent = 0);
};
②在界面类中开启新的线程
构造函数内代码
HistorySave *hSave = new HistorySave();
hSave->moveToThread(&thread);
//thread是头文件创建的全局变量
connect(&thread,SIGNAL(finished()),hSave,SLOT(deleteLater()));
connect(this,SIGNAL(saveHistoryResult(QString,QString)),hSave,SLOT(saveHistory(QString,QString)));
2.触发事件
在单击按钮后，发送新的线程信号，并且启动
/**
* @brief HistoryResult::on_pushButton_export_clicked
* 导出文件按钮
*/
void HistoryResult::on_pushButton_export_clicked()
{
QString strAdd = mainWin->StrDir; //当前程序运行目录 StrDir = qApp->applicationDirPath();
QDir dir;
bool is;
QString dirPath1,dirPath2;
QString dateStr = ui->line_date->text(); //转换成 年月日的格式
QString fName = driverName+"_"+dateStr; //文件名为 驱动器_日期.xls
dirPath1 = strAdd+"/result"; //目录 ...../Debug/result
dirPath2 = dirPath1+"/"+dateStr; //目录 ...../Debug/result/年-月-日
//上面的mkdir 只能创建单层目录 mkpath 如果父目录不在 也可以直接创建
is = dir.exists(dirPath2);
if(!is)
{
is = dir.mkpath(dirPath2);
}
QString title = fName+"_result";
QString fileName = QFileDialog::getSaveFileName(this, tr("保存"),tr("%1/%2").arg(dirPath2).arg(title),"Excel 文件(*.xls *.xlsx)");
if(fileName == "") return; //点击取消 fileName = "" 不保存
emit saveHistoryResult(fileName,dateStr); //发送信号 进入新的线程
thread.start();
}
3.接收信号处理数据
/**
* @brief HistorySave::saveHistory
* @param fileName 文件名
* @param dateStr 日期(查询对应日期的数据)
* 接收的信号
*/
void HistorySave::saveHistory(QString fileName,QString dateStr)
{
qDebug() <<tr("222's ThreadId is : ") << QThread::currentThreadId(); //打印观看线程id
userDB = new UserDB();
QList<QString> names;
QList<QString> datas;
QString driverName; //获取正在作业的驱动器名
QString title;
names = userDB->queryFieldNames(); //导出数据库表格的字段名集合
title = dateStr+"_Result";
emit showProgress(10,fileName);
//在函数中添加代码
HRESULT r = OleInitialize(0);
if(r!=S_OK && r != S_FALSE)
{
return;
}
QAxObject *excel = new QAxObject();
// if (excel) //连接Excel控件
if (excel->setControl("Excel.Application")) //连接Excel控件
{
excel->dynamicCall("SetVisible (bool Visible)","false"); //不显示窗体
excel->setProperty("DisplayAlerts", false); //不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
QAxObject *workbooks = excel->querySubObject("WorkBooks"); //获取工作簿集合
workbooks->dynamicCall("Add"); //新建一个工作簿
QAxObject *workbook = excel->querySubObject("ActiveWorkBook"); //获取当前工作簿
QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);
int i,j;
int colcount = names.size(); // 获取数据的列数
int rowscount=TRs.size(); // 获取数据的行数
QAxObject *cell,*col;
//标题行
cell=worksheet->querySubObject("Cells(int,int)", 1, 1);
cell->dynamicCall("SetValue(const QString&)", title);
cell->querySubObject("Font")->setProperty("Size", 18);
//调整行高
worksheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 30);
//合并标题行
QString cellTitle;
cellTitle.append("A1:");
cellTitle.append(QChar(colcount - 1 + 'A'));
cellTitle.append(QString::number(1));
qDebug() << tr("first print error:");
QAxObject *range = worksheet->querySubObject("Range(const QString&)", cellTitle);
//列标题 PRAGMA table_info('DRIVER_PARAM') 查出所有字段信息
for(i=0;i<colcount;i++)
{
QString columnName;
columnName.append(QChar(i + 'A'));
columnName.append(":");
columnName.append(QChar(i + 'A'));
qDebug() << tr("i = %1").arg(i);
col = worksheet->querySubObject("Columns(const QString&)", columnName);
cell=worksheet->querySubObject("Cells(int,int)", 2, i+1);
//QTableWidget 获取表格头部文字信息
columnName=names.at(i);
//获取表格头部文字信息
cell->dynamicCall("SetValue(const QString&)", columnName);
cell->querySubObject("Font")->setProperty("Bold", true);
}
//获取表格数据部分
for(i=0;i<rowscount;i++) //行数
{
datas.append(“...”);
// ...
// 数据的插入 就是你自己要导出的数据
for (j=0;j<colcount;j++)
{
worksheet->querySubObject("Cells(int,int)", i+3, j+1)->dynamicCall("SetValue(const QString&)",datas.at(j));
}
float proBarValue = 10; //进度条初始10
proBarValue = 10+80*(i+1)/rowscount; //10~90 每存一条 走总条数分之一
int intBarValue1 = (int)proBarValue; //
emit showProgress(intBarValue1,fileName);
}
//画框线
QString lrange;
lrange.append("A2:");
lrange.append(colcount - 1 + 'A');
lrange.append(QString::number(rowscount + 2));
qDebug() << tr("the third print error:");
range = worksheet->querySubObject("Range(const QString&)", lrange);
//调整数据区行高s
QString rowsName;
rowsName.append("2:");
rowsName.append(QString::number(rowscount + 2));
range = worksheet->querySubObject("Range(const QString&)", rowsName);
range->setProperty("RowHeight", 20);
workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(fileName));//保存至fileName
workbook->dynamicCall("Close()"); //关闭工作簿
qDebug() << tr("the fourth print error:");
excel->dynamicCall("QhisResult->uit()"); //关闭excel
delete excel;
excel=NULL;
emit showProgress(100,fileName);
qDebug() << "save data Success";
qDebug() <<tr("end's ThreadId is : ") << QThread::currentThreadId(); //打印观看线程id
}
}
4.看一下效果
①单击导出后，提示下面的第一张图；
②单击最右上角的home标记，退出当前界面，就可以看到
③home界面的进度条
当进度条走完，就会提示文件已经保存，是否打开看看。
5.看看我们中间用qDebug() 打印在控制台的结果
1.主次进程id
"000's ThreadId is : " 0x2994 主
"222's ThreadId is : " 0x26ac 次
2.一些错误显示
第一次错误：打印的是1004
qDebug() << tr("first print error:");
QAxObject *range = worksheet->querySubObject("Range(const QString&)", cellTitle);
"first print error:"
QAxBase: Error calling IDispatch member Range: Exception thrown by server
Code : 1004
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
第二次错误：总共92列 不是每列都有错误提示
qDebug() << tr("i = %1").arg(i);
col = worksheet->querySubObject("Columns(const QString&)", columnName);
"i = 0"
"i = 1"
"i = 2"
"i = 3"
"i = 4"
"i = 5"
"i = 6"
"i = 7"
"i = 8"
"i = 9"
"i = 10"
"i = 11"
"i = 12"
"i = 13"
"i = 14"
"i = 15"
"i = 16"
"i = 17"
"i = 18"
"i = 19"
"i = 20"
"i = 21"
"i = 22"
"i = 23"
"i = 24"
"i = 25"
"i = 26"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 27"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 28"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 29"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 30"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 31"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 32"
"i = 33"
"i = 34"
"i = 35"
"i = 36"
"i = 37"
"i = 38"
"i = 39"
"i = 40"
"i = 41"
"i = 42"
"i = 43"
"i = 44"
"i = 45"
"i = 46"
"i = 47"
"i = 48"
"i = 49"
"i = 50"
"i = 51"
"i = 52"
"i = 53"
"i = 54"
"i = 55"
"i = 56"
"i = 57"
"i = 58"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 59"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 60"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 61"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 62"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 63"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 64"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 65"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 66"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 67"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 68"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 69"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 70"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 71"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 72"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 73"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 74"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 75"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 76"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 77"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 78"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 79"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 80"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 81"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 82"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 83"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 84"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 85"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 86"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 87"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 88"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 89"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 90"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 91"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
"i = 92"
QAxBase: Error calling IDispatch member Columns: Exception thrown by server
Code : 5
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
因为退出了当时的界面：打印了下面的结果
QThread: Destroyed while thread is still running
第三次错误：给每格填入值
qDebug() << tr("the third print error:");
range = worksheet->querySubObject("Range(const QString&)", lrange);
"the third print error:"
QAxBase: Error calling IDispatch member Range: Exception thrown by server
Code : 1004
Source :
Description:
Help :
Connect to the exception(int,QString,QString,QString) signal to catch this exception
第四次错误：关闭Excel
qDebug() << tr("the fourth print error:");
excel->dynamicCall("QhisResult->uit()"); //关闭excel
"the fourth print error:"
QAxBase::dynamicCallHelper: QhisResult->uit(): No such property in {00024500-0000-0000-c000-000000000046} [unknown]
Candidates are:
我查询了一些博客，提到的是，此类错误是因为保存路径不是相对 | 绝对路径，但是我这边的路径位置却没有错误提示，所以我依次尝试，注释代码来解决。
就不再提示了，并且结果表也是可以接受。
三、总结
以上两种方法都可以导出成功，因为这种导出方法比较慢(手动测试1万条，得要二十分钟+++，可能是电脑问题，也可能就是慢)，所以就选择了新的线程导出。
在这里推荐新的导出方法，大家可以借鉴下。
1133
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
