1.Qt Xlsx库简介
官方文档：Qt Xlsx | QtXlsx 0.3 (debao.me)
下载地址：dbzhang800/QtXlsxWriter: .xlsx file reader and writer for Qt5 (github.com)
QtXlsx是可以读取和写入Excel文件的库。 它不需要Microsoft Excel，并且可以在Qt5支持的任何平台中使用。 该库可用于：
- 从头开始生成新的.xlsx文件
- 从现有的.xlsx文件提取数据
- 编辑现有的.xlsx文件
注意：
对于Linux用户，如果Qt是通过“ apt-get”之类的软件包管理器工具安装的，请确保已安装Qt5开发软件包qtbase5-private-dev
2. 用法①：使用Xlsx作为Qt5的附加模块
2.1 构建模块
注意：此步骤需要用到Perl。
- 未安装：
- 已安装：
如果您已经安装则可进行2.2步骤，否则请先安装perl
2.2 下载QtXlsx源代码
https://github.com/dbzhang800/QtXlsxWriter
注意，源代码前级目录不要有中文！！！
2.3 为编译器安装Xlsx模块
我这里有5个编译器：
现在以给MinGW 32-bit 作为示例，其它编译器类似：
2.3.1 打开 MinGW 7.3.0 32-bit 控制台
2.3.2 找到QtXlsx源码路径
我的路径地址为：
H:\0.Download\Edge\QtXlsxWriter-master\src
2.3.3 再MinGW32控制台进入路径
接下来进行以下操作(Linux)：
1 qmake src.pro
2 make
3 make install
接下来进行以下操作(mingw32、mingw64)：
1 qmake src.pro
2 mingw32-make
3 mingw32-make install
2.3.4 执行qmake
执行qmake，会自动生成makefile文件
2.3.5 执行makefile
这里会编译很久，喝杯茶休息休息：
编译完成：
2.3.6 为编译器安装QtXlsx
执行以下命令：
2.3.7 安装完成
1 H:\0.Download\Edge\QtXlsxWriter-master\src>mingw32-make install
2 cd xlsx\ && ( if not exist Makefile E:\Qt5.14.2\5.14.2\mingw73_32\bin\qmake.exe -o Makefile H:\0.Download\Edge\QtXlsxWriter-master\src\xlsx\xlsx.pro ) && mingw32-make -f Makefile install
3 mingw32-make[1]: Entering directory 'H:/0.Download/Edge/QtXlsxWriter-master/src/xlsx'
4 mingw32-make -f Makefile.Release install
5 mingw32-make[2]: Entering directory 'H:/0.Download/Edge/QtXlsxWriter-master/src/xlsx'
6 copy /y ..\..\lib\Qt5Xlsx.dll E:\Qt5.14.2\5.14.2\mingw73_32\bin\Qt5Xlsx.dll
7 已复制 1 个文件。
8
9 ....此处省略一万字
10
11 E:\Qt5.14.2\5.14.2\mingw73_32\bin\qmake.exe -install qinstall H:\0.Download\Edge\QtXlsxWriter-master\lib\cmake\Qt5Xlsx\Qt5XlsxConfig.cmake E:\Qt5.14.2\5.14.2\mingw73_32\lib\cmake\Qt5Xlsx\Qt5XlsxConfig.cmake
12 E:\Qt5.14.2\5.14.2\mingw73_32\bin\qmake.exe -install qinstall H:\0.Download\Edge\QtXlsxWriter-master\lib\cmake\Qt5Xlsx\Qt5XlsxConfigVersion.cmake E:\Qt5.14.2\5.14.2\mingw73_32\lib\cmake\Qt5Xlsx\Qt5XlsxConfigVersion.cmake
13 mingw32-make[2]: Leaving directory 'H:/0.Download/Edge/QtXlsxWriter-master/src/xlsx'
14 mingw32-make[1]: Leaving directory 'H:/0.Download/Edge/QtXlsxWriter-master/src/xlsx'
2.3.8 同样的方法为MinGw 64安装模块
首先要解压另一份源码：
2.4 使用QtXlsx模块
这里我们用64bit工程做示范，32位一样：
2.4.1 创建工程
设置工程名称：
选择编译器版本：
创建完成：
2.4.2 打开QtXlsx示例文件
我这里地址为：
H:\0.Download\Edge\QtXlsxWriter-master\examples\xlsx
2.4.3 复制hello工程内容
1 #include <QtCore>
2 #include "xlsxdocument.h"
3
4 int main()
5 {
6 //![0]
7 QXlsx::Document xlsx;
8 //![0]
9
10 //![1]
11 xlsx.write("A1", "Hello Qt!");
12 xlsx.write("A2", 12345);
13 xlsx.write("A3", "=44+33");
14 xlsx.write("A4", true);
15 xlsx.write("A5", "http://qt-project.org");
16 xlsx.write("A6", QDate(2013, 12, 27));
17 xlsx.write("A7", QTime(6, 30));
18 //![1]
19
20 //![2]
21 xlsx.save();
22 //![2]
23
24 return 0;
25 }
2.4.4 替换原工程的main.cpp
看到有报错，是因为没有引入xlsx模块。
2.4.5 在.pro文件中引入xlsx模块
2.4.6 运行查看效果
生成了xlsx格式的文件
表格内容如下：
3.用法②：直接使用源代码
该包包含一个 qtxlsx.pri文件，允许您将组件集成到使用 qmake 进行生成步骤的应用程序中。
3.1 新建工程
3.2 工程目录下添加源代码
来到工程目录下：
打开网上下载的源码路径：
3.3 在工程中引入xlsx源代码
3.4 修改main.cpp内容，测试
1 #include <QtCore>
2 #include "xlsxdocument.h"
3
4 int main()
5 {
6 //![0]
7 QXlsx::Document xlsx;
8 //![0]
9
10 //![1]
11 xlsx.write("A1", "Hello Qt!");
12 xlsx.write("A2", 12345);
13 xlsx.write("A3", "=44+33");
14 xlsx.write("A4", true);
15 xlsx.write("A5", "http://qt-project.org");
16 xlsx.write("A6", QDate(2013, 12, 27));
17 xlsx.write("A7", QTime(6, 30));
18 //![1]
19
20 //![2]
21 xlsx.save();
22 //![2]
23
24 return 0;
25 }
3.5 查看效果
内容一致：
4. 官方examples例子
4.1 calendar Qt操作Excel生成日历
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxformat.h"
4 #include "xlsxcellrange.h"
5 #include "xlsxworksheet.h"
6
7 QTXLSX_USE_NAMESPACE
8
9 int main(int argc, char **argv)
10 {
11 QCoreApplication app(argc, argv);
12
13 // Select a proper locale
14 // QLocale::setDefault(QLocale(QLocale::English));
15
16 Document xlsx;
17 QDate today(QDate::currentDate());
18 for (int month = 1; month <= 12; ++month) {
19 xlsx.addSheet(QLocale().monthName(month));
20 xlsx.currentWorksheet()->setGridLinesVisible(false);
21
22 // the header row
23 Format headerStyle;
24 headerStyle.setFontSize(48);
25 headerStyle.setFontColor(Qt::darkBlue);
26 headerStyle.setHorizontalAlignment(Format::AlignHCenter);
27 headerStyle.setVerticalAlignment(Format::AlignVCenter);
28 xlsx.setRowHeight(1, 80);
29 xlsx.write("A1", QString("%1 %2").arg(QLocale().monthName(month)).arg(today.year()));
30 xlsx.mergeCells("A1:N1", headerStyle);
31
32 // header with month titles
33 for (int day = 1; day <= 7; ++day) {
34 Format monthStyle;
35 monthStyle.setFontSize(12);
36 monthStyle.setFontColor(Qt::white);
37 monthStyle.setFontBold(true);
38 monthStyle.setHorizontalAlignment(Format::AlignHCenter);
39 monthStyle.setVerticalAlignment(Format::AlignVCenter);
40 monthStyle.setFillPattern(Format::PatternSolid);
41 monthStyle.setPatternBackgroundColor(Qt::darkBlue);
42
43 xlsx.setColumnWidth(day * 2 - 1, day * 2 - 1, 5);
44 xlsx.setColumnWidth(day * 2, day * 2, 13);
45 xlsx.write(2, day * 2 - 1, QLocale().dayName(day));
46 xlsx.mergeCells(CellRange(2, day * 2 - 1, 2, day * 2), monthStyle);
47 }
48
49 QColor borderColor = QColor(Qt::gray);
50
51 Format weekendLeftStyle;
52 weekendLeftStyle.setFontSize(14);
53 weekendLeftStyle.setFontBold(true);
54 weekendLeftStyle.setHorizontalAlignment(Format::AlignLeft);
55 weekendLeftStyle.setVerticalAlignment(Format::AlignTop);
56 weekendLeftStyle.setPatternBackgroundColor(QColor("#93CCEA"));
57 weekendLeftStyle.setLeftBorderStyle(Format::BorderThin);
58 weekendLeftStyle.setLeftBorderColor(borderColor);
59 weekendLeftStyle.setBottomBorderStyle(Format::BorderThin);
60 weekendLeftStyle.setBottomBorderColor(borderColor);
61
62 Format weekendRightStyle;
63 weekendRightStyle.setHorizontalAlignment(Format::AlignHCenter);
64 weekendRightStyle.setVerticalAlignment(Format::AlignTop);
65 weekendRightStyle.setPatternBackgroundColor(QColor("#93CCEA"));
66 weekendRightStyle.setRightBorderStyle(Format::BorderThin);
67 weekendRightStyle.setRightBorderColor(borderColor);
68 weekendRightStyle.setBottomBorderStyle(Format::BorderThin);
69 weekendRightStyle.setBottomBorderColor(borderColor);
70
71 Format workdayLeftStyle;
72 workdayLeftStyle.setHorizontalAlignment(Format::AlignLeft);
73 workdayLeftStyle.setVerticalAlignment(Format::AlignTop);
74 workdayLeftStyle.setPatternBackgroundColor(Qt::white);
75 workdayLeftStyle.setLeftBorderStyle(Format::BorderThin);
76 workdayLeftStyle.setLeftBorderColor(borderColor);
77 workdayLeftStyle.setBottomBorderStyle(Format::BorderThin);
78 workdayLeftStyle.setBottomBorderColor(borderColor);
79
80 Format workdayRightStyle;
81 workdayRightStyle.setHorizontalAlignment(Format::AlignHCenter);
82 workdayRightStyle.setVerticalAlignment(Format::AlignTop);
83 workdayRightStyle.setPatternBackgroundColor(Qt::white);
84 workdayRightStyle.setRightBorderStyle(Format::BorderThin);
85 workdayRightStyle.setRightBorderColor(borderColor);
86 workdayRightStyle.setBottomBorderStyle(Format::BorderThin);
87 workdayRightStyle.setBottomBorderColor(borderColor);
88
89 Format greyLeftStyle;
90 greyLeftStyle.setPatternBackgroundColor(Qt::lightGray);
91 greyLeftStyle.setLeftBorderStyle(Format::BorderThin);
92 greyLeftStyle.setLeftBorderColor(borderColor);
93 greyLeftStyle.setBottomBorderStyle(Format::BorderThin);
94 greyLeftStyle.setBottomBorderColor(borderColor);
95
96 Format greyRightStyle;
97 greyRightStyle.setPatternBackgroundColor(Qt::lightGray);
98 greyRightStyle.setRightBorderStyle(Format::BorderThin);
99 greyRightStyle.setRightBorderColor(borderColor);
100 greyRightStyle.setBottomBorderStyle(Format::BorderThin);
101 greyRightStyle.setBottomBorderColor(borderColor);
102
103 int rownum = 3;
104 for (int day = 1; day <= 31; ++day) {
105 QDate date(today.year(), month, day);
106 if (!date.isValid())
107 break;
108 xlsx.setRowHeight(rownum, 100);
109 int dow = date.dayOfWeek();
110 int colnum = dow * 2 - 1;
111
112 if (dow <= 5) {
113 xlsx.write(rownum, colnum, day, workdayLeftStyle);
114 xlsx.write(rownum, colnum + 1, QVariant(), workdayRightStyle);
115 } else {
116 xlsx.write(rownum, colnum, day, weekendLeftStyle);
117 xlsx.write(rownum, colnum + 1, QVariant(), weekendRightStyle);
118 }
119
120 if (day == 1 && dow != 1) { // First day
121 for (int i = 1; i < dow; ++i) {
122 xlsx.write(rownum, i * 2 - 1, QVariant(), greyLeftStyle);
123 xlsx.write(rownum, i * 2, QVariant(), greyRightStyle);
124 }
125 } else if (day == date.daysInMonth() && dow != 7) { // Last day
126 for (int i = dow + 1; i <= 7; ++i) {
127 xlsx.write(rownum, i * 2 - 1, QVariant(), greyLeftStyle);
128 xlsx.write(rownum, i * 2, QVariant(), greyRightStyle);
129 }
130 }
131
132 if (dow == 7)
133 rownum++;
134 }
135 }
136
137 xlsx.saveAs("Book1.xlsx");
138
139 // Make sure that read/write works well.
140 Document xlsx2("Book1.xlsx");
141 xlsx2.saveAs("Book2.xlsx");
142
143 return 0;
144 }
4.2 chart Qt操作Excel生成图表
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxcellrange.h"
4 #include "xlsxchart.h"
5
6 using namespace QXlsx;
7
8 int main()
9 {
10 //![0]
11 Document xlsx;
12 for (int i = 1; i < 10; ++i) {
13 xlsx.write(i, 1, i * i * i); // A1:A9
14 xlsx.write(i, 2, i * i); // B1:B9
15 xlsx.write(i, 3, i * i - 1); // C1:C9
16 }
17 //![0]
18
19 //![1]
20 Chart *pieChart = xlsx.insertChart(3, 3, QSize(300, 300));
21 pieChart->setChartType(Chart::CT_Pie);
22 pieChart->addSeries(CellRange("A1:A9"));
23 pieChart->addSeries(CellRange("B1:B9"));
24 pieChart->addSeries(CellRange("C1:C9"));
25
26 Chart *pie3DChart = xlsx.insertChart(3, 9, QSize(300, 300));
27 pie3DChart->setChartType(Chart::CT_Pie3D);
28 pie3DChart->addSeries(CellRange("A1:C9"));
29
30 Chart *barChart = xlsx.insertChart(23, 3, QSize(300, 300));
31 barChart->setChartType(Chart::CT_Bar);
32 barChart->addSeries(CellRange("A1:C9"));
33
34 Chart *bar3DChart = xlsx.insertChart(23, 9, QSize(300, 300));
35 bar3DChart->setChartType(Chart::CT_Bar3D);
36 bar3DChart->addSeries(CellRange("A1:C9"));
37
38 Chart *lineChart = xlsx.insertChart(43, 3, QSize(300, 300));
39 lineChart->setChartType(Chart::CT_Line);
40 lineChart->addSeries(CellRange("A1:C9"));
41
42 Chart *line3DChart = xlsx.insertChart(43, 9, QSize(300, 300));
43 line3DChart->setChartType(Chart::CT_Line3D);
44 line3DChart->addSeries(CellRange("A1:C9"));
45
46 Chart *areaChart = xlsx.insertChart(63, 3, QSize(300, 300));
47 areaChart->setChartType(Chart::CT_Area);
48 areaChart->addSeries(CellRange("A1:C9"));
49
50 Chart *area3DChart = xlsx.insertChart(63, 9, QSize(300, 300));
51 area3DChart->setChartType(Chart::CT_Area3D);
52 area3DChart->addSeries(CellRange("A1:C9"));
53
54 Chart *scatterChart = xlsx.insertChart(83, 3, QSize(300, 300));
55 scatterChart->setChartType(Chart::CT_Scatter);
56 // Will generate three lines.
57 scatterChart->addSeries(CellRange("A1:A9"));
58 scatterChart->addSeries(CellRange("B1:B9"));
59 scatterChart->addSeries(CellRange("C1:C9"));
60
61 Chart *scatterChart_2 = xlsx.insertChart(83, 9, QSize(300, 300));
62 scatterChart_2->setChartType(Chart::CT_Scatter);
63 // Will generate two lines.
64 scatterChart_2->addSeries(CellRange("A1:C9"));
65
66 Chart *doughnutChart = xlsx.insertChart(103, 3, QSize(300, 300));
67 doughnutChart->setChartType(Chart::CT_Doughnut);
68 doughnutChart->addSeries(CellRange("A1:C9"));
69 //![1]
70
71 //![2]
72 xlsx.saveAs("Book1.xlsx");
73 //![2]
74
75 Document xlsx2("Book1.xlsx");
76 xlsx2.saveAs("Book2.xlsx");
77 return 0;
78 }
4.3 chartsheet Qt操作Excel生成图表
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxchartsheet.h"
4 #include "xlsxcellrange.h"
5 #include "xlsxchart.h"
6
7 using namespace QXlsx;
8
9 int main()
10 {
11 //![0]
12 Document xlsx;
13 for (int i = 1; i < 10; ++i)
14 xlsx.write(i, 1, i * i);
15 //![0]
16
17 //![1]
18 xlsx.addSheet("Chart1", AbstractSheet::ST_ChartSheet);
19 Chartsheet *sheet = static_cast<Chartsheet *>(xlsx.currentSheet());
20 Chart *barChart = sheet->chart();
21 barChart->setChartType(Chart::CT_Bar);
22 barChart->addSeries(CellRange("A1:A9"), xlsx.sheet("Sheet1"));
23 //![1]
24
25 //![2]
26 xlsx.saveAs("Book1.xlsx");
27 //![2]
28
29 Document xlsx2("Book1.xlsx");
30 xlsx2.saveAs("Book2.xlsx");
31 return 0;
32 }
4.4 conditionalformatting Qt操作Excel 条件格式化
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxconditionalformatting.h"
4
5 using namespace QXlsx;
6
7 int main()
8 {
9 //![0]
10 Document xlsx;
11 Format hFmt;
12 hFmt.setFontBold(true);
13 xlsx.write("B1", "(-inf,40)", hFmt);
14 xlsx.write("C1", "[30,70]", hFmt);
15 xlsx.write("D1", "startsWith 2", hFmt);
16 xlsx.write("E1", "dataBar", hFmt);
17 xlsx.write("F1", "colorScale", hFmt);
18
19 for (int row = 3; row < 22; ++row) {
20 for (int col = 2; col < 22; ++col)
21 xlsx.write(row, col, qrand() % 100);
22 }
23 //![0]
24
25 //![cf1]
26 ConditionalFormatting cf1;
27 Format fmt1;
28 fmt1.setFontColor(Qt::green);
29 fmt1.setBorderStyle(Format::BorderDashed);
30 cf1.addHighlightCellsRule(ConditionalFormatting::Highlight_LessThan, "40", fmt1);
31 cf1.addRange("B3:B21");
32 xlsx.addConditionalFormatting(cf1);
33 //![cf1]
34
35 //![cf2]
36 ConditionalFormatting cf2;
37 Format fmt2;
38 fmt2.setBorderStyle(Format::BorderDotted);
39 fmt2.setBorderColor(Qt::blue);
40 cf2.addHighlightCellsRule(ConditionalFormatting::Highlight_Between, "30", "70", fmt2);
41 cf2.addRange("C3:C21");
42 xlsx.addConditionalFormatting(cf2);
43 //![cf2]
44
45 //![cf3]
46 ConditionalFormatting cf3;
47 Format fmt3;
48 fmt3.setFontStrikeOut(true);
49 fmt3.setFontBold(true);
50 cf3.addHighlightCellsRule(ConditionalFormatting::Highlight_BeginsWith, "2", fmt3);
51 cf3.addRange("D3:D21");
52 xlsx.addConditionalFormatting(cf3);
53 //![cf3]
54
55 //![cf4]
56 ConditionalFormatting cf4;
57 cf4.addDataBarRule(Qt::blue);
58 cf4.addRange("E3:E21");
59 xlsx.addConditionalFormatting(cf4);
60 //![cf4]
61
62 //![cf5]
63 ConditionalFormatting cf5;
64 cf5.add2ColorScaleRule(Qt::blue, Qt::red);
65 cf5.addRange("F3:F21");
66 xlsx.addConditionalFormatting(cf5);
67 //![cf5]
68
69 //![2]
70 xlsx.saveAs("Book1.xlsx");
71 //![2]
72
73 Document xlsx2("Book1.xlsx");
74 xlsx2.saveAs("Book2.xlsx");
75 return 0;
76 }
4.5 datavalidation Qt操作Excel 数据有效性
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxdatavalidation.h"
4
5 QTXLSX_USE_NAMESPACE
6
7 int main()
8 {
9 Document xlsx;
10 xlsx.write("A1", "A2 and A3:E5 only accept the number between 33 and 99");
11
12 //![1]
13 DataValidation validation(DataValidation::Whole, DataValidation::Between, "33", "99");
14 validation.addRange("A2");
15 validation.addRange("A3:E5");
16 validation.setPromptMessage("Please Input Integer between 33 and 99");
17 xlsx.addDataValidation(validation);
18 //![1]
19
20 xlsx.saveAs("Book1.xlsx");
21 return 0;
22 }
4.6 definename Qt操作Excel 定义名字
1 #include <QtCore>
2 #include "xlsxdocument.h"
3
4 QTXLSX_USE_NAMESPACE
5
6 int main()
7 {
8 //![0]
9 Document xlsx;
10 for (int i = 1; i <= 10; ++i) {
11 xlsx.write(i, 1, i);
12 xlsx.write(i, 2, i * i);
13 xlsx.write(i, 3, i * i * i);
14 }
15 //![0]
16 //![1]
17 xlsx.defineName("MyCol_1", "=Sheet1!$A$1:$A$10");
18 xlsx.defineName("MyCol_2", "=Sheet1!$B$1:$B$10", "This is comments");
19 xlsx.defineName("MyCol_3", "=Sheet1!$C$1:$C$10", "", "Sheet1");
20 xlsx.defineName("Factor", "=0.5");
21 //![1]
22 //![2]
23 xlsx.write(11, 1, "=SUM(MyCol_1)");
24 xlsx.write(11, 2, "=SUM(MyCol_2)");
25 xlsx.write(11, 3, "=SUM(MyCol_3)");
26 xlsx.write(12, 1, "=SUM(MyCol_1)*Factor");
27 xlsx.write(12, 2, "=SUM(MyCol_2)*Factor");
28 xlsx.write(12, 3, "=SUM(MyCol_3)*Factor");
29 //![2]
30
31 xlsx.saveAs("Book1.xlsx");
32 return 0;
33 }
4.7 demo Qt操作Excel 示例
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxformat.h"
4 #include "xlsxcellrange.h"
5 #include "xlsxworksheet.h"
6
7 QTXLSX_USE_NAMESPACE
8
9 void writeHorizontalAlignCell(Document &xlsx, const QString &cell, const QString &text,
10 Format::HorizontalAlignment align)
11 {
12 Format format;
13 format.setHorizontalAlignment(align);
14 format.setBorderStyle(Format::BorderThin);
15 xlsx.write(cell, text, format);
16 }
17
18 void writeVerticalAlignCell(Document &xlsx, const QString &range, const QString &text,
19 Format::VerticalAlignment align)
20 {
21 Format format;
22 format.setVerticalAlignment(align);
23 format.setBorderStyle(Format::BorderThin);
24 CellRange r(range);
25 xlsx.write(r.firstRow(), r.firstColumn(), text);
26 xlsx.mergeCells(r, format);
27 }
28
29 void writeBorderStyleCell(Document &xlsx, const QString &cell, const QString &text,
30 Format::BorderStyle bs)
31 {
32 Format format;
33 format.setBorderStyle(bs);
34 xlsx.write(cell, text, format);
35 }
36
37 void writeSolidFillCell(Document &xlsx, const QString &cell, const QColor &color)
38 {
39 Format format;
40 format.setPatternBackgroundColor(color);
41 xlsx.write(cell, QVariant(), format);
42 }
43
44 void writePatternFillCell(Document &xlsx, const QString &cell, Format::FillPattern pattern,
45 const QColor &color)
46 {
47 Format format;
48 format.setPatternForegroundColor(color);
49 format.setFillPattern(pattern);
50 xlsx.write(cell, QVariant(), format);
51 }
52
53 void writeBorderAndFontColorCell(Document &xlsx, const QString &cell, const QString &text,
54 const QColor &color)
55 {
56 Format format;
57 format.setBorderStyle(Format::BorderThin);
58 format.setBorderColor(color);
59 format.setFontColor(color);
60 xlsx.write(cell, text, format);
61 }
62
63 void writeFontNameCell(Document &xlsx, const QString &cell, const QString &text)
64 {
65 Format format;
66 format.setFontName(text);
67 format.setFontSize(16);
68 xlsx.write(cell, text, format);
69 }
70
71 void writeFontSizeCell(Document &xlsx, const QString &cell, int size)
72 {
73 Format format;
74 format.setFontSize(size);
75 xlsx.write(cell, "Qt Xlsx", format);
76 }
77
78 void writeInternalNumFormatsCell(Document &xlsx, int row, double value, int numFmt)
79 {
80 Format format;
81 format.setNumberFormatIndex(numFmt);
82 xlsx.write(row, 1, value);
83 xlsx.write(row, 2, QString("Builtin NumFmt %1").arg(numFmt));
84 xlsx.write(row, 3, value, format);
85 }
86
87 void writeCustomNumFormatsCell(Document &xlsx, int row, double value, const QString &numFmt)
88 {
89 Format format;
90 format.setNumberFormat(numFmt);
91 xlsx.write(row, 1, value);
92 xlsx.write(row, 2, numFmt);
93 xlsx.write(row, 3, value, format);
94 }
95
96 int main()
97 {
98 Document xlsx;
99
100 //---------------------------------------------------------------
101 // Create the first sheet (Otherwise, default "Sheet1" will be created)
102 xlsx.addSheet("Aligns & Borders");
103 xlsx.setColumnWidth(2, 20); // Column B
104 xlsx.setColumnWidth(8, 12); // Column H
105 xlsx.currentWorksheet()->setGridLinesVisible(false);
106
107 // Alignment
108 writeHorizontalAlignCell(xlsx, "B3", "AlignLeft", Format::AlignLeft);
109 writeHorizontalAlignCell(xlsx, "B5", "AlignHCenter", Format::AlignHCenter);
110 writeHorizontalAlignCell(xlsx, "B7", "AlignRight", Format::AlignRight);
111 writeVerticalAlignCell(xlsx, "D3:D7", "AlignTop", Format::AlignTop);
112 writeVerticalAlignCell(xlsx, "F3:F7", "AlignVCenter", Format::AlignVCenter);
113 writeVerticalAlignCell(xlsx, "H3:H7", "AlignBottom", Format::AlignBottom);
114
115 // Border
116 writeBorderStyleCell(xlsx, "B13", "BorderMedium", Format::BorderMedium);
117 writeBorderStyleCell(xlsx, "B15", "BorderDashed", Format::BorderDashed);
118 writeBorderStyleCell(xlsx, "B17", "BorderDotted", Format::BorderDotted);
119 writeBorderStyleCell(xlsx, "B19", "BorderThick", Format::BorderThick);
120 writeBorderStyleCell(xlsx, "B21", "BorderDouble", Format::BorderDouble);
121 writeBorderStyleCell(xlsx, "B23", "BorderDashDot", Format::BorderDashDot);
122
123 // Fill
124 writeSolidFillCell(xlsx, "D13", Qt::red);
125 writeSolidFillCell(xlsx, "D15", Qt::blue);
126 writeSolidFillCell(xlsx, "D17", Qt::yellow);
127 writeSolidFillCell(xlsx, "D19", Qt::magenta);
128 writeSolidFillCell(xlsx, "D21", Qt::green);
129 writeSolidFillCell(xlsx, "D23", Qt::gray);
130 writePatternFillCell(xlsx, "F13", Format::PatternMediumGray, Qt::red);
131 writePatternFillCell(xlsx, "F15", Format::PatternDarkHorizontal, Qt::blue);
132 writePatternFillCell(xlsx, "F17", Format::PatternDarkVertical, Qt::yellow);
133 writePatternFillCell(xlsx, "F19", Format::PatternDarkDown, Qt::magenta);
134 writePatternFillCell(xlsx, "F21", Format::PatternLightVertical, Qt::green);
135 writePatternFillCell(xlsx, "F23", Format::PatternLightTrellis, Qt::gray);
136
137 writeBorderAndFontColorCell(xlsx, "H13", "Qt::red", Qt::red);
138 writeBorderAndFontColorCell(xlsx, "H15", "Qt::blue", Qt::blue);
139 writeBorderAndFontColorCell(xlsx, "H17", "Qt::yellow", Qt::yellow);
140 writeBorderAndFontColorCell(xlsx, "H19", "Qt::magenta", Qt::magenta);
141 writeBorderAndFontColorCell(xlsx, "H21", "Qt::green", Qt::green);
142 writeBorderAndFontColorCell(xlsx, "H23", "Qt::gray", Qt::gray);
143
144 //---------------------------------------------------------------
145 // Create the second sheet.
146 xlsx.addSheet("Fonts");
147
148 xlsx.write("B3", "Normal");
149 Format font_bold;
150 font_bold.setFontBold(true);
151 xlsx.write("B4", "Bold", font_bold);
152 Format font_italic;
153 font_italic.setFontItalic(true);
154 xlsx.write("B5", "Italic", font_italic);
155 Format font_underline;
156 font_underline.setFontUnderline(Format::FontUnderlineSingle);
157 xlsx.write("B6", "Underline", font_underline);
158 Format font_strikeout;
159 font_strikeout.setFontStrikeOut(true);
160 xlsx.write("B7", "StrikeOut", font_strikeout);
161
162 writeFontNameCell(xlsx, "D3", "Arial");
163 writeFontNameCell(xlsx, "D4", "Arial Black");
164 writeFontNameCell(xlsx, "D5", "Comic Sans MS");
165 writeFontNameCell(xlsx, "D6", "Courier New");
166 writeFontNameCell(xlsx, "D7", "Impact");
167 writeFontNameCell(xlsx, "D8", "Times New Roman");
168 writeFontNameCell(xlsx, "D9", "Verdana");
169
170 writeFontSizeCell(xlsx, "G3", 10);
171 writeFontSizeCell(xlsx, "G4", 12);
172 writeFontSizeCell(xlsx, "G5", 14);
173 writeFontSizeCell(xlsx, "G6", 16);
174 writeFontSizeCell(xlsx, "G7", 18);
175 writeFontSizeCell(xlsx, "G8", 20);
176 writeFontSizeCell(xlsx, "G9", 25);
177
178 Format font_vertical;
179 font_vertical.setRotation(255);
180 font_vertical.setFontSize(16);
181 xlsx.write("J3", "vertical", font_vertical);
182 xlsx.mergeCells("J3:J9");
183
184 //---------------------------------------------------------------
185 // Create the third sheet.
186 xlsx.addSheet("Formulas");
187 xlsx.setColumnWidth(1, 2, 40);
188 Format rAlign;
189 rAlign.setHorizontalAlignment(Format::AlignRight);
190 Format lAlign;
191 lAlign.setHorizontalAlignment(Format::AlignLeft);
192 xlsx.write("B3", 40, lAlign);
193 xlsx.write("B4", 30, lAlign);
194 xlsx.write("B5", 50, lAlign);
195 xlsx.write("A7", "SUM(B3:B5)=", rAlign);
196 xlsx.write("B7", "=SUM(B3:B5)", lAlign);
197 xlsx.write("A8", "AVERAGE(B3:B5)=", rAlign);
198 xlsx.write("B8", "=AVERAGE(B3:B5)", lAlign);
199 xlsx.write("A9", "MAX(B3:B5)=", rAlign);
200 xlsx.write("B9", "=MAX(B3:B5)", lAlign);
201 xlsx.write("A10", "MIN(B3:B5)=", rAlign);
202 xlsx.write("B10", "=MIN(B3:B5)", lAlign);
203 xlsx.write("A11", "COUNT(B3:B5)=", rAlign);
204 xlsx.write("B11", "=COUNT(B3:B5)", lAlign);
205
206 xlsx.write("A13", "IF(B7>100,\"large\",\"small\")=", rAlign);
207 xlsx.write("B13", "=IF(B7>100,\"large\",\"small\")", lAlign);
208
209 xlsx.write("A15", "SQRT(25)=", rAlign);
210 xlsx.write("B15", "=SQRT(25)", lAlign);
211 xlsx.write("A16", "RAND()=", rAlign);
212 xlsx.write("B16", "=RAND()", lAlign);
213 xlsx.write("A17", "2*PI()=", rAlign);
214 xlsx.write("B17", "=2*PI()", lAlign);
215
216 xlsx.write("A19", "UPPER(\"qtxlsx\")=", rAlign);
217 xlsx.write("B19", "=UPPER(\"qtxlsx\")", lAlign);
218 xlsx.write("A20", "LEFT(\"ubuntu\",3)=", rAlign);
219 xlsx.write("B20", "=LEFT(\"ubuntu\",3)", lAlign);
220 xlsx.write("A21", "LEN(\"Hello Qt!\")=", rAlign);
221 xlsx.write("B21", "=LEN(\"Hello Qt!\")", lAlign);
222
223 Format dateFormat;
224 dateFormat.setHorizontalAlignment(Format::AlignLeft);
225 dateFormat.setNumberFormat("yyyy-mm-dd");
226 xlsx.write("A23", "DATE(2013,8,13)=", rAlign);
227 xlsx.write("B23", "=DATE(2013,8,13)", dateFormat);
228 xlsx.write("A24", "DAY(B23)=", rAlign);
229 xlsx.write("B24", "=DAY(B23)", lAlign);
230 xlsx.write("A25", "MONTH(B23)=", rAlign);
231 xlsx.write("B25", "=MONTH(B23)", lAlign);
232 xlsx.write("A26", "YEAR(B23)=", rAlign);
233 xlsx.write("B26", "=YEAR(B23)", lAlign);
234 xlsx.write("A27", "DAYS360(B23,TODAY())=", rAlign);
235 xlsx.write("B27", "=DAYS360(B23,TODAY())", lAlign);
236
237 xlsx.write("A29", "B3+100*(2-COS(0)))=", rAlign);
238 xlsx.write("B29", "=B3+100*(2-COS(0))", lAlign);
239 xlsx.write("A30", "ISNUMBER(B29)=", rAlign);
240 xlsx.write("B30", "=ISNUMBER(B29)", lAlign);
241 xlsx.write("A31", "AND(1,0)=", rAlign);
242 xlsx.write("B31", "=AND(1,0)", lAlign);
243
244 xlsx.write("A33", "HYPERLINK(\"http://qt-project.org\")=", rAlign);
245 xlsx.write("B33", "=HYPERLINK(\"http://qt-project.org\")", lAlign);
246
247 //---------------------------------------------------------------
248 // Create the fourth sheet.
249 xlsx.addSheet("NumFormats");
250 xlsx.setColumnWidth(2, 40);
251 writeInternalNumFormatsCell(xlsx, 4, 2.5681, 2);
252 writeInternalNumFormatsCell(xlsx, 5, 2500000, 3);
253 writeInternalNumFormatsCell(xlsx, 6, -500, 5);
254 writeInternalNumFormatsCell(xlsx, 7, -0.25, 9);
255 writeInternalNumFormatsCell(xlsx, 8, 890, 11);
256 writeInternalNumFormatsCell(xlsx, 9, 0.75, 12);
257 writeInternalNumFormatsCell(xlsx, 10, 41499, 14);
258 writeInternalNumFormatsCell(xlsx, 11, 41499, 17);
259
260 writeCustomNumFormatsCell(xlsx, 13, 20.5627, "#.###");
261 writeCustomNumFormatsCell(xlsx, 14, 4.8, "#.00");
262 writeCustomNumFormatsCell(xlsx, 15, 1.23, "0.00 \"RMB\"");
263 writeCustomNumFormatsCell(xlsx, 16, 60, "[Red][<=100];[Green][>100]");
264
265 //---------------------------------------------------------------
266 // Create the fifth sheet.
267 xlsx.addSheet("Merging");
268 Format centerAlign;
269 centerAlign.setHorizontalAlignment(Format::AlignHCenter);
270 centerAlign.setVerticalAlignment(Format::AlignVCenter);
271 xlsx.write("B4", "Hello Qt!");
272 xlsx.mergeCells("B4:F6", centerAlign);
273 xlsx.write("B8", 1);
274 xlsx.mergeCells("B8:C21", centerAlign);
275 xlsx.write("E8", 2);
276 xlsx.mergeCells("E8:F21", centerAlign);
277
278 //---------------------------------------------------------------
279 // Create the fifth sheet.
280 xlsx.addSheet("Grouping");
281 qsrand(QDateTime::currentMSecsSinceEpoch());
282 for (int row = 2; row < 31; ++row) {
283 for (int col = 1; col <= 10; ++col)
284 xlsx.write(row, col, qrand() % 100);
285 }
286 xlsx.groupRows(4, 7);
287 xlsx.groupRows(11, 26, false);
288 xlsx.groupRows(15, 17, false);
289 xlsx.groupRows(20, 22, false);
290 xlsx.setColumnWidth(1, 10, 10.0);
291 xlsx.groupColumns(1, 2);
292 xlsx.groupColumns(5, 8, false);
293
294 xlsx.saveAs("Book1.xlsx");
295
296 // Make sure that read/write works well.
297 Document xlsx2("Book1.xlsx");
298 xlsx2.saveAs("Book2.xlsx");
299
300 return 0;
301 }
4.8 documentproperty Qt操作Excel 文档属性
1 #include <QtCore>
2 #include "xlsxdocument.h"
3
4 int main()
5 {
6 QXlsx::Document xlsx;
7 xlsx.write("A1", "View the properties through:");
8 xlsx.write("A2", "Office Button -> Prepare -> Properties option in Excel");
9
10 xlsx.setDocumentProperty("title", "This is an example spreadsheet");
11 xlsx.setDocumentProperty("subject", "With document properties");
12 xlsx.setDocumentProperty("creator", "Debao Zhang");
13 xlsx.setDocumentProperty("company", "HMICN");
14 xlsx.setDocumentProperty("category", "Example spreadsheets");
15 xlsx.setDocumentProperty("keywords", "Sample, Example, Properties");
16 xlsx.setDocumentProperty("description", "Created with Qt Xlsx");
17
18 xlsx.saveAs("Test.xlsx");
19 return 0;
20 }
4.9 extractdata Qt操作Excel 提取数据
1 #include <QtCore>
2 #include "xlsxdocument.h"
3
4 int main()
5 {
6 {
7 // Create a new .xlsx file.
8 QXlsx::Document xlsx;
9 xlsx.write("A1", "Hello Qt!");
10 xlsx.write("A2", 12345);
11 xlsx.write("A3", "=44+33");
12 xlsx.write("A4", true);
13 xlsx.write("A5", "http://qt-project.org");
14 xlsx.write("A6", QDate(2013, 12, 27));
15 xlsx.write("A7", QTime(6, 30));
16 xlsx.saveAs("Book1.xlsx");
17 }
18
19 //![0]
20 QXlsx::Document xlsx("Book1.xlsx");
21 //![0]
22
23 //![1]
24 qDebug() << xlsx.read("A1");
25 qDebug() << xlsx.read("A2");
26 qDebug() << xlsx.read("A3");
27 qDebug() << xlsx.read("A4");
28 qDebug() << xlsx.read("A5");
29 qDebug() << xlsx.read("A6");
30 qDebug() << xlsx.read("A7");
31 //![1]
32
33 //![2]
34 for (int row = 1; row < 10; ++row) {
35 if (QXlsx::Cell *cell = xlsx.cellAt(row, 1))
36 qDebug() << cell->value();
37 }
38 //![2]
39
40 return 0;
41 }
4.10 formulas Qt操作Excel 公式
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxformat.h"
4 #include "xlsxworksheet.h"
5 #include "xlsxcellformula.h"
6
7 QTXLSX_USE_NAMESPACE
8
9 int main()
10 {
11 //![0]
12 Document xlsx;
13 //![0]
14
15 //![1]
16 xlsx.setColumnWidth(1, 2, 40);
17 Format rAlign;
18 rAlign.setHorizontalAlignment(Format::AlignRight);
19 Format lAlign;
20 lAlign.setHorizontalAlignment(Format::AlignLeft);
21 xlsx.write("B3", 40, lAlign);
22 xlsx.write("B4", 30, lAlign);
23 xlsx.write("B5", 50, lAlign);
24 xlsx.write("A7", "SUM(B3:B5)=", rAlign);
25 xlsx.write("B7", "=SUM(B3:B5)", lAlign);
26 xlsx.write("A8", "AVERAGE(B3:B5)=", rAlign);
27 xlsx.write("B8", "=AVERAGE(B3:B5)", lAlign);
28 xlsx.write("A9", "MAX(B3:B5)=", rAlign);
29 xlsx.write("B9", "=MAX(B3:B5)", lAlign);
30 xlsx.write("A10", "MIN(B3:B5)=", rAlign);
31 xlsx.write("B10", "=MIN(B3:B5)", lAlign);
32 xlsx.write("A11", "COUNT(B3:B5)=", rAlign);
33 xlsx.write("B11", "=COUNT(B3:B5)", lAlign);
34
35 xlsx.write("A13", "IF(B7>100,\"large\",\"small\")=", rAlign);
36 xlsx.write("B13", "=IF(B7>100,\"large\",\"small\")", lAlign);
37
38 xlsx.write("A15", "SQRT(25)=", rAlign);
39 xlsx.write("B15", "=SQRT(25)", lAlign);
40 xlsx.write("A16", "RAND()=", rAlign);
41 xlsx.write("B16", "=RAND()", lAlign);
42 xlsx.write("A17", "2*PI()=", rAlign);
43 xlsx.write("B17", "=2*PI()", lAlign);
44
45 xlsx.write("A19", "UPPER(\"qtxlsx\")=", rAlign);
46 xlsx.write("B19", "=UPPER(\"qtxlsx\")", lAlign);
47 xlsx.write("A20", "LEFT(\"ubuntu\",3)=", rAlign);
48 xlsx.write("B20", "=LEFT(\"ubuntu\",3)", lAlign);
49 xlsx.write("A21", "LEN(\"Hello Qt!\")=", rAlign);
50 xlsx.write("B21", "=LEN(\"Hello Qt!\")", lAlign);
51 //![1]
52
53 //![2]
54 xlsx.addSheet("ArrayFormula");
55 Worksheet *sheet = xlsx.currentWorksheet();
56
57 for (int row = 2; row < 20; ++row) {
58 sheet->write(row, 2, row * 2); // B2:B19
59 sheet->write(row, 3, row * 3); // C2:C19
60 }
61 sheet->writeFormula("D2", CellFormula("B2:B19+C2:C19", "D2:D19", CellFormula::ArrayType));
62 sheet->writeFormula("E2",
63 CellFormula("=CONCATENATE(\"The total is \",D2:D19,\" units\")", "E2:E19",
64 CellFormula::ArrayType));
65 //![2]
66
67 //![21]
68 xlsx.addSheet("SharedFormula");
69 sheet = xlsx.currentWorksheet();
70
71 for (int row = 2; row < 20; ++row) {
72 sheet->write(row, 2, row * 2); // B2:B19
73 sheet->write(row, 3, row * 3); // C2:C19
74 }
75 sheet->writeFormula("D2", CellFormula("=B2+C2", "D2:D19", CellFormula::SharedType));
76 sheet->writeFormula("E2",
77 CellFormula("=CONCATENATE(\"The total is \",D2,\" units\")", "E2:E19",
78 CellFormula::SharedType));
79
80 //![21]
81
82 //![3]
83 xlsx.save();
84 //![3]
85
86 // Make sure that read/write works well.
87 Document xlsx2("Book1.xlsx");
88 Worksheet *sharedFormulaSheet = dynamic_cast<Worksheet *>(xlsx2.sheet("SharedFormula"));
89 for (int row = 2; row < 20; ++row) {
90 qDebug() << sharedFormulaSheet->read(row, 4);
91 }
92
93 xlsx2.saveAs("Book2.xlsx");
94
95 return 0;
96 }
4.11 hello Qt操作Excel 问候
1 #include <QtCore>
2 #include "xlsxdocument.h"
3
4 int main()
5 {
6 //![0]
7 QXlsx::Document xlsx;
8 //![0]
9
10 //![1]
11 xlsx.write("A1", "Hello Qt!");
12 xlsx.write("A2", 12345);
13 xlsx.write("A3", "=44+33");
14 xlsx.write("A4", true);
15 xlsx.write("A5", "http://qt-project.org");
16 xlsx.write("A6", QDate(2013, 12, 27));
17 xlsx.write("A7", QTime(6, 30));
18 //![1]
19
20 //![2]
21 xlsx.save();
22 //![2]
23
24 return 0;
25 }
4.12 hyperlinks Qt操作Excel 超链接
1 #include <QtCore>
2 #include "xlsxdocument.h"
3
4 int main()
5 {
6 //![0]
7 QXlsx::Document xlsx;
8 //![0]
9
10 //![1]
11 xlsx.write("A1", "http://qt-project.org");
12 xlsx.write("A2", "http://qt-project.org/wiki#0f68b904e33d9ac04605aecc958bcf52");
13 xlsx.write("A3", "mailto:info@qt-project.org");
14 xlsx.write("A4", "file:///C:/User/test/abc.txt");
15 //![1]
16
17 //![2]
18 xlsx.save();
19 //![2]
20
21 QXlsx::Document xlsx2("Book1.xlsx");
22 xlsx2.saveAs("Book2.xlsx");
23
24 return 0;
25 }
4.13 image Qt操作Excel 图像
1 #include <QtGui>
2 #include "xlsxdocument.h"
3
4 int main(int argc, char **argv)
5 {
6 QGuiApplication(argc, argv);
7
8 QXlsx::Document xlsx;
9 QImage image(40, 30, QImage::Format_RGB32);
10 image.fill(Qt::green);
11 for (int i = 0; i < 10; ++i)
12 xlsx.insertImage(10 * i, 5, image);
13 xlsx.saveAs("Book1.xlsx");
14
15 QXlsx::Document xlsx2("Book1.xlsx");
16 xlsx2.saveAs("Book2.xlsx");
17
18 return 0;
19 }
4.14 mergecells Qt操作Excel 合并单元格
1 #include "xlsxdocument.h"
2 #include "xlsxformat.h"
3
4 QTXLSX_USE_NAMESPACE
5
6 int main()
7 {
8 Document xlsx;
9 //![0]
10 Format format;
11 format.setHorizontalAlignment(Format::AlignHCenter);
12 format.setVerticalAlignment(Format::AlignVCenter);
13 //![0]
14 //![1]
15 xlsx.write("B4", "Hello Qt!");
16 xlsx.mergeCells("B4:F6", format);
17 xlsx.write("B8", 1);
18 xlsx.mergeCells("B8:C21", format);
19 xlsx.write("E8", 2);
20 xlsx.mergeCells("E8:F21", format);
21 //![1]
22 xlsx.save();
23
24 return 0;
25 }
4.15 numberformat Qt操作Excel 数值格式
1 #include <QtGui>
2 #include "xlsxdocument.h"
3 #include "xlsxformat.h"
4
5 int main(int argc, char **argv)
6 {
7 QGuiApplication(argc, argv);
8
9 QXlsx::Document xlsx;
10 xlsx.setColumnWidth(1, 4, 20.0);
11
12 QXlsx::Format header;
13 header.setFontBold(true);
14 header.setFontSize(20);
15
16 // Custom number formats
17 QStringList numFormats;
18 numFormats << "Qt #"
19 << "yyyy-mmm-dd"
20 << "$ #,##0.00"
21 << "[red]0.00";
22 xlsx.write(1, 1, "Raw data", header);
23 xlsx.write(1, 2, "Format", header);
24 xlsx.write(1, 3, "Shown value", header);
25 for (int i = 0; i < numFormats.size(); ++i) {
26 int row = i + 2;
27 xlsx.write(row, 1, 100.0);
28 xlsx.write(row, 2, numFormats[i]);
29 QXlsx::Format format;
30 format.setNumberFormat(numFormats[i]);
31 xlsx.write(row, 3, 100.0, format);
32 }
33
34 // Builtin number formats
35 xlsx.addSheet();
36 xlsx.setColumnWidth(1, 4, 20.0);
37 xlsx.write(1, 1, "Raw data", header);
38 xlsx.write(1, 2, "Builtin Format", header);
39 xlsx.write(1, 3, "Shown value", header);
40 for (int i = 0; i < 50; ++i) {
41 int row = i + 2;
42 int numFmt = i;
43 xlsx.write(row, 1, 100.0);
44 xlsx.write(row, 2, numFmt);
45 QXlsx::Format format;
46 format.setNumberFormatIndex(numFmt);
47 xlsx.write(row, 3, 100.0, format);
48 }
49
50 xlsx.save();
51 return 0;
52 }
4.16 richtext Qt操作Excel 富文本
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxrichstring.h"
4 #include "xlsxworkbook.h"
5 #include "xlsxformat.h"
6
7 int main()
8 {
9 //![0]
10 QXlsx::Document xlsx;
11 //![0]
12
13 //![1]
14 QXlsx::Format blue;
15 blue.setFontColor(Qt::blue);
16 QXlsx::Format red;
17 red.setFontColor(Qt::red);
18 red.setFontSize(15);
19 QXlsx::Format bold;
20 bold.setFontBold(true);
21
22 QXlsx::RichString rich;
23 rich.addFragment("Hello ", blue);
24 rich.addFragment("Qt ", red);
25 rich.addFragment("Xlsx", bold);
26 xlsx.write("B2", rich);
27
28 xlsx.workbook()->setHtmlToRichStringEnabled(true);
29 xlsx.write("B4", "<b>Hello</b> <font color=\"red\">Qt</font> <i>Xlsx</i>");
30
31 xlsx.write("B6", "<font color=\"red\"><b><u><i>Qt Xlsx</i></u></b></font>");
32
33 //![1]
34
35 //![2]
36 xlsx.saveAs("Test1.xlsx");
37 //![2]
38
39 QXlsx::Document("Test1.xlsx");
40 xlsx.saveAs("Test2.xlsx");
41
42 return 0;
43 }
4.17 rowcolumn Qt操作Excel 行列
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxformat.h"
4
5 int main()
6 {
7 QXlsx::Document xlsx;
8 xlsx.write(1, 2, "Row:0, Col:2 ==> (C1)");
9
10 // Set the height of the first row to 50.0(points)
11 xlsx.setRowHeight(1, 50.0);
12
13 // Set the width of the third column to 40.0(chars)
14 xlsx.setColumnWidth(3, 3, 40.0);
15
16 // Set style for the row 11th.
17 QXlsx::Format format1;
18 format1.setFontBold(true);
19 format1.setFontColor(QColor(Qt::blue));
20 format1.setFontSize(20);
21 xlsx.write(11, 1, "Hello Row Style");
22 xlsx.write(11, 6, "Blue Color");
23 xlsx.setRowFormat(11, format1);
24 xlsx.setRowHeight(11, 41);
25
26 // Set style for the col [9th, 16th)
27 QXlsx::Format format2;
28 format2.setFontBold(true);
29 format2.setFontColor(QColor(Qt::magenta));
30 for (int row = 12; row <= 30; row++)
31 for (int col = 9; col <= 15; col++)
32 xlsx.write(row, col, row + col);
33 xlsx.setColumnWidth(9, 16, 5.0);
34 xlsx.setColumnFormat(9, 16, format2);
35
36 xlsx.save();
37 return 0;
38 }
4.18 style Qt操作Excel 设置样式
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxformat.h"
4
5 int main()
6 {
7 QXlsx::Document xlsx;
8 QXlsx::Format format1;
9 format1.setFontColor(QColor(Qt::red));
10 format1.setFontSize(15);
11 format1.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
12 format1.setBorderStyle(QXlsx::Format::BorderDashDotDot);
13 xlsx.write("A1", "Hello Qt!", format1);
14 xlsx.write("B3", 12345, format1);
15
16 QXlsx::Format format2;
17 format2.setFontBold(true);
18 format2.setFontUnderline(QXlsx::Format::FontUnderlineDouble);
19 format2.setFillPattern(QXlsx::Format::PatternLightUp);
20 xlsx.write("C5", "=44+33", format2);
21 xlsx.write("D7", true, format2);
22
23 QXlsx::Format format3;
24 format3.setFontBold(true);
25 format3.setFontColor(QColor(Qt::blue));
26 format3.setFontSize(20);
27 xlsx.write(11, 1, "Hello Row Style");
28 xlsx.write(11, 6, "Blue Color");
29 xlsx.setRowFormat(11, 41, format3);
30
31 QXlsx::Format format4;
32 format4.setFontBold(true);
33 format4.setFontColor(QColor(Qt::magenta));
34 for (int row = 21; row <= 40; row++)
35 for (int col = 9; col < 16; col++)
36 xlsx.write(row, col, row + col);
37 xlsx.setColumnFormat(9, 16, format4);
38
39 xlsx.write("A5", QDate(2013, 8, 29));
40
41 QXlsx::Format format6;
42 format6.setPatternBackgroundColor(QColor(Qt::green));
43 xlsx.write("A6", "Background color: green", format6);
44
45 xlsx.saveAs("book1.xlsx");
46
47 return 0;
48 }
4.19 worksheetoperations 工作表的操作
1 #include <QtCore>
2 #include "xlsxdocument.h"
3 #include "xlsxabstractsheet.h"
4
5 QTXLSX_USE_NAMESPACE
6
7 int main()
8 {
9 //![Create a xlsx file]
10 Document xlsx;
11
12 for (int i = 1; i < 20; ++i) {
13 for (int j = 1; j < 15; ++j)
14 xlsx.write(i, j, QString("R %1 C %2").arg(i).arg(j));
15 }
16 xlsx.addSheet();
17 xlsx.write(2, 2, "Hello Qt Xlsx");
18 xlsx.addSheet();
19 xlsx.write(3, 3, "This will be deleted...");
20
21 xlsx.addSheet("HiddenSheet");
22 xlsx.currentSheet()->setHidden(true);
23 xlsx.write("A1", "This sheet is hidden.");
24
25 xlsx.addSheet("VeryHiddenSheet");
26 xlsx.sheet("VeryHiddenSheet")->setSheetState(AbstractSheet::SS_VeryHidden);
27 xlsx.write("A1", "This sheet is very hidden.");
28
29 xlsx.save(); // Default name is "Book1.xlsx"
30 //![Create a xlsx file]
31
32 Document xlsx2("Book1.xlsx");
33 //![add_copy_move_delete]
34 xlsx2.renameSheet("Sheet1", "TheFirstSheet");
35
36 xlsx2.copySheet("TheFirstSheet", "CopyOfTheFirst");
37
38 xlsx2.selectSheet("CopyOfTheFirst");
39 xlsx2.write(25, 2, "On the Copy Sheet");
40
41 xlsx2.deleteSheet("Sheet3");
42
43 xlsx2.moveSheet("Sheet2", 0);
44 //![add_copy_move_delete]
45
46 //![show_hidden_sheets]
47 xlsx2.sheet("HiddenSheet")->setVisible(true);
48 xlsx2.sheet("VeryHiddenSheet")->setVisible(true);
49 //![show_hidden_sheets]
50
51 xlsx2.saveAs("Book2.xlsx");
52 return 0;
53 }
