我的QT版本是5.3
//下面是代码
void MainWindow: lot2Excel(QString title, QString tablename){
QAxObject *excel = new QAxObject;
QVector< QString> SubTitle;
SubTitle.fill("", 2);
SubTitle[0] = tr("时间");
SubTitle[1] = tr("温度");
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 Column_Cnt = FBGcfg.History.self_FBGHistory2D.size() * SubTitle.size();
//标题行
QAxObject *title1=worksheet->querySubObject("Cells(int,int)", 1, 1);
title1->dynamicCall("SetValue(const QString&)", title);
title1->querySubObject("Font")->setProperty("Size", 18);
//合并标题行
QString cellTitle;
cellTitle.append("A1:");
cellTitle.append(QChar(Column_Cnt - 1 + 'A'));
cellTitle.append(QString::number(1));
QAxObject *range1 = worksheet->querySubObject("Range(const QString&)", cellTitle);
range1->setProperty("WrapText", true); /* 文本自动换行 */
range1->setProperty("MergeCells", true); /* 合并单元格 */
range1->setProperty("HorizontalAlignment", -4108); //xlCenter
range1->setProperty("VerticalAlignment", -4108); //xlCenter
/* 第二行标题 */
for(int i=0; i<FBGcfg.History.self_FBGHistory2D.size(); i++){
int subID = 0;
for(subID = 0; subID < FBGcfg.self_FBGCnt; subID ++){
if(FBGcfg.self_FBGID[subID] == FBGcfg.History.self_FBGHistory2D[i])
break;
}
if(subID == FBGcfg.self_FBGCnt) /* 查询历史表格出错 */
return;
QAxObject *title2 = worksheet->querySubObject("Cells(int,int)", 2, i*2 + 1);
title2->dynamicCall("SetValue(const QString&)", FBGcfg.self_FBGNAME[subID] + QString::number(FBGcfg.History.self_FBGHistory2D[i]));
title2->querySubObject("Font")->setProperty("Size", 14);
title2->querySubObject("Interior")->setProperty("Color",QColor(141, 182, 205));
}
/* 第二行标题合并单元格 */
for(int i = 0; i < Column_Cnt; i+=2){
QString cellTitle2;
cellTitle2.append(QChar(i + 'A')).append("2:").append(QChar(i + 1 + 'A')).append("2");
QAxObject *range2 = worksheet->querySubObject("Range(const QString&)", cellTitle2);
range2->setProperty("WrapText", true); /* 文本自动换行 */
range2->setProperty("MergeCells", true); /* 合并单元格 */
}
/* 第三行标题 */
for(int i = 0; i < Column_Cnt; i++){
QAxObject *colwidth;
QAxObject *title3 = worksheet->querySubObject("Cells(int,int)", 3, i+1);
QString lie;
lie = lie.append(QChar(i + 'A')).append(":").append(QChar(i + 'A'));
colwidth = worksheet->querySubObject("Columns(const QString&)", lie);
if(i%2){
colwidth->setProperty("ColumnWidth", 11);
title3->dynamicCall("SetValue(const QString&)", SubTitle[1]);
}else{
colwidth->setProperty("ColumnWidth", 22);
title3->dynamicCall("SetValue(const QString&)", SubTitle[0]);
}
title3->querySubObject("Font")->setProperty("Size", 12);
title3->querySubObject("Interior")->setProperty("Color",QColor(191, 191, 191));
}
//调整行高 第一行30 第二行25 第三行20
worksheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 30);
worksheet->querySubObject("Range(const QString&)", "2:2")->setProperty("RowHeight", 25);
worksheet->querySubObject("Range(const QString&)", "3:3")->setProperty("RowHeight", 20);
/* 数据区 */
int MaxSize = 0;
QAxObject *CellDataTime = NULL;
QAxObject *CellDataTmp = NULL;
for(int i = 0; i < History_BackUp.size(); i++){
if(History_BackUp[i][0].size() > MaxSize)
MaxSize = History_BackUp[i][0].size();
for(int j = 0; j < History_BackUp[i][0].size(); j ++){
QString timeStr = QDateTime::fromMSecsSinceEpoch(History_BackUp[i][0][j] * 1000.0).toString("yyyy-MM-dd hh:mm:ss");
CellDataTime = worksheet->querySubObject("Cells(int,int)", j + 4, i*2 + 1);
if(CellDataTime)
CellDataTime->dynamicCall("SetValue(const QVariant&)",timeStr);
CellDataTmp = worksheet->querySubObject("Cells(int,int)", j + 4, i*2 + 2);
if(CellDataTmp)
CellDataTmp->dynamicCall("SetValue(const QVariant&)",QString::number(History_BackUp[i][1][j], 'f', 2));
}
}
//画框线
QString lrange;
lrange.append("A2:");
lrange.append(Column_Cnt - 1 + 'A').append(QString::number(MaxSize + 3));
QAxObject *rangeline = worksheet->querySubObject("Range(const QString&)", lrange);
rangeline->querySubObject("Borders")->setProperty("LineStyle", QString::number(1));
rangeline->querySubObject("Borders")->setProperty("Color", QColor(0, 0, 0));
rangeline->setProperty("HorizontalAlignment", -4108); //xlCenter
rangeline->setProperty("VerticalAlignment", -4108); //xlCenter
//保存当前目录为我的文档目录,所以要用全路径
workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(tablename));
workbook->dynamicCall("Close()");//关闭工作簿
excel->dynamicCall("Quit()");//关闭excel
delete excel;
excel=NULL;
}
}
//上面是代码,希望对你有帮助 |
