Qt使用QAxObject写入Excel表格,包括填色、合并单元格、多行写入等
QAxObject可以调用Excel表中的各种函数或者事件,来操控读写Excel表格。
Excel表中所有的接口可以查看官方文档。
末尾可见源码。
第一步 明确自己需要写入的数据
我这边的示例是写入一个二维码解码结果的表格。
例如我有4张图片,每张图片中都有一个或多个二维码,甚至也可能没有二维码。
我需要把解码失败的二维码标记为红色,没有二维码的图片标记为灰色。
得到一个这样的结果。
第二步 生成数据
数据是模拟的,随便写点数据就好。
定义一个数据结构体。
struct Qrcode{
QString result;
QString points;
int grade;
};
struct ExcelData{
QString fileName;
float elapsed;
QVector<Qrcode> qrcodes;
};
填入数据
void Datasets::initData()
{
Qrcode oneQrcodeOne = getQrcodeData("第一个二维码;", "(12,34),(566,322)", 4);
Qrcode oneQrcodeTwo = getQrcodeData("第二个二维码;", "(87,190),(200,98)", 0);
Qrcode oneQrcodeThree = getQrcodeData("第三个二维码", "(122,41),(59,345)", 1);
ExcelData excelDataOne = getExcelData("1.bmp", 26.5, {oneQrcodeOne, oneQrcodeTwo, oneQrcodeThree});
excelDatas.append(excelDataOne);
Qrcode twoQrcodeOne = getQrcodeData("", "(87,140),(200,98)", 0);
ExcelData excelDataTwo = getExcelData("2.bmp", 23.7, {twoQrcodeOne});
excelDatas.append(excelDataTwo);
ExcelData excelDataThree = getExcelData("3.bmp", 33.1, {});
excelDatas.append(excelDataThree);
Qrcode fourQrcodeTwo = getQrcodeData("第一个二维码;", "(87,1901),(200,98)", 2);
Qrcode fourQrcodeThree = getQrcodeData("第二个二维码;", "(122,41),(59,345)", 2);
ExcelData excelDataFour = getExcelData("4.bmp", 24.9, {fourQrcodeTwo, fourQrcodeThree});
excelDatas.append(excelDataFour);
Qrcode fiveQrcodeOne = getQrcodeData("第一个二维码;", "(87,192),(200,98)", 2);
Qrcode fiveQrcodeTwo = getQrcodeData("第二个二维码;", "(122,41),(59,345)", 2);
Qrcode fiveQrcodeThree = getQrcodeData("第三个二维码;", "(122,41),(59,345)", 2);
Qrcode fiveQrcodeFour = getQrcodeData("", "(87,190),(200,98)", 0);
Qrcode fiveQrcodeFive = getQrcodeData("第四个二维码;", "(122,41),(59,345)", 2);
ExcelData excelDataFive = getExcelData("5.bmp", 32.1, {fiveQrcodeOne, fiveQrcodeTwo, fiveQrcodeThree, fiveQrcodeFour, fiveQrcodeFive});
excelDatas.append(excelDataFive);
}
第三步 打开/新建一个Excel表格
bool ExcelHandle::open(QString excelPath)
{
this->excelPath = excelPath;
QFileInfo fileInfo(excelPath);
excel = new QAxObject("Excel.Application");
excel->setProperty("Visible", false);
excel->setProperty("DisplayAlerts", false); // 在save和save as时,不弹窗提示
workBooks = excel->querySubObject("WorkBooks");
if(!fileInfo.exists()){
//!!!新建工作簿
workBooks->querySubObject("Add");
} else {
//!!!打开已存在的工作簿
workBooks->querySubObject("Open (const QString&)", QDir::toNativeSeparators(excelPath));
}
workBook = excel->querySubObject("ActiveWorkBook");
if (workBook == nullptr)
return false;
//!!!获取表页对象
workSheets = workBook->querySubObject("WorkSheets");
workSheet = workSheets->querySubObject("Item(int)", 1);
changeSheetName("二维码"); // 给这个表页换一个名字
if(workSheets == NULL)
return false;
return true;
}
第四步 写入二维码数据
填入多行数据时,需要用QList<QList>转成QVariantList,再转成QVariant。不能直接用QVariantList写入再转成QVariant格式,会导致写入数据失败。
bool ExcelHandle::writeExcelData(QVector<Datasets::ExcelData> excelDatas)
{
// 先写入一个头
QList<QVariant> header;
header << "序号" << "图片" << "耗时" << "解码内容" << "坐标" << "等级";
QVariant headerV = header;
writeMulty(headerV, "A1", "F1");
int row = 2; // 行
for(int i = 0; i < excelDatas.size(); i++){
// 填入序号
QString indexStart = QString("A%1").arg(row);
QString indexend = QString("A%1").arg(row + qMax(excelDatas[i].qrcodes.size(),1) - 1);
mergeCells(indexStart, indexend, QString::number(i));
// 填入图片名
QString imgStart = QString("B%1").arg(row);
QString imgEnd = QString("B%1").arg(row + qMax(excelDatas[i].qrcodes.size(),1) - 1);
mergeCells(imgStart, imgEnd, excelDatas[i].fileName);
// 填入耗时
QString timeStart = QString("C%1").arg(row);
QString timeEnd = QString("C%1").arg(row + qMax(excelDatas[i].qrcodes.size(),1) - 1);
mergeCells(timeStart, timeEnd, QString("%1ms").arg(QString::number(excelDatas[i].elapsed,'f',2)));
// 如果没有二维码,填入灰色
if(excelDatas[i].qrcodes.size() <= 0){
QString colorStart = QString("A%1").arg(row);
QString colorEnd = QString("F%1").arg(row);
setColor(QColor(100,100,100), colorStart, colorEnd);
}
// 填入解码数据
QVariantList vars;
QList<QList<QVariant>> qrcodeList; // 需要先用QList<QList<QVariant>>包装一次
for(int j = 0; j < excelDatas[i].qrcodes.size(); j++){
QList<QVariant> cells;
cells.append(excelDatas[i].qrcodes[j].result);
cells.append(excelDatas[i].qrcodes[j].points);
cells.append(excelDatas[i].qrcodes[j].grade);
qrcodeList.append(cells);
vars.append(QVariant(qrcodeList.last()));
// 设置颜色,解码失败为红色,没有二维码的为灰色
if(excelDatas[i].qrcodes[j].result == ""){
QString colorStart = QString("D%1").arg(row+j);
QString colorEnd = QString("F%1").arg(row+j);
setColor(QColor(255,0,0), colorStart, colorEnd);
}
}
QVariant result = vars;
QString dataStart = QString("D%1").arg(row);
QString dataEnd = QString("F%1").arg(row + qMax(excelDatas[i].qrcodes.size(),1) - 1);
writeMulty(result, dataStart, dataEnd);
row = row + qMax(excelDatas[i].qrcodes.size(),1);
}
setAutoFit(row);
return true;
}
第五步 保存并退出Excel
bool ExcelHandle::save()
{
if(excel == NULL || workBooks == NULL || workBook == NULL)
return false;
/** 保存文件 **/
QFileInfo fileInfo(excelPath);
if(!fileInfo.exists())
workBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(excelPath));
else
workBook->dynamicCall("Save()");
/** 关闭并退出文件 **/
workBooks->dynamicCall("Close()");
excel->dynamicCall("Quit(void)");
delete workSheet;
workSheet = NULL;
delete workSheets;
workSheets = NULL;
delete workBook;
workBook = NULL;
delete workBooks;
workBooks = NULL;
delete excel;
excel = NULL;
return true;
}
合并单元格
bool ExcelHandle::mergeCells(QString start, QString end, QString value)
{
if(workSheet == NULL)
return false;
QAxObject *range = workSheet->querySubObject("Range(const Qvariant&)", QVariant("=(" +start+ ": " +end+ ")"));
if(range == NULL)
return false;
range->setProperty("MergeCells", true); // 合并单元格
range->setProperty("Value", value);
return true;
}
设置背景色
bool ExcelHandle::setColor(QColor color, QString start, QString end)
{
QAxObject *range = workSheet->querySubObject("Range(const Qvariant&)", QVariant("=(" +start+ ": " +end+ ")"));
if(range == NULL)
return false;
QAxObject *cells = range->querySubObject("Columns");
QAxObject *interior = cells->querySubObject("Interior");
interior->setProperty("Color", color);
delete interior;
interior = NULL;
delete cells;
cells = NULL;
return true;
}
插入多行数据
bool ExcelHandle::writeMulty(QVariant value, QString start, QString end)
{
if(workSheet == NULL)
return false;
QAxObject *range = workSheet->querySubObject("Range(const QString&)",QString("(" +start+ ": " +end+ ")"));
range->setProperty("Value", value);
delete range;
range = NULL;
return true;
}
自动设置单元格宽度
bool ExcelHandle::setAutoFit(int lines)
{
if(workSheet == NULL)
return false;
QAxObject *range = workSheet->querySubObject("Range(const Qvariant&)", QVariant(QString("A1:F%1").arg(lines)));
if(range == NULL)
return false;
QAxObject *cells = range->querySubObject("Columns");
cells->dynamicCall("AutoFit");
return true;
}
源码
数据文件:
#ifndef DATASETS_H
#define DATASETS_H
/** Datasets.h **/
#include <QObject>
#include <QDebug>
#pragma execution_character_set("utf-8")
class Datasets : public QObject
{
Q_OBJECT
public:
struct Qrcode{
QString result;
QString points;
int grade;
};
struct ExcelData{
QString fileName;
float elapsed;
QVector<Qrcode> qrcodes;
};
public:
explicit Datasets(QObject *parent = nullptr);
QVector<ExcelData> getData();
private:
void initData();
Qrcode getQrcodeData(QString result, QString point, int grade);
ExcelData getExcelData(QString fileName, float elapsed, QVector<Qrcode> qrcodes);
private:
QVector<ExcelData> excelDatas;
signals:
};
#endif // DATASETS_H
/** Datasets.cpp **/
#include "Datasets.h"
Datasets::Datasets(QObject *parent) : QObject(parent)
{
initData();
}
QVector<Datasets::ExcelData> Datasets::getData()
{
return excelDatas;
}
void Datasets::initData()
{
Qrcode oneQrcodeOne = getQrcodeData("第一个二维码;", "(12,34),(566,322)", 4);
Qrcode oneQrcodeTwo = getQrcodeData("第二个二维码;", "(87,190),(200,98)", 0);
Qrcode oneQrcodeThree = getQrcodeData("第三个二维码", "(122,41),(59,345)", 1);
ExcelData excelDataOne = getExcelData("1.bmp", 26.5, {oneQrcodeOne, oneQrcodeTwo, oneQrcodeThree});
excelDatas.append(excelDataOne);
Qrcode twoQrcodeOne = getQrcodeData("", "(87,140),(200,98)", 0);
ExcelData excelDataTwo = getExcelData("2.bmp", 23.7, {twoQrcodeOne});
excelDatas.append(excelDataTwo);
ExcelData excelDataThree = getExcelData("3.bmp", 33.1, {});
excelDatas.append(excelDataThree);
Qrcode fourQrcodeTwo = getQrcodeData("第一个二维码;", "(87,1901),(200,98)", 2);
Qrcode fourQrcodeThree = getQrcodeData("第二个二维码;", "(122,41),(59,345)", 2);
ExcelData excelDataFour = getExcelData("4.bmp", 24.9, {fourQrcodeTwo, fourQrcodeThree});
excelDatas.append(excelDataFour);
Qrcode fiveQrcodeOne = getQrcodeData("第一个二维码;", "(87,192),(200,98)", 2);
Qrcode fiveQrcodeTwo = getQrcodeData("第二个二维码;", "(122,41),(59,345)", 2);
Qrcode fiveQrcodeThree = getQrcodeData("第三个二维码;", "(122,41),(59,345)", 2);
Qrcode fiveQrcodeFour = getQrcodeData("", "(87,190),(200,98)", 0);
Qrcode fiveQrcodeFive = getQrcodeData("第四个二维码;", "(122,41),(59,345)", 2);
ExcelData excelDataFive = getExcelData("5.bmp", 32.1, {fiveQrcodeOne, fiveQrcodeTwo, fiveQrcodeThree, fiveQrcodeFour, fiveQrcodeFive});
excelDatas.append(excelDataFive);
}
Datasets::Qrcode Datasets::getQrcodeData(QString result, QString point, int grade)
{
Qrcode qrcode;
qrcode.result = result;
qrcode.grade = grade;
qrcode.points = point;
return qrcode;
}
Datasets::ExcelData Datasets::getExcelData(QString fileName, float elapsed, QVector<Datasets::Qrcode> qrcodes)
{
ExcelData excelData;
excelData.fileName = fileName;
excelData.elapsed = elapsed;
excelData.qrcodes.append(qrcodes);
return excelData;
}
Excel表格操作文件:
/** ExcelHandle.h **/
#ifndef EXCELHANDLE_H
#define EXCELHANDLE_H
#include <QObject>
#include <QDebug>
#include <QAxObject>
#include <QDir>
#include <QColor>
#include "Datasets.h"
#pragma execution_character_set("utf-8")
class ExcelHandle : public QObject
{
Q_OBJECT
public:
explicit ExcelHandle(QObject *parent = nullptr);
bool open(QString excelPath);
bool save();
bool changeSheetName(QString newName);
bool setColor(QColor color, QString start, QString end);
bool writeOne(QString value, int row, int col);
bool writeMulty(QVariant value, QString start, QString end); // value <== QVariantList <== QList<QList<QVariant>>
bool writeExcelData(QVector<Datasets::ExcelData> excelDatas);
private:
bool mergeCells(QString start, QString end, QString value);
bool setAutoFit(int lines);
private:
QString excelPath;
QAxObject *excel = NULL;
QAxObject *workBooks = NULL;
QAxObject *workBook = NULL;
QAxObject *workSheets = NULL;
QAxObject *workSheet = NULL;
signals:
};
#endif // EXCELHANDLE_H
/** ExcelHandle.cpp **/
#include "ExcelHandle.h"
ExcelHandle::ExcelHandle(QObject *parent) : QObject(parent)
{
}
/** 打开一个excel **/
bool ExcelHandle::open(QString excelPath)
{
this->excelPath = excelPath;
QFileInfo fileInfo(excelPath);
excel = new QAxObject("Excel.Application");
excel->setProperty("Visible", false);
excel->setProperty("DisplayAlerts", false); // 在save和save as时,不弹窗提示
workBooks = excel->querySubObject("WorkBooks");
if(!fileInfo.exists()){
//!!!新建工作簿
workBooks->querySubObject("Add");
} else {
//!!!打开已存在的工作簿
workBooks->querySubObject("Open (const QString&)", QDir::toNativeSeparators(excelPath));
}
workBook = excel->querySubObject("ActiveWorkBook");
if (workBook == nullptr)
return false;
//!!!获取表页对象
workSheets = workBook->querySubObject("WorkSheets");
workSheet = workSheets->querySubObject("Item(int)", 1);
changeSheetName("二维码"); // 给这个表页换一个名字
if(workSheets == NULL)
return false;
return true;
}
bool ExcelHandle::save()
{
if(excel == NULL || workBooks == NULL || workBook == NULL)
return false;
/** 保存文件 **/
QFileInfo fileInfo(excelPath);
if(!fileInfo.exists())
workBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(excelPath));
else
workBook->dynamicCall("Save()");
/** 关闭并退出文件 **/
workBooks->dynamicCall("Close()");
excel->dynamicCall("Quit(void)");
delete workSheet;
workSheet = NULL;
delete workSheets;
workSheets = NULL;
delete workBook;
workBook = NULL;
delete workBooks;
workBooks = NULL;
delete excel;
excel = NULL;
return true;
}
bool ExcelHandle::changeSheetName(QString newName)
{
if(workSheet == NULL)
return false;
workSheet->setProperty("Name", newName);
QString sheetName = workSheet->property("Name").toString();
qDebug() << sheetName;
return true;
}
bool ExcelHandle::setColor(QColor color, QString start, QString end)
{
QAxObject *range = workSheet->querySubObject("Range(const Qvariant&)", QVariant("=(" +start+ ": " +end+ ")"));
if(range == NULL)
return false;
QAxObject *cells = range->querySubObject("Columns");
QAxObject *interior = cells->querySubObject("Interior");
interior->setProperty("Color", color);
delete interior;
interior = NULL;
delete cells;
cells = NULL;
return true;
}
bool ExcelHandle::writeOne(QString value, int row, int col)
{
if(workSheet == NULL)
return false;
QAxObject* range = workSheet->querySubObject("Cells(int, int)", qMax(row, 1), qMax(col, 1));
range->setProperty("Value", value);
delete range;
range = NULL;
return true;
}
bool ExcelHandle::writeMulty(QVariant value, QString start, QString end)
{
if(workSheet == NULL)
return false;
QAxObject *range = workSheet->querySubObject("Range(const QString&)",QString("(" +start+ ": " +end+ ")"));
range->setProperty("Value", value);
delete range;
range = NULL;
return true;
}
bool ExcelHandle::writeExcelData(QVector<Datasets::ExcelData> excelDatas)
{
// 先写入一个头
QList<QVariant> header;
header << "序号" << "图片" << "耗时" << "解码内容" << "坐标" << "等级";
QVariant headerV = header;
writeMulty(headerV, "A1", "F1");
int row = 2; // 行
for(int i = 0; i < excelDatas.size(); i++){
// 填入序号
QString indexStart = QString("A%1").arg(row);
QString indexend = QString("A%1").arg(row + qMax(excelDatas[i].qrcodes.size(),1) - 1);
mergeCells(indexStart, indexend, QString::number(i));
// 填入图片名
QString imgStart = QString("B%1").arg(row);
QString imgEnd = QString("B%1").arg(row + qMax(excelDatas[i].qrcodes.size(),1) - 1);
mergeCells(imgStart, imgEnd, excelDatas[i].fileName);
// 填入耗时
QString timeStart = QString("C%1").arg(row);
QString timeEnd = QString("C%1").arg(row + qMax(excelDatas[i].qrcodes.size(),1) - 1);
mergeCells(timeStart, timeEnd, QString("%1ms").arg(QString::number(excelDatas[i].elapsed,'f',2)));
// 如果没有二维码,填入灰色
if(excelDatas[i].qrcodes.size() <= 0){
QString colorStart = QString("A%1").arg(row);
QString colorEnd = QString("F%1").arg(row);
setColor(QColor(100,100,100), colorStart, colorEnd);
}
// 填入解码数据
QVariantList vars;
QList<QList<QVariant>> qrcodeList; // 需要先用QList<QList<QVariant>>包装一次
for(int j = 0; j < excelDatas[i].qrcodes.size(); j++){
QList<QVariant> cells;
cells.append(excelDatas[i].qrcodes[j].result);
cells.append(excelDatas[i].qrcodes[j].points);
cells.append(excelDatas[i].qrcodes[j].grade);
qrcodeList.append(cells);
vars.append(QVariant(qrcodeList.last()));
// 设置颜色,解码失败为红色,没有二维码的为灰色
if(excelDatas[i].qrcodes[j].result == ""){
QString colorStart = QString("D%1").arg(row+j);
QString colorEnd = QString("F%1").arg(row+j);
setColor(QColor(255,0,0), colorStart, colorEnd);
}
}
QVariant result = vars;
QString dataStart = QString("D%1").arg(row);
QString dataEnd = QString("F%1").arg(row + qMax(excelDatas[i].qrcodes.size(),1) - 1);
writeMulty(result, dataStart, dataEnd);
row = row + qMax(excelDatas[i].qrcodes.size(),1);
}
setAutoFit(row);
return true;
}
bool ExcelHandle::mergeCells(QString start, QString end, QString value)
{
if(workSheet == NULL)
return false;
QAxObject *range = workSheet->querySubObject("Range(const Qvariant&)", QVariant("=(" +start+ ": " +end+ ")"));
if(range == NULL)
return false;
range->setProperty("MergeCells", true); // 合并单元格
range->setProperty("Value", value);
return true;
}
bool ExcelHandle::setAutoFit(int lines)
{
if(workSheet == NULL)
return false;
QAxObject *range = workSheet->querySubObject("Range(const Qvariant&)", QVariant(QString("A1:F%1").arg(lines)));
if(range == NULL)
return false;
QAxObject *cells = range->querySubObject("Columns");
cells->dynamicCall("AutoFit");
return true;
}
demo示例文件:
/** Demo.h **/
#ifndef DEMO_H
#define DEMO_H
#include <QObject>
#include <QDebug>
#include <QFileInfo>
#include "Module/ExcelHandle.h"
#include "Module/Datasets.h"
#pragma execution_character_set("utf-8")
class Demo : public QObject
{
Q_OBJECT
public:
explicit Demo(QObject *parent = nullptr);
private:
void test();
signals:
};
#endif // DEMO_H
/** Demo.cpp **/
#include "Demo.h"
Demo::Demo(QObject *parent) : QObject(parent)
{
test();
}
void Demo::test()
{
QFileInfo infor("./testExcel.xlsx");
Datasets dataSets;
QVector<Datasets::ExcelData> excelDatas = dataSets.getData();
ExcelHandle excelHandle;
excelHandle.open(infor.absoluteFilePath());// 要绝对路径
// excelHandle.writeOne("ceshi一下中文", 1, 1);
excelHandle.writeExcelData(excelDatas);
excelHandle.save();
}
再贴一些其它功能
——居中
QAxObject *range = workSheet->querySubObject("Range(const Qvariant&)", QVariant(QString("A1:F1")));
if(range == NULL)
return false;
// 设置自动适配宽度
range->setProperty("VerticalAlignment", -4108); // 水平居中
range->setProperty("HorizontalAlignment", -4108); // 垂直居中
——固定宽度、自动换行
// 把E那一列,设定固定宽度30,并设置自动换行
QAxObject *E = workSheet->querySubObject("Columns(const QString&)", "E");
E->setProperty("ColumnWidth", 30);
E->setProperty("WrapText", true);
——设置字体
QAxObject *font = workSheet->querySubObject("Range(const QString&)", "A1:P1")->querySubObject("Font");// 获取单元格字体
font->setProperty("Bold",true);// 设置单元格字体加粗
font->setProperty("Size",13);// 设置单元格字体大小
——冻结首行
QAxObject* window = excel->querySubObject("ActiveWindow");
window->setProperty("SplitRow", 1);
window->setProperty("FreezePanes", true);