excel扒数据到ini文件小工具

一、源码

注释很详细，就不讲了

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QVariant>

QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

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

private:
    Ui::MainWindow *ui;

private:
    void readFile();
    bool isXlsxFile(const QString &filePath);

    void readFileIni();
    bool isIniFile(const QString &filePath);
    void selectFileIni();
    void chooseFileIni();
    void setUIIni(const QStringList keys, const QVariant value);
    bool isSectionEmpty();

    void matchData();
    bool isFilesOpenEmpty();
    void dataHandle();
    void brushUIIni();


private:
    QList<QList<QVariant>> xls_data;
    QString filepathini;
    QString filepathexcel;
    QStringList sectionKeys; // 用于存储特定节的所有键
    QVariant valueIni;
    QStringList keysIniMatch;
    QVariant valuesIniMatch;
};
#endif // MAINWINDOW_H

#include "mainwindow.h"
#include "ui_mainwindow.h"

#include <QDir>
#include <QFile>
#include <QFileDialog>
#include <QMessageBox>
#include <QFileInfo>
#include <QDebug>
#include <QAxObject>
#include <QVariant>
#include <QMap>
#include <QStandardItemModel>
#include <QSettings>

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

    readFile();
    selectFileIni();
    matchData();
}

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

void MainWindow::readFile()
{
    connect(ui->pushButton, &QPushButton::clicked, this, [=](){
        QString currentpath = QDir::homePath();
        QString openfiletitle = "请选择文件";
        QString filefilter = "all(*.*)";
        filepathexcel = QFileDialog::getOpenFileName(this, openfiletitle, currentpath, filefilter);

        if(filepathexcel.isEmpty())
        {
            QMessageBox::warning(this, "警告！！", "文件夹不能为空！");
            return;
        }

        QFileInfo info(filepathexcel);//获取文件信息
        ui->lineEdit->setText(info.fileName());

        //excel表格读取
        QAxObject *excel = new QAxObject(this);
        excel->dynamicCall("Visible(bool)", false);
        if (isXlsxFile(filepathexcel))
        {
            excel->setControl("Excel.Application");// 加载 Microsoft Excel 控件
        }
        else
        {
            excel->setControl("ket.Application");  // 加载 WPS Excel 控件
        }

        excel->setProperty("Visible", false);  // 不显示 Excel 窗体
        QAxObject* workBooks = excel->querySubObject("WorkBooks");  //获取工作簿集合
        workBooks->dynamicCall("Open(const QString&)", filepathexcel); //打开打开已存在的工作簿
        QAxObject* workBook = excel->querySubObject("ActiveWorkBook"); //获取当前工作簿

        QAxObject* sheets = workBook->querySubObject("Sheets");  //获取工作表集合，Sheets也可换用WorkSheets
        QAxObject* sheet = workBook->querySubObject("WorkSheets(int)", 1);//获取工作表集合的工作表1，即sheet1

        //获取该sheet的使用范围对象（一般读取 Excel 时会选取全部范围）
        QAxObject* usedRange = sheet->querySubObject("UsedRange");

        /*
        //获取 sheet 的指定范围（一般写入数据时，会选取指定范围）
        QAxObject* usedRange = sheet->querySubObject("Range(const QString)", "A1:C12");

        //获取 sheet 的指定范围（一般修改数据时，会选取指定单元格）
        QAxObject* usedRange = sheet->querySubObject("Range(QVariant,QVariant)", "A6");
        */

        //读取单元格内容
        QVariant var = usedRange->dynamicCall("Value");
        delete usedRange;

        //数据转换
        QVariantList varRows = var.toList();
        if (varRows.isEmpty()) {
            return;
        }

        const int rowCount = varRows.size();

        QVariantList rowData;

        for (int i = 0; i < rowCount; i++){
            rowData = varRows[i].toList();
            int clumnCount = rowData.size();
            if(rowData.count() > clumnCount)
            {
                clumnCount = rowData.count();
            }
            xls_data.push_back(rowData);
        }

        //写进UI表格
        // 创建模型
        QStandardItemModel *model = new QStandardItemModel(rowCount, rowData.size(), this);
        for (int i = 0; i < xls_data.count(); i++) {
            for (int j = 0; j < xls_data.at(i).count(); j++) {
                QStandardItem *item = new QStandardItem(QString(xls_data.at(i).at(j).toByteArray()));
                model->setItem(i, j, item);
//                delete item;
            }
        }
        ui->tableView->setModel(model);

        // 隐藏行号和列号
        ui->tableView->verticalHeader()->hide();
        ui->tableView->horizontalHeader()->hide();
    });
}

bool MainWindow::isXlsxFile(const QString &filePath)
{
    QFileInfo fileInfo(filePath);
    QString extension = fileInfo.suffix().toLower();

    // 列出常见的文本文件扩展名
    static const QStringList textExtensions = {"xlsx", "xls", "xlsm"};

    return textExtensions.contains(extension);
}

void MainWindow::selectFileIni()
{
    //选择ini文件
    connect(ui->pushButton_ini, &QPushButton::clicked, this, [=](){
        if(isSectionEmpty())
        {
            QMessageBox::warning(this, "警告", "请先输入查询的节名称！！！");
            return;
        }
        else
        {
            chooseFileIni();
            readFileIni();
        }
    });
}

void MainWindow::chooseFileIni()
{
    QString currentpath = QDir::homePath();
    QString openfiletitle = "请选择文件";
    QString filefilter = "all(*.*)";
    filepathini = QFileDialog::getOpenFileName(this, openfiletitle, currentpath, filefilter);

    if(filepathini.isEmpty())
    {
        QMessageBox::warning(this, "警告！！", "文件夹不能为空！");
        return;
    }
    else
    {
        //是否为ini文件
        if(isIniFile(filepathini))
        {
            QFileInfo info(filepathini);//获取文件信息
            ui->lineEdit_ini->setText(info.fileName());
        }
    }
}

void MainWindow::readFileIni()
{
    //创建qsettings对象
    QSettings settings(filepathini, QSettings::IniFormat);

    //读取
    const QString sectionName = ui->lineEdit_section->text();

    // 读取所有键的前缀
    QStringList allKeys = settings.allKeys();

    // 遍历所有键，找到属于特定节的键
    for (const QString &key : allKeys) {
        if (key.startsWith(sectionName)) {
            // 去掉节前缀
            QString cleanKey = key.mid(sectionName.size() + 1);
            sectionKeys.append(cleanKey);
        }
    }

    // 遍历特定节的所有键并读取对应的值
    foreach (const QString &key, sectionKeys) {
        valueIni = settings.value(key);
//        qDebug() << key << ": " << value.toString();
        setUIIni(sectionKeys, valueIni);
    }
}

bool MainWindow::isIniFile(const QString &filePath)
{
    QFileInfo fileInfo(filePath);
    QString extension = fileInfo.suffix().toLower();

    // 列出常见的文本文件扩展名
    static const QStringList textExtensions = {"ini"};

    return textExtensions.contains(extension);
}

void MainWindow::setUIIni(const QStringList keys, const QVariant value)
{
    QStandardItemModel *model = new QStandardItemModel(keys.size(), 2, this);
    for (int i = 0; i < keys.size(); i++)
    {
        QStandardItem *item = new QStandardItem(keys.at(i));
        model->setItem(i, 0, item);
    }
    for (int i = 0; i < QString(value.toByteArray()).size(); i++)
    {
        QStandardItem *item = new QStandardItem(QString(value.toByteArray()));
        model->setItem(i, 1, item);
    }
    ui->tableView_ini->setModel(model);

    // 隐藏行号和列号
    ui->tableView_ini->verticalHeader()->hide();
    ui->tableView_ini->horizontalHeader()->hide();
}

bool MainWindow::isSectionEmpty()
{
    if(ui->lineEdit_section->text().isEmpty())
        return true;
}

void MainWindow::matchData()
{
    connect(ui->pushButton_match, &QPushButton::clicked, this, [=](){
        //判断excel和ini选择没有
        if(!isFilesOpenEmpty())
        {
            dataHandle();
        }
        else
        {
            QMessageBox::warning(this,"警告","请选择excel和ini文件！！！");
        }
    });
}

bool MainWindow::isFilesOpenEmpty()
{
    if(!(isXlsxFile(filepathexcel)&&isIniFile(filepathini)))
    {
        return true;
    }
}

void MainWindow::dataHandle()
{
    QSettings settings(filepathini, QSettings::IniFormat);
    for (int i = 0; i < xls_data.count(); i++) {
        for (int j = 0; j < xls_data.at(i).count(); j++) {
            if(sectionKeys.contains(QString(xls_data.at(i).at(0).toByteArray())))
            {
                //找到key值，ini数据写入
                const QString sectionName = ui->lineEdit_section->text();

                settings.beginGroup(sectionName);
                // 定义要写入的键值对
                settings.setValue(QString(xls_data.at(i).at(0).toByteArray()), QString(xls_data.at(i).at(1).toByteArray()));

                settings.endGroup();

                // 同步到磁盘
                settings.sync();
            }
        }
    }
    QVariant valueIniMatch;
    foreach (const QString &key, settings.allKeys()) {
        valueIniMatch = settings.value(key);
    }

    //刷新页面
    brushUIIni();

    //成功提示
    QMessageBox::information(this,"success","success!!!");
}

void MainWindow::brushUIIni()
{
    // 使用QSettings读取INI文件
    QSettings settings(filepathini, QSettings::IniFormat);

    // 创建一个模型
    QStandardItemModel *model = new QStandardItemModel(0, 2, this); // 假设我们有两列
    model->setHorizontalHeaderItem(0, new QStandardItem("Key"));
    model->setHorizontalHeaderItem(1, new QStandardItem("Value"));

    settings.beginGroup(ui->lineEdit_section->text());
    // 遍历组内的键
    QStringList keys = settings.allKeys();
    for (const QString &key : keys) {
        // 添加行
        int row = model->rowCount();
        model->insertRow(row);
        model->setData(model->index(row, 0), key);
        model->setData(model->index(row, 1), settings.value(key));
    }
    settings.endGroup();

    // 将模型设置到TableView
    ui->tableView_ini->setModel(model);

    // 隐藏行号和列号
    ui->tableView_ini->verticalHeader()->hide();
    ui->tableView_ini->horizontalHeader()->hide();
}

二、视频

QQ202493-163449