qtxlsx操作excel

qtxlsx的使用

• 下载qtxlsx
  官网:http://qtxlsx.debao.me/
• 解压
  

• 将源码文件拷贝到Qt新建的工程的qtxlsx文件夹中(qtxlsx文件夹是自己手动创建的)
  
  
• qt工程的"xxxx.pro"文件中添加如下代码
  
  保存之后就会出现左边的“qtxlsx”文件夹

以下官方例子

//example:qt操作worksheetoperation工作表的操作
#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;
}

 //exmple:qt操作设置excel样式
#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;
}

 
 // exmaple:qt设置excel的行列
 
#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;
}
 

 
 // example:qt操作富文本
 
#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;
}
 

 
 
// example:qt操作Excel数值格式
 
#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;
}
 

 // example:qt操作合并单元格
 
#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;
}
 

// example:Qt操作Excel图像
#include <QtGui>
#include "xlsxdocument.h"

int main(int argc, char **argv