读写EXCEL WORD

void StaticPage::showProgressBarCentered() {
    // 创建一个进度条（如果还没有的话）

    progressBar = new QProgressBar(this); // 假设this是StaticPage的指针，且StaticPage已经是一个QWidget
    progressBar->setFixedSize(400, 30);
    progressBar->setValue(0);
    progressBar->setMaximum(3);
    QString styleSheet = R"(
            QProgressBar {
                /* 这里设置进度条的样式，但字体大小可能不起作用 */
                font-size: 16px; /* 尝试设置字体大小，但可能无效 */
                text-align: center; /* 文本居中 */
            }
            QProgressBar::chunk {
                /* 这里设置进度条已填充部分的样式 */
                background-color: blue; /* 举例：已填充部分为蓝色 */
            }
        )";
    progressBar->setStyleSheet(styleSheet);

    // 计算进度条应该放置的位置以使其中心与父窗口中心对齐
    QRect parentRect = this->rect(); // 获取父窗口的矩形区域（注意：如果父窗口未显示，这可能不准确）
    QRect progressRect = progressBar->rect(); // 获取进度条的矩形区域

    // 计算左上角的坐标，使得进度条中心与父窗口中心对齐
    int x = parentRect.center().x() - progressRect.width() / 2;
    int y = parentRect.center().y() - progressRect.height() / 2;

    // 设置进度条的位置
    progressBar->move(x, y);

    // 显示进度条（如果之前未显示）
    progressBar->show();
}


//静态导出word报告
void StaticPage::exportWord()
{
    HRESULT hr = CoInitialize(NULL);
    if (FAILED(hr)) {
        qDebug() << "Failed to initialize COM";
        return ;
    }
    showProgressBarCentered();
    QString templatePath = "";
    QString outputPath = "";

    if(IsReturn->currentText() == "使能"){
        templatePath = QCoreApplication::applicationDirPath() + "/models/static.dot";
    }else if(IsReturn->currentText() == "禁用")
    {
        templatePath = QCoreApplication::applicationDirPath() + "/models/static-1.dot";
    }
    QDateTime currentDateTime = QDateTime::currentDateTime();
    QString formattedDateTime = currentDateTime.toString("yyyyMMdd_hhmmss");
    outputPath = QCoreApplication::applicationDirPath()  + "/data/" + formattedDateTime + ".docx";
    createDocumentFromTemplate(templatePath,outputPath);
}


void StaticPage::createDocumentFromTemplate(const QString &templatePath, const QString &outputPath)
{
    QAxObject *word = new QAxObject();
    word->setControl("Word.Application");
    if (word->isNull()) {
        qDebug() << "Failed to create Word.Application COM object";
        progressBar->setValue(progressBar->maximum()); // 完成
        return;
    }
    word->setProperty("Visible", false);
    QAxObject *documents = word->querySubObject("Documents");
    if (documents) {
        QAxObject *document = documents->querySubObject("Open(const QString&)", templatePath);
        progressBar->setValue(1);
        //替换
        QString ba = (Direction->currentText() == "压向") ? "压" : "拉";
        Replace(document, "#1#", ba);
        Replace(document,"#1#",ba);
        //替换量程
        QString range = getString(3,0);
        qDebug() << range;
        Replace(document,"#2-1#",range);

        for(int i = 2;i < table_row - 1;i++)
        {
            for(int j = 2;j < table_column + 1;j++)
            {
                QString str = getString(i + 1,j - 1);
                QString str1 = QObject::tr("#%1-%2#").arg(i).arg(j);
                Replace(document,str1,str);
            }
        }

        progressBar->setValue(2);
        document->dynamicCall("SaveAs(const QString&)", outputPath);
        document->dynamicCall("Close()");
        delete document;
        delete documents;
        progressBar->setValue(3);

    }
    word->dynamicCall("Quit()");
    progressBar->setValue(progressBar->maximum()); // 完全完成
    delete progressBar;
    progressBar = nullptr;
    CoUninitialize();
}
//获取表格字符串
QString StaticPage::getString(int row,int column)
{
    QTableWidgetItem *item = tableStatic->item(row, column);
    if(!item) return "";
    return item->text();
}

void StaticPage::Replace(QAxObject *document,QString label,QString replace)
{
    if (document) {
        qDebug() << "Document opened from template";
        QAxObject *range = document->querySubObject("Content");
        if (range) {
            QAxObject *find = range->querySubObject("Find");
            if (find) {
                find->dynamicCall("ClearFormatting()");
                find->setProperty("Text", label);
                bool found = find->dynamicCall("Execute()").toBool();
                if (found) {
                    range->dynamicCall("SetText(const QString&)", replace);
                    qDebug() << "Text replaced successfully";
                } else {
                    qDebug() << "Text not found";
                }

            }
        }
    }else {
        qDebug() << "Failed to get Documents object";
    }

}

void StaticPage::replaceInExcel(const QString &templatePath, const QString &newFilePath, const QMap<QString, QString> &replacements)
{
    HRESULT hr = CoInitialize(NULL);
    if (FAILED(hr)) {
        qDebug() << "Failed to initialize COM";
        return ;
    }
    // 创建 Excel 应用程序对象
    QAxObject *excel = new QAxObject("Excel.Application");
    excel->setProperty("Visible", false); // 设置为不可见

    // 打开 Excel 模板文件
    QAxObject *workbooks = excel->querySubObject("Workbooks");
    QAxObject *workbook = workbooks->querySubObject("Open(const QString&)", templatePath);

    // 获取第一个工作表
    QAxObject *sheets = workbook->querySubObject("Sheets");
    QAxObject *sheet = sheets->querySubObject("Item(int)", 1); // 这里选择第一个工作表

    // 获取单元格的总行数和总列数
    QAxObject *usedRange = sheet->querySubObject("UsedRange");
    QAxObject *rows = usedRange->querySubObject("Rows");
    QAxObject *columns = usedRange->querySubObject("Columns");

    int rowCount = rows->property("Count").toInt();
    int colCount = columns->property("Count").toInt();

    // 遍历单元格进行查找和替换
    for (int row = 1; row <= rowCount; ++row) {
        for (int col = 1; col <= colCount; ++col) {
            QAxObject *cell = sheet->querySubObject("Cells(int,int)", row, col);
            QString cellValue = cell->property("Value").toString();

            // 遍历替换项
            for (auto it = replacements.constBegin(); it != replacements.constEnd(); ++it) {
                QString findText = it.key();
                QString replaceText = it.value();

                // 查找并替换
                if (cellValue.contains(findText, Qt::CaseInsensitive)) {
                    QString newValue = cellValue.replace(findText, replaceText, Qt::CaseInsensitive);
                    cell->setProperty("Value", newValue);
                }
            }
        }
    }
    // 保存到新的 Excel 文件
    try {
        workbook->dynamicCall("SaveAs(const QString&)", newFilePath);
    } catch (...) {
        qDebug() << "Failed to save the file.";
    }

    // 关闭工作簿
    workbook->dynamicCall("Close()");
    excel->dynamicCall("Quit()");

    // 清理
    delete sheet;
    delete sheets;
    delete workbook;
    delete workbooks;
    delete excel;
    CoUninitialize();
}

void StaticPage::createExcelFromTemplate(const QString &templatePath, const QString &outputPath)
{
    // 定义多个替换项
    QMap<QString, QString> replacements;
//  replacements["旧文本1"] = "新文本1";
    QString ba = (Direction->currentText() == "压向") ? "压向进程数据" : "拉向进程数据";
    QString ba1 = (Direction->currentText() == "压向") ? "压向回程数据" : "拉向回程数据";
    replacements["#1#"] = ba;
    replacements["#2#"] = ba1;
    replacements["#2-1#"] = getString(3,0);
    for(int i = 2;i < table_row - 1;i++)
    {
        for(int j = 2;j < table_column + 1;j++)
        {
            QString str = getString(i + 1,j - 1);
            QString str1 = QObject::tr("#%1-%2#").arg(i).arg(j);
            replacements[str1] = str;
        }
    }
    replaceInExcel(templatePath, outputPath, replacements);

}
#include <QFileInfo>
void StaticPage::exportExcel()
{
    QString templatePath = "";
    QString outputPath = "";
    if(IsReturn->currentText() == "使能"){
        templatePath = QCoreApplication::applicationDirPath() + "/models/static.xlsx";
    }else if(IsReturn->currentText() == "禁用")
    {
        templatePath = QCoreApplication::applicationDirPath() + "/models/static-1.xlsx";
    }
    QFileInfo info(templatePath);
    if (!info.exists())
    {
        qDebug() << "不存在的文件" << templatePath;
        return;
    }

    QDateTime currentDateTime = QDateTime::currentDateTime();
    QString formattedDateTime = currentDateTime.toString("yyyyMMdd_hhmmss");
    outputPath = QCoreApplication::applicationDirPath() + "/data/" + formattedDateTime + ".xlsx";
    outputPath.replace("/", "\\");
    //outputPath = "C:\\Users\\86134\\Desktop\\rebuild0918\\ReBuildTest\\debug\\data\\1.xlsx";
;

    createExcelFromTemplate(templatePath,outputPath);
}