2020-12-19

QT操作excel文档，有两种方法，

1，QAXObject + Excel VBA

    需要安装了excel，然后就是各种COM对象操作，不熟悉的话还是比较复杂的。

   excel = new QAxObject();
    excel->setControl("ket.Application");//wps
    excel->setProperty("Visible", false);
    QAxObject *books = excel->querySubObject("WorkBooks");//获取拥有指定COM对象的指针
    books->dynamicCall("Open(const Qstring&)", filePath);//打开现有文件, COM对象的一个函数
    book = excel->querySubObject("ActiveWorkBook");//从excel对象获取
    QAxObject *sheets = book->querySubObject("Sheets");
    sheet = sheets->querySubObject("Item(int)", 1);//sheet1

 QAxObject * usedrange = sheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
    QAxObject * rows = usedrange->querySubObject("Rows");
    QAxObject * columns = usedrange->querySubObject("Columns");
    int intRows = rows->property("Count").toInt();
    index = columns->property("Count").toInt() + 1;

 

 QAxObject *cell = sheet->querySubObject("Cells(int, int)", row+i, index);
        if(NULL != cell)
        {
            cell->dynamicCall("SetValue(const QVariant&)", col_data[i]);

}

 book->dynamicCall("Close(Boolean)", true);//false的话不会保存更改
        excel->dynamicCall("Quit()");//需要等待成功退出

2，QTxlsX库

     不需要安装execel，接口都是熟悉的C++形式。

QXlsx::Format format_red, format_green, image_format;
        format_red.setFontColor(QColor(255,0,0));
        format_red.setVerticalAlignment(QXlsx::Format::AlignVCenter);
        format_red.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
        format_green.setFontColor(QColor(0,255,0));
        format_green.setVerticalAlignment(QXlsx::Format::AlignVCenter);
        format_green.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
        image_format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
        image_format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);

        QXlsx::Document xlsx("Test2.csv");
        bool status = xlsx.write("A1", "hello qt!");
        status = xlsx.write(2, 2, "5.0", format_green);
        status = xlsx.write(3, 3, "6.0", format_green);

        QImage image(QString::fromUtf8("单位相对畸变.jpg"));
        QXlsx::CellRange cell_range(4, 4, 10, 5);
        xlsx.mergeCells(cell_range);
        status = xlsx.insertImage(3, 3, image.scaled(120,120, Qt::IgnoreAspectRatio, Qt::SmoothTransformation));//row,col的设定区别于write函数
        //QXlsx::CellRange cell_range2(11, 6, 11, 10);
        xlsx.setColumnWidth(6, 50);
        status = xlsx.write(11,6, "long long ago, there was a kindom... it was a long story.", format_red);
        xlsx.save();