Qt Xlsx使用教程、Qt操作Excel、Qt生成Excel图表、跨平台不依赖Office_超级大洋葱806的博客-程序员秘密_qtxlsx

技术标签: QXlsx的使用  Qt  生成图表  操作Excel  QtXlsx  # 3.1 Qt Widget  # 0.1 C/C++  

文章目录

1.Qt Xlsx库简介

官方文档:Qt Xlsx | QtXlsx 0.3 (debao.me)

下载地址:dbzhang800/QtXlsxWriter: .xlsx file reader and writer for Qt5 (github.com)

xlsx_demo

QtXlsx是可以读取和写入Excel文件的库。 它不需要Microsoft Excel,并且可以在Qt5支持的任何平台中使用。 该库可用于:

  • 从头开始生成新的.xlsx文件
  • 从现有的.xlsx文件提取数据
  • 编辑现有的.xlsx文件

注意:

对于Linux用户,如果Qt是通过“ apt-get”之类的软件包管理器工具安装的,请确保已安装Qt5开发软件包qtbase5-private-dev

2. 用法①:使用Xlsx作为Qt5的附加模块

2.1 构建模块

注意:此步骤需要用到Perl。

  1. 未安装:

image-20201226193009612

  1. 已安装:

image-20201226192945690

如果您已经安装则可进行2.2步骤,否则请先安装perl,参考我另一篇博客:

perl安装教程_超级大洋葱的博客-程序员秘密

2.2 下载QtXlsx源代码

https://github.com/dbzhang800/QtXlsxWriter

image-20201226194821585

image-20201226195039762

注意,源代码前级目录不要有中文!!!

2.3 为编译器安装Xlsx模块

我这里有5个编译器:

image-20201226195246124

现在以给MinGW 32-bit 作为示例,其它编译器类似:

2.3.1 打开 MinGW 7.3.0 32-bit 控制台

image-20201226195455046

2.3.2 找到QtXlsx源码路径

我的路径地址为:

H:\0.Download\Edge\QtXlsxWriter-master\src

image-20201226195543138

2.3.3 再MinGW32控制台进入路径

image-20201226195722811

接下来进行以下操作(Linux):

qmake src.pro
make
make install

接下来进行以下操作(mingw32、mingw64):

image-20201226203252431

qmake src.pro
mingw32-make
mingw32-make install

2.3.4 执行qmake

执行qmake,会自动生成makefile文件

image-20201226201206115

2.3.5 执行makefile

这里会编译很久,喝杯茶休息休息:

image-20201226201250110

编译完成:

image-20201226201510203

2.3.6 为编译器安装QtXlsx

执行以下命令:

image-20201226201554499

2.3.7 安装完成

image-20201226201729553

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安装模块

首先要解压另一份源码:

image-20201226203851617

image-20201226203429802

image-20201226203817737

2.4 使用QtXlsx模块

这里我们用64bit工程做示范,32位一样:

2.4.1 创建工程

image-20201226204029750

设置工程名称:

image-20201226204042902

选择编译器版本:

image-20201226204105010

创建完成:

image-20201226204133917

2.4.2 打开QtXlsx示例文件

我这里地址为:

H:\0.Download\Edge\QtXlsxWriter-master\examples\xlsx

image-20201226204228177

2.4.3 复制hello工程内容

image-20201226204329763

#include <QtCore>
#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模块

image-20201226204430368

2.4.5 在.pro文件中引入xlsx模块

QT += xlsx

image-20201226204550061

image-20201226204618995

2.4.6 运行查看效果

生成了xlsx格式的文件

image-20201226204736088

表格内容如下:

image-20201226204712899

3.用法②:直接使用源代码

该包包含一个 qtxlsx.pri文件,允许您将组件集成到使用 qmake 进行生成步骤的应用程序中。

3.1 新建工程

image-20201226205038404

image-20201226205059424

image-20201226205124412

3.2 工程目录下添加源代码

来到工程目录下:

image-20201226205203050

image-20201226205443060

打开网上下载的源码路径:

image-20201226205352282

image-20201226205539247

3.3 在工程中引入xlsx源代码

# 使用qtxlsx源代码
include(qtxlsx/src/xlsx/qtxlsx.pri)

image-20201226205721327

3.4 修改main.cpp内容,测试

#include <QtCore>
#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;
}

image-20201226205922936

3.5 查看效果

image-20201226210021994

内容一致:

image-20201226210038055

4. 官方examples例子

4.1 calendar Qt操作Excel生成日历

image-20201226211343990

#include <QtCore>
#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 <= 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");

    return 0;
}

4.2 chart Qt操作Excel生成图表

img

#include <QtCore>
#include "xlsxdocument.h"
#include "xlsxcellrange.h"
#include "xlsxchart.h"

using namespace QXlsx;

int main()
{
    
    //![0]
    Document xlsx;
    for (int i = 1; i < 10; ++i) {
    
        xlsx.write(i, 1, i * i * i); // A1:A9
        xlsx.write(i, 2, i * i); // B1:B9
        xlsx.write(i, 3, i * i - 1); // C1:C9
    }
    //![0]

    //![1]
    Chart *pieChart = xlsx.insertChart(3, 3, QSize(300, 300));
    pieChart->setChartType(Chart::CT_Pie);
    pieChart->addSeries(CellRange("A1:A9"));
    pieChart->addSeries(CellRange("B1:B9"));
    pieChart->addSeries(CellRange("C1:C9"));

    Chart *pie3DChart = xlsx.insertChart(3, 9, QSize(300, 300));
    pie3DChart->setChartType(Chart::CT_Pie3D);
    pie3DChart->addSeries(CellRange("A1:C9"));

    Chart *barChart = xlsx.insertChart(23, 3, QSize(300, 300));
    barChart->setChartType(Chart::CT_Bar);
    barChart->addSeries(CellRange("A1:C9"));

    Chart *bar3DChart = xlsx.insertChart(23, 9, QSize(300, 300));
    bar3DChart->setChartType(Chart::CT_Bar3D);
    bar3DChart->addSeries(CellRange("A1:C9"));

    Chart *lineChart = xlsx.insertChart(43, 3, QSize(300, 300));
    lineChart->setChartType(Chart::CT_Line);
    lineChart->addSeries(CellRange("A1:C9"));

    Chart *line3DChart = xlsx.insertChart(43, 9, QSize(300, 300));
    line3DChart->setChartType(Chart::CT_Line3D);
    line3DChart->addSeries(CellRange("A1:C9"));

    Chart *areaChart = xlsx.insertChart(63, 3, QSize(300, 300));
    areaChart->setChartType(Chart::CT_Area);
    areaChart->addSeries(CellRange("A1:C9"));

    Chart *area3DChart = xlsx.insertChart(63, 9, QSize(300, 300));
    area3DChart->setChartType(Chart::CT_Area3D);
    area3DChart->addSeries(CellRange("A1:C9"));

    Chart *scatterChart = xlsx.insertChart(83, 3, QSize(300, 300));
    scatterChart->setChartType(Chart::CT_Scatter);
    // Will generate three lines.
    scatterChart->addSeries(CellRange("A1:A9"));
    scatterChart->addSeries(CellRange("B1:B9"));
    scatterChart->addSeries(CellRange("C1:C9"));

    Chart *scatterChart_2 = xlsx.insertChart(83, 9, QSize(300, 300));
    scatterChart_2->setChartType(Chart::CT_Scatter);
    // Will generate two lines.
    scatterChart_2->addSeries(CellRange("A1:C9"));

    Chart *doughnutChart = xlsx.insertChart(103, 3, QSize(300, 300));
    doughnutChart->setChartType(Chart::CT_Doughnut);
    doughnutChart->addSeries(CellRange("A1:C9"));
    //![1]

    //![2]
    xlsx.saveAs("Book1.xlsx");
    //![2]

    Document xlsx2("Book1.xlsx");
    xlsx2.saveAs("Book2.xlsx");
    return 0;
}

4.3 chartsheet Qt操作Excel生成图表

image-20201226211642355

#include <QtCore>
#include "xlsxdocument.h"
#include "xlsxchartsheet.h"
#include "xlsxcellrange.h"
#include "xlsxchart.h"

using namespace QXlsx;

int main()
{
    
    //![0]
    Document xlsx;
    for (int i = 1; i < 10; ++i)
        xlsx.write(i, 1, i * i);
    //![0]

    //![1]
    xlsx.addSheet("Chart1", AbstractSheet::ST_ChartSheet);
    Chartsheet *sheet = static_cast<Chartsheet *>(xlsx.currentSheet());
    Chart *barChart = sheet->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 条件格式化

image-20201226211815134

#include <QtCore>
#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 < 22; ++row) {
    
        for (int col = 2; col < 22; ++col)
            xlsx.write(row, col, qrand() % 100);
    }
    //![0]

    //![cf1]
    ConditionalFormatting cf1;
    Format fmt1;
    fmt1.setFontColor(Qt::green);
    fmt1.setBorderStyle(Format::BorderDashed);
    cf1.addHighlightCellsRule(ConditionalFormatting::Highlight_LessThan, "40", fmt1);
    cf1.addRange("B3:B21");
    xlsx.addConditionalFormatting(cf1);
    //![cf1]

    //![cf2]
    ConditionalFormatting cf2;
    Format fmt2;
    fmt2.setBorderStyle(Format::BorderDotted);
    fmt2.setBorderColor(Qt::blue);
    cf2.addHighlightCellsRule(ConditionalFormatting::Highlight_Between, "30", "70", fmt2);
    cf2.addRange("C3:C21");
    xlsx.addConditionalFormatting(cf2);
    //![cf2]

    //![cf3]
    ConditionalFormatting cf3;
    Format fmt3;
    fmt3.setFontStrikeOut(true);
    fmt3.setFontBold(true);
    cf3.addHighlightCellsRule(ConditionalFormatting::Highlight_BeginsWith, "2", fmt3);
    cf3.addRange("D3:D21");
    xlsx.addConditionalFormatting(cf3);
    //![cf3]

    //![cf4]
    ConditionalFormatting cf4;
    cf4.addDataBarRule(Qt::blue);
    cf4.addRange("E3:E21");
    xlsx.addConditionalFormatting(cf4);
    //![cf4]

    //![cf5]
    ConditionalFormatting cf5;
    cf5.add2ColorScaleRule(Qt::blue, Qt::red);
    cf5.addRange("F3:F21");
    xlsx.addConditionalFormatting(cf5);
    //![cf5]

    //![2]
    xlsx.saveAs("Book1.xlsx");
    //![2]

    Document xlsx2("Book1.xlsx");
    xlsx2.saveAs("Book2.xlsx");
    return 0;
}

4.5 datavalidation Qt操作Excel 数据有效性

image-20201226212029350

image-20201226212044883

#include <QtCore>
#include "xlsxdocument.h"
#include "xlsxdatavalidation.h"

QTXLSX_USE_NAMESPACE

int main()
{
    
    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 定义名字

image-20201226212142868

#include <QtCore>
#include "xlsxdocument.h"

QTXLSX_USE_NAMESPACE

int main()
{
    
    //![0]
    Document xlsx;
    for (int i = 1; i <= 10; ++i) {
    
        xlsx.write(i, 1, i);
        xlsx.write(i, 2, i * i);
        xlsx.write(i, 3, i * i * i);
    }
    //![0]
    //![1]
    xlsx.defineName("MyCol_1", "=Sheet1!$A$1:$A$10");
    xlsx.defineName("MyCol_2", "=Sheet1!$B$1:$B$10", "This is comments");
    xlsx.defineName("MyCol_3", "=Sheet1!$C$1:$C$10", "", "Sheet1");
    xlsx.defineName("Factor", "=0.5");
    //![1]
    //![2]
    xlsx.write(11, 1, "=SUM(MyCol_1)");
    xlsx.write(11, 2, "=SUM(MyCol_2)");
    xlsx.write(11, 3, "=SUM(MyCol_3)");
    xlsx.write(12, 1, "=SUM(MyCol_1)*Factor");
    xlsx.write(12, 2, "=SUM(MyCol_2)*Factor");
    xlsx.write(12, 3, "=SUM(MyCol_3)*Factor");
    //![2]

    xlsx.saveAs("Book1.xlsx");
    return 0;
}

4.7 demo Qt操作Excel 示例

image-20201226212605679

#include <QtCore>
#include "xlsxdocument.h"
#include "xlsxformat.h"
#include "xlsxcellrange.h"
#include "xlsxworksheet.h"

QTXLSX_USE_NAMESPACE

void writeHorizontalAlignCell(Document &xlsx, const QString &cell, const QString &text,
                              Format::HorizontalAlignment align)
{
    
    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];[Green][>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 < 31; ++row) {
    
        for (int col = 1; col <= 10; ++col)
            xlsx.write(row, col, qrand() % 100);
    }
    xlsx.groupRows(4, 7);
    xlsx.groupRows(11, 26, false);
    xlsx.groupRows(15, 17, false);
    xlsx.groupRows(20, 22, false);
    xlsx.setColumnWidth(1, 10, 10.0);
    xlsx.groupColumns(1, 2);
    xlsx.groupColumns(5, 8, false);

    xlsx.saveAs("Book1.xlsx");

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

    return 0;
}

4.8 documentproperty Qt操作Excel 文档属性

image-20201226212707720

#include <QtCore>
#include "xlsxdocument.h"

int main()
{
    
    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 提取数据

image-20201226212804913

#include <QtCore>
#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() << xlsx.read("A1");
    qDebug() << xlsx.read("A2");
    qDebug() << xlsx.read("A3");
    qDebug() << xlsx.read("A4");
    qDebug() << xlsx.read("A5");
    qDebug() << xlsx.read("A6");
    qDebug() << xlsx.read("A7");
    //![1]

    //![2]
    for (int row = 1; row < 10; ++row) {
    
        if (QXlsx::Cell *cell = xlsx.cellAt(row, 1))
            qDebug() << cell->value();
    }
    //![2]

    return 0;
}

4.10 formulas Qt操作Excel 公式

image-20201226212903137

#include <QtCore>
#include "xlsxdocument.h"
#include "xlsxformat.h"
#include "xlsxworksheet.h"
#include "xlsxcellformula.h"

QTXLSX_USE_NAMESPACE

int main()
{
    
    //![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 < 20; ++row) {
    
        sheet->write(row, 2, row * 2); // B2:B19
        sheet->write(row, 3, row * 3); // C2:C19
    }
    sheet->writeFormula("D2", CellFormula("B2:B19+C2:C19", "D2:D19", CellFormula::ArrayType));
    sheet->writeFormula("E2",
                        CellFormula("=CONCATENATE(\"The total is \",D2:D19,\" units\")", "E2:E19",
                                    CellFormula::ArrayType));
    //![2]

    //![21]
    xlsx.addSheet("SharedFormula");
    sheet = xlsx.currentWorksheet();

    for (int row = 2; row < 20; ++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<Worksheet *>(xlsx2.sheet("SharedFormula"));
    for (int row = 2; row < 20; ++row) {
    
        qDebug() << sharedFormulaSheet->read(row, 4);
    }

    xlsx2.saveAs("Book2.xlsx");

    return 0;
}

4.11 hello Qt操作Excel 问候

image-20201226212936154

#include <QtCore>
#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;
}

4.12 hyperlinks Qt操作Excel 超链接

image-20201226213030385

#include <QtCore>
#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 图像

image-20201226213112458

#include <QtGui>
#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 < 10; ++i)
        xlsx.insertImage(10 * i, 5, image);
    xlsx.saveAs("Book1.xlsx");

    QXlsx::Document xlsx2("Book1.xlsx");
    xlsx2.saveAs("Book2.xlsx");

    return 0;
}

4.14 mergecells Qt操作Excel 合并单元格

image-20201226213150606

#include "xlsxdocument.h"
#include "xlsxformat.h"

QTXLSX_USE_NAMESPACE

int main()
{
    
    Document xlsx;
    //![0]
    Format format;
    format.setHorizontalAlignment(Format::AlignHCenter);
    format.setVerticalAlignment(Format::AlignVCenter);
    //![0]
    //![1]
    xlsx.write("B4", "Hello Qt!");
    xlsx.mergeCells("B4:F6", format);
    xlsx.write("B8", 1);
    xlsx.mergeCells("B8:C21", format);
    xlsx.write("E8", 2);
    xlsx.mergeCells("E8:F21", format);
    //![1]
    xlsx.save();

    return 0;
}

4.15 numberformat Qt操作Excel 数值格式

image-20201226213232276

#include <QtGui>
#include "xlsxdocument.h"
#include "xlsxformat.h"

int main(int argc, char **argv)
{
    
    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 << "Qt #"
               << "yyyy-mmm-dd"
               << "$ #,##0.00"
               << "[red]0.00";
    xlsx.write(1, 1, "Raw data", header);
    xlsx.write(1, 2, "Format", header);
    xlsx.write(1, 3, "Shown value", header);
    for (int i = 0; i < numFormats.size(); ++i) {
    
        int row = i + 2;
        xlsx.write(row, 1, 100.0);
        xlsx.write(row, 2, numFormats[i]);
        QXlsx::Format format;
        format.setNumberFormat(numFormats[i]);
        xlsx.write(row, 3, 100.0, format);
    }

    // Builtin number formats
    xlsx.addSheet();
    xlsx.setColumnWidth(1, 4, 20.0);
    xlsx.write(1, 1, "Raw data", header);
    xlsx.write(1, 2, "Builtin Format", header);
    xlsx.write(1, 3, "Shown value", header);
    for (int i = 0; i < 50; ++i) {
    
        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 富文本

image-20201226213311436

#include <QtCore>
#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", "<b>Hello</b> <font color=\"red\">Qt</font> <i>Xlsx</i>");

    xlsx.write("B6", "<font color=\"red\"><b><u><i>Qt Xlsx</i></u></b></font>");

    //![1]

    //![2]
    xlsx.saveAs("Test1.xlsx");
    //![2]

    QXlsx::Document("Test1.xlsx");
    xlsx.saveAs("Test2.xlsx");

    return 0;
}

4.17 rowcolumn Qt操作Excel 行列

image-20201226213345997

#include <QtCore>
#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 <= 30; row++)
        for (int col = 9; col <= 15; col++)
            xlsx.write(row, col, row + col);
    xlsx.setColumnWidth(9, 16, 5.0);
    xlsx.setColumnFormat(9, 16, format2);

    xlsx.save();
    return 0;
}

4.18 style Qt操作Excel 设置样式

image-20201226213437471

#include <QtCore>
#include "xlsxdocument.h"
#include "xlsxformat.h"

int main()
{
    
    QXlsx::Document xlsx;
    QXlsx::Format format1;
    format1.setFontColor(QColor(Qt::red));
    format1.setFontSize(15);
    format1.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    format1.setBorderStyle(QXlsx::Format::BorderDashDotDot);
    xlsx.write("A1", "Hello Qt!", format1);
    xlsx.write("B3", 12345, format1);

    QXlsx::Format format2;
    format2.setFontBold(true);
    format2.setFontUnderline(QXlsx::Format::FontUnderlineDouble);
    format2.setFillPattern(QXlsx::Format::PatternLightUp);
    xlsx.write("C5", "=44+33", format2);
    xlsx.write("D7", true, format2);

    QXlsx::Format format3;
    format3.setFontBold(true);
    format3.setFontColor(QColor(Qt::blue));
    format3.setFontSize(20);
    xlsx.write(11, 1, "Hello Row Style");
    xlsx.write(11, 6, "Blue Color");
    xlsx.setRowFormat(11, 41, format3);

    QXlsx::Format format4;
    format4.setFontBold(true);
    format4.setFontColor(QColor(Qt::magenta));
    for (int row = 21; row <= 40; row++)
        for (int col = 9; col < 16; col++)
            xlsx.write(row, col, row + col);
    xlsx.setColumnFormat(9, 16, format4);

    xlsx.write("A5", QDate(2013, 8, 29));

    QXlsx::Format format6;
    format6.setPatternBackgroundColor(QColor(Qt::green));
    xlsx.write("A6", "Background color: green", format6);

    xlsx.saveAs("book1.xlsx");

    return 0;
}

4.19 worksheetoperations 工作表的操作

image-20201226213519198

#include <QtCore>
#include "xlsxdocument.h"
#include "xlsxabstractsheet.h"

QTXLSX_USE_NAMESPACE

int main()
{
    
    //![Create a xlsx file]
    Document xlsx;

    for (int i = 1; i < 20; ++i) {
    
        for (int j = 1; j < 15; ++j)
            xlsx.write(i, j, QString("R %1 C %2").arg(i).arg(j));
    }
    xlsx.addSheet();
    xlsx.write(2, 2, "Hello Qt Xlsx");
    xlsx.addSheet();
    xlsx.write(3, 3, "This will be deleted...");

    xlsx.addSheet("HiddenSheet");
    xlsx.currentSheet()->setHidden(true);
    xlsx.write("A1", "This sheet is hidden.");

    xlsx.addSheet("VeryHiddenSheet");
    xlsx.sheet("VeryHiddenSheet")->setSheetState(AbstractSheet::SS_VeryHidden);
    xlsx.write("A1", "This sheet is very hidden.");

    xlsx.save(); // Default name is "Book1.xlsx"
    //![Create a xlsx file]

    Document xlsx2("Book1.xlsx");
    //![add_copy_move_delete]
    xlsx2.renameSheet("Sheet1", "TheFirstSheet");

    xlsx2.copySheet("TheFirstSheet", "CopyOfTheFirst");

    xlsx2.selectSheet("CopyOfTheFirst");
    xlsx2.write(25, 2, "On the Copy Sheet");

    xlsx2.deleteSheet("Sheet3");

    xlsx2.moveSheet("Sheet2", 0);
    //![add_copy_move_delete]

    //![show_hidden_sheets]
    xlsx2.sheet("HiddenSheet")->setVisible(true);
    xlsx2.sheet("VeryHiddenSheet")->setVisible(true);
    //![show_hidden_sheets]

    xlsx2.saveAs("Book2.xlsx");
    return 0;
}
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u014779536/article/details/111769792

智能推荐

android 背景图缩放,解决android:background背景图片被拉伸问题_weixin_39639600的博客-程序员资料

ImageView中XML属性src和background的区别:background会根据ImageView组件给定的长宽进行拉伸,而src就存放的是原图的大小,不会进行拉伸。src是图片内容(前景),bg是背景,可以同时使用。此外:scaleType只对src起作用;bg可设置透明度,比如在ImageButton中就可以用android:scaleType控制图片的缩放方式如上所述,backg...

qt用odbc连接mysql_【原创】Qt 使用ODBC driver 连接SQL Server_午餐时间到了的博客-程序员资料

最近在做数据库的课程设计。第一个需要解决的问题是使用什么工具来实现这个系统。经过一番资料查找,决定使用SQL Server Express 2012作为服务器,使用Qt作为编写客户端程序语言。问题是client如何连接SQL Server? 下面是我的解决方法。1.开启windows上的SQL Server 的ODBC驱动ODBC 是一个调用级接口,它使得应用程序得以访问任何具有 ODBC 驱动程...

翻页时钟代码大公开_lovefan的博客-程序员资料_翻页钟开源代码

不少朋友向我要翻页时钟的代码,现在贴给大家。代码水平有限,见谅。看不明白的可以问我:)js// miniprogram/pages/flipClock/jsconst moment = require('../../../utils/moment-with-locales.min.js');const Lunar = require('../../../utils/lunar.js');var startX, endX;var moveFlag = true; // 判断执行滑动事件.

JAVA切换不了FTP服务器目录_解决linux下ftp指定访问目录无法修改的问题_豪睿刘爱上楼楼梯的博客-程序员资料

他的系统是CentOS,是RH派系的。我把vsftpd安装配置好了,以为大功告成,但客户端访问提示如下错误:500 OOPS: cannot change directory:/home/ftp原因是他的CentOS系统安装了SELinux,因为默认下是没有开启FTP的支持,所以访问时都被阻止了。//查看SELinux设置# getsebool -a|grep ftpftpd_disable_tr...

.NET实现网络爬虫_hyunbar的博客-程序员资料_.net 爬虫

爬虫的特征和运行方式User-Agent:主要用来将我们的爬虫伪装成浏览器。Cookie:主要用来保存爬虫的登录状态。连接数:主要用来限制单台机器与服务端的连接数量。代理IP:主要用来伪装请求地址,提高单机并发数量。爬虫工作的方式可以归纳为两种:深度优先、广度优先。深度优先就是一个连接一个连接的向内爬,处理完成后再换一下一个连接,这种方式对于我们来说缺点很明显。 广度优先...

c web service_简单并快乐着的博客-程序员资料

转自 http://blog.163.com/wzbwzb_187/blog/static/6632425720106424158919/ 以下是源代码: /**************filename: Server.cpp**************** 该程序通过标准socket实现简单Http服务器 运行该服务器可以通过浏览器访问服务器目录下的 Html文件和j

随便推点

Paxos、Raft、ZAB算法_知知之之的博客-程序员资料_paxos raft zab

Paxos算法是Leslie Lamport在1990年提出的一种基于消息传递的一致性算法。基于Paxos协议的数据同步与传统主备方式最大的区别在于:Paxos只需超过半数的副本在线且相互通信正常,就可以保证服务的持续可用,且数据不丢失。Paxos将系统中的角色分为提议者 (Proposer),决策者 (Acceptor),和最终决策学习者 (Learner):在多副本状态机中,每个副本同时具有Proposer、Acceptor、Learner三种角色。Paxos算法通过一个决议分为两个阶段(Learn阶段

Bibtex格式类型_小王同学w的博客-程序员资料_bibtex类型

出于保留资源和查找方便,转载自https://blog.csdn.net/kmsj0x00/article/details/85318057类型简介必需关键字可省略关键字@article期刊或杂志上的一篇文章。author, title, journal, year.volume, number, pages, month, [email protected]有确定出版社的书籍。author或editor, title, publisher, year.volume或number, series, address, e

SpringBoot-Google二步验证_apkqfa6158的博客-程序员资料

SpringBoot-Google二步验证概念:Google身份验证器Google Authenticator是谷歌推出的基于时间的一次性密码(Time-based One-time Password,简称TOTP),只需要在手机上安装该APP,就可以生成一个随着时间变化的一次性密码,用于帐户验证。Google身份验证器是一款基于时间与哈希的一次性密码算法的两步验证软件令牌,此软件...

Maven_weixin_41249041的博客-程序员资料

maven帮助构建项目 项目和项目之间的依赖关系,管理jar包。项目管理工具,管理java项目:1、项目对象模型 POM对象模型,每个maven工程中都有一个pom.xml文件,定义工程所依赖的jar包、本工程的坐标、打包运行方式。。2、依赖管理系统(基础核心 )maven通过坐标对项目工程所依赖的jar包统一规范管理。3、maven定义一套项目生命周期清理、初始...

css教程系列5:px、em、rem区别介绍_邢晋宇的博客-程序员资料

1 PXpx像素(Pixel)。相对长度单位。像素px是相对于显示器屏幕分辨率而言的。PX特点1. 浏览器无法调整px单位的字体,以em或rem为字体单位可调整字体。 2 EMem是相对长度单位。EM特点1. em的值并不是固定的;2. 行高:n倍的 em = n *父级元素的像素px大小(若无则*浏览器的默认字体)。3 REMrem是CSS3新增的一个相对单位(root em,根em),也是相对...

Matlab: 作图_weixin_34413357的博客-程序员资料

查看当前变量可以用who,查看当前变量及其结构,大小,类型等,可以用whos。控制图的大小figure(‘position’,[x0,y0,dx,dy]);figure(fig number);控制图例(legend)legend('leg1','leg2','Location','NorthWest');% hide the legend boxlegend boxoff...

推荐文章

热门文章

相关标签