QT 数据导出到Excel

小鱼儿 2024-03-24 09:30 47阅读 0赞

![bb38a52aa7844b8d8458b497bb9aec1e.png][]

转载自[QT 数据保存到Excel,并把异常数据标红\_qt保存excel文件\_小华昭的博客-CSDN博客][QT _Excel_qt_excel_-CSDN]

在Qt自带的axcontainer模块中，我们可以使用QAxObject类来将数据保存到Excel中。Qt中将数据保存到Excel通常有两种方式：一种是以Excel格式导出，需要电脑上安装Office软件；另一种是以CSV格式导出，无需安装Office软件。这里采用第一种方式，实现了UI界面数据的展示、将数据保存到Excel以及将Excel中异常数据标记为红色等功能。

.Pro文件

QT += axcontainer

**mainwindow.h**

#ifndef MAINWINDOW_H
    #define MAINWINDOW_H
    #include   <QAxObject>
    #include   <QDir>
    #include   <QFile>
    #include <QDateTime>
    #include <QTableWidget>
    #include <QString>
    #include<QMainWindow>
    #include <QWidget>
    
    namespace Ui {
    class MainWindow;
    }
    
    class MainWindow : public QMainWindow
    {
        Q_OBJECT
    
    public:
        explicit MainWindow(QWidget *parent = nullptr);
        ~MainWindow();
    QDateTime begin_time;
    private slots:
        void data_display();
        void on_SAVE_clicked();
    
        void on_buttonCreate_clicked();
    
    private:
        Ui::MainWindow *ui;
    };
    
    #endif // MAINWINDOW_H

**mianwindow.cpp**  
数据表格显示初始化

ui->MESEXCEL->setColumnCount(3);
        ui->MESEXCEL->setHorizontalHeaderLabels(QStringList() << tr("个数")<< tr("系统时间")<< tr("数据")   );
        ui->MESEXCEL->setColumnWidth(0,100);
        ui->MESEXCEL->setColumnWidth(1,120);
        ui->MESEXCEL->setColumnWidth(2,100);
        ui->MESEXCEL->setFixedWidth(500);
        ui->MESEXCEL->horizontalHeader()->setStretchLastSection(true);
        ui->MESEXCEL->setEditTriggers(QAbstractItemView::NoEditTriggers);
        ui->MESEXCEL->setSelectionBehavior(QAbstractItemView::SelectRows);
        ui->MESEXCEL->horizontalHeader()->setStyleSheet("QHeaderView::section{background:rgb(0,0,0);color: white;}");
        ui->MESEXCEL->setShowGrid(true);
        ui->MESEXCEL->setStyleSheet("QTableWidget::Item{border:0px solid rgb(255,255,255);"
                                   "border-bottom:1px solid rgb(227,23,13);}");
        QHeaderView* headerView = ui->MESEXCEL->verticalHeader();
        headerView->setHidden(true);

数据显示(这里用的随机数来表示)

void MainWindow::data_display()
    {
        begin_time = QDateTime::currentDateTime();
        QString strtime=begin_time.toString("HH:mm:ss.zzz");
        QString data=QString("%1").arg(rand());
        int nCount = ui->MESEXCEL->rowCount();
        ui->MESEXCEL->insertRow(nCount);
        QString strnCount   = QString::number(nCount);
        QTableWidgetItem  *Hutem0 = new QTableWidgetItem;
        Hutem0->setText(strnCount);
        ui->MESEXCEL->setItem(nCount, 0, Hutem0);
        ui->MESEXCEL->item(nCount,0)->setTextAlignment(Qt::AlignCenter);
    
        QTableWidgetItem  *Hutem1 = new QTableWidgetItem;
        Hutem1->setText(strtime);
        ui->MESEXCEL->setItem(nCount, 1, Hutem1);
        ui->MESEXCEL->item(nCount,1)->setTextAlignment(Qt::AlignCenter);
    
        QTableWidgetItem  *Hutem2 = new QTableWidgetItem;
        Hutem2->setText(data);
        ui->MESEXCEL->setItem(nCount, 2, Hutem2);
        ui->MESEXCEL->item(nCount,2)->setTextAlignment(Qt::AlignCenter);
    }

数据保存到Excel里(含异常值判断)

void MainWindow::on_SAVE_clicked()
    {
        QString fileName = QFileDialog::getSaveFileName(this,tr("Excle file"),QString("./test.xlsx"),tr("Excel Files(*.xlsx)"));    //设置保存的文件名
        if(fileName != NULL)
        {
            QAxObject *excel = new QAxObject;
            if(excel->setControl("Excel.Application"))
            {
                excel->dynamicCall("SetVisible (bool Visible)",false);
                excel->setProperty("DisplayAlerts",false);
                QAxObject *workbooks = excel->querySubObject("WorkBooks");
                workbooks->dynamicCall("Add");
                QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
                QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);
                QAxObject *cell;
                int rowCount = ui->MESEXCEL->rowCount();
                int columnCount = ui->MESEXCEL->columnCount();
                for(int i = 1; i <= columnCount ; i++)
                {
                    cell = worksheet->querySubObject("Cells(int,int)", 1, i);
                    cell->setProperty("RowHeight", 40);
                    cell->dynamicCall("SetValue(const QString&)", ui->MESEXCEL->horizontalHeaderItem(i-1)->data(0).toString());
                }
                for(int j = 2; j <= rowCount + 1;j++)
                {
                    for(int k = 1;k <= ui->MESEXCEL->columnCount();k++)
                    {
    
                        cell = worksheet->querySubObject("Cells(int,int)", j, k);
                        cell->dynamicCall("SetValue(const QString&)",ui->MESEXCEL->item(j-2,k-1)->text()+ "\t");
    
                        int value = ui->MESEXCEL->item(j-2,2)->text().toInt();
                        if(value > 20000) //大于20000标红
                        {
                            QAxObject*interior = cell->querySubObject("Interior");
                            interior->setProperty("Color", QColor(Qt::red));
                        }
                    }
                }
    
                workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(fileName));
                workbook->dynamicCall("Close()");
                excel->dynamicCall("Quit()");
                delete excel;
                excel = NULL;
    }
        }
    }

ui界面([QTableWidget][])

![3beb0a03929f4a039cec79d36a86cda3.png][]

![3e9d4418e2ec46deae94e4249ed160af.png][]

![9e4fb4c14fe1470f98b4da3ef3daa70b.png][]

[bb38a52aa7844b8d8458b497bb9aec1e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/fcb8dfce36d24459bc6fe81e93374c35.png
[QT _Excel_qt_excel_-CSDN]: https://blog.csdn.net/qq_63538338/article/details/129637313
[QTableWidget]: https://so.csdn.net/so/search?q=QTableWidget&spm=1001.2101.3001.7020
[3beb0a03929f4a039cec79d36a86cda3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/0300df31202141dc8d3b6c85b9ace1a8.png
[3e9d4418e2ec46deae94e4249ed160af.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/bc4a85a95d334cf4812e5d622d6fa65d.png
[9e4fb4c14fe1470f98b4da3ef3daa70b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/ce8bd2ac4ba4460ebcedc1d9694f77a2.png