1101

wumu

2024-11-01 71c27f9ead97e52e50a59495c6955756e21004cf

 internal_system_v1/exporttofile.cpp

@@ -1,5 +1,12 @@
#include "exporttofile.h"
#include <QFileDialog>
#include <QDebug>
#include <QAxObject>
#include <QFile>
#include <QDateTime>
#include <QMessageBox>

#pragma execution_character_set("utf-8")
ExportToFile::ExportToFile(QObject *parent) : QObject(parent)
{

@@ -7,15 +14,276 @@

int ExportToFile::saveToExcelFromComInfo(ComInfo &info)
{
    // 读取当前目录下的模板文件，然后往里写数据，修改保存

//    QString savePath = QFileDialog::getExistingDirectory(nullptr,"选择文件夹",QDir::currentPath());
//    qDebug()<<"savepath:"<<savePath;

    QString demoPath = QDir::currentPath()+"/Data/附件2_内审统01表_单位及内部审计机构基本情况表-1.301版_demo.xls";
    QDir dir(demoPath);
    if(!dir.exists()){
        demoPath = QDir::currentPath()+"/release/Data/附件2_内审统01表_单位及内部审计机构基本情况表-1.301版_demo.xls";
    }
    QString dt = QDateTime::currentDateTime().toString("yyyyMMdd-HHmmss");
    QString savePath = QFileDialog::getSaveFileName(nullptr,"保存文件",QDir::currentPath()+QString("/内审统01表_%1_%2.xls").arg(QString::fromLocal8Bit(info.name)).arg(dt),"Excel File(*.xls;*.xlsx)");
    qDebug()<<"savepath:"<<savePath <<endl << "demoPath:"<<demoPath;



    if(savePath.size() > 0){
        QAxObject excel("ket.Application");
        if(!excel.setControl("ket"
                             ".Application")){  // windows内核
            excel.setControl("Excel.Application"); // wps内核
        }

        excel.setProperty("Visible",false);
        excel.setProperty("DisplayAlerts",false);
        QAxObject * wbs = excel.querySubObject("WorkBooks");
        qDebug()<<"wbs:"<<wbs->className();
        QAxObject * wb = wbs->querySubObject("Open(QString&)",demoPath);
        qDebug()<<"wb open ok";
        QAxObject * shs = wb->querySubObject("WorkSheets"); // WorkSheets 也可以
        // 获取具体表格，行列
        QAxObject * sheet = shs->querySubObject("Item(int)", 1); // 第一个表
        QAxObject *usedRange = sheet->querySubObject("UsedRange");
        int rows = usedRange->querySubObject("Rows")->property("Count").toInt();
        int columns = usedRange->querySubObject("Columns")->property("Count").toInt();
        qDebug()<<"r--c:"<<rows<<columns;

        // 修改某个值
        //sheet->querySubObject("Cells(int,int)", 1, 10)->dynamicCall("SetValue(const QString&)","1000200300");
//        QAxObject *cell1 = sheet->querySubObject("Range(QVariant, QVariant)", "D10");
//        qDebug()<<"B3原数据："<<cell1->dynamicCall("Value2()").toString();
//        /* ---- 修改单个单元格中的数据 ---- */
//        cell1->setProperty("Value2", "21321321312");
//        qDebug()<<"B3修改后的数据："<<cell1->dynamicCall("Value2()").toString();

//        QAxObject *cell2 = sheet->querySubObject("Range(QVariant, QVariant)", "G63");
//        qDebug()<<"B3原数据："<<cell2->dynamicCall("Value2()").toString();
//        /* ---- 修改单个单元格中的数据 ---- */
//        cell2->setProperty("Value2", "10");
//        qDebug()<<"G63修改后的数据："<<cell2->dynamicCall("Value2()").toString();

//        QAxObject *cell3 = sheet->querySubObject("Range(QVariant, QVariant)", "E57");
//        qDebug()<<"B3原数据："<<cell3->dynamicCall("Value2()").toString();
//        /* ---- 修改单个单元格中的数据 ---- */
//        cell3->setProperty("Value2", "4 其他");
//        qDebug()<<"E57修改后的数据："<<cell3->dynamicCall("Value2()").toString();

        // 依次赋值
        // 基本情况
        sheet->querySubObject("Range(QVariant, QVariant)", "D10")->setProperty("Value2", info.creditCode); // 统一信用代码
        qDebug()<<1;
        sheet->querySubObject("Range(QVariant, QVariant)", "I10")->setProperty("Value2", QString::fromLocal8Bit(info.name)); // 单位名称
        qDebug()<<2;
        sheet->querySubObject("Range(QVariant, QVariant)", "D13")->setProperty("Value2", QString::fromLocal8Bit(info.comType)); // 机构类型
        qDebug()<<3;
        sheet->querySubObject("Range(QVariant, QVariant)", "C19")->setProperty("Value2", QString::fromLocal8Bit(info.business)); // 业务活动
        qDebug()<<4;
        sheet->querySubObject("Range(QVariant, QVariant)", "E20")->setProperty("Value2", info.businessCode); // 行业代码
        qDebug()<<5;
        sheet->querySubObject("Range(QVariant, QVariant)", "B22")->setProperty("Value2", QString::fromLocal8Bit(info.addr)); // 注册地及区划
        qDebug()<<6;
        sheet->querySubObject("Range(QVariant, QVariant)", "C25")->setProperty("Value2", info.areaCode); // 区域代码
        qDebug()<<7;
        sheet->querySubObject("Range(QVariant, QVariant)", "G25")->setProperty("Value2", info.townCode); // 城乡代码
        qDebug()<<8;
        sheet->querySubObject("Range(QVariant, QVariant)", "C26")->setProperty("Value2", QString::fromLocal8Bit(info.scale)); // 单位规模
        qDebug()<<9;
        sheet->querySubObject("Range(QVariant, QVariant)", "G27")->setProperty("Value2", info.people); // 从业人员数量
        qDebug()<<10;
        sheet->querySubObject("Range(QVariant, QVariant)", "E28")->setProperty("Value2", QString::fromLocal8Bit(info.representative)); // 法人
        qDebug()<<11;
        sheet->querySubObject("Range(QVariant, QVariant)", "I28")->setProperty("Value2", QString::fromLocal8Bit(info.standardType)); // 执行会计标准
        qDebug()<<12;
        sheet->querySubObject("Range(QVariant, QVariant)", "C32")->setProperty("Value2", info.trunkCode); // 长途区号
        qDebug()<<13;
        sheet->querySubObject("Range(QVariant, QVariant)", "C33")->setProperty("Value2", info.fixedTel); // 固定电话
        qDebug()<<14;
        sheet->querySubObject("Range(QVariant, QVariant)", "C34")->setProperty("Value2", info.postCode); // 邮政编码
        qDebug()<<15;
        sheet->querySubObject("Range(QVariant, QVariant)", "F31")->setProperty("Value2", info.email); // 电子邮箱
        qDebug()<<16;
        sheet->querySubObject("Range(QVariant, QVariant)", "F33")->setProperty("Value2", info.webSite); // 网址
        qDebug()<<17;

        // 二 单位组织结构情况
        sheet->querySubObject("Range(QVariant, QVariant)", "E36")->setProperty("Value2", QString::fromLocal8Bit(info.hasUpLegal)); // 是否有上一级法人
        qDebug()<<18;
        sheet->querySubObject("Range(QVariant, QVariant)", "H37")->setProperty("Value2", QString::fromLocal8Bit(info.upCreditCode)); // 上一级统一社会信用代码
        qDebug()<<19;
        sheet->querySubObject("Range(QVariant, QVariant)", "H38")->setProperty("Value2", QString::fromLocal8Bit(info.oldCreditCode)); // 原组织机构代码
        qDebug()<<20;
        sheet->querySubObject("Range(QVariant, QVariant)", "H39")->setProperty("Value2", QString::fromLocal8Bit(info.upName)); // 上一级单位名称
        qDebug()<<21;

        // 三 总审计师与内部审计机构基本情况
        sheet->querySubObject("Range(QVariant, QVariant)", "E41")->setProperty("Value2", QString::fromLocal8Bit(info.hasChiefDesigner)); // 是否设置总审计师
        qDebug()<<22;
        sheet->querySubObject("Range(QVariant, QVariant)", "E42")->setProperty("Value2", QString::fromLocal8Bit(info.ChiefDesignerLevel)); // 总审计师职位层级
        qDebug()<<23;
        sheet->querySubObject("Range(QVariant, QVariant)", "E45")->setProperty("Value2", QString::fromLocal8Bit(info.employmentMode)); // 总审任职方式
        qDebug()<<24;
        sheet->querySubObject("Range(QVariant, QVariant)", "E48")->setProperty("Value2", QString::fromLocal8Bit(info.hasSetIntervalAudit)); // 是否设置内审机构
        qDebug()<<25;
        sheet->querySubObject("Range(QVariant, QVariant)", "E49")->setProperty("Value2", QString::fromLocal8Bit(info.internalName)); // 内审机构名称
        qDebug()<<26;
        sheet->querySubObject("Range(QVariant, QVariant)", "E50")->setProperty("Value2", QString::fromLocal8Bit(info.leadingOrganization)); // 领导机构
        qDebug()<<27;
        sheet->querySubObject("Range(QVariant, QVariant)", "E54")->setProperty("Value2", QString::fromLocal8Bit(info.hasSetSeparateIA)); // 是否独立设置内审机构
        qDebug()<<28;
        sheet->querySubObject("Range(QVariant, QVariant)", "E56")->setProperty("Value2", info.financeDepartment); // 财务部门
        qDebug()<<29;
        sheet->querySubObject("Range(QVariant, QVariant)", "E56")->setProperty("Value2", info.legalDepartment); // 法务部门
        qDebug()<<30;
        sheet->querySubObject("Range(QVariant, QVariant)", "E56")->setProperty("Value2", info.internalConDepart); // 内部控制部门
        qDebug()<<31;
        sheet->querySubObject("Range(QVariant, QVariant)", "E56")->setProperty("Value2", info.disceplineInDepart); // 纪检部门
        qDebug()<<32;
        sheet->querySubObject("Range(QVariant, QVariant)", "E56")->setProperty("Value2", info.otherDepart); // 其他部门
        qDebug()<<33;
        sheet->querySubObject("Range(QVariant, QVariant)", "E57")->setProperty("Value2", QString::fromLocal8Bit(info.intavalAuditLeval)); // 内审层级
        qDebug()<<34;

        // 四 内部审计人员配备基本情况
        sheet->querySubObject("Range(QVariant, QVariant)", "E61")->setProperty("Value2", info.organazationNum); // 编制数量
        qDebug()<<35;
        sheet->querySubObject("Range(QVariant, QVariant)", "G61")->setProperty("Value2", info.realNum); // 实有人员数
        qDebug()<<36;
        sheet->querySubObject("Range(QVariant, QVariant)", "I61")->setProperty("Value2", info.professionalNum); // 专职人员数量
        qDebug()<<37;
        sheet->querySubObject("Range(QVariant, QVariant)", "G62")->setProperty("Value2", info.CIANum); // 拥有CIA人数
        qDebug()<<38;
        sheet->querySubObject("Range(QVariant, QVariant)", "G63")->setProperty("Value2", info.masterNum); // 硕士学历以上人数
        qDebug()<<39;
        sheet->querySubObject("Range(QVariant, QVariant)", "G64")->setProperty("Value2", info.undergraduatesNum); // 本科人数
        qDebug()<<40;
        sheet->querySubObject("Range(QVariant, QVariant)", "G65")->setProperty("Value2", info.juniorNum); // 专科及以下人数
        qDebug()<<41;
        sheet->querySubObject("Range(QVariant, QVariant)", "G66")->setProperty("Value2", info.seniorNum); // 高级职称人数
        qDebug()<<42;
        sheet->querySubObject("Range(QVariant, QVariant)", "G67")->setProperty("Value2", info.intermediateNum); // 中级职称人数
        qDebug()<<43;
        sheet->querySubObject("Range(QVariant, QVariant)", "G68")->setProperty("Value2", info.primaryNum); // 初级职称人数
        qDebug()<<44;
        sheet->querySubObject("Range(QVariant, QVariant)", "G69")->setProperty("Value2", info.noTitleNum); // 无职称人数
        qDebug()<<45;
        sheet->querySubObject("Range(QVariant, QVariant)", "G70")->setProperty("Value2", info.upFiftyOldNum); // 50岁以上人数
        qDebug()<<46;
        sheet->querySubObject("Range(QVariant, QVariant)", "G71")->setProperty("Value2", info.upThirtyOldNum); // 30-50岁人数
        qDebug()<<47;
        sheet->querySubObject("Range(QVariant, QVariant)", "G72")->setProperty("Value2", info.downThirtyOldNum); // 30岁以下人数
        qDebug()<<48;
        sheet->querySubObject("Range(QVariant, QVariant)", "G73")->setProperty("Value2", info.auditNum); // 审计数量
        qDebug()<<49;
        sheet->querySubObject("Range(QVariant, QVariant)", "G74")->setProperty("Value2", info.accountingNum); // 会计数量
        qDebug()<<50;
        sheet->querySubObject("Range(QVariant, QVariant)", "G75")->setProperty("Value2", info.economyNum); // 经济数量
        qDebug()<<51;
        sheet->querySubObject("Range(QVariant, QVariant)", "G76")->setProperty("Value2", info.lawNum); // 法律数量
        qDebug()<<52;
        sheet->querySubObject("Range(QVariant, QVariant)", "G77")->setProperty("Value2", info.managerNum); // 管理数量
        qDebug()<<53;
        sheet->querySubObject("Range(QVariant, QVariant)", "G78")->setProperty("Value2", info.itNum); // 信息技术数量
        qDebug()<<54;
        sheet->querySubObject("Range(QVariant, QVariant)", "G79")->setProperty("Value2", info.engineeringNum); // 工程数量
        qDebug()<<55;
        sheet->querySubObject("Range(QVariant, QVariant)", "G80")->setProperty("Value2", info.otherNum); // 其他数量
        qDebug()<<56;

        // 后面的4个信息
        sheet->querySubObject("Range(QVariant, QVariant)", "B82")->setProperty("Value2", QString::fromLocal8Bit(info.statisticalConOfficer)); // 统计负责人
        qDebug()<<57;
        sheet->querySubObject("Range(QVariant, QVariant)", "E82")->setProperty("Value2", QString::fromLocal8Bit(info.personFilling)); // 填表人
        qDebug()<<58;
        sheet->querySubObject("Range(QVariant, QVariant)", "B83")->setProperty("Value2", QString::fromLocal8Bit(info.officerTel)); // 联系电话
        qDebug()<<59;
        sheet->querySubObject("Range(QVariant, QVariant)", "E83")->setProperty("Value2", QString::fromLocal8Bit(info.fillingDateTime)); // 填报日期
        qDebug()<<60;

        // 完事了，结束，保存或者退出
        wb->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(savePath));
        wbs->dynamicCall("Close()");
        excel.dynamicCall("Quit(void)");

        QMessageBox::information(nullptr,"导出完成","文件位置:"+savePath);

    }
    return 0;

}

int ExportToFile::saveToExcelFromThreeMergePro()
{

    return 0;
}

int ExportToFile::saveToExcelFromNeiShenZonghe()
int ExportToFile::saveToExcelFromNeiShenZonghe(QString name,NeiShenZongHeInfo &nszh)
{
    qDebug()<<"内审综合保存导出";

    if(name.size() == 0) return -1; // 文件为空

    QString demoPath = QDir::currentPath()+"/Data/附件4_内审统03表_内部审计统计综合表_demo.xls";
    QDir dir(demoPath);
    if(!dir.exists()){
        demoPath = QDir::currentPath()+"/release/Data/附件4_内审统03表_内部审计统计综合表_demo.xls";
    }
    QString dt = QDateTime::currentDateTime().toString("yyyyMMdd-HHmmss");
    QString savePath = QFileDialog::getSaveFileName(nullptr,"保存文件",QDir::currentPath()+QString("/综合表_%1_%2.xls").arg(name).arg(dt),"Excel File(*.xls;*.xlsx)");
    qDebug()<<"savepath:"<<savePath << endl << "demoPath:"<<demoPath;



    if(savePath.size() > 0){
        QAxObject excel("ket.Application");
        if(!excel.setControl("ket"
                             ".Application")){  // windows内核
            excel.setControl("Excel.Application"); // wps内核
        }

        excel.setProperty("Visible",false);
        excel.setProperty("DisplayAlerts",false);
        QAxObject * wbs = excel.querySubObject("WorkBooks");
        qDebug()<<"wbs:"<<wbs->className();
        QAxObject * wb = wbs->querySubObject("Open(QString&)",demoPath);
        qDebug()<<"wb open ok";
        QAxObject * shs = wb->querySubObject("WorkSheets"); // WorkSheets 也可以
        // 获取具体表格，行列
        QAxObject * sheet = shs->querySubObject("Item(int)", 1); // 第一个表
        QAxObject *usedRange = sheet->querySubObject("UsedRange");
        int rows = usedRange->querySubObject("Rows")->property("Count").toInt();
        int columns = usedRange->querySubObject("Columns")->property("Count").toInt();
        qDebug()<<"r--c:"<<rows<<columns;

        // 修改内容

        // 四 项目审计  单位 个
        char index[5]={'G','H','I','J','K'};

        for(int i=0;i<5;++i){
            QString cellStr = QString("%1%2").arg(index[i]).arg(40);
            qDebug()<<cellStr<<nszh.code_030300[i];
            qDebug()<<"修改之前:"<<sheet->querySubObject("Range(QVariant, QVariant)", cellStr)->property("Value2").toInt();
            sheet->querySubObject("Range(QVariant, QVariant)", cellStr)->setProperty("Value2", nszh.code_030300[i]); // 审计项目
            qDebug()<<"修改结果:"<<sheet->querySubObject("Range(QVariant, QVariant)", cellStr)->property("Value2").toInt();
        }
        for(int i=0;i<5;++i){
            sheet->querySubObject("Range(QVariant, QVariant)", QString("%1%2").arg(index[i]).arg(41))->setProperty("Value2", nszh.code_030301[i]); // 其中：贯彻落实国家重大政策措施审计
        }

        qDebug()<<40;


        // 完事了，结束，保存或者退出
        wb->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(savePath));
        wbs->dynamicCall("Close()");
        excel.dynamicCall("Quit(void)");

        QMessageBox::information(nullptr,"导出完成","文件位置:"+savePath);

    }

    return 0;
}