Qt uses QAxObject to write to Excel tables for color filling, merging cells, multi-line writing, etc.

Qt uses QAxObject to write to Excel tables, including color filling, merging cells, multi-line writing, etc.

QAxObject can call various functions or events in the Excel table to control reading and writing Excel tables.

All interfaces in the Excel table can be viewed in the official documentation.

Source code can be seen at the end.

The first step is to clarify the data you need to write

My example here is to write a table of QR code decoding results.

For example, I have 4 pictures, each of which has one or more QR codes, or even no QR codes.

I need to mark the QR code that failed to decode in red, and the image without QR code in gray.

Get a result like this.

Step 2: Generate data

The data is simulated, just write some data casually.
Define a data structure.

 struct Qrcode{
        QString result;
        QString points;
        int grade;
    };

    struct ExcelData{
        QString fileName;
        float elapsed;
        QVector<Qrcode> qrcodes;
    };

Fill in data

void Datasets::initData()
{
    Qrcode oneQrcodeOne = getQrcodeData("The first QR code;", "(12,34),(566,322)", 4);
    Qrcode oneQrcodeTwo = getQrcodeData("The second QR code;", "(87,190),(200,98)", 0);
    Qrcode oneQrcodeThree = getQrcodeData("The third QR code", "(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("The first QR code;", "(87,1901),(200,98)", 2);
    Qrcode fourQrcodeThree = getQrcodeData("Second QR code;", "(122,41),(59,345)", 2);
    ExcelData excelDataFour = getExcelData("4.bmp", 24.9, {fourQrcodeTwo, fourQrcodeThree});
    excelDatas.append(excelDataFour);

    Qrcode fiveQrcodeOne = getQrcodeData("The first QR code;", "(87,192),(200,98)", 2);
    Qrcode fiveQrcodeTwo = getQrcodeData("The second QR code;", "(122,41),(59,345)", 2);
    Qrcode fiveQrcodeThree = getQrcodeData("The third QR code;", "(122,41),(59,345)", 2);
    Qrcode fiveQrcodeFour = getQrcodeData("", "(87,190),(200,98)", 0);
    Qrcode fiveQrcodeFive = getQrcodeData("The fourth QR code;", "(122,41),(59,345)", 2);
    ExcelData excelDataFive = getExcelData("5.bmp", 32.1, {fiveQrcodeOne, fiveQrcodeTwo, fiveQrcodeThree, fiveQrcodeFour, fiveQrcodeFive});
    excelDatas.append(excelDataFive);
}

Step 3: Open/create a new Excel table

bool ExcelHandle::open(QString excelPath)
{
    this->excelPath = excelPath;
    QFileInfo fileInfo(excelPath);

    excel = new QAxObject("Excel.Application");
    excel->setProperty("Visible", false);
    excel->setProperty("DisplayAlerts", false); // No pop-up window prompts when saving and saving as
    workBooks = excel->querySubObject("WorkBooks");

    if(!fileInfo.exists()){
        //!!!Create a new workbook
        workBooks->querySubObject("Add");
    } else {
        //!!!Open an existing workbook
        workBooks->querySubObject("Open (const QString & amp;)", QDir::toNativeSeparators(excelPath));
    }
    workBook = excel->querySubObject("ActiveWorkBook");
    if (workBook == nullptr)
        return false;

    //!!!Get the table page object
    workSheets = workBook->querySubObject("WorkSheets");
    workSheet = workSheets->querySubObject("Item(int)", 1);
    changeSheetName("QR code"); // Change the name of this table page

    if(workSheets == NULL)
        return false;
    return true;
}

[Article Benefits] Qt development learning information package, interview questions from major manufacturers, technical videos and learning roadmaps, including (Qt C++ basics, database programming, Qt project practice, Qt framework, QML, Opencv, qt threads, etc. etc.) If you need it, you can go to Penguin Skirt661714027to get it~

Step 4: Write QR code data

When filling in multiple rows of data, you need to use QList to convert to QVariantList, and then convert to QVariant. You cannot write directly using QVariantList and then convert it to QVariant format, which will cause the data to be written to fail.

bool ExcelHandle::writeExcelData(QVector<Datasets::ExcelData> excelDatas)
{
    //Write a header first
    QList<QVariant> header;
    header << "Serial number" << "Picture" << "Time consuming" << "Decoding content" << "Coordinates" << "Level";
    QVariant headerV = header;
    writeMulty(headerV, "A1", "F1");
    int row = 2; // row
    for(int i = 0; i < excelDatas.size(); i + + ){
        // Fill in the serial number
        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));
        // Fill in the picture name
        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);
        // Fill in the time taken
        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 there is no QR code, fill in gray
        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);
        }
        // Fill in the decoded data
        QVariantList vars;
        QList<QList<QVariant>> qrcodeList; // Need to wrap it with QList<QList<QVariant>> first
        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()));
            //Set the color, red if decoding fails, gray if there is no QR code
            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;
}

Step 5 Save and exit Excel

bool ExcelHandle::save()
{
    if(excel == NULL || workBooks == NULL || workBook == NULL)
        return false;
    /** save document **/
    QFileInfo fileInfo(excelPath);
    if(!fileInfo.exists())
        workBook->dynamicCall("SaveAs(const QString & amp;)", QDir::toNativeSeparators(excelPath));
    else
        workBook->dynamicCall("Save()");

    /** Close and exit the file **/
    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;
}

Merge cells

bool ExcelHandle::mergeCells(QString start, QString end, QString value)
{
    if(workSheet == NULL)
        return false;
    QAxObject *range = workSheet->querySubObject("Range(const Qvariant & amp;)", QVariant("=(" + start + ": " + end + ")"));
    if(range == NULL)
        return false;

    range->setProperty("MergeCells", true); // Merge cells
    range->setProperty("Value", value);
    return true;
}

Set background color

bool ExcelHandle::setColor(QColor color, QString start, QString end)
{
    QAxObject *range = workSheet->querySubObject("Range(const Qvariant & amp;)", 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;
}

Insert multiple rows of data

bool ExcelHandle::writeMulty(QVariant value, QString start, QString end)
{
if(workSheet == NULL)
return false;
QAxObject *range = workSheet->querySubObject("Range(const QString & amp;)",QString("(" + start + ": " + end + ")"));
range->setProperty("Value", value);
delete range;
range = NULL;
return true;
}

Automatically set cell width

bool ExcelHandle::setAutoFit(int lines)
{
    if(workSheet == NULL)
        return false;
    QAxObject *range = workSheet->querySubObject("Range(const Qvariant & amp;)", QVariant(QString("A1:F%1").arg(lines)));
    if(range == NULL)
        return false;

    QAxObject *cells = range->querySubObject("Columns");
    cells->dynamicCall("AutoFit");
    return true;
}

Source code

data file:

#ifndef DATASETS_H
#define DATASETS_H

/** Datasets.h **/

#include 
#include 


#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 getData();

private:
    void initData();
    Qrcode getQrcodeData(QString result, QString point, int grade);
    ExcelData getExcelData(QString fileName, float elapsed, QVector qrcodes);

private:
    QVector 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("The first QR code;", "(12,34),(566,322)", 4);
    Qrcode oneQrcodeTwo = getQrcodeData("The second QR code;", "(87,190),(200,98)", 0);
    Qrcode oneQrcodeThree = getQrcodeData("The third QR code", "(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("The first QR code;", "(87,1901),(200,98)", 2);
    Qrcode fourQrcodeThree = getQrcodeData("Second QR code;", "(122,41),(59,345)", 2);
    ExcelData excelDataFour = getExcelData("4.bmp", 24.9, {fourQrcodeTwo, fourQrcodeThree});
    excelDatas.append(excelDataFour);

    Qrcode fiveQrcodeOne = getQrcodeData("The first QR code;", "(87,192),(200,98)", 2);
    Qrcode fiveQrcodeTwo = getQrcodeData("The second QR code;", "(122,41),(59,345)", 2);
    Qrcode fiveQrcodeThree = getQrcodeData("The third QR code;", "(122,41),(59,345)", 2);
    Qrcode fiveQrcodeFour = getQrcodeData("", "(87,190),(200,98)", 0);
    Qrcode fiveQrcodeFive = getQrcodeData("The fourth QR code;", "(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 table operation file:

/** 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)
{

}

/** Open an 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); // No pop-up window prompts when saving and saving as
    workBooks = excel->querySubObject("WorkBooks");

    if(!fileInfo.exists()){
        //!!!Create a new workbook
        workBooks->querySubObject("Add");
    } else {
        //!!!Open an existing workbook
        workBooks->querySubObject("Open (const QString & amp;)", QDir::toNativeSeparators(excelPath));
    }
    workBook = excel->querySubObject("ActiveWorkBook");
    if (workBook == nullptr)
        return false;

    //!!!Get the table page object
    workSheets = workBook->querySubObject("WorkSheets");
    workSheet = workSheets->querySubObject("Item(int)", 1);
    changeSheetName("QR code"); // Change the name of this table page

    if(workSheets == NULL)
        return false;
    return true;
}

bool ExcelHandle::save()
{
    if(excel == NULL || workBooks == NULL || workBook == NULL)
        return false;
    /** save document **/
    QFileInfo fileInfo(excelPath);
    if(!fileInfo.exists())
        workBook->dynamicCall("SaveAs(const QString & amp;)", QDir::toNativeSeparators(excelPath));
    else
        workBook->dynamicCall("Save()");

    /** Close and exit the file **/
    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 & amp;)", 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 & amp;)",QString("(" + start + ": " + end + ")"));
    range->setProperty("Value", value);
    delete range;
    range = NULL;
    return true;
}

bool ExcelHandle::writeExcelData(QVector excelDatas)
{
    //Write a header first
    QList header;
    header << "Serial number" << "Picture" << "Time consuming" << "Decoding content" << "Coordinates" << "Level";
    QVariant headerV = header;
    writeMulty(headerV, "A1", "F1");
    int row = 2; // row
    for(int i = 0; i < excelDatas.size(); i + + ){
        // Fill in the serial number
        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));
        // Fill in the picture name
        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);
        // Fill in the time taken
        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 there is no QR code, fill in gray
        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);
        }
        // Fill in the decoded data
        QVariantList vars;
        QList> qrcodeList; // Need to wrap it with QList> first
        for(int j = 0; j < excelDatas[i].qrcodes.size(); j + + ){
            QList 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()));

            //Set the color, red if decoding fails, gray if there is no QR code
            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 & amp;)", QVariant("=(" + start + ": " + end + ")"));
    if(range == NULL)
        return false;

    range->setProperty("MergeCells", true); // Merge cells
    range->setProperty("Value", value);
    return true;
}

bool ExcelHandle::setAutoFit(int lines)
{
    if(workSheet == NULL)
        return false;
    QAxObject *range = workSheet->querySubObject("Range(const Qvariant & amp;)", QVariant(QString("A1:F%1").arg(lines)));
    if(range == NULL)
        return false;

    QAxObject *cells = range->querySubObject("Columns");
    cells->dynamicCall("AutoFit");
    return true;
}

Demo sample file:

/** 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());//requires absolute path
// excelHandle.writeOne("ceshi a Chinese", 1, 1);
    excelHandle.writeExcelData(excelDatas);
    excelHandle.save();
}

Post some other functions

–Centered

 QAxObject *range = workSheet->querySubObject("Range(const Qvariant & amp;)", QVariant(QString("A1:F1")));
    if(range == NULL)
        return false;

    //Set automatic adaptation width
    range->setProperty("VerticalAlignment", -4108); // Center horizontally
    range->setProperty("HorizontalAlignment", -4108); // Vertically centered

–Fixed width, automatic word wrapping

 // Set the fixed width of column E to 30 and set automatic line wrapping
    QAxObject *E = workSheet->querySubObject("Columns(const QString & amp;)", "E");
    E->setProperty("ColumnWidth", 30);
    E->setProperty("WrapText", true);

–Set font

 QAxObject *font = workSheet->querySubObject("Range(const QString & amp;)", "A1:P1")->querySubObject("Font");// Get the cell font
    font->setProperty("Bold",true);//Set the cell font to be bold
    font->setProperty("Size",13);//Set the cell font size

–Freeze the first line

 QAxObject* window = excel->querySubObject("ActiveWindow");
    window->setProperty("SplitRow", 1);
    window->setProperty("FreezePanes", true);

[Article Benefits] Qt development learning information package, interview questions from major manufacturers, technical videos and learning roadmaps, including (Qt C++ basics, database programming, Qt project practice, Qt framework, QML, Opencv, qt threads, etc. etc.) If you need it, you can go to Penguin Skirt661714027to get it~