Qt中操作Excel表格读写

最新推荐文章于 2025-05-26 14:45:33 发布

一二三四五六七八九

最新推荐文章于 2025-05-26 14:45:33 发布

阅读量3.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： Qt 数据库文章标签： qt excel

本文链接：https://blog.youkuaiyun.com/object__/article/details/122827549

Qt 同时被 2 个专栏收录

20 篇文章

订阅专栏

数据库

7 篇文章

订阅专栏

这篇博客展示了如何使用C++通过QAxObject与Excel进行交互，实现数据从数据库导入到Excel以及从Excel回填到数据库的操作。首先，代码创建Excel对象并隐藏界面，然后打开或创建工作簿，设定标题并填充数据。接着，从Excel读取数据，存入结构化数据，并执行相应的数据库操作。最后，关闭工作簿和Excel应用，完成数据迁移过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

示例代码（将数据从数据库导入到Excel，将数据从Excel导入到数据库）：

void slotExportBtn(){
    QAxObject *excel = new QAxObject("Excel.Application");
    if(excel->isNull()){
        if(excel != NULL){
            excel->dynamicCall("Quit()");
            delete excel;
        }
        QMessageBox::critical(0, tr("error"), tr("Excel Application is not Exist"));
        return ;
    }

    QAxObject *workbooks = NULL;
    QAxObject *workbook = NULL;
    QAxObject *worksheets = NULL;
    QAxObject *worksheet = NULL;

    excel->dynamicCall("SetVisible(bool)", false);
    excel->setProperty("DisplayAlerts", false);
    workbooks = excel->querySubObject("WorkBooks");

    if(QFile::exists(fileName)){
        workbook = workbooks->querySubObject("Open(const QString &)", fileName);
    }else{
        workbooks->dynamicCall("Add");
        workbook = excel->querySubObject("ActiveWorkBook");
    }

    worksheets = workbook->querySubObject("Sheets");
    worksheet = worksheets->querySubObject("Item(int)", 1);

    QAxObject * usedrange = worksheet->querySubObject("UsedRange");
    //    QAxObject * rows = usedrange->querySubObject("Rows");
    //    QAxObject * columns = usedrange->querySubObject("Columns");
    int intRowStart = usedrange->property("Row").toInt();
    int intColStart = usedrange->property("Column").toInt();
    //    int intCols = columns->property("Count").toInt();
    //    int intRows = rows->property("Count").toInt();

    QAxObject *cell = NULL;
    QString str;
    int rowCount = model->rowCount();
    int colCount = model->columnCount();

    //    // 清空数据
    //    for(int i=intRowStart;i <intRowStart + intRows;i++)
    //    {
    //        for(int j=intColStart ;j<intColStart+intCols;j++)
    //        {
    //            QString a;
    //            switch (j) {
    //            case 1: a = "A" + QString::number(i);
    //                break;
    //            case 2: a = "B" + QString::number(i);
    //                break;
    //            case 3: a = "C" + QString::number(i);
    //                break;
    //            case 4: a = "D" + QString::number(i);
    //                break;
    //            }
    //            str = " ";
    //            cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
    //            cell->dynamicCall("SetValue(const QVariant&)", str);
    //        }
    //    }

    //标题行
    //        QAxObject *cell;
    cell=worksheet->querySubObject("Cells(int,int)", 1, 1);
    cell->dynamicCall("SetValue(const QString&)", "地址表");
    cell->querySubObject("Font")->setProperty("Size", 11);
    //合并标题行
    QString cellTitle;
    cellTitle.append("A1:");
    cellTitle.append(QChar(3 + 'A'));
    cellTitle.append(QString::number(1));
    QAxObject *range = worksheet->querySubObject("Range(const QString&)", cellTitle);
    range->setProperty("WrapText", true);
    range->setProperty("MergeCells", true);
    range->setProperty("HorizontalAlignment", -4108);//xlCenter
    range->setProperty("VerticalAlignment", -4108);//xlCenter

    QString a;
    a = "A2";
    cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
    cell->dynamicCall("SetValue(const QVariant&)", "id");
    a = "B2";
    cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
    cell->dynamicCall("SetValue(const QVariant&)", "address");
    a = "C2";
    cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
    cell->dynamicCall("SetValue(const QVariant&)", "warnmessage");
    a = "D2";
    cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
    cell->dynamicCall("SetValue(const QVariant&)", "code");

    // 插入数据
    for (int i = intRowStart; i < intRowStart + rowCount; i++)
    {
        for(int j = intColStart; j < intColStart + colCount; j++)
        {
            QString a;
            switch (j) {
            case 1: a = "A" + QString::number(i+2);
                break;
            case 2: a = "B" + QString::number(i+2);
                break;
            case 3: a = "C" + QString::number(i+2);
                break;
            case 4: a = "D" + QString::number(i+2);
                break;
            }
            str = model->record(i-1).value(j-1).toString();
            cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
            cell->dynamicCall("SetValue(const QVariant&)", str);

        }
    }

    //    QString a = "A1";
    //    cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
    //    cell->dynamicCall("SetValue(const QVariant&)", "123456");
    //    worksheet->dynamicCall(pp.toUtf8());

    workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(fileName));//保存至filepath，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\"，不然一定保存不了。
    workbook->dynamicCall("Close()");//关闭工作簿
    excel->dynamicCall("Quit()");//关闭excel
    delete workbook;
    delete workbooks;
    delete excel;
    QMessageBox::information(0, tr("success"), tr("1111"));
}

void slotImportBtn(){
    QString strFile = QFileDialog::getOpenFileName(this,QStringLiteral("选择Excel文件"),"",tr("Exel file(*.xls *.xlsx)"));
    if (strFile.isEmpty()){
        return;
    }
    QVector<QString> arr;	//存储每一行数据
    QVector<QVector<QString>> array;	//将每行数据汇总，类似于二维数组，但不需要手动定义空间大小，造成内存浪费
    QAxObject excel("Excel.Application"); //加载Excel驱动
    excel.setProperty("Visible", false);//不显示Excel界面，如果为true会看到启动的Excel界面
    QAxObject *work_books = excel.querySubObject("WorkBooks");
    work_books->dynamicCall("Open (const QString&)", strFile); //打开指定文件
    QAxObject *work_book = excel.querySubObject("ActiveWorkBook");
    QAxObject *work_sheets = work_book->querySubObject("Sheets");  //获取工作表
    QString ExcelName;
    static int row_count = 0,column_count = 0;
    int sheet_count = work_sheets->property("Count").toInt();  //获取工作表数目,如下图，有 3 页

    if(sheet_count > 0)
    {
        QAxObject *work_sheet = work_book->querySubObject("Sheets(int)", 1); //设置为 获取第一页 数据
        QAxObject *used_range = work_sheet->querySubObject("UsedRange");
        QAxObject *rows = used_range->querySubObject("Rows");
        row_count = rows->property("Count").toInt();  //获取行数

        QAxObject *column = used_range->querySubObject("Columns");
        column_count = column->property("Count").toInt();  //获取列数
        //获取第一行第一列数据
        ExcelName = work_sheet->querySubObject("Cells(int,int)", 1,1)->property("Value2()").toString();

        //            QAxObject *range = work_sheet->querySubObject("Cells(int,int)",1,1); //获取cell的值
        //            QString strVal = range->dynamicCall("Value2()").toString();


        //获取表格中需要的数据，此处是从第三行第二列获取数据，具体原因看下图理解，根据自己的需求获取信息
        for (int i =3; i <= row_count; i++) {
            for (int j = 1; j <= column_count;j++) {
                //                    QString txt = work_sheet->querySubObject("Cells(int,int)", i,j)->property("Value2()").toString(); //获取单元格内容
                //                    arr.append(txt);
                //                    qDebug()<<"lllllllll"<<txt;
                QAxObject *range = work_sheet->querySubObject("Cells(int,int)",i,j); //获取cell的值
                QString strVal = range->dynamicCall("Value2()").toString();
                arr.append(strVal);
            }
            array.append(arr);
            arr.clear();	//将每行数据存储到array后，清空arr，避免下次循环时数据累计；arr.appeng()是添加不是赋值
        }

        work_book->dynamicCall("Close(Boolean)", false);  //关闭文件
        excel.dynamicCall("Quit(void)");  //退出
    }
    qDebug()<<"lllllllll"<<array;
    if(!array.isEmpty()){
        SqlHelper::insertBlockToWarning(array);
    }
}