Qt 使用QtXlsx操作Excel表

1.环境搭建

QtXlsx是一个用于读写Microsoft Excel文件（.xlsx）的Qt库。它提供了一组简单易用的API，可以方便地处理电子表格数据。

Github下载：GitHub - dbzhang800/QtXlsxWriter: .xlsx file reader and writer for Qt5
官方文档：http://qtxlsx.debao.me/

环境搭建

解压压缩包

QtXlsx源码嵌入QTCreator中使用。

新建一个QTCreator窗体项目，将上图src文件夹拷贝到该项目路径中。

将如下代码拷贝到.pro文件中

qmake，编译代码。

2.代码示例

做一个日历表格。

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include "xlsxdocument.h"
#include "xlsxchartsheet.h"
#include "xlsxcellrange.h"
#include "xlsxchart.h"
#include "xlsxrichstring.h"
#include "xlsxworkbook.h"
#include <QDate>

QTXLSX_USE_NAMESPACE

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    Document xlsx;
    QDate today(QDate::currentDate());
    for (int month = 1; month <= 12; ++month) {
        xlsx.addSheet(QLocale().monthName(month));
        xlsx.currentWorksheet()->setGridLinesVisible(false);

        // the header row
        Format headerStyle;
        headerStyle.setFontSize(48);
        headerStyle.setFontColor(Qt::darkBlue);
        headerStyle.setHorizontalAlignment(Format::AlignHCenter);
        headerStyle.setVerticalAlignment(Format::AlignVCenter);
        xlsx.setRowHeight(1, 80);
        xlsx.write("A1", QString("%1 %2").arg(QLocale().monthName(month)).arg(today.year()));
        xlsx.mergeCells("A1:N1", headerStyle);

        // header with month titles
        for (int day = 1; day <= 7; ++day) {
            Format monthStyle;
            monthStyle.setFontSize(12);
            monthStyle.setFontColor(Qt::white);
            monthStyle.setFontBold(true);
            monthStyle.setHorizontalAlignment(Format::AlignHCenter);
            monthStyle.setVerticalAlignment(Format::AlignVCenter);
            monthStyle.setFillPattern(Format::PatternSolid);
            monthStyle.setPatternBackgroundColor(Qt::darkBlue);

            xlsx.setColumnWidth(day * 2 - 1, day * 2 - 1, 5);
            xlsx.setColumnWidth(day * 2, day * 2, 13);
            xlsx.write(2, day * 2 - 1, QLocale().dayName(day));
            xlsx.mergeCells(CellRange(2, day * 2 - 1, 2, day * 2), monthStyle);
        }

        QColor borderColor = QColor(Qt::gray);

        Format weekendLeftStyle;
        weekendLeftStyle.setFontSize(14);
        weekendLeftStyle.setFontBold(true);
        weekendLeftStyle.setHorizontalAlignment(Format::AlignLeft);
        weekendLeftStyle.setVerticalAlignment(Format::AlignTop);
        weekendLeftStyle.setPatternBackgroundColor(QColor("#93CCEA"));
        weekendLeftStyle.setLeftBorderStyle(Format::BorderThin);
        weekendLeftStyle.setLeftBorderColor(borderColor);
        weekendLeftStyle.setBottomBorderStyle(Format::BorderThin);
        weekendLeftStyle.setBottomBorderColor(borderColor);

        Format weekendRightStyle;
        weekendRightStyle.setHorizontalAlignment(Format::AlignHCenter);
        weekendRightStyle.setVerticalAlignment(Format::AlignTop);
        weekendRightStyle.setPatternBackgroundColor(QColor("#93CCEA"));
        weekendRightStyle.setRightBorderStyle(Format::BorderThin);
        weekendRightStyle.setRightBorderColor(borderColor);
        weekendRightStyle.setBottomBorderStyle(Format::BorderThin);
        weekendRightStyle.setBottomBorderColor(borderColor);

        Format workdayLeftStyle;
        workdayLeftStyle.setHorizontalAlignment(Format::AlignLeft);
        workdayLeftStyle.setVerticalAlignment(Format::AlignTop);
        workdayLeftStyle.setPatternBackgroundColor(Qt::white);
        workdayLeftStyle.setLeftBorderStyle(Format::BorderThin);
        workdayLeftStyle.setLeftBorderColor(borderColor);
        workdayLeftStyle.setBottomBorderStyle(Format::BorderThin);
        workdayLeftStyle.setBottomBorderColor(borderColor);

        Format workdayRightStyle;
        workdayRightStyle.setHorizontalAlignment(Format::AlignHCenter);
        workdayRightStyle.setVerticalAlignment(Format::AlignTop);
        workdayRightStyle.setPatternBackgroundColor(Qt::white);
        workdayRightStyle.setRightBorderStyle(Format::BorderThin);
        workdayRightStyle.setRightBorderColor(borderColor);
        workdayRightStyle.setBottomBorderStyle(Format::BorderThin);
        workdayRightStyle.setBottomBorderColor(borderColor);

        Format greyLeftStyle;
        greyLeftStyle.setPatternBackgroundColor(Qt::lightGray);
        greyLeftStyle.setLeftBorderStyle(Format::BorderThin);
        greyLeftStyle.setLeftBorderColor(borderColor);
        greyLeftStyle.setBottomBorderStyle(Format::BorderThin);
        greyLeftStyle.setBottomBorderColor(borderColor);

        Format greyRightStyle;
        greyRightStyle.setPatternBackgroundColor(Qt::lightGray);
        greyRightStyle.setRightBorderStyle(Format::BorderThin);
        greyRightStyle.setRightBorderColor(borderColor);
        greyRightStyle.setBottomBorderStyle(Format::BorderThin);
        greyRightStyle.setBottomBorderColor(borderColor);

        int rownum = 3;
        for (int day = 1; day <= 31; ++day) {
            QDate date(today.year(), month, day);
            if (!date.isValid())
                break;
            xlsx.setRowHeight(rownum, 100);
            int dow = date.dayOfWeek();
            int colnum = dow * 2 - 1;

            if (dow <= 5) {
                xlsx.write(rownum, colnum, day, workdayLeftStyle);
                xlsx.write(rownum, colnum + 1, QVariant(), workdayRightStyle);
            } else {
                xlsx.write(rownum, colnum, day, weekendLeftStyle);
                xlsx.write(rownum, colnum + 1, QVariant(), weekendRightStyle);
            }

            if (day == 1 && dow != 1) { // First day
                for (int i = 1; i < dow; ++i) {
                    xlsx.write(rownum, i * 2 - 1, QVariant(), greyLeftStyle);
                    xlsx.write(rownum, i * 2, QVariant(), greyRightStyle);
                }
            } else if (day == date.daysInMonth() && dow != 7) { // Last day
                for (int i = dow + 1; i <= 7; ++i) {
                    xlsx.write(rownum, i * 2 - 1, QVariant(), greyLeftStyle);
                    xlsx.write(rownum, i * 2, QVariant(), greyRightStyle);
                }
            }

            if (dow == 7)
                rownum++;
        }
    }

    xlsx.saveAs("Book1.xlsx");

    // Make sure that read/write works well.
    Document xlsx2("Book1.xlsx");
    xlsx2.saveAs("Book2.xlsx");

}

MainWindow::~MainWindow()
{
    delete ui;
}

3.常用方法

创建和保存Excel文件：

QXlsx::Document xlsx;
xlsx.write("A1", "Hello");
xlsx.write("B1", "World");
xlsx.saveAs("example.xlsx");

读取单元格数据：

QXlsx::Document xlsx("example.xlsx");
QString cellValue = xlsx.read("A1")->toString();

读取列数据：

QXlsx::Document xlsx("example.xlsx")；
QStringList columnValues = xlsx.read("B")->toStringList();

修改单元格数据：

QXlsx::Document xlsx("example.xlsx");
xlsx.write("A2", 123);
xlsx.save();

合并单元格：

QXlsx::Document xlsx("example.xlsx");
xlsx.mergeCells("A1:B1");
xlsx.save();

设置单元格格式：

QXlsx::Document xlsx("example.xlsx");
xlsx.setColumnWidth(1, 30);
xlsx.setCellFont(1, 1, QFont("Arial", 12, QFont::Bold));
xlsx.save();

操作工作表：

QXlsx::Document xlsx("example.xlsx");
xlsx.selectSheet("Sheet2"); // 选中某个工作表
xlsx.addSheet("NewSheet"); // 添加一个新的工作表
xlsx.deleteSheet("Sheet1"); // 删除指定工作表
xlsx.save();

插入图片：

QXlsx::Document xlsx("example.xlsx");
QImage image("image.png");
xlsx.insertImage(1, 1, image);
xlsx.save();