目录
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);//100mAvoid 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;}// 读取一个 sheetthis->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;}// 读取一个 sheetthis->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.";}
}