qtxlsx读写 excel

系列文章目录

Qt 读取Excel表格数据 生成Excel表格并写入数据

---

前言

相较于QAxObject读写excel,qtxlsx的使用更方便，而且不会因为excel驱动的问题导致读取失败。

---

一、QtXlsx的编译

网上关于QtXlsx源码编译库的文章很多，在这不做多余赘述。

二、使用步骤

1.引入库

代码如下（示例）：

QT       += core gui  xlsx  // .pro


#include <QtXlsx> // xxx.h

2.读数据

代码如下（示例）：

//!
void ExcelToSql::read_excel(const QString& path, const QString& tableName,  myDateBase& db)
{
    QXlsx::Document xlsx(path);
    QXlsx::Workbook *workBook = xlsx.workbook();  
    int sheetCount = workBook->sheetCount();

    if(sheetCount < 4) return;

    bool ok = db.openTransactions(); 
    read_excel_first_page(workBook,tableName,db);
    read_excel_second_page(workBook,tableName,db);
    read_excel_third_page(workBook,tableName,db);
    read_excel_fourth_page(workBook,tableName,db);
    if(ok) db.commitTransactions();
}


//!
void ExcelToSql::read_excel_first_page(QXlsx::Workbook* workBook, const QString& tableName, myDateBase& db)
{
    int index = 0;
    QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));//第一个标签页
    int idx;
    QString disc;
    int rowCount = workSheet->dimension().rowCount();
    rowCount = rowCount > 168 ? 168 :rowCount;
    for (int i = 1; i <= workSheet->dimension().rowCount(); i++)
    {
        QXlsx::Cell *cell_A = workSheet->cellAt(i, 1);
        QXlsx::Cell *cell_B = workSheet->cellAt(i, 2);
        idx = cell_A->value().toInt();
        disc = cell_B->value().toString();

        db.save_disc(tableName,index+idx-1,disc);
    }
}

//!
void ExcelToSql::read_excel_second_page(QXlsx::Workbook *workBook, const QString& tableName, myDateBase& db)
{
    int index = 168;
    QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(1));
    int idx;
    QString disc;
    int rowCount = workSheet->dimension().rowCount();
    rowCount = rowCount > 56 ? 56 :rowCount;
    for (int i = 1; i <= workSheet->dimension().rowCount(); i++)
    {
        QXlsx::Cell *cell_A = workSheet->cellAt(i, 1);
        QXlsx::Cell *cell_B = workSheet->cellAt(i, 2);
        idx = cell_A->value().toInt();
        disc = cell_B->value().toString();
        db.save_disc(tableName,index+idx-1,disc);
    }
}
//!
void ExcelToSql::read_excel_third_page(QXlsx::Workbook *workBook, const QString& tableName, myDateBase& db)
{
    int index = 224;
    QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(2));
    int idx;
    QString disc;
    int rowCount = workSheet->dimension().rowCount();
    rowCount = rowCount > 1 ? 1 :rowCount;
    for (int i = 1; i <= rowCount; i++)
    {
        QXlsx::Cell *cell_A = workSheet->cellAt(i, 1);
        QXlsx::Cell *cell_B = workSheet->cellAt(i, 2);
        idx = cell_A->value().toInt();
        disc = cell_B->value().toString();
        db.save_disc(tableName,index+idx-1,disc);
    }
}
//!
void ExcelToSql::read_excel_fourth_page(QXlsx::Workbook *workBook, const QString& tableName, myDateBase& db)
{
    int index = 225;
    QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(3));
    int idx;
    QString disc;
    int rowCount = workSheet->dimension().rowCount();
    rowCount = rowCount > 48 ? 48 :rowCount;
    for (int i = 1; i <= workSheet->dimension().rowCount(); i++)
    {
        QXlsx::Cell *cell_A = workSheet->cellAt(i, 1);
        QXlsx::Cell *cell_B = workSheet->cellAt(i, 2);
        idx = cell_A->value().toInt();
        disc = cell_B->value().toString();
        db.save_disc(tableName,index+idx-1,disc);
    }
}

3. 写数据

void DiscToExcel::work(const QString& path, const QString& ipv4_1, const QString& ipv4_2)
{
    QString fileName = path.split("/").last();

    myDateBase db;
    auto tableName = db.getTabelName_Disc(fileName,ipv4_1,ipv4_2);
    if(tableName.isEmpty()) return;

    //
    QXlsx::Document xlsx;

    //
    QXlsx::Format format;
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    format.setVerticalAlignment(QXlsx::Format::AlignVCenter);

    QXlsx::Workbook *workBook = xlsx.workbook();
    workBook->addSheet("IO"); //设置页脚名
    workBook->addSheet("TCMS_to_本车LCU");
    workBook->addSheet("本车LCU_to_TCMS");
    workBook->addSheet("LCU_to_LCU");
    
    int idx;
    QString disc;
    QString sql = QString("select idx,disc from %1").arg(tableName);
    QSqlQuery query;
    query.exec(sql);
    while(query.next())
    {
        idx = query.record().value("idx").toInt();
        disc = query.record().value("disc").toString();
        addOne(idx,disc,xlsx);
    }
    xlsx.saveAs(path);
}


void DiscToExcel::addOne(const int& idx, const QString& disc, QXlsx::Document& xlsx)
{
    QXlsx::Format format;
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    format.setVerticalAlignment(QXlsx::Format::AlignVCenter);

    if(idx < 168)
    {
        xlsx.selectSheet("IO"); //根据页脚名选择页
        xlsx.write(idx+1,1,idx+1,format);
        xlsx.write(idx+1,2,disc,format);

    }
    else if(idx < 224)
    {
        xlsx.selectSheet("TCMS_to_本车LCU");
        xlsx.write(idx+1-168,1,idx+1-168,format);
        xlsx.write(idx+1-168,2,disc,format);
    }
    else if(idx < 225)
    {
        xlsx.selectSheet("本车LCU_to_TCMS");
        xlsx.write(idx+1-224,1,idx+1-224,format);
        xlsx.write(idx+1-224,2,disc,format);
    }
    else if(idx < 273)
    {
        xlsx.selectSheet("LCU_to_LCU");
        xlsx.write(idx+1-225,1,idx+1-225,format);
        xlsx.write(idx+1-225,2,disc,format);
    }
}

---