#include "coverinfo.h"
|
#include "ui_coverinfo.h"
|
#include <QDebug>
|
#include <QDirIterator>
|
#include <QFileDialog>
|
#include <QSqlQuery>
|
#include <QSqlRecord>
|
|
int g_comId = 0; // 全局变量，保存单位id
|
|
CoverInfo::CoverInfo(QWidget *parent) :
|
QMainWindow(parent),
|
ui(new Ui::CoverInfo)
|
{
|
ui->setupUi(this);
|
readInfo();
|
readDataFromSQL(); // 从数据库读取数据
|
|
}
|
|
CoverInfo::~CoverInfo()
|
{
|
delete ui;
|
}
|
|
void CoverInfo::readInfo()
|
{
|
// QXlsx::Document doc("./debug/Data/test.xlsx");
|
// QString cc = doc.read("A1").toString();
|
// qDebug()<<"cc"<<cc;
|
// ui->label_creditCode->setText(cc);
|
|
QString dirPath = "./debug/Data";
|
QString dirPathRelease = "./release/Data";
|
QString dirPathData = "./Data";
|
QDir dir(dirPathData);
|
if(dir.exists()){
|
dirPath = dirPathData; // 存在则覆盖
|
}
|
|
QDir dirRe(dirPathRelease);
|
if(dirRe.exists()){
|
dirPath = dirPathRelease;
|
}
|
QStringList list;
|
QDirIterator it(dirPath, {"*.xls","*.xlsx"}, QDir::Files | QDir::NoSymLinks | QDir::NoDotAndDotDot, QDirIterator::Subdirectories);
|
while(it.hasNext())
|
{
|
it.next();
|
list.append(it.fileInfo().absoluteFilePath());
|
}
|
foreach(QString str, list)
|
{
|
qDebug() << str;
|
int index = str.indexOf("内审统01表");
|
qDebug()<<"index:"<<index;
|
if(index > 0){
|
QAxObject excel("Excel.Application");
|
excel.setProperty("Visible",false);
|
excel.setProperty("DisplayAlerts",false);
|
qDebug()<<"open:"<<str;
|
qDebug()<<"excel:"<<excel.className();
|
QAxObject * wbs = excel.querySubObject("WorkBooks");
|
QAxObject * wb = wbs->querySubObject("Open(QString&)",str);
|
//QAxObject* pWorkBook = excel.querySubObject("ActiveWorkBook");
|
//QAxObject* pWorkSheets = pWorkBook->querySubObject("Sheets");//获取工作表
|
QAxObject * shs = wb->querySubObject("Sheets"); // WorkSheets 也可以
|
int cnt = shs->property("Count").toInt();
|
qDebug()<<"cnt="<<cnt;
|
for(int i=0;i<cnt;++i){
|
QAxObject * sheet = shs->querySubObject("Item(int)", i+1);
|
QString sheetName = sheet->property("Name").toString();
|
qDebug()<<"sh name:"<<sheetName;
|
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;
|
QVariant val = usedRange->dynamicCall("Value"); // 拿到二维表格
|
QVariantList valRows = val.toList();
|
// 遍历每一项
|
// for(int i=0;i<rows;++i){
|
// QVariantList valrow = valRows[i].toList();
|
// for(int j=0;j<columns;++j){
|
// QVariant valData = valrow[j];
|
// if(valData.isValid()){
|
// qDebug()<<"val:"<< i<< j << valData;
|
// }
|
|
// }
|
// }
|
|
// 统一信用代码
|
QVariant uniCode = valRows[9].toList().at(3);
|
if(uniCode.isValid()){
|
ui->label_creditCode->setText(uniCode.toString());
|
}
|
// 单位名称
|
QVariant name = valRows[9].toList().at(8);
|
if(name.isValid()){
|
ui->label_name->setText(name.toString());
|
}
|
// 机构类型
|
QVariant comType = valRows[12].toList().at(3);
|
if(comType.isValid()){
|
ui->label_comType->setText(comType.toString());
|
}
|
// 主要业务活动
|
QString business;
|
QVariant first = valRows[18].toList().at(2);
|
if(first.isValid()){
|
business.append(first.toString());
|
}
|
QVariant sec = valRows[18].toList().at(5);
|
if(sec.isValid()){
|
business += "、";
|
business.append(sec.toString());
|
}
|
QVariant third = valRows[18].toList().at(8);
|
if(third.isValid()){
|
business += "、";
|
business.append(third.toString());
|
}
|
if(business.size() > 0){
|
ui->label_business->setText(business);
|
}
|
// 业务代码
|
QVariant busCode = valRows[19].toList().at(4);
|
if(busCode.isValid()){
|
ui->label_businessCode->setText(busCode.toString());
|
}
|
// 注册地址
|
QString addr="";
|
QVariant province = valRows[21].toList().at(1); // 省份
|
QVariant city = valRows[21].toList().at(4); // 城市
|
QVariant district = valRows[21].toList().at(7); // 管辖区
|
QVariant village = valRows[22].toList().at(2); // 乡镇
|
QVariant street = valRows[22].toList().at(4); // 街道
|
QVariant Sub = valRows[23].toList().at(2); // 街道办事处
|
QVariant committee = valRows[25].toList().at(3); // 居委会
|
//qDebug()<<province<<city<<district<<village<<street<<Sub<<committee;
|
if(province.isValid()) addr += province.toString();
|
if(city.isValid()) addr += city.toString();
|
if(district.isValid()) addr += district.toString();
|
if(village.isValid()) addr += village.toString();
|
if(street.isValid()) addr += street.toString();
|
if(Sub.isValid()) addr += Sub.toString();
|
if(committee.isValid()) addr += committee.toString();
|
if(addr.size()>10)ui->label_addr->setText(addr);
|
// 区域代码
|
QVariant areaCode = valRows[24].toList().at(2);
|
if(areaCode.isValid()) ui->label_areaCode->setText(areaCode.toString());
|
// 城乡代码
|
QVariant townCode = valRows[24].toList().at(6);
|
if(townCode.isValid()) ui->label_townCode->setText(townCode.toString());
|
// 单位规模
|
QVariant scale = valRows[25].toList().at(3);
|
if(scale.isValid()) ui->label_scale->setText(scale.toString());
|
// 从业人数
|
QVariant people = valRows[26].toList().at(6);
|
if(people.isValid()) ui->label_people->setText(people.toString());
|
// 法人
|
QVariant representative = valRows[27].toList().at(4);
|
if(representative.isValid()) ui->label_representative->setText(representative.toString());
|
// 会计标准类别 standardType
|
QVariant standardType = valRows[27].toList().at(8);
|
if(standardType.isValid()) ui->label_standardType->setText(standardType.toString());
|
// 长途区号
|
QVariant trunkCode = valRows[31].toList().at(2);
|
if(trunkCode.isValid()) ui->label_trunkCode->setText(trunkCode.toString());
|
// 固定电话
|
QVariant fixedTel = valRows[32].toList().at(2);
|
if(fixedTel.isValid()) ui->label_fixedTel->setText(fixedTel.toString());
|
// 邮政编码
|
QVariant postCode = valRows[33].toList().at(2);
|
if(postCode.isValid()) ui->label_postCode->setText(postCode.toString());
|
// 电子邮箱
|
QVariant email = valRows[30].toList().at(5);
|
if(email.isValid()) ui->label_email->setText(email.toString());
|
// 网址
|
QVariant webSite = valRows[32].toList().at(5);
|
if(webSite.isValid()) ui->label_webSite->setText(webSite.toString());
|
}
|
wbs->dynamicCall("Close()");
|
excel.dynamicCall("Quit(void)");
|
}
|
}
|
|
|
// QAxObject excel("./debug/Data/附件2_内审统01表_单位及内部审计机构基本情况表-1.301版(1).xls");
|
// excel.setProperty("Visible",true);
|
// excel.setProperty("DisplayAlerts",false);
|
|
}
|
|
void CoverInfo::readDataFromSQL()
|
{
|
QSqlQuery query;
|
QString sql;
|
if(ui->label_name->text().size() > 1){
|
sql = QString("select id,name from company_info where name='%1'").arg(ui->label_name->text());
|
qDebug()<<"查询公司sql:"<<sql;
|
if(query.exec(sql)){
|
qDebug()<<"OK sql"<<__FUNCTION__;
|
QSqlRecord res = query.record();
|
qDebug()<<query.size()<<query.numRowsAffected()<<res.count();
|
if(!query.next()){
|
// 插入一条单位信息：
|
sql = QString("insert into company_info (name,code,机构类型,主要业务活动,行业代码,单位注册地及区域,"
|
"区域代码,城乡代码,单位规模,从业期末人数,法定代表人,执行会记标准类别,长途区号,固定电话,邮政编码,"
|
"电子邮箱,网址) values ('%1','%2','%3','%4','%5','%6','%7','%8','%9','%10','%11','%12','%13','%14','%15','%16','%17')").arg(ui->label_name->text())
|
.arg(ui->label_creditCode->text()).arg(ui->label_comType->text()).arg(ui->label_business->text()).arg(ui->label_businessCode->text()).arg(ui->label_addr->text())
|
.arg(ui->label_areaCode->text()).arg(ui->label_townCode->text()).arg(ui->label_scale->text()).arg(ui->label_people->text()).arg(ui->label_representative->text())
|
.arg(ui->label_standardType->text()).arg(ui->label_trunkCode->text()).arg(ui->label_fixedTel->text()).arg(ui->label_postCode->text()).arg(ui->label_email->text())
|
.arg(ui->label_webSite->text());
|
qDebug()<<"com_insert sql:"<<sql;
|
if(query.exec(sql)){
|
qDebug()<<"insert com ok";
|
}else{
|
qDebug()<<"insert com fail";
|
}
|
|
}else{
|
g_comId = query.value(0).toInt();
|
}
|
}else{
|
qDebug()<<"fail sql"<<__FUNCTION__;
|
}
|
}
|
sql = QString("select id,name from company_info");
|
if(query.exec(sql)){
|
qDebug()<<"size:"<<query.size();
|
while(query.next()){
|
int id = query.value(0).toInt();
|
QString name = query.value(1).toString();
|
qDebug()<<"com select:"<<id<<name;
|
bool isInBox = false;
|
for(int i=0;i<ui->comboBox->count();++i){
|
if(ui->comboBox->itemText(i) == name){
|
isInBox = true;
|
break;
|
}
|
}
|
if(!isInBox){
|
m_com[name] = id;
|
ui->comboBox->addItem(name);
|
emit sendComName(name);
|
ui->comboBox->setCurrentText(name);
|
}
|
}
|
}
|
|
}
|
|
void CoverInfo::readExcelFromPath(QString path)
|
{
|
QAxObject excel("Excel.Application");
|
excel.setProperty("Visible",false);
|
excel.setProperty("DisplayAlerts",false);
|
qDebug()<<"open:"<<path;
|
qDebug()<<"excel:"<<excel.className();
|
QAxObject * wbs = excel.querySubObject("WorkBooks");
|
QAxObject * wb = wbs->querySubObject("Open(QString&)",path);
|
//QAxObject* pWorkBook = excel.querySubObject("ActiveWorkBook");
|
//QAxObject* pWorkSheets = pWorkBook->querySubObject("Sheets");//获取工作表
|
QAxObject * shs = wb->querySubObject("Sheets"); // WorkSheets 也可以
|
int cnt = shs->property("Count").toInt();
|
qDebug()<<"cnt="<<cnt;
|
for(int i=0;i<cnt;++i){
|
QAxObject * sheet = shs->querySubObject("Item(int)", i+1);
|
QString sheetName = sheet->property("Name").toString();
|
qDebug()<<"sh name:"<<sheetName;
|
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;
|
QVariant val = usedRange->dynamicCall("Value"); // 拿到二维表格
|
QVariantList valRows = val.toList();
|
// 统一信用代码
|
QVariant uniCode = valRows[9].toList().at(3);
|
if(uniCode.isValid()){
|
ui->label_creditCode->setText(uniCode.toString());
|
}
|
// 单位名称
|
QVariant name = valRows[9].toList().at(8);
|
if(name.isValid()){
|
ui->label_name->setText(name.toString());
|
}
|
// 机构类型
|
QVariant comType = valRows[12].toList().at(3);
|
if(comType.isValid()){
|
ui->label_comType->setText(comType.toString());
|
}
|
// 主要业务活动
|
QString business;
|
QVariant first = valRows[18].toList().at(2);
|
if(first.isValid()){
|
business.append(first.toString());
|
}
|
QVariant sec = valRows[18].toList().at(5);
|
if(sec.isValid()){
|
business += "、";
|
business.append(sec.toString());
|
}
|
QVariant third = valRows[18].toList().at(8);
|
if(third.isValid()){
|
business += "、";
|
business.append(third.toString());
|
}
|
if(business.size() > 0){
|
ui->label_business->setText(business);
|
}
|
// 业务代码
|
QVariant busCode = valRows[19].toList().at(4);
|
if(busCode.isValid()){
|
ui->label_businessCode->setText(busCode.toString());
|
}
|
// 注册地址
|
QString addr="";
|
QVariant province = valRows[21].toList().at(1); // 省份
|
QVariant city = valRows[21].toList().at(4); // 城市
|
QVariant district = valRows[21].toList().at(7); // 管辖区
|
QVariant village = valRows[22].toList().at(2); // 乡镇
|
QVariant street = valRows[22].toList().at(4); // 街道
|
QVariant Sub = valRows[23].toList().at(2); // 街道办事处
|
QVariant committee = valRows[25].toList().at(3); // 居委会
|
//qDebug()<<province<<city<<district<<village<<street<<Sub<<committee;
|
if(province.isValid()) addr += province.toString();
|
if(city.isValid()) addr += city.toString();
|
if(district.isValid()) addr += district.toString();
|
if(village.isValid()) addr += village.toString();
|
if(street.isValid()) addr += street.toString();
|
if(Sub.isValid()) addr += Sub.toString();
|
if(committee.isValid()) addr += committee.toString();
|
if(addr.size()>10)ui->label_addr->setText(addr);
|
// 区域代码
|
QVariant areaCode = valRows[24].toList().at(2);
|
if(areaCode.isValid()) ui->label_areaCode->setText(areaCode.toString());
|
// 城乡代码
|
QVariant townCode = valRows[24].toList().at(6);
|
if(townCode.isValid()) ui->label_townCode->setText(townCode.toString());
|
// 单位规模
|
QVariant scale = valRows[25].toList().at(3);
|
if(scale.isValid()) ui->label_scale->setText(scale.toString());
|
// 从业人数
|
QVariant people = valRows[26].toList().at(6);
|
if(people.isValid()) ui->label_people->setText(people.toString());
|
// 法人
|
QVariant representative = valRows[27].toList().at(4);
|
if(representative.isValid()) ui->label_representative->setText(representative.toString());
|
// 会计标准类别 standardType
|
QVariant standardType = valRows[27].toList().at(8);
|
if(standardType.isValid()) ui->label_standardType->setText(standardType.toString());
|
// 长途区号
|
QVariant trunkCode = valRows[31].toList().at(2);
|
if(trunkCode.isValid()) ui->label_trunkCode->setText(trunkCode.toString());
|
// 固定电话
|
QVariant fixedTel = valRows[32].toList().at(2);
|
if(fixedTel.isValid()) ui->label_fixedTel->setText(fixedTel.toString());
|
// 邮政编码
|
QVariant postCode = valRows[33].toList().at(2);
|
if(postCode.isValid()) ui->label_postCode->setText(postCode.toString());
|
// 电子邮箱
|
QVariant email = valRows[30].toList().at(5);
|
if(email.isValid()) ui->label_email->setText(email.toString());
|
// 网址
|
QVariant webSite = valRows[32].toList().at(5);
|
if(webSite.isValid()) ui->label_webSite->setText(webSite.toString());
|
|
// 如果单位是新增加的，得写入到数据库中保存起来
|
readDataFromSQL(); // 再次调用API 确认数据进到表中
|
|
}
|
wbs->dynamicCall("Close()");
|
excel.dynamicCall("Quit(void)");
|
}
|
|
void CoverInfo::on_comboBox_currentIndexChanged(const QString &arg1)
|
{
|
QString sql = QString("select * from company_info where name='%1'").arg(arg1);
|
QSqlQuery query;
|
if(query.exec(sql)){
|
qDebug()<<"切换查询成功:"<<arg1;
|
while (query.next()) {
|
qDebug()<<"切换查询成功查询到目标单位:"<<arg1;
|
g_comId = query.value(0).toInt();
|
ui->label_name->setText(query.value(1).toString());
|
ui->label_creditCode->setText(query.value(2).toString());
|
ui->label_comType->setText(query.value(3).toString());
|
ui->label_business->setText(query.value(4).toString());
|
ui->label_businessCode->setText(query.value(5).toString());
|
ui->label_addr->setText(query.value(6).toString());
|
ui->label_areaCode->setText(query.value(7).toString());
|
ui->label_townCode->setText(query.value(8).toString());
|
ui->label_scale->setText(query.value(9).toString());
|
ui->label_people->setText(query.value(10).toString());
|
ui->label_representative->setText(query.value(11).toString());
|
ui->label_standardType->setText(query.value(12).toString());
|
ui->label_trunkCode->setText(query.value(13).toString());
|
ui->label_fixedTel->setText(query.value(14).toString());
|
ui->label_postCode->setText(query.value(15).toString());
|
ui->label_email->setText(query.value(16).toString());
|
ui->label_webSite->setText(query.value(17).toString());
|
break;
|
}
|
}else{
|
qDebug()<<"切换查询失败:"<<arg1;
|
}
|
}
|
|
void CoverInfo::on_pushButton_importExcel_clicked()
|
{
|
QString path = QFileDialog::getOpenFileName(this,"选择表格:单位及内部审计机构基本情况表","./","excel(*基本情况表*.xls)");
|
qDebug()<<"path:"<<path;
|
if(path.size() > 1){
|
readExcelFromPath(path);
|
}
|
}
|
