qt操作excel文件

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QMap>
#include <QFile>
QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = nullptr);
    void readExcels(QStringList &);
    void writeExcel(QMap<QString,int>&,QString &);
    ~MainWindow();

private slots:
    void on_pushButton_clicked();
    void on_bt_save_csv_clicked();

private:
    Ui::MainWindow *ui;
    QMap<QString,int> m_foodCount;

    QFile m_saveFile;
};
#endif // MAINWINDOW_H

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include "QFileDialog"
#include "QAxObject"
#include "QDebug"
#include "QMessageBox"

using namespace std;

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);

}

void MainWindow::on_pushButton_clicked()
{
    if(ui->lineEdit->text().isEmpty()){
        //弹出提示框
        QMessageBox::information(NULL, "提示", "请输入生成文件名称！", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
        return;
    }

    QStringList strFileList = QFileDialog::getOpenFileNames(this,QStringLiteral("选择Excel文件"),"E:\\Doc\\FoodCount",tr("Exel file(*.xls *.xlsx)"));
    if (strFileList.isEmpty()){
        return;
    }

    readExcels(strFileList);

    //测试打印
    for (QMap<QString, int>::const_iterator it = m_foodCount.constBegin(); it != m_foodCount.constEnd(); it++) {
        qDebug() << it.key() << ": " << it.value();
    }

    //写进excle中
    QString inputPath = ui->lineEdit->text();
    //QString filePath = "E:\\Doc\\FoodCount\\308盒饭统计";
    QString filePath = "E:\\Doc\\FoodCount\\"+inputPath;
    if(inputPath.isEmpty()){
        //弹出提示框
        QMessageBox::information(NULL, "提示", "请输入生成文件名称！", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);
        return;
    }else {
        writeExcel(m_foodCount,filePath);

        //弹出提示框
        QMessageBox::information(NULL, "提示", "文件输出完成！", QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);

        ui->lineEdit->clear();
    }
}

void MainWindow::on_bt_save_csv_clicked()
{

    //创建一个file文件
    QFileDialog fileDialog;
    int length = -1;
    QString file_top = "";
    QString fileName = fileDialog.getSaveFileName(this,tr("Open File"),"E:\Code\qt\OperateExcel",tr("CSV file(*.csv)"));
    if(fileName == "")
    {
        return;
    }
    QFile file(fileName);//可以自己选择路径来保存文件名
    if(!file.open(QIODevice::WriteOnly | QIODevice::Text))
    {
        QMessageBox::warning(this,tr("错误"),tr("打开文件失败"));
        return;
    }
    else
    {
        file_top = "SettingFlow,Token,Flow,ValveValue,Pressure,FlowBack\r\n";
        length = file.write(file_top.toLatin1(), file_top.length());
        qDebug("111 length = %d\n", length);

        if(length == -1){
            qDebug()<<"写入文件失败";
        }else{
            qDebug()<<"写入文件成功";
        }
        QMessageBox::warning(this,tr("提示"),tr("保存文件成功"));
        file.close();
    }
}


void MainWindow::readExcels(QStringList &strFileList)
{
    for (const QString &fileName : strFileList)
    {
        QAxObject excel("Excel.Application"); //加载Excel驱动
        excel.setProperty("Visible", false);//不显示Excel界面，如果为true会看到启动的Excel界面
        QAxObject *work_books = excel.querySubObject("WorkBooks");
        work_books->dynamicCall("Open (const QString&)", fileName); //打开指定文件
        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,2)->property("Value").toString();
            //获取表格中需要的数据
            for (int i =2; i <= row_count; i++) {
                int total = 0;
                QString wanFan = "";
                int iWuFan = 0;
                int iWanFan = 0;
                QString name = work_sheet->querySubObject("Cells(int,int)",i,3)->property("Value").toString();//获取订饭者的姓名
                QString wuFan = work_sheet->querySubObject("Cells(int,int)",i,4)->property("Value").toString();//午饭
                if (wuFan.contains("不")){
                    iWuFan = 0;
                }else {
                    iWuFan = 1;
                }
                if(column_count >= 5){
                    wanFan = work_sheet->querySubObject("Cells(int,int)",i,5)->property("Value").toString();//晚饭
                    if (wanFan.contains("不")){
                        iWanFan = 0;
                    }else {
                        iWanFan = 1;
                    }
                }
                total = iWuFan + iWanFan;

                //map插入之前先查找有没有key
                if(m_foodCount.find(name) != m_foodCount.end()){//存在key值
                    int count = m_foodCount.value(name) + total;//之前map中存的数量
                    m_foodCount.insert(name,count);//调用insert会覆盖之前的数据
                }else {
                    m_foodCount.insert(name,total);
                }
            }

            work_book->dynamicCall("Close(Boolean)", false);  //关闭文件
            excel.dynamicCall("Quit(void)");  //退出
        }
    }
}

void MainWindow::writeExcel(QMap<QString, int> &map,QString &filePath)
{
    QAxObject* excel = new QAxObject("ket.Application");
    excel->setControl("ket.Application");       // wps程序（需要安装wps）
    //excel->setControl("Excel.Application");   // excel文件（需要激活）
    excel->setProperty("Visible", false);       // 不显示窗体
    excel->setProperty("DisplayAlerts", false); // 不显示任何警告信息。如果为true, 那么关闭时会出现类似"文件已修改，是否保存"的提示
    QAxObject* workbooks = excel->querySubObject("WorkBooks");  // 获取工作簿(excel文件)集合
    workbooks->dynamicCall("Add");              // 新建一个工作簿
    QAxObject* workbook = excel->querySubObject("ActiveWorkBook");  //获取当前工作簿
    QAxObject* sheet = workbook->querySubObject("WorkSheets(int)", 1);//获取工作表集合的工作表1，即sheet1
    QAxObject* user_range = sheet->querySubObject("Range(const QString&)", "A1:B60");//写数据在表格的范围（A1：A列1行位置；D100：D列100行位置）
    if (NULL == user_range || user_range->isNull()) {
        return;
    }
    QList<QVariant> dataList;
    dataList.push_back(QList<QVariant>() << "姓名" << "数量");
    for (QMap<QString, int>::const_iterator it = map.constBegin(); it != map.constEnd(); it++) {
        dataList.push_back(QList<QVariant>() << it.key() << it.value());
    }
    user_range->dynamicCall("SetValue(const QVariant&)", QVariant(dataList));
    workbook->dynamicCall("SaveAs(const QString&)", filePath); // 保存到filepath,路径需要把"/"改为"\\"
    workbook->dynamicCall("Close (Boolean)", false);  // 关闭文件
    excel->dynamicCall("Quit(void)");   // 关闭excel
}

MainWindow::~MainWindow()
{
    delete ui;
}

写入csv

//创建一个file文件
    QFileDialog fileDialog;
    int length = -1;
    QString file_top = "";
    QString fileName = fileDialog.getSaveFileName(this,tr("Open File"),"E:\Code\qt\OperateExcel",tr("CSV file(*.csv)"));
    if(fileName == "")
    {
        return;
    }
    QFile file(fileName);//可以自己选择路径来保存文件名
    if(!file.open(QIODevice::WriteOnly | QIODevice::Text))
    {
        QMessageBox::warning(this,tr("错误"),tr("打开文件失败"));
        return;
    }
    else
    {
        file_top = "SettingFlow,Token,Flow,ValveValue,Pressure,FlowBack\r\n";
        length = file.write(file_top.toLatin1(), file_top.length());
        qDebug("111 length = %d\n", length);

        if(length == -1){
            qDebug()<<"写入文件失败";
        }else{
            qDebug()<<"写入文件成功";
        }
        QMessageBox::warning(this,tr("提示"),tr("保存文件成功"));
        file.close();
    }