|
文章目录
1.Qt Xlsx库简介2. 用法①：使用Xlsx作为Qt5的附加模块2.1 构建模块2.2 下载QtXlsx源代码2.3 为编译器安装Xlsx模块2.3.1 打开 MinGW 7.3.0 32-bit 控制台2.3.2 找到QtXlsx源码路径2.3.3 再MinGW32控制台进入路径2.3.4 执行qmake2.3.5 执行makefile2.3.6 为编译器安装QtXlsx2.3.7 安装完成2.3.8 同样的方法为MinGw 64安装模块
2.4 使用QtXlsx模块2.4.1 创建工程2.4.2 打开QtXlsx示例文件2.4.3 复制hello工程内容2.4.4 替换原工程的main.cpp2.4.5 在.pro文件中引入xlsx模块2.4.6 运行查看效果
3.用法②：直接使用源代码3.1 新建工程3.2 工程目录下添加源代码3.3 在工程中引入xlsx源代码3.4 修改main.cpp内容，测试3.5 查看效果
4. 官方examples例子4.1 calendar Qt操作Excel生成日历4.2 chart Qt操作Excel生成图表4.3 chartsheet Qt操作Excel生成图表4.4 conditionalformatting Qt操作Excel 条件格式化4.5 datavalidation Qt操作Excel 数据有效性4.6 definename Qt操作Excel 定义名字4.7 demo Qt操作Excel 示例4.8 documentproperty Qt操作Excel 文档属性4.9 extractdata Qt操作Excel 提取数据4.10 formulas Qt操作Excel 公式4.11 hello Qt操作Excel 问候4.12 hyperlinks Qt操作Excel 超链接4.13 image Qt操作Excel 图像4.14 mergecells Qt操作Excel 合并单元格4.15 numberformat Qt操作Excel 数值格式4.16 richtext Qt操作Excel 富文本4.17 rowcolumn Qt操作Excel 行列4.18 style Qt操作Excel 设置样式4.19 worksheetoperations 工作表的操作
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，参考我另一篇博客：
perl安装教程_超级大洋葱的博客-CSDN博客
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)：
qmake src.pro
make
make install
接下来进行以下操作(mingw32、mingw64)：
qmake src.pro
mingw32-make
mingw32-make install
2.3.4 执行qmake
执行qmake，会自动生成makefile文件
2.3.5 执行makefile
这里会编译很久，喝杯茶休息休息：
编译完成：
2.3.6 为编译器安装QtXlsx
执行以下命令：
2.3.7 安装完成
H:\0.Download\Edge\QtXlsxWriter-master\src>mingw32-make install
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
mingw32-make[1]: Entering directory 'H:/0.Download/Edge/QtXlsxWriter-master/src/xlsx'
mingw32-make -f Makefile.Release install
mingw32-make[2]: Entering directory 'H:/0.Download/Edge/QtXlsxWriter-master/src/xlsx'
copy /y ..\..\lib\Qt5Xlsx.dll E:\Qt5.14.2\5.14.2\mingw73_32\bin\Qt5Xlsx.dll
已复制 1 个文件。
....此处省略一万字
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
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
mingw32-make[2]: Leaving directory 'H:/0.Download/Edge/QtXlsxWriter-master/src/xlsx'
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工程内容
#include
#include "xlsxdocument.h"
int main()
{
//![0]
QXlsx::Document xlsx;
//![0]
//![1]
xlsx.write("A1", "Hello Qt!");
xlsx.write("A2", 12345);
xlsx.write("A3", "=44+33");
xlsx.write("A4", true);
xlsx.write("A5", "http://qt-project.org");
xlsx.write("A6", QDate(2013, 12, 27));
xlsx.write("A7", QTime(6, 30));
//![1]
//![2]
xlsx.save();
//![2]
return 0;
}
2.4.4 替换原工程的main.cpp
看到有报错，是因为没有引入xlsx模块。
2.4.5 在.pro文件中引入xlsx模块
QT += xlsx
2.4.6 运行查看效果
生成了xlsx格式的文件
表格内容如下：
3.用法②：直接使用源代码
该包包含一个 qtxlsx.pri文件，允许您将组件集成到使用 qmake 进行生成步骤的应用程序中。
3.1 新建工程
3.2 工程目录下添加源代码
来到工程目录下：
打开网上下载的源码路径：
3.3 在工程中引入xlsx源代码
# 使用qtxlsx源代码
include(qtxlsx/src/xlsx/qtxlsx.pri)
3.4 修改main.cpp内容，测试
#include
#include "xlsxdocument.h"
int main()
{
//![0]
QXlsx::Document xlsx;
//![0]
//![1]
xlsx.write("A1", "Hello Qt!");
xlsx.write("A2", 12345);
xlsx.write("A3", "=44+33");
xlsx.write("A4", true);
xlsx.write("A5", "http://qt-project.org");
xlsx.write("A6", QDate(2013, 12, 27));
xlsx.write("A7", QTime(6, 30));
//![1]
//![2]
xlsx.save();
//![2]
return 0;
}
3.5 查看效果
内容一致：
4. 官方examples例子
4.1 calendar Qt操作Excel生成日历
#include
#include "xlsxdocument.h"
#include "xlsxformat.h"
#include "xlsxcellrange.h"
#include "xlsxworksheet.h"
QTXLSX_USE_NAMESPACE
int main(int argc, char **argv)
{
QCoreApplication app(argc, argv);
// Select a proper locale
// QLocale::setDefault(QLocale(QLocale::English));
Document xlsx;
QDate today(QDate::currentDate());
for (int month = 1; month
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
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 // Last day
for (int i = dow + 1; i
//![0]
Document xlsx;
for (int i = 1; i
//![0]
Document xlsx;
for (int i = 1; i chart();
barChart->setChartType(Chart::CT_Bar);
barChart->addSeries(CellRange("A1:A9"), xlsx.sheet("Sheet1"));
//![1]
//![2]
xlsx.saveAs("Book1.xlsx");
//![2]
Document xlsx2("Book1.xlsx");
xlsx2.saveAs("Book2.xlsx");
return 0;
}
4.4 conditionalformatting Qt操作Excel 条件格式化
#include
#include "xlsxdocument.h"
#include "xlsxconditionalformatting.h"
using namespace QXlsx;
int main()
{
//![0]
Document xlsx;
Format hFmt;
hFmt.setFontBold(true);
xlsx.write("B1", "(-inf,40)", hFmt);
xlsx.write("C1", "[30,70]", hFmt);
xlsx.write("D1", "startsWith 2", hFmt);
xlsx.write("E1", "dataBar", hFmt);
xlsx.write("F1", "colorScale", hFmt);
for (int row = 3; row
Document xlsx;
xlsx.write("A1", "A2 and A3:E5 only accept the number between 33 and 99");
//![1]
DataValidation validation(DataValidation::Whole, DataValidation::Between, "33", "99");
validation.addRange("A2");
validation.addRange("A3:E5");
validation.setPromptMessage("Please Input Integer between 33 and 99");
xlsx.addDataValidation(validation);
//![1]
xlsx.saveAs("Book1.xlsx");
return 0;
}
4.6 definename Qt操作Excel 定义名字
#include
#include "xlsxdocument.h"
QTXLSX_USE_NAMESPACE
int main()
{
//![0]
Document xlsx;
for (int i = 1; i
Format format;
format.setHorizontalAlignment(align);
format.setBorderStyle(Format::BorderThin);
xlsx.write(cell, text, format);
}
void writeVerticalAlignCell(Document &xlsx, const QString &range, const QString &text,
Format::VerticalAlignment align)
{
Format format;
format.setVerticalAlignment(align);
format.setBorderStyle(Format::BorderThin);
CellRange r(range);
xlsx.write(r.firstRow(), r.firstColumn(), text);
xlsx.mergeCells(r, format);
}
void writeBorderStyleCell(Document &xlsx, const QString &cell, const QString &text,
Format::BorderStyle bs)
{
Format format;
format.setBorderStyle(bs);
xlsx.write(cell, text, format);
}
void writeSolidFillCell(Document &xlsx, const QString &cell, const QColor &color)
{
Format format;
format.setPatternBackgroundColor(color);
xlsx.write(cell, QVariant(), format);
}
void writePatternFillCell(Document &xlsx, const QString &cell, Format::FillPattern pattern,
const QColor &color)
{
Format format;
format.setPatternForegroundColor(color);
format.setFillPattern(pattern);
xlsx.write(cell, QVariant(), format);
}
void writeBorderAndFontColorCell(Document &xlsx, const QString &cell, const QString &text,
const QColor &color)
{
Format format;
format.setBorderStyle(Format::BorderThin);
format.setBorderColor(color);
format.setFontColor(color);
xlsx.write(cell, text, format);
}
void writeFontNameCell(Document &xlsx, const QString &cell, const QString &text)
{
Format format;
format.setFontName(text);
format.setFontSize(16);
xlsx.write(cell, text, format);
}
void writeFontSizeCell(Document &xlsx, const QString &cell, int size)
{
Format format;
format.setFontSize(size);
xlsx.write(cell, "Qt Xlsx", format);
}
void writeInternalNumFormatsCell(Document &xlsx, int row, double value, int numFmt)
{
Format format;
format.setNumberFormatIndex(numFmt);
xlsx.write(row, 1, value);
xlsx.write(row, 2, QString("Builtin NumFmt %1").arg(numFmt));
xlsx.write(row, 3, value, format);
}
void writeCustomNumFormatsCell(Document &xlsx, int row, double value, const QString &numFmt)
{
Format format;
format.setNumberFormat(numFmt);
xlsx.write(row, 1, value);
xlsx.write(row, 2, numFmt);
xlsx.write(row, 3, value, format);
}
int main()
{
Document xlsx;
//---------------------------------------------------------------
// Create the first sheet (Otherwise, default "Sheet1" will be created)
xlsx.addSheet("Aligns & Borders");
xlsx.setColumnWidth(2, 20); // Column B
xlsx.setColumnWidth(8, 12); // Column H
xlsx.currentWorksheet()->setGridLinesVisible(false);
// Alignment
writeHorizontalAlignCell(xlsx, "B3", "AlignLeft", Format::AlignLeft);
writeHorizontalAlignCell(xlsx, "B5", "AlignHCenter", Format::AlignHCenter);
writeHorizontalAlignCell(xlsx, "B7", "AlignRight", Format::AlignRight);
writeVerticalAlignCell(xlsx, "D3:D7", "AlignTop", Format::AlignTop);
writeVerticalAlignCell(xlsx, "F3:F7", "AlignVCenter", Format::AlignVCenter);
writeVerticalAlignCell(xlsx, "H3:H7", "AlignBottom", Format::AlignBottom);
// Border
writeBorderStyleCell(xlsx, "B13", "BorderMedium", Format::BorderMedium);
writeBorderStyleCell(xlsx, "B15", "BorderDashed", Format::BorderDashed);
writeBorderStyleCell(xlsx, "B17", "BorderDotted", Format::BorderDotted);
writeBorderStyleCell(xlsx, "B19", "BorderThick", Format::BorderThick);
writeBorderStyleCell(xlsx, "B21", "BorderDouble", Format::BorderDouble);
writeBorderStyleCell(xlsx, "B23", "BorderDashDot", Format::BorderDashDot);
// Fill
writeSolidFillCell(xlsx, "D13", Qt::red);
writeSolidFillCell(xlsx, "D15", Qt::blue);
writeSolidFillCell(xlsx, "D17", Qt::yellow);
writeSolidFillCell(xlsx, "D19", Qt::magenta);
writeSolidFillCell(xlsx, "D21", Qt::green);
writeSolidFillCell(xlsx, "D23", Qt::gray);
writePatternFillCell(xlsx, "F13", Format::PatternMediumGray, Qt::red);
writePatternFillCell(xlsx, "F15", Format::PatternDarkHorizontal, Qt::blue);
writePatternFillCell(xlsx, "F17", Format::PatternDarkVertical, Qt::yellow);
writePatternFillCell(xlsx, "F19", Format::PatternDarkDown, Qt::magenta);
writePatternFillCell(xlsx, "F21", Format::PatternLightVertical, Qt::green);
writePatternFillCell(xlsx, "F23", Format::PatternLightTrellis, Qt::gray);
writeBorderAndFontColorCell(xlsx, "H13", "Qt::red", Qt::red);
writeBorderAndFontColorCell(xlsx, "H15", "Qt::blue", Qt::blue);
writeBorderAndFontColorCell(xlsx, "H17", "Qt::yellow", Qt::yellow);
writeBorderAndFontColorCell(xlsx, "H19", "Qt::magenta", Qt::magenta);
writeBorderAndFontColorCell(xlsx, "H21", "Qt::green", Qt::green);
writeBorderAndFontColorCell(xlsx, "H23", "Qt::gray", Qt::gray);
//---------------------------------------------------------------
// Create the second sheet.
xlsx.addSheet("Fonts");
xlsx.write("B3", "Normal");
Format font_bold;
font_bold.setFontBold(true);
xlsx.write("B4", "Bold", font_bold);
Format font_italic;
font_italic.setFontItalic(true);
xlsx.write("B5", "Italic", font_italic);
Format font_underline;
font_underline.setFontUnderline(Format::FontUnderlineSingle);
xlsx.write("B6", "Underline", font_underline);
Format font_strikeout;
font_strikeout.setFontStrikeOut(true);
xlsx.write("B7", "StrikeOut", font_strikeout);
writeFontNameCell(xlsx, "D3", "Arial");
writeFontNameCell(xlsx, "D4", "Arial Black");
writeFontNameCell(xlsx, "D5", "Comic Sans MS");
writeFontNameCell(xlsx, "D6", "Courier New");
writeFontNameCell(xlsx, "D7", "Impact");
writeFontNameCell(xlsx, "D8", "Times New Roman");
writeFontNameCell(xlsx, "D9", "Verdana");
writeFontSizeCell(xlsx, "G3", 10);
writeFontSizeCell(xlsx, "G4", 12);
writeFontSizeCell(xlsx, "G5", 14);
writeFontSizeCell(xlsx, "G6", 16);
writeFontSizeCell(xlsx, "G7", 18);
writeFontSizeCell(xlsx, "G8", 20);
writeFontSizeCell(xlsx, "G9", 25);
Format font_vertical;
font_vertical.setRotation(255);
font_vertical.setFontSize(16);
xlsx.write("J3", "vertical", font_vertical);
xlsx.mergeCells("J3:J9");
//---------------------------------------------------------------
// Create the third sheet.
xlsx.addSheet("Formulas");
xlsx.setColumnWidth(1, 2, 40);
Format rAlign;
rAlign.setHorizontalAlignment(Format::AlignRight);
Format lAlign;
lAlign.setHorizontalAlignment(Format::AlignLeft);
xlsx.write("B3", 40, lAlign);
xlsx.write("B4", 30, lAlign);
xlsx.write("B5", 50, lAlign);
xlsx.write("A7", "SUM(B3:B5)=", rAlign);
xlsx.write("B7", "=SUM(B3:B5)", lAlign);
xlsx.write("A8", "AVERAGE(B3:B5)=", rAlign);
xlsx.write("B8", "=AVERAGE(B3:B5)", lAlign);
xlsx.write("A9", "MAX(B3:B5)=", rAlign);
xlsx.write("B9", "=MAX(B3:B5)", lAlign);
xlsx.write("A10", "MIN(B3:B5)=", rAlign);
xlsx.write("B10", "=MIN(B3:B5)", lAlign);
xlsx.write("A11", "COUNT(B3:B5)=", rAlign);
xlsx.write("B11", "=COUNT(B3:B5)", lAlign);
xlsx.write("A13", "IF(B7>100,\"large\",\"small\")=", rAlign);
xlsx.write("B13", "=IF(B7>100,\"large\",\"small\")", lAlign);
xlsx.write("A15", "SQRT(25)=", rAlign);
xlsx.write("B15", "=SQRT(25)", lAlign);
xlsx.write("A16", "RAND()=", rAlign);
xlsx.write("B16", "=RAND()", lAlign);
xlsx.write("A17", "2*PI()=", rAlign);
xlsx.write("B17", "=2*PI()", lAlign);
xlsx.write("A19", "UPPER(\"qtxlsx\")=", rAlign);
xlsx.write("B19", "=UPPER(\"qtxlsx\")", lAlign);
xlsx.write("A20", "LEFT(\"ubuntu\",3)=", rAlign);
xlsx.write("B20", "=LEFT(\"ubuntu\",3)", lAlign);
xlsx.write("A21", "LEN(\"Hello Qt!\")=", rAlign);
xlsx.write("B21", "=LEN(\"Hello Qt!\")", lAlign);
Format dateFormat;
dateFormat.setHorizontalAlignment(Format::AlignLeft);
dateFormat.setNumberFormat("yyyy-mm-dd");
xlsx.write("A23", "DATE(2013,8,13)=", rAlign);
xlsx.write("B23", "=DATE(2013,8,13)", dateFormat);
xlsx.write("A24", "DAY(B23)=", rAlign);
xlsx.write("B24", "=DAY(B23)", lAlign);
xlsx.write("A25", "MONTH(B23)=", rAlign);
xlsx.write("B25", "=MONTH(B23)", lAlign);
xlsx.write("A26", "YEAR(B23)=", rAlign);
xlsx.write("B26", "=YEAR(B23)", lAlign);
xlsx.write("A27", "DAYS360(B23,TODAY())=", rAlign);
xlsx.write("B27", "=DAYS360(B23,TODAY())", lAlign);
xlsx.write("A29", "B3+100*(2-COS(0)))=", rAlign);
xlsx.write("B29", "=B3+100*(2-COS(0))", lAlign);
xlsx.write("A30", "ISNUMBER(B29)=", rAlign);
xlsx.write("B30", "=ISNUMBER(B29)", lAlign);
xlsx.write("A31", "AND(1,0)=", rAlign);
xlsx.write("B31", "=AND(1,0)", lAlign);
xlsx.write("A33", "HYPERLINK(\"http://qt-project.org\")=", rAlign);
xlsx.write("B33", "=HYPERLINK(\"http://qt-project.org\")", lAlign);
//---------------------------------------------------------------
// Create the fourth sheet.
xlsx.addSheet("NumFormats");
xlsx.setColumnWidth(2, 40);
writeInternalNumFormatsCell(xlsx, 4, 2.5681, 2);
writeInternalNumFormatsCell(xlsx, 5, 2500000, 3);
writeInternalNumFormatsCell(xlsx, 6, -500, 5);
writeInternalNumFormatsCell(xlsx, 7, -0.25, 9);
writeInternalNumFormatsCell(xlsx, 8, 890, 11);
writeInternalNumFormatsCell(xlsx, 9, 0.75, 12);
writeInternalNumFormatsCell(xlsx, 10, 41499, 14);
writeInternalNumFormatsCell(xlsx, 11, 41499, 17);
writeCustomNumFormatsCell(xlsx, 13, 20.5627, "#.###");
writeCustomNumFormatsCell(xlsx, 14, 4.8, "#.00");
writeCustomNumFormatsCell(xlsx, 15, 1.23, "0.00 \"RMB\"");
writeCustomNumFormatsCell(xlsx, 16, 60, "[Red][100]");
//---------------------------------------------------------------
// Create the fifth sheet.
xlsx.addSheet("Merging");
Format centerAlign;
centerAlign.setHorizontalAlignment(Format::AlignHCenter);
centerAlign.setVerticalAlignment(Format::AlignVCenter);
xlsx.write("B4", "Hello Qt!");
xlsx.mergeCells("B4:F6", centerAlign);
xlsx.write("B8", 1);
xlsx.mergeCells("B8:C21", centerAlign);
xlsx.write("E8", 2);
xlsx.mergeCells("E8:F21", centerAlign);
//---------------------------------------------------------------
// Create the fifth sheet.
xlsx.addSheet("Grouping");
qsrand(QDateTime::currentMSecsSinceEpoch());
for (int row = 2; row
QXlsx::Document xlsx;
xlsx.write("A1", "View the properties through:");
xlsx.write("A2", "Office Button -> Prepare -> Properties option in Excel");
xlsx.setDocumentProperty("title", "This is an example spreadsheet");
xlsx.setDocumentProperty("subject", "With document properties");
xlsx.setDocumentProperty("creator", "Debao Zhang");
xlsx.setDocumentProperty("company", "HMICN");
xlsx.setDocumentProperty("category", "Example spreadsheets");
xlsx.setDocumentProperty("keywords", "Sample, Example, Properties");
xlsx.setDocumentProperty("description", "Created with Qt Xlsx");
xlsx.saveAs("Test.xlsx");
return 0;
}
4.9 extractdata Qt操作Excel 提取数据
#include
#include "xlsxdocument.h"
int main()
{
{
// Create a new .xlsx file.
QXlsx::Document xlsx;
xlsx.write("A1", "Hello Qt!");
xlsx.write("A2", 12345);
xlsx.write("A3", "=44+33");
xlsx.write("A4", true);
xlsx.write("A5", "http://qt-project.org");
xlsx.write("A6", QDate(2013, 12, 27));
xlsx.write("A7", QTime(6, 30));
xlsx.saveAs("Book1.xlsx");
}
//![0]
QXlsx::Document xlsx("Book1.xlsx");
//![0]
//![1]
qDebug()
//![0]
Document xlsx;
//![0]
//![1]
xlsx.setColumnWidth(1, 2, 40);
Format rAlign;
rAlign.setHorizontalAlignment(Format::AlignRight);
Format lAlign;
lAlign.setHorizontalAlignment(Format::AlignLeft);
xlsx.write("B3", 40, lAlign);
xlsx.write("B4", 30, lAlign);
xlsx.write("B5", 50, lAlign);
xlsx.write("A7", "SUM(B3:B5)=", rAlign);
xlsx.write("B7", "=SUM(B3:B5)", lAlign);
xlsx.write("A8", "AVERAGE(B3:B5)=", rAlign);
xlsx.write("B8", "=AVERAGE(B3:B5)", lAlign);
xlsx.write("A9", "MAX(B3:B5)=", rAlign);
xlsx.write("B9", "=MAX(B3:B5)", lAlign);
xlsx.write("A10", "MIN(B3:B5)=", rAlign);
xlsx.write("B10", "=MIN(B3:B5)", lAlign);
xlsx.write("A11", "COUNT(B3:B5)=", rAlign);
xlsx.write("B11", "=COUNT(B3:B5)", lAlign);
xlsx.write("A13", "IF(B7>100,\"large\",\"small\")=", rAlign);
xlsx.write("B13", "=IF(B7>100,\"large\",\"small\")", lAlign);
xlsx.write("A15", "SQRT(25)=", rAlign);
xlsx.write("B15", "=SQRT(25)", lAlign);
xlsx.write("A16", "RAND()=", rAlign);
xlsx.write("B16", "=RAND()", lAlign);
xlsx.write("A17", "2*PI()=", rAlign);
xlsx.write("B17", "=2*PI()", lAlign);
xlsx.write("A19", "UPPER(\"qtxlsx\")=", rAlign);
xlsx.write("B19", "=UPPER(\"qtxlsx\")", lAlign);
xlsx.write("A20", "LEFT(\"ubuntu\",3)=", rAlign);
xlsx.write("B20", "=LEFT(\"ubuntu\",3)", lAlign);
xlsx.write("A21", "LEN(\"Hello Qt!\")=", rAlign);
xlsx.write("B21", "=LEN(\"Hello Qt!\")", lAlign);
//![1]
//![2]
xlsx.addSheet("ArrayFormula");
Worksheet *sheet = xlsx.currentWorksheet();
for (int row = 2; row
sheet->write(row, 2, row * 2); // B2:B19
sheet->write(row, 3, row * 3); // C2:C19
}
sheet->writeFormula("D2", CellFormula("=B2+C2", "D2:D19", CellFormula::SharedType));
sheet->writeFormula("E2",
CellFormula("=CONCATENATE(\"The total is \",D2,\" units\")", "E2:E19",
CellFormula::SharedType));
//![21]
//![3]
xlsx.save();
//![3]
// Make sure that read/write works well.
Document xlsx2("Book1.xlsx");
Worksheet *sharedFormulaSheet = dynamic_cast(xlsx2.sheet("SharedFormula"));
for (int row = 2; row
//![0]
QXlsx::Document xlsx;
//![0]
//![1]
xlsx.write("A1", "Hello Qt!");
xlsx.write("A2", 12345);
xlsx.write("A3", "=44+33");
xlsx.write("A4", true);
xlsx.write("A5", "http://qt-project.org");
xlsx.write("A6", QDate(2013, 12, 27));
xlsx.write("A7", QTime(6, 30));
//![1]
//![2]
xlsx.save();
//![2]
return 0;
}
4.12 hyperlinks Qt操作Excel 超链接
#include
#include "xlsxdocument.h"
int main()
{
//![0]
QXlsx::Document xlsx;
//![0]
//![1]
xlsx.write("A1", "http://qt-project.org");
xlsx.write("A2", "http://qt-project.org/wiki#0f68b904e33d9ac04605aecc958bcf52");
xlsx.write("A3", "mailto:[email protected]");
xlsx.write("A4", "file:///C:/User/test/abc.txt");
//![1]
//![2]
xlsx.save();
//![2]
QXlsx::Document xlsx2("Book1.xlsx");
xlsx2.saveAs("Book2.xlsx");
return 0;
}
4.13 image Qt操作Excel 图像
#include
#include "xlsxdocument.h"
int main(int argc, char **argv)
{
QGuiApplication(argc, argv);
QXlsx::Document xlsx;
QImage image(40, 30, QImage::Format_RGB32);
image.fill(Qt::green);
for (int i = 0; i
QGuiApplication(argc, argv);
QXlsx::Document xlsx;
xlsx.setColumnWidth(1, 4, 20.0);
QXlsx::Format header;
header.setFontBold(true);
header.setFontSize(20);
// Custom number formats
QStringList numFormats;
numFormats
int row = i + 2;
int numFmt = i;
xlsx.write(row, 1, 100.0);
xlsx.write(row, 2, numFmt);
QXlsx::Format format;
format.setNumberFormatIndex(numFmt);
xlsx.write(row, 3, 100.0, format);
}
xlsx.save();
return 0;
}
4.16 richtext Qt操作Excel 富文本
#include
#include "xlsxdocument.h"
#include "xlsxrichstring.h"
#include "xlsxworkbook.h"
#include "xlsxformat.h"
int main()
{
//![0]
QXlsx::Document xlsx;
//![0]
//![1]
QXlsx::Format blue;
blue.setFontColor(Qt::blue);
QXlsx::Format red;
red.setFontColor(Qt::red);
red.setFontSize(15);
QXlsx::Format bold;
bold.setFontBold(true);
QXlsx::RichString rich;
rich.addFragment("Hello ", blue);
rich.addFragment("Qt ", red);
rich.addFragment("Xlsx", bold);
xlsx.write("B2", rich);
xlsx.workbook()->setHtmlToRichStringEnabled(true);
xlsx.write("B4", "Hello Qt Xlsx");
xlsx.write("B6", "Qt Xlsx");
//![1]
//![2]
xlsx.saveAs("Test1.xlsx");
//![2]
QXlsx::Document("Test1.xlsx");
xlsx.saveAs("Test2.xlsx");
return 0;
}
4.17 rowcolumn Qt操作Excel 行列
#include
#include "xlsxdocument.h"
#include "xlsxformat.h"
int main()
{
QXlsx::Document xlsx;
xlsx.write(1, 2, "Row:0, Col:2 ==> (C1)");
// Set the height of the first row to 50.0(points)
xlsx.setRowHeight(1, 50.0);
// Set the width of the third column to 40.0(chars)
xlsx.setColumnWidth(3, 3, 40.0);
// Set style for the row 11th.
QXlsx::Format format1;
format1.setFontBold(true);
format1.setFontColor(QColor(Qt::blue));
format1.setFontSize(20);
xlsx.write(11, 1, "Hello Row Style");
xlsx.write(11, 6, "Blue Color");
xlsx.setRowFormat(11, format1);
xlsx.setRowHeight(11, 41);
// Set style for the col [9th, 16th)
QXlsx::Format format2;
format2.setFontBold(true);
format2.setFontColor(QColor(Qt::magenta));
for (int row = 12; row
//![Create a xlsx file]
Document xlsx;
for (int i = 1; i |