#include "coverinfo.h" #include "ui_coverinfo.h" #include <QDebug> #include <QDirIterator> #include <QFileDialog> #include <QSqlQuery> #include <QSqlRecord> #include "windows.h" int g_comId = 0; // 全局å˜é‡ï¼Œä¿å˜å•ä½id QString g_dataRootPath; // æ•°æ®çš„æ ¹ç›®å½• CoverInfo::CoverInfo(QWidget *parent) : QMainWindow(parent), ui(new Ui::CoverInfo) { ui->setupUi(this); CoInitializeEx(NULL, COINIT_MULTITHREADED); // 先将COM库åˆå§‹åŒ– //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; } g_dataRootPath = dirPath; // å°†æ ¹ç›®å½•ä¿å˜èµ·æ¥ 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"); if(!excel.setControl("Excel.Application")){ excel.setControl("ket.Application"); // wpså†…æ ¸ } 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()); }else{ ui->label_name->setText(""); } // 机构类型 QVariant comType = valRows[12].toList().at(3); if(comType.isValid()){ ui->label_comType->setText(comType.toString()); }else{ ui->label_comType->setText(""); } // 主è¦ä¸šåŠ¡æ´»åŠ¨ 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); ui->label_business->setToolTip(business); }else{ ui->label_business->setText(""); } // 业务代ç QVariant busCode = valRows[19].toList().at(4); if(busCode.isValid()){ ui->label_businessCode->setText(busCode.toString()); }else{ ui->label_businessCode->setText(""); } // æ³¨å†Œåœ°å€ 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); }else{ ui->label_addr->setText(""); } // 区域代ç QVariant areaCode = valRows[24].toList().at(2); if(areaCode.isValid()){ ui->label_areaCode->setText(areaCode.toString()); }else{ ui->label_areaCode->setText(""); } // 城乡代ç QVariant townCode = valRows[24].toList().at(6); if(townCode.isValid()) { ui->label_townCode->setText(townCode.toString()); }else{ ui->label_townCode->setText(""); } // å•ä½è§„模 QVariant scale = valRows[25].toList().at(3); if(scale.isValid()){ ui->label_scale->setText(scale.toString()); }else{ ui->label_scale->setText(""); } // 从业人数 QVariant people = valRows[26].toList().at(6); if(people.isValid()) { ui->label_people->setText(people.toString()); }else{ ui->label_people->setText(""); } // 法人 QVariant representative = valRows[27].toList().at(4); if(representative.isValid()) { ui->label_representative->setText(representative.toString()); }else{ ui->label_representative->setText(""); } // ä¼šè®¡æ ‡å‡†ç±»åˆ« standardType QVariant standardType = valRows[27].toList().at(8); if(standardType.isValid()){ ui->label_standardType->setText(standardType.toString()); }else{ ui->label_standardType->setText(""); } // é•¿é€”åŒºå· QVariant trunkCode = valRows[31].toList().at(2); if(trunkCode.isValid()){ ui->label_trunkCode->setText(trunkCode.toString()); }else{ ui->label_trunkCode->setText(""); } // å›ºå®šç”µè¯ QVariant fixedTel = valRows[32].toList().at(2); if(fixedTel.isValid()) { ui->label_fixedTel->setText(fixedTel.toString()); }else{ ui->label_fixedTel->setText(""); } // 邮政编ç QVariant postCode = valRows[33].toList().at(2); if(postCode.isValid()){ ui->label_postCode->setText(postCode.toString()); }else{ ui->label_postCode->setText(""); } // 电å邮箱 QVariant email = valRows[30].toList().at(5); if(email.isValid()) { ui->label_email->setText(email.toString()); }else{ ui->label_email->setText(""); } // ç½‘å€ QVariant webSite = valRows[32].toList().at(5); if(webSite.isValid()) { ui->label_webSite->setText(webSite.toString()); }else{ ui->label_webSite->setText(""); } } 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()); // 统一信用代ç 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()); }else{ ui->label_name->setText(""); } // 机构类型 QVariant comType = valRows[12].toList().at(3); if(comType.isValid()){ ui->label_comType->setText(comType.toString()); }else{ ui->label_comType->setText(""); } // 主è¦ä¸šåŠ¡æ´»åŠ¨ 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); }else{ ui->label_business->setText(""); } // 业务代ç QVariant busCode = valRows[19].toList().at(4); if(busCode.isValid()){ ui->label_businessCode->setText(busCode.toString()); }else{ ui->label_businessCode->setText(""); } // æ³¨å†Œåœ°å€ 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); }else{ ui->label_addr->setText(""); } // 区域代ç QVariant areaCode = valRows[24].toList().at(2); if(areaCode.isValid()){ ui->label_areaCode->setText(areaCode.toString()); }else{ ui->label_areaCode->setText(""); } // 城乡代ç QVariant townCode = valRows[24].toList().at(6); if(townCode.isValid()) { ui->label_townCode->setText(townCode.toString()); }else{ ui->label_townCode->setText(""); } // å•ä½è§„模 QVariant scale = valRows[25].toList().at(3); if(scale.isValid()){ ui->label_scale->setText(scale.toString()); }else{ ui->label_scale->setText(""); } // 从业人数 QVariant people = valRows[26].toList().at(6); if(people.isValid()) { ui->label_people->setText(people.toString()); }else{ ui->label_people->setText(""); } // 法人 QVariant representative = valRows[27].toList().at(4); if(representative.isValid()) { ui->label_representative->setText(representative.toString()); }else{ ui->label_representative->setText(""); } // ä¼šè®¡æ ‡å‡†ç±»åˆ« standardType QVariant standardType = valRows[27].toList().at(8); if(standardType.isValid()){ ui->label_standardType->setText(standardType.toString()); }else{ ui->label_standardType->setText(""); } // é•¿é€”åŒºå· QVariant trunkCode = valRows[31].toList().at(2); if(trunkCode.isValid()){ ui->label_trunkCode->setText(trunkCode.toString()); }else{ ui->label_trunkCode->setText(""); } // å›ºå®šç”µè¯ QVariant fixedTel = valRows[32].toList().at(2); if(fixedTel.isValid()) { ui->label_fixedTel->setText(fixedTel.toString()); }else{ ui->label_fixedTel->setText(""); } // 邮政编ç QVariant postCode = valRows[33].toList().at(2); if(postCode.isValid()){ ui->label_postCode->setText(postCode.toString()); }else{ ui->label_postCode->setText(""); } // 电å邮箱 QVariant email = valRows[30].toList().at(5); if(email.isValid()) { ui->label_email->setText(email.toString()); }else{ ui->label_email->setText(""); } // ç½‘å€ QVariant webSite = valRows[32].toList().at(5); if(webSite.isValid()) { ui->label_webSite->setText(webSite.toString()); }else{ ui->label_webSite->setText(""); } // 如果å•使˜¯æ–°å¢žåŠ çš„ï¼Œå¾—å†™å…¥åˆ°æ•°æ®åº“ä¸ä¿å˜èµ·æ¥ 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); } }