Office属性说明:https://docs.microsoft.com/zh-CN/office/vba/api/excel.font.size
CSDN博客 :https://blog.csdn.net/qq_20371377/article/details/67639927(评论区)
QAxObject *excel;
QAxObject *workbooks;
QAxObject *workbook;
QAxObject *worksheets;
QAxObject *worksheet;
QAxObject *range;
QAxObject *interior;
QAxObject *cell;
QAxObject *font;
1.创建Excel:
excel = new QAxObject("Excel.Application");
2.设置Excel
excel->dynamicCall("SetVisible(bool Visible)", true); //是否可视化excel
excel->dynamicCall("SetUserControl(bool UserControl)", false); //是否用户可操作
excel->setProperty("DisplayAlerts", true); //是否弹出警告窗口
3.工作溥操作
workbooks = excel->querySubObject("WorkBooks"); //获取工作簿集合
workbooks->dynamicCall("Add"); //新建一个工作簿
workbook = excel->querySubObject("ActiveWorkBook"); //获取当前工作簿
4.工作表操作
worksheets = workbook->querySubObject("Sheets"); //获取工作表格集合
worksheet = worksheets->querySubObject("Item(int)", 1); //获取当前工作表格1,即sheet1
worksheet->setProperty("Name","雪下村林夕"); //修改sheet名称
5.设置一级标题
range=worksheet->querySubObject("Range(const QString&)","A1:B1");
range->setProperty("MergeCells",true);
range->setProperty("Value","雪下村林夕");
6.设置二级标题
range=worksheet->querySubObject("Range(const QString&)","A1:B1");
range->setProperty("Value","真帅");
7.按序号设置背景色
range=worksheet->querySubObject("Range(const QString&)","A1:B1");
interior=range->querySubObject("Interior");
interior->setProperty("ColorIndex",3); //按颜色序号进行颜色填充
8.设置字体属性
range=worksheet->querySubObject("Range(const QString&)","A1:B1");
range->setProperty("ColumnWidth",5); //设置列宽
range->setProperty("WrapText", true); //设置自动换行
range->setProperty("HorizontalAlignment", -4108); //水平对齐:默认=1,居中=-4108,左=-4131,右=-4152
range->setProperty("VerticalAlignment", -4108); //垂直对齐:默认=2,居中=-4108,左=-4160,右=-4107
font = range->querySubObject("Font"); //获取单元格字体
font->setProperty("Name", QStringLiteral("微软雅黑")); //设置单元格字体
font->setProperty("Bold", true); //设置单元格字体加粗
font->setProperty("Size", 12); //设置单元格字体大小
font->setProperty("ColorIndex",2); //按颜色序号进行颜色填充
9.写入数据
range=worksheet->querySubObject("Range(const QString&)","A1:B1");
range->setProperty("Value","23333");
10.保存并关闭
workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(path)); //保存Excel
workbook->dynamicCall("Close()"); //关闭工作簿
excel->dynamicCall("Quit()"); //退出
用setProperty("Value", var);就只能写office,用setProperty("Value2", var);就既可以office也可以wps
实例: 导入编写模板并将其以PDF格式输出
void ExportReport::exportResultReport()
{
//打开模板操作
QString modelPath = QApplication::applicationDirPath() + "\\template\\template001.xlsx";
QFile file(modelPath);
if (!file.exists()) // 模板不存在,直接退出
{
std::cout << "Mould File is not exit !" << std::endl;
return;
}
m_excel.setControl("Excel.Application");
m_excel.setProperty("DisplayAlerts", false);
m_pWorkbooks = m_excel.querySubObject("WorkBooks");
if (m_pWorkbooks)
{
m_pWorkbooks->dynamicCall("Open(const QString&)", modelPath);
}
else // 获取对象失败,直接退出
{
std::cout << "Office Version is wrong!" << std::endl;
return;
}
m_pWorkbook = m_excel.querySubObject("ActiveWorkBook");
m_pWorksheets = m_pWorkbook->querySubObject("Sheets");
m_pWorksheet = m_pWorksheets->querySubObject("Item(int)", 1);
setRangeData("B4:E4", m_cameraSn);
setRangeData("G4:J4", "V 1.17");
setRangeData("B5:E5", m_startTime);
setRangeData("G5:J5", m_stopTime);
setRangeData("B6:J6", m_testTotalCount);
setRangeData("D9:D10", m_testTotalCount);
setRangeData("D13:D14", m_ledMatchBrightFailed);
setRangeData("D16:D17", m_testTotalCount);
setRangeData("D20:D21", m_ledMatchDarkFailed);
setRangeData("D24:D25", m_testTotalCount);
setRangeData("D28:D29", m_laserMatchBrightFailed);
setRangeData("D31:D32", m_testTotalCount);
setRangeData("D35:D36", m_laserMatchDarkFailed);
setRangeData("F41:G42", m_orderTotalCount);
setRangeData("F45:G46", m_orderFailedCount);
setRangeData("F48:G49", m_resivePacketCount);
setRangeData("F50:G51", m_failedPacketCount);
setRangeData("F52:G53", m_resendPacketCount);
setRangeData("F55:G56", m_orderTotalCount);
setRangeData("F57:G58", m_underRunBufferCount);
setRangeData("F59:G60", m_orderFailedCount);
setRangeData("F62:G63", m_resendRequestCount);
//文件保存路径
QString savePath = QApplication::applicationDirPath() + QString("\\resultreport\\resultreport_%1.pdf").arg(QDateTime::currentDateTime().toString("yyyy-MM-dd_hh-mm-ss")), tr("PDF (*.pdf)");
m_pWorksheet->dynamicCall("ExportAsFixedFormat(Type,Filename,Quality,IncludeDocProperties,IgnorePrintAreas)",0, savePath, 0, true, false).toBool();
m_pWorkbook->dynamicCall("Close");
m_excel.dynamicCall("Quit(void)");
}