QT读写excel

最新推荐文章于 2024-05-10 23:46:17 发布

FlechazoCLF

最新推荐文章于 2024-05-10 23:46:17 发布

阅读量945

点赞数

分类专栏：嵌入式软件开发代码库文章标签： qt excel 单片机

本文链接：https://blog.csdn.net/qianshang52013/article/details/132548809

版权

嵌入式软件开发代码库专栏收录该内容

21 篇文章 2 订阅

订阅专栏

最近呢在做一个生产工具，这样的

需要将这表格里的元素存下来，支持导入导出

首先需要这几个头文件

#include "QFileDialog"
#include "QAxObject"
#include <QObject>
#include "QFile"

读取excel

//读取excel
void MainWindow::vexcelReadInfo(QString button){
    static QString strFile = QFileDialog::getOpenFileName(this,QStringLiteral("选择Excel文件"),"","Exel file(*.xls *.xlsx)");
    if (strFile.isEmpty()){
        return;
    }
    //存储每一行数据
    QVector<QString> excelArrRow;
    //将每行数据汇总，类似于二维数组，但不需要手动定义空间大小，造成内存浪费
    QVector<QVector<QString>> excelArray;
    //加载Excel驱动
    QAxObject excel("Excel.Application");
    //不显示Excel界面，如果为true会看到启动的Excel界面
    excel.setProperty("Visible", false);
    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();

    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();

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

        work_book->dynamicCall("Close(Boolean)", false);  //关闭文件
        excel.dynamicCall("Quit(void)");  //退出
    }
    int showcurrentRow = ui->tableWidget_factory_log->rowCount();;
    volatile int showcurrentColumn=0;


    for (QVector<QVector<QString>>::iterator iterRow=excelArray.begin();iterRow!=excelArray.end();iterRow++)
    {
        showcurrentColumn=0;
        if(button=="读取全部"){
            //插入1行
            ui->tableWidget_factory_log->insertRow(showcurrentRow);
        }

        for (QVector<QString>::iterator iter=(*iterRow).begin();iter!=(*iterRow).end();iter++)
        {
            QTableWidgetItem *item = new QTableWidgetItem();
            QString iterstr = *iter;
            item->setText(iterstr);
            if(button=="读取全部"){
            ui->textBrowser->append(*iter);
            ui->tableWidget_factory_log->setItem(showcurrentRow, showcurrentColumn, item);
            }
            //把最后1行刷入控件
            if((iterRow+1)==excelArray.end()){
                vexcelReadInfoTOlineEdit(showcurrentColumn,iterstr);
            }

            showcurrentColumn++;
        }
        showcurrentRow++;
    }
}

写入excel

//写入数据
bool MainWindow::bexcelSaveInfo(){

    //加载Excel驱动
    QAxObject* excel = new QAxObject("Excel.Application", this);
    if(excel->isNull()){
        if(excel != NULL){
            excel->dynamicCall("Quit()");
            delete excel;
        }
        QMessageBox::critical(0, "error", "Excel Application is not Exist");
        return false;
    }

    excel->dynamicCall("SetVisible(bool)", false); // 设置Excel应用程序不可见
    //不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
    excel->setProperty("DisplayAlerts", false);

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

    static int row_count = 0,column_count = 0;

    workbooks = excel->querySubObject("WorkBooks");
    if(QFile::exists(excel_save_path)){
        workbook = workbooks->querySubObject("Open(const QString &)", excel_save_path);
    }else{
        //新建一个工作簿
        workbooks->dynamicCall("Add");
        //获取当前工作簿
        workbook = excel->querySubObject("ActiveWorkBook");
    }
    //获取工作表集合
    worksheets = workbook->querySubObject("Sheets");
    //获取工作表集合的工作表1，即sheet1
    worksheet = worksheets->querySubObject("Item(int)", 1);

    //标题行
    QAxObject *cell;
    cell=worksheet->querySubObject("Cells(int,int)", 1, 1);
    cell->dynamicCall("SetValue(const QString&)", "生产记录表");
    cell->querySubObject("Font")->setProperty("Size", 18);

    //合并标题行
    QString cellTitle;
    cellTitle.append("A1:");
    cellTitle.append(QChar(9 + '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
    QAxObject *cellA,*cellB,*cellC,*cellD,*cellE,*cellF,*cellG,*cellH,*cellI;
    //设置标题 从第2行开始
    int cellrow=2;
    QString A="A"+QString::number(cellrow);//设置要操作的单元格，如A1
    QString B="B"+QString::number(cellrow);
    QString C="C"+QString::number(cellrow);
    QString D="D"+QString::number(cellrow);
    QString E="E"+QString::number(cellrow);
    QString F="F"+QString::number(cellrow);
    QString G="G"+QString::number(cellrow);
    QString H="H"+QString::number(cellrow);
    QString I="I"+QString::number(cellrow);

    cellA = worksheet->querySubObject("Range(QVariant, QVariant)",A);//获取单元格
    cellB = worksheet->querySubObject("Range(QVariant, QVariant)",B);
    cellC = worksheet->querySubObject("Range(QVariant, QVariant)",C);
    cellD = worksheet->querySubObject("Range(QVariant, QVariant)",D);
    cellE = worksheet->querySubObject("Range(QVariant, QVariant)",E);
    cellF = worksheet->querySubObject("Range(QVariant, QVariant)",F);
    cellG = worksheet->querySubObject("Range(QVariant, QVariant)",G);
    cellH = worksheet->querySubObject("Range(QVariant, QVariant)",H);
    cellI = worksheet->querySubObject("Range(QVariant, QVariant)",I);

    cellA->dynamicCall("SetValue(const QVariant&)",QVariant("序号"));//设置单元格的值
    cellB->dynamicCall("SetValue(const QVariant&)",QVariant("项目"));
    cellC->dynamicCall("SetValue(const QVariant&)",QVariant("生产批号"));
    cellD->dynamicCall("SetValue(const QVariant&)",QVariant("ID号"));
    cellE->dynamicCall("SetValue(const QVariant&)",QVariant("Lora信道"));
    cellF->dynamicCall("SetValue(const QVariant&)",QVariant("LoraAES"));
    cellG->dynamicCall("SetValue(const QVariant&)",QVariant("Lora空速"));
    cellH->dynamicCall("SetValue(const QVariant&)",QVariant("电机电流值"));
    cellI->dynamicCall("SetValue(const QVariant&)",QVariant("灯电流值"));

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


    for(int i=0;i<ui->tableWidget_factory_log->rowCount();i++){
        //从第row_count行开始
        QString A="A"+QString::number(i+row_count+1);//设置要操作的单元格，如A1
        QString B="B"+QString::number(i+row_count+1);
        QString C="C"+QString::number(i+row_count+1);
        QString D="D"+QString::number(i+row_count+1);
        QString E="E"+QString::number(i+row_count+1);
        QString F="F"+QString::number(i+row_count+1);
        QString G="G"+QString::number(i+row_count+1);
        QString H="H"+QString::number(i+row_count+1);
        QString I="I"+QString::number(i+row_count+1);
        cellA = worksheet->querySubObject("Range(QVariant, QVariant)",A);//获取单元格
        cellB = worksheet->querySubObject("Range(QVariant, QVariant)",B);
        cellC = worksheet->querySubObject("Range(QVariant, QVariant)",C);
        cellD = worksheet->querySubObject("Range(QVariant, QVariant)",D);
        cellE = worksheet->querySubObject("Range(QVariant, QVariant)",E);
        cellF = worksheet->querySubObject("Range(QVariant, QVariant)",F);
        cellG = worksheet->querySubObject("Range(QVariant, QVariant)",G);
        cellH = worksheet->querySubObject("Range(QVariant, QVariant)",H);
        cellI = worksheet->querySubObject("Range(QVariant, QVariant)",I);

        cellA->dynamicCall("SetValue(const QVariant&)",ui->tableWidget_factory_log->item(i,0)->text());//设置单元格的值
        range = worksheet->querySubObject("Range(const QString&)", A); //设置A单元格元素居中显示
        range->setProperty("HorizontalAlignment", -4108);//xlCenter
        range->setProperty("VerticalAlignment", -4108);//xlCenter

        cellB->dynamicCall("SetValue(const QVariant&)",ui->tableWidget_factory_log->item(i,1)->text());
        range = worksheet->querySubObject("Range(const QString&)", B); //设置B单元格元素居中显示
        range->setProperty("HorizontalAlignment", -4108);//xlCenter
        range->setProperty("VerticalAlignment", -4108);//xlCenter

        cellC->dynamicCall("SetValue(const QVariant&)",ui->tableWidget_factory_log->item(i,2)->text());
        range = worksheet->querySubObject("Range(const QString&)", C); //设置C单元格元素居中显示
        range->setProperty("HorizontalAlignment", -4108);//xlCenter
        range->setProperty("VerticalAlignment", -4108);//xlCenter

        cellD->dynamicCall("SetValue(const QVariant&)",ui->tableWidget_factory_log->item(i,3)->text());
        range = worksheet->querySubObject("Range(const QString&)", D); //设置D单元格元素居中显示
        range->setProperty("HorizontalAlignment", -4108);//xlCenter
        range->setProperty("VerticalAlignment", -4108);//xlCenter

        cellE->dynamicCall("SetValue(const QVariant&)",ui->tableWidget_factory_log->item(i,4)->text());
        range = worksheet->querySubObject("Range(const QString&)", E); //设置E单元格元素居中显示
        range->setProperty("HorizontalAlignment", -4108);//xlCenter
        range->setProperty("VerticalAlignment", -4108);//xlCenter

        cellF->dynamicCall("SetValue(const QVariant&)",ui->tableWidget_factory_log->item(i,5)->text());
        range = worksheet->querySubObject("Range(const QString&)", F); //设置E单元格元素居中显示
        range->setProperty("HorizontalAlignment", -4108);//xlCenter
        range->setProperty("VerticalAlignment", -4108);//xlCenter

        cellG->dynamicCall("SetValue(const QVariant&)",ui->tableWidget_factory_log->item(i,6)->text());
        range = worksheet->querySubObject("Range(const QString&)", G); //设置E单元格元素居中显示
        range->setProperty("HorizontalAlignment", -4108);//xlCenter
        range->setProperty("VerticalAlignment", -4108);//xlCenter

        cellH->dynamicCall("SetValue(const QVariant&)",ui->tableWidget_factory_log->item(i,7)->text());
        range = worksheet->querySubObject("Range(const QString&)", H); //设置E单元格元素居中显示
        range->setProperty("HorizontalAlignment", -4108);//xlCenter
        range->setProperty("VerticalAlignment", -4108);//xlCenter

        cellI->dynamicCall("SetValue(const QVariant&)",ui->tableWidget_factory_log->item(i,8)->text());
        range = worksheet->querySubObject("Range(const QString&)", I); //设置E单元格元素居中显示
        range->setProperty("HorizontalAlignment", -4108);//xlCenter
        range->setProperty("VerticalAlignment", -4108);//xlCenter
    }

    //保存至filepath，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\"，不然一定保存不了。
    workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(excel_save_path));
    workbook->dynamicCall("Close()");//关闭工作簿
    excel->dynamicCall("Quit()");//关闭excel
    delete excel;
    excel=NULL;
    QMessageBox::information(NULL,"","控制器数据导出完成");

    return true;
}