QT Excel文件读写

目录

1 ExcelRW.h

2 ExcelRW.cpp

---

 

1 ExcelRW.h

#ifndef EXCELRW_H
#define EXCELRW_H

#include <QObject>
#include <QString>
#include <QFileDialog>
#include <QAxObject>
#include <QVariant>
#include <QVariantList>
#include <QStandardItemModel>

typedef struct {
    QAxObject *excel;
    QAxObject *workBooks;
    QAxObject *current_workBook;
    QAxObject *workSheets;
    QAxObject *current_workSheet;

    QAxObject *usedRange;
    QAxObject *rows;
    QAxObject *columns;
    int sheetCount, rowCount, columnCount;


}ExcelFile;
typedef struct {
    int row;
    int col;
    int val;
} OCVToler;
typedef struct {
    int sheet;
    int col_src;
    //int col;
    int col_dest;
} CalcMode;
class ExcelRW : public QObject
{
    Q_OBJECT
public:
    explicit ExcelRW(QObject *parent = 0);

    QString *fileName;

    ExcelFile excel;

    QList<QList<QVariant>> xls_data;    // excel 表数据

    CalcMode calcMode;

    QStandardItemModel *tab_model;

    ~ExcelRW();
    void test();
    void initExcel(void);
    void openExcelFile();
    void showOneSheet(int sheet_num);
    void readExcel_OneSheet(int sheet_num);
    int readExcel_OneSheet_read(int sheet_num);
    void setPramStruct();
    void getOCVToler();
    void closeExcelFile();

    void regExpTemp(QString input, int &num1, int &num2);
    //100mA
    void regExpUnit(QString input, int &num1);

    void modifyData(QString target_, QVariant value);

    void modifyAll();
signals:

public slots:
};

#endif // EXCELRW_H

2 ExcelRW.cpp

#include "excelrw.h"
#include <qDebug>
#include <QRegularExpression>
#include <QMessageBox>
ExcelRW::ExcelRW(QObject *parent) : QObject(parent)
{
    this->fileName = new QString;
    this->tab_model = new QStandardItemModel();
    this->excel.current_workBook = nullptr;

    initExcel();


}

ExcelRW::~ExcelRW()
{
    if(fileName != nullptr)
        delete fileName;

    this->tab_model->clear();

    if(tab_model != nullptr)
        delete this->tab_model;

}

void ExcelRW::test()
{


}

void ExcelRW::modifyData(QString target_, QVariant value)
{
    QString  target = target_;
    this->excel.usedRange = this->excel.current_workSheet->querySubObject("Range(const QString)", target);
    this->excel.usedRange->setProperty("NumberFormat", "@");    //  设置所有单元格为文本属性
    this->excel.usedRange->dynamicCall("SetValue(const QVariant&", value);

    // 退出Excel
    //excel.excel->dynamicCall("Quit()");
}

void ExcelRW::modifyAll()
{


    modifyData("B3",123);
    modifyData("B5",234);
    modifyData("B7",234);
    modifyData("B8",123);
    modifyData("B9",123);




    this->excel.current_workBook->dynamicCall("Save()");
    this->closeExcelFile();
}

void ExcelRW::initExcel()
{
    this->excel.excel = new QAxObject();   // 加载 excel 驱动
    if (excel.excel->setControl("Excel.Application")) {	// 加载 Microsoft Excel 控件
    } else {
        excel.excel->setControl("ket.Application");  // 加载 WPS Excel 控件
    }
    this->excel.excel->setProperty("Visible", false);//不显示Excel界面，如果为true会看到启动的Excel界面
    //this->excel.excel->setProperty("Visible", true);
    this->excel.workBooks = this->excel.excel->querySubObject("WorkBooks");
}

void ExcelRW::openExcelFile()
{

    if (this->excel.excel == nullptr) {
        return ;
    }

    this->excel.workBooks->dynamicCall("Open (const QString&)", *this->fileName);
    this->excel.current_workBook = this->excel.excel->querySubObject("ActiveWorkBook");
    this->excel.workSheets = this->excel.current_workBook->querySubObject("Sheets");

    this->excel.rowCount = 0;
    this->excel.columnCount = 0;

    this->excel.sheetCount = this->excel.workSheets->property("Count").toInt();

    qDebug() << "Sheet num: " << this->excel.sheetCount << "\n";
}

void ExcelRW::showOneSheet(int sheet_num)
{
    if (sheet_num > this->excel.sheetCount) {
        QMessageBox::warning(0, "warning", "sheet3 无数据!");
        return;
    }

    this->readExcel_OneSheet(sheet_num);
    this->tab_model->clear();
    for (int i = 0; i < this->xls_data.count(); i++) {
        for (int j = 0; j < this->xls_data.at(i).count(); j++) {
            QStandardItem *item = new QStandardItem(QString(this->xls_data.at(i).at(j).toByteArray()));
            qDebug()<<"i "<<i<<"j "<<j <<" "<<QString(this->xls_data.at(i).at(j).toByteArray());
            this->tab_model->setItem(i, j, item);

            //delete item;
        }
    }
}

void ExcelRW::readExcel_OneSheet(int sheet_num)
{
    if (sheet_num > this->excel.sheetCount) {
        return;
    }


    // 读取一个 sheet
    this->excel.current_workSheet = this->excel.current_workBook->querySubObject("Sheets(int)", sheet_num);
    this->excel.usedRange = this->excel.current_workSheet->querySubObject("UsedRange");

    if (nullptr == this->excel.usedRange || this->excel.usedRange->isNull()) {
        return;
    }


    QVariant var = this->excel.usedRange->dynamicCall("Value");
    delete this->excel.usedRange;
    this->excel.usedRange = nullptr;


    // 读取一个 sheet 结束
    for (int i = 0; i < this->xls_data.count(); i++) {
        this->xls_data.value(i).clear();
    }

    this->xls_data.clear();

    QVariantList varRows = var.toList();
    if (varRows.isEmpty()) {
        return;
    }

    const int rowCount = varRows.size();
    this->excel.rowCount = rowCount;


    QVariantList rowData;

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

    //this->excel.current_workBook->dynamicCall("Close()");
    qDebug() << "Sheet:: row:" << this->excel.rowCount << "colum:" << this->excel.columnCount << "\n";

    setPramStruct();

}

int ExcelRW::readExcel_OneSheet_read(int sheet_num)
{
    if (sheet_num > this->excel.sheetCount) {
        QMessageBox::warning(0, "warning", "sheet3 无数据!");
        return -1;
    }


    // 读取一个 sheet
    this->excel.current_workSheet = this->excel.current_workBook->querySubObject("Sheets(int)", sheet_num);
    this->excel.usedRange = this->excel.current_workSheet->querySubObject("UsedRange");

    if (nullptr == this->excel.usedRange || this->excel.usedRange->isNull()) {
        return -2;
    }


    QVariant var = this->excel.usedRange->dynamicCall("Value");
    delete this->excel.usedRange;
    this->excel.usedRange = nullptr;


    // 读取一个 sheet 结束
    for (int i = 0; i < this->xls_data.count(); i++) {
        this->xls_data.value(i).clear();
    }

    this->xls_data.clear();

    QVariantList varRows = var.toList();
    if (varRows.isEmpty()) {
        return -3;
    }

    const int rowCount = varRows.size();
    this->excel.rowCount = rowCount;


    QVariantList rowData;

    for (int i = 0; i < rowCount; i++){
        rowData = varRows[i].toList();
        if (rowData.count() > this->excel.columnCount) {
            this->excel.columnCount = rowData.count();
        }
        this->xls_data.push_back(rowData);
    }
    modifyAll();
    //this->excel.current_workBook->dynamicCall("Close()");
    qDebug() << "Sheet:: row:" << this->excel.rowCount << "colum:" << this->excel.columnCount << "\n";
    return 1;
    //setPramStruct();
}

void ExcelRW::getOCVToler()
{


}

void ExcelRW::setPramStruct()
{

    getOCVToler();
    for (int i = 0; i < this->xls_data.count(); i++) {
        for (int j = 0; j < this->xls_data.at(i).count(); j++) {
            QString value = QString(this->xls_data.at(i).at(j).toByteArray());
            switch (i) {
            case 1:{
                switch (j) {
                case 0:{

                }
                    break;

                case 1:{

                }
                    break;


                default:
                    break;
                }

            }
                break;


            default:
                break;
            }

        }
    }

}



void ExcelRW::closeExcelFile()
{
    if (this->excel.current_workBook != nullptr) {
        //this->excel.current_workBook->dynamicCall("Save()");
        this->excel.current_workBook->dynamicCall("Close()"); //关闭文件
        //this->excel.current_workBook = nullptr;
    }

    if (this->excel.workBooks != nullptr) {
        this->excel.workBooks->dynamicCall("Close()"); //关闭文件
        //delete this->excel.workBooks;
        //this->excel.workBooks = nullptr;
    }


//    this->excel.excel->dynamicCall("Quit(void)");   // 退出
//    delete this->excel.excel;
//    this->excel.excel= nullptr;

}

void ExcelRW::regExpTemp(QString input, int &num1, int &num2)
{
    // 首先，尝试找到所有可能包含数字的片段
    // 注意：这里我们假设数字前后都可能是非数字字符，除了负号
    QStringList parts = input.split(QRegExp("[^\\d-]+"), QString::SkipEmptyParts);

    // 由于split可能会将连续的数字分割（比如"-15"和"-10"可能被看作两个独立的），
    // 我们可能需要进一步处理parts，但在这个特定例子中，split的参数已经足够
    // 来直接得到我们想要的数字（假设输入格式是固定的）

    // 打印结果
    foreach(QString part, parts) {
        qDebug() << part; // 这将输出 "-15" 和 "-10"
    }
    if(parts.isEmpty())
        return ;
    // 如果你需要将这些字符串转换为整数
    bool ok1, ok2;
    num1 = parts.first().toInt(&ok1);
    num2 = parts.last().toInt(&ok2);

    if(ok1 && ok2) {
        qDebug() << "第一个数字是：" << num1;
        qDebug() << "第二个数字是：" << num2;
    } else {
        qDebug() << "转换失败";
    }
}

void ExcelRW::regExpUnit(QString input, int &num1)
{

    input = input.trimmed();
    QRegularExpression re("^[-+]?\\d+"); // 匹配字符串开头的数字部分
    QRegularExpressionMatch match = re.match(input);

    if (match.hasMatch()) {
        QString numberPart = match.captured(0); // 捕获匹配的部分
        num1 = numberPart.toDouble();
        qDebug() << "Extracted number:" << numberPart;
    } else {

        num1 = 0;
        qDebug() << input << "====No number found at the beginning of the string.";
    }
}