windows下 Qt 操作xlsx 和 csv

需求：

工作中遇到一个需求，有两张表格，一个xlsx表，一个csv表格，格式如下：

以csv表格中船台标识为基础，读取xlsx中的数据，如果存在该MMSI则把船名写道csv中对应船名的后面，不存在的话，则添加进csv中，合并两个表格。由于表格数据非常多，有十几万个，所以只能通过程序判断。

提前声明：该代码仅供参考，速度很慢。建议用插件QtXlsxWriter来读写xlsx文件

代码：

pro文件

QT       += core gui

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

CONFIG += c++17

CONFIG += qaxcontainer

# You can make your code fail to compile if it uses deprecated APIs.
# In order to do so, uncomment the following line.
#DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000    # disables all the APIs deprecated before Qt 6.0.0

SOURCES += \
    main.cpp \
    widget.cpp

HEADERS += \
    widget.h

FORMS += \
    widget.ui

# Default rules for deployment.
qnx: target.path = /tmp/$${TARGET}/bin
else: unix:!android: target.path = /opt/$${TARGET}/bin
!isEmpty(target.path): INSTALLS += target

widget.h

#ifndef WIDGET_H
#define WIDGET_H

#include <QWidget>

QT_BEGIN_NAMESPACE
namespace Ui {
class Widget;
}
QT_END_NAMESPACE

class Widget : public QWidget
{
    Q_OBJECT

public:
    Widget(QWidget *parent = nullptr);
    ~Widget();

    void readXls(const QString& file_path);
    void read_csv(const std::string& file_path);
    void write_csv(const std::string& file_path);
    void judge();

private:
    Ui::Widget *ui;

    std::vector<std::string> path_point;
    std::vector<std::string> path_xlsx;
    // QStringList str;

    std::vector<std::string> path_point_new;
    std::vector<std::string> path_xlsx_new;
    QStringList str_new;
};
#endif // WIDGET_H

widget.cpp

#include "widget.h"
#include "ui_widget.h"
#include <QFileDialog>
#include <QMessageBox>
#include <qfiledialog.h>
#include <ActiveQt/qaxobject.h>
#include <QDebug>
#include <iostream>
#include <fstream>
#include <sstream>
#include <algorithm>

Widget::Widget(QWidget *parent)
    : QWidget(parent)
    , ui(new Ui::Widget)
{
    ui->setupUi(this);
    readXls("C:/Users/85720/Desktop/1.xlsx");
    read_csv("C:/Users/85720/Desktop/船名列表.CSV");
    judge();
    write_csv("C:/Users/85720/Desktop/new.CSV");
}

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

// 读取csv文件
void Widget::read_csv(const std::string& file_path)
{
    std::cout<<"文件路径: "<< file_path<<"\n";
    std::ifstream csv_data(file_path, std::ios::in);
    std::string line;

    if (!csv_data.is_open()) {
        std::cout << "Error: failed to open file\n";
        std::exit(1);
    }

    std::istringstream sin;  // 将整行字符串读入到字符串流中
    std::vector<std::string> words;
    std::string word;
    // std::vector<std::vector<std::string>> path_points;
    // std::vector<std::string> path_point;
    // 读取标题行
    std::getline(csv_data, line);
    // 读取数据
    while (std::getline(csv_data, line)) {
        sin.clear();
        sin.str(line);
        words.clear();
        // std::vector<std::string> path_point;
        while (std::getline(sin, word, ',')) {  // 将字符串流sin中的字符读到word中，以字符'逗号'为分隔符
            // double value = std::atof(word.c_str());
            // qDebug() << QString::fromLocal8Bit(word.data());;
            path_point.push_back(word);
        }
        // path_points.push_back(path_point);
    }

    csv_data.close();  // 关闭文件

    // for(int i = 0; i < path_point.size(); i++)
    // {
    //     qDebug() << QString::fromLocal8Bit(path_point[i].data());
    // }
    qDebug() << "read csv over!";
    qDebug() << path_point.size();
}


void Widget::readXls(const QString& file_path)
{
    //读取excel文件
    QString readFile = file_path/*QFileDialog::getOpenFileName(this, QStringLiteral("选择Excel文件"), "", tr("Exel file(*.xls *.xlsx)"))*/;
    int row_count, col_count;
    // QStringList str;
    if (!readFile.isEmpty())
    {
        QAxObject excel("Excel.Application");
        excel.setProperty("Visible", false); //不显示Excel界面，如果为true会看到启动的Excel界面
        QAxObject* work_books = excel.querySubObject("WorkBooks");
        work_books->dynamicCall("Open (const QString&)", readFile);//打开指定文件
        QAxObject* work_book = excel.querySubObject("ActiveWorkBook");
        QAxObject* work_sheets = work_book->querySubObject("Sheets");  //获取工作表，Sheets也可换用WorkSheets
        int sheet_count = work_sheets->property("Count").toInt();  //获取工作表数目

        if (sheet_count > 0)
        {
            qDebug() << "begin read xlsx!";
            QAxObject* work_sheet = work_book->querySubObject("Sheets(int)", 1); //表格sheet，参数 "1" 代表第1个sheet
            QAxObject* used_range = work_sheet->querySubObject("UsedRange");
            QAxObject* rows = used_range->querySubObject("Rows");
            QAxObject* colums = used_range->querySubObject("Columns");
            row_count = rows->property("Count").toInt();  //获取行数
            col_count = colums->property("Count").toInt(); //获取列数
            //QString txt = work_sheet->querySubObject("Cells(int,int)", i, 1)->property("Value").toString(); //获取单元格内容
            for (int i = 2; i <= row_count; i++)
            {
                // for (int j = 1; j <= col_count; j++)
                // {
                //     QString cell = work_sheet->querySubObject("Cells(int,int)", i, j)->property("Value").toString(); //获取表格内容
                //     str.append(cell);
                //     qDebug() << cell;
                // }
                QString cell = work_sheet->querySubObject("Cells(int,int)", i, 1)->property("Value").toString(); //获取表格内容
                path_xlsx.push_back(cell.toLocal8Bit().data());
                // str.append(cell);
                // qDebug() << cell;
                cell = work_sheet->querySubObject("Cells(int,int)", i, 3)->property("Value").toString(); //获取表格内容
                path_xlsx.push_back(cell.toLocal8Bit().data());
                // str.append(cell);
                // qDebug() << cell;

            }

            work_book->dynamicCall("Close()", false);  //关闭文件
            excel.dynamicCall("Quit()");  //退出
        }
    }
    else
    {
        QMessageBox::warning(this, "提示 ", "文件路径为空！");
    }
    qDebug() << "read xlsx over!";
    qDebug() << path_xlsx.size();
}

void Widget::judge()
{
    std::vector<int> vxlsIdx;
    for(int i = 0; i < path_point.size(); i+=2)
    {
       bool flag = false;
       std::string str = path_point[i];
       std::string str2 = path_point[i+1];
       path_point_new.push_back(str);
       path_point_new.push_back(str2);
       for(int j = 0; j < path_xlsx.size(); j+=2)
       {
           if(str == path_xlsx[j])
           {
               flag = true;
               vxlsIdx.push_back(j);
               path_point_new.push_back(path_xlsx[j+1]);
               break;
           }
       }
       if(!flag)
           path_point_new.push_back("");
    }

    //---------------------------------------------
    for(int i = 0; i < path_xlsx.size(); i+=2)
    {
        auto it = std::find(vxlsIdx.begin(),vxlsIdx.end(),i);
        if(it == vxlsIdx.end()) //没找到
        {
            path_point_new.push_back(path_xlsx[i]);
            path_point_new.push_back(path_xlsx[i+1]);
            path_point_new.push_back("");
        }
    }
    qDebug() << "judge over!";
    qDebug() << path_point_new.size();
}

void Widget::write_csv(const std::string& file_path)
{
    std::cout << "写入路径为: " << file_path << "\n";
    std::ofstream out_file(file_path,std::ios::out);  // 默认通过iso::out方式进行写入，当文件不存在时会进行创建
    if (out_file.is_open()) { //判定文件是否打开
        // 写入标题行
        out_file << "船台标识" << ',' << "船名中文" << std::endl;

        // 写入数据
        for (int i = 0; i < path_point_new.size(); i+=3) {
            out_file << path_point_new[i] << ',' << path_point_new[i + 1] << ','
                     << path_point_new[i + 2] << std::endl;
        }

        out_file.close();
    }else{
        std::cout<<"文件无法打开\n";
    }
    qDebug() << "write csv over!";
}

void writeXls()
{
    // //写出excel文件
    // QAxObject writeexcel("Excel.Application");
    // writeexcel.setProperty("Visible", false);
    // writeexcel.setProperty("DisplayAlerts", false);
    // QAxObject* writework_books = writeexcel.querySubObject("WorkBooks");
    // writework_books->dynamicCall("Add");
    // QAxObject* writework_book = writeexcel.querySubObject("ActiveWorkBook");
    // QAxObject* writework_sheets = writework_book->querySubObject("Sheets");
    // QAxObject* writework_sheet = writework_sheets->querySubObject("Item(int)",1);

    // QVariantList mlist;
    // for (int i = 0; i < row_count; i++)
    // {
    //     QVariantList tempVarRow;
    //     for (int j = 0; j < col_count; j++)
    //     {
    //         tempVarRow << str[i * col_count + j]; //将表格内容写出到tempVarRow中
    //     }
    //     mlist.append(QVariant(tempVarRow));
    // }
    // QAxObject* pRange = writework_sheet->querySubObject("Range(QString)", "A1");
    // pRange = pRange->querySubObject("Resize(int,int)", row_count, col_count);
    // pRange->setProperty("Value", mlist); //写出文件

    // // 导出excel文件路径，导出文件格式最好与源文件格式相同
    // // 如读取的文件格式为.xlsx，则导出文件格式最好也为.xlsx，否则打开导出文件可能出现格式兼容问题
    // QString writefile = QFileDialog::getSaveFileName(this, QStringLiteral("选择保存路径"), "", tr("Excel file(*.xls *.xlsx)"));
    // if (writefile.isEmpty())
    // {
    //     QMessageBox::warning(this, "提示", "导出路径为空！");
    // }
    // writework_book->dynamicCall("SaveAs(const Qstring&)", QDir::toNativeSeparators(writefile)); //写出文件
    // writework_book->dynamicCall("Close()");
    // writeexcel.dynamicCall("Quit()");

    // QMessageBox::information(this, "提示", "导出完成！");


}