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_NAMESPACEMainWindow::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 rowFormat 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 titlesfor (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 dayfor (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 dayfor (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();