QT 数据保存到Excel,并把异常数据标红

在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>

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();
QDateTime begin_time;
private slots:
    void data_display();
    void on_SAVE_clicked();

private:
    Ui::Widget *ui;
};

#endif // WIDGET_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);
}

异常值判断，并标红(这里模拟的是值大于20000时，被标红)

if(value > 20000)
  {
     QAxObject*interior = cell->querySubObject("Interior");
     interior->setProperty("Color", QColor(Qt::red));
  }

数据保存到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)
                    {
                        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)

运行后的结果