1. Environment setup
QtXlsx is a Qt library for reading and writing Microsoft Excel files (.xlsx). It provides a set of simple and easy-to-use APIs for convenient processing of spreadsheet data.
Github download: GitHub – dbzhang800/QtXlsxWriter: .xlsx file reader and writer for Qt5
Official documentation: http://qtxlsx.debao.me/
Environment setup
Unzip the compressed package
QtXlsx source code is embedded in QTCreator for use.
Create a new QTCreator form project and copy the src folder in the picture above to the project path.
Copy the following code to the test project.pro file
qmake, compile the code.
2. Commonly used methods
Create and save Excel files:
QXlsx::Document xlsx;
xlsx.write(“A1”, “Hello”);
xlsx.write(“B1”, “World”);
xlsx.saveAs(“example.xlsx”);
Read cell data:
QXlsx::Document xlsx(“example.xlsx”);
QString cellValue = xlsx.read(“A1”)->toString();
Read column data:
QXlsx::Document xlsx(“example.xlsx”);
QStringList columnValues = xlsx.read(“B”)->toStringList();
Modify cell data:
QXlsx::Document xlsx(“example.xlsx”);
xlsx.write(“A2”, 123);
xlsx.save();
Merge Cells:
QXlsx::Document xlsx(“example.xlsx”);
xlsx.mergeCells(“A1:B1”);
xlsx.save();
Set cell format:
QXlsx::Document xlsx(“example.xlsx”);
xlsx.setColumnWidth(1, 30);
xlsx.setCellFont(1, 1, QFont(“Arial”, 12, QFont::Bold));
xlsx.save();
Operation worksheet:
QXlsx::Document xlsx(“example.xlsx”);
xlsx.selectSheet(“Sheet2”); // Select a worksheet
xlsx.addSheet(“NewSheet”); // Add a new worksheet
xlsx.deleteSheet(“Sheet1”); // Delete the specified worksheet
xlsx.save();
Insert picture:
QXlsx::Document xlsx(“example.xlsx”);
QImage image(“image.png”);
xlsx.insertImage(1, 1, image);
xlsx.save();
For more detailed cases, please refer to the official help document: Official document: http://qtxlsx.debao.me/
3. Code example
Make a calendar spreadsheet.
#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;
grayLeftStyle.setPatternBackgroundColor(Qt::lightGray);
grayLeftStyle.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);
grayRightStyle.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 & amp; & amp; 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(), grayRightStyle);
}
} else if (day == date.daysInMonth() & amp; & amp; 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(), grayRightStyle);
}
}
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;
}
4.Related recommendations
Qt uses Quazip to decompress and compress files-CSDN Blog
Detailed explanation of QCustomplot_The role of rescαlerαxis in qcustomplot_Mr.codeee’s blog-CSDN blog
Qt QR code generation and recognition-CSDN Blog