使用xInt 和 Qtxlsx 读写excel

一. xLnt

//读取excel
int xLnt_Excel_read(QList<QVariantList> &result, QString fileName, std::string &errorMsg)
{
    result.clear();
    try{
        xlnt::workbook wb;
        wb.load(fileName.toStdString());
        auto ws = wb.active_sheet();

        qDebug() << "____process speed reading_____________";
        for (auto row : ws.rows(false))
        {
            QVariantList str;
            for (auto cell : row)
            {
                str.append(QString::fromStdString(cell.to_string()));
            }
            result.append(str);
        }
        qDebug() << "____process completed_____________";
    }
    catch (std::exception &e)
    {
        errorMsg = e.what();
        qDebug() << "___error: " <<QString::fromStdString(e.what());
        return -1;
    }
    return result.size();
}


////保存excel [仅支持.xlsx格式]
int xLnt_Excel_write(QList<QVariantList> &input, QString outputFile, std::string &errorMsg)
{
    try {
        xlnt::workbook wbOut;
        std::string dest_filename = outputFile.toStdString();
        //Creating the output worksheet
        xlnt::worksheet wsOut = wbOut.active_sheet();
        //Giving the output worksheet a title/name
        wsOut.title("属性");

        qDebug() << "Looping through vector and writing to spread sheet";


        for (int fOut = 0; fOut < input.size(); fOut++)
        {
            std::clog << "Row" << fOut << std::endl;
            for (int fIn = 0; fIn < input.at(fOut).size(); fIn++)
            {
                wsOut.cell(xlnt::cell_reference(fIn + 1, fOut+1)).value(input.at(fOut).at(fIn).toString().toStdString());

            }
        }
        qDebug() <<  "Finished writing spread sheet";
        wbOut.save(dest_filename);
    } catch (std::exception &e) {
        errorMsg = e.what();
        qDebug() << "___error: " <<QString::fromStdString(e.what());
        return -1;
    }
    return 0;
}

二. Qtxlsx

//标准读excel
int QtXlsx_Excel_Read(QString excelPath, QList<QVariantList> &retVec)
{
    JQ_QXlsx::Document xlsx(excelPath);
    int colnum =   xlsx.dimension ().lastColumn ();
    int rownum  =  xlsx.dimension ().lastRow ();

    for (int i=1; i <= rownum; i++)
    {
        QVariantList Vlist;
        for (int j=1; j<=colnum; j++)
        {
            JQ_QXlsx::Cell *cell;
            cell =xlsx.cellAt(i,j);
            if (cell)
                Vlist.append(cell->value());
            else
                Vlist.append("");
        }
        retVec.push_back(Vlist);
    }
    return retVec.size();
}

//标准写excel
bool  QtXlsx_Excel_Write_Standard(QList<QVariantList> &input, QString outputFile)
{
    if (input.isEmpty())
        return false;
    JQ_QXlsx::Document xlsx;
    xlsx.addSheet("属性模板");
    JQ_QXlsx::Format  format; //格式化对象
    format.setHorizontalAlignment(JQ_QXlsx::Format::AlignHCenter);//水平居中
    //  format.setVerticalAlignment(JQ_QXlsx::Format::AlignVCenter);//垂直居中
    format.setBorderStyle(JQ_QXlsx::Format::BorderThin);//设置边框
    //  format.setShrinkToFit(true);//单元格内容缩放
    format.setFontSize(10);

    //写头
    //  format.setFontBold(true);  //字体加粗
    format.setPatternBackgroundColor(QColor("#FFFF00")); //单元格背景色
    // format.setPatternForegroundColor(Qt::red);
    QVariantList header = input[0];
    for (int i = 0; i < header.size(); i++)
    {
        xlsx.write(1, i+1, header[i], format);
    }

    //写表
    format.setFontBold(false);
    format.setFontSize(9);
    format.setPatternBackgroundColor(QColor("#FFFFFF"));
    for (int fOut = 1; fOut < input.size(); fOut++)
    {
        for (int fIn = 0; fIn < input.at(fOut).size(); fIn++)
        {
            xlsx.write(fOut+1, fIn+1, input.at(fOut).at(fIn), format);
        }
    }

    xlsx.saveAs(outputFile);
    return true;
}