//xlsxexportor.h
#ifndef XLSXEXPORTOR_H
#define XLSXEXPORTOR_H
#include <QWidget>
#include "QXlsx/header/xlsxdocument.h"
#include "QDebug"
#include "qaxobject.h"
#include "qjsonobject.h"
namespace Ui {
class XlsxExportor;
}
class XlsxExportor : public QWidget
{
Q_OBJECT
public:
explicit XlsxExportor(QWidget *parent = nullptr);
~XlsxExportor();
void init_date_time_edit(void);
void get_already_params(void);
QList<QStringList> get_record_msg( QString startDate,QString stopDate );
void set_ui_controls(qint16 pageNo); //
void initListView(void);
void get_docs_to_fill(QDate start_time,QDate stopTime);
void get_docs_to_fill(QString sampleUnit);
void initial(void);
QJsonObject get_experment_params(void);
void printExcel(QString path);
void launchWpsWithPrintPreview( QString excelFilePath);
void bringExcelToFront(QAxObject *excel);
void setExcelBoreds(QAxObject* bordersRange);
void closeExcel() ;
public slots:
void export_test_table(QStringList tableHeadInfo,QList<QStringList> recordToExport);
void set_ui_on_top(bool flag);
void calculate_reses(QString temperatureVal,QString id,QList<QStringList>dtas);
QStringList caculate_permeability(QStringList caculParams);
QJsonObject get_efficient_params(void);
QStringList caculate_permeability_t20(float currentTemper,float efficientT);
void on_sig_is_experment_run(bool runOrNot);
void fill_other_report_params(QStringList params);
void fill_form_complete(QString id,QString timeStr);
void make_report(void);
void slot_set_res(QString id,QList<QStringList> recordToExport);
signals:
void sig_print_excel_finish(void);
void sig_is_experment_run(void);
void sig_dta_is_ready(QString id);
private slots:
void on_pushButton_4_clicked();
void on_pushButton_clicked();
void on_pushButton_2_clicked();
void on_pushButton_3_clicked();
void on_pushButton_5_clicked();
private:
Ui::XlsxExportor *ui;
public:
QList<QStringList> resCaculatedForExp; //23al; log; coefficientT; coefficient20;ratio; usedtime;starttime;stoptime;starthead;stophead;temperature;
};
#endif // XLSXEXPORTOR_H
//xlsxexportor.cpp
#include "xlsxexportor.h"
#include "ui_xlsxexportor.h"
#include "const_def.h"
#include "util.h"
#include "dbmanager.h"
#include "QXlsx/header/xlsxcellrange.h"
#include "qdesktopservices.h"
#include "qprinter.h"
#include "qprintdialog.h"
#include "QTableWidget"
#include "qpainter.h"
#include "qaxobject.h"
#include "qsplashscreen.h"
#include "qprocess.h"
#include "QtTest/qtest.h"
#include <windows.h>
#include "qmath.h"
XlsxExportor::XlsxExportor(QWidget *parent) :
QWidget(parent),
ui(new Ui::XlsxExportor)
{
ui->setupUi(this);
// this->setWindowFlags(QWidget::windowFlags() | Qt::WindowStaysOnTopHint);
this->setWindowFlags(QWidget::windowFlags() &~Qt::WindowMinMaxButtonsHint);
this->setWindowTitle(QString("报表文件信息"));
this->setFixedSize(1186,392);
set_ui_controls(0);
init_date_time_edit();
initListView();
// initial();
}
XlsxExportor::~XlsxExportor()
{
delete ui;
}
void XlsxExportor::slot_set_res(QString id,QList<QStringList> recordToExport)
{
resCaculatedForExp=recordToExport;
//fill_form_complete(id,recordToExport);
//make_report();
emit sig_dta_is_ready(id);
}
void XlsxExportor::fill_other_report_params(QStringList params)
{
if(params.count()<8){
qDebug()<<__FUNCTION__<<"user message is not enough!";
return ;
}
ui->lineEdit_1->setText(params.at(1)); //工程名称
ui->lineEdit_4->setText(params.at(5)); //表号
ui->lineEdit_5->setText(params.at(3)); //土样编号
ui->lineEdit_7->setText(params.at(2)); //孔隙比
ui->lineEdit_8->setText(params.at(7)); //试验者(计算)
ui->lineEdit_11->setText(params.at(4)); //土样说明
ui->lineEdit_12->setText(params.at(6)); //核校者
QDateTime tmpDateTime=QDateTime::fromString(params.at(8),"yyyy-MM-dd hh:mm:ss");
QString timeStr=tmpDateTime.toString("yyyy-MM-dd-hh-mm-ss");
QString reportName = QString("%1-%3-%2").arg(params.at(0)).arg(timeStr).arg(params.at(1));
ui->lineEdit_13->setText(reportName); //报表名
//qDebug()<<__FUNCTION__<<params<<reportName<<"glmhh";
}
void XlsxExportor::fill_form_complete(QString id,QString timeStr)
{
QString paramFName= QString("expermentparam%1.json").arg(id.trimmed());
QString paramsStr=QDir::currentPath()+"/data/device/"+paramFName;
//QJsonObject tmpObj;
QJsonObject expParams=Util::get_json_root_obj(paramsStr);
ui->lineEdit_2->setText(timeStr);
ui->lineEdit_3->setText(expParams.value("areaA").toString());
ui->lineEdit_6->setText(expParams.value("pipearea").toString());
ui->lineEdit_10->setText(expParams.value("earthlength").toString());
ui->lineEdit_9->setText(id.trimmed());
}
void XlsxExportor::make_report(void)
{
on_pushButton_4_clicked();
on_pushButton_3_clicked();
}
void XlsxExportor::calculate_reses(QString temperatureVal,QString id,QList<QStringList> dtas)
{
int resNum=0;
QDateTime startTime=QDateTime::fromString(dtas.at(0).at(3),"yyyy-MM-dd hh:mm:ss");
QDateTime stopTime=QDateTime::fromString(dtas.at(dtas.count()-1).at(3),"yyyy-MM-dd hh:mm:ss");
// long secsByPass=stopTime.secsTo(startTime);
//resNum=secsByPass/(RESINTERVAL*60);
//int restNums=secsByPass%(RESINTERVAL*60);
//if(restNums!=0&&resNum==0)resNum=1;
//int resIndexInterval=dtas.count()/resNum;
//QList<int> resParamIndex
QList<QStringList> resParams;
// if(secsByPass<=RESINTERVAL*60){
// resParams.append(dtas.at(0));
// resParams.append(dtas.at(dtas.count()-1));
// }
// else
// {
// }
QString paramsStr1=QDir::currentPath()+EXPERMENTPARAMS;
//QJsonObject tmpObj;
QJsonObject expParams1=Util::get_json_root_obj(paramsStr1);
int j=0;
resParams.append(dtas.at(0));
for(int i=1;i<dtas.count();++i){
if(QDateTime::fromString(resParams.at(j).at(3),"yyyy-MM-dd hh:mm:ss").secsTo(QDateTime::fromString(dtas.at(i).at(3),"yyyy-MM-dd hh:mm:ss"))>=expParams1.value("readinterval").toString().toInt()){
j++;
resParams.append(dtas.at(i));
}
else if(i==dtas.count()-1){
resParams.append(dtas.at(i));
}
}
QString paramFName= QString("expermentparam%1.json").arg(id.trimmed());
QString paramsStr=QDir::currentPath()+"/data/device/"+paramFName;
//QJsonObject tmpObj;
QJsonObject expParams=Util::get_json_root_obj(paramsStr);
QList<QStringList> resCaculated;
//pressureObject=expParams.value("density").toString().toFloat()*expParams.value("g").toString().toFloat()*expParams.value("waterlevelh").toString().toFloat()/100000;
//warterDropObject=expParams.value("density").toString().toFloat()*expParams.value("g").toString().toFloat()*expParams.value("waterlevell").toString().toFloat()/100000;
emit_gas_flag=expParams.value("emitgasnum").toString().toInt();
//emitgasobject=warterDropObject;
//waterHLevel=expParams.value("waterlevelh").toString().toFloat();
//waterLLevel=expParams.value("waterlevell").toString().toFloat();
//pipeArea=expParams.value("pipearea").toString().toFloat();
//sampleLength=expParams.value("earthlength").toString().toFloat();
//sampleA=expParams.value("areaA").toString().toFloat();
//wash_time=expParams.value("washtime").toString().toInt();
//wash_time_delay=WASHDELAY; //expParams.value("washtimedelay").toString().toInt();
for(int i=0;i<resParams.count();i++){
if(i>0){
int waterdropusedtime=QDateTime::fromString(resParams.at(i-1).at(3),"yyyy-MM-dd hh:mm:ss").secsTo(QDateTime::fromString(resParams.at(i).at(3),"yyyy-MM-dd hh:mm:ss"));
QStringList caculatedReses=caculate_permeability(QStringList()<<resParams.at(i-1).at(2)<<resParams.at(i).at(2)<<expParams.value("pipearea").toString()<<expParams.value("earthlength").toString()<<expParams.value("areaA").toString()
<<QString::number(waterdropusedtime));
QStringList coefficient20=caculate_permeability_t20(temperatureVal.trimmed().toFloat(),caculatedReses.at(caculatedReses.count()-1).toFloat());
caculatedReses.append(coefficient20);
caculatedReses.append(QString::number(waterdropusedtime));
caculatedReses.append(resParams.at(i-1).at(3));
caculatedReses.append(resParams.at(i).at(3));
caculatedReses.append(QString::number(resParams.at(i-1).at(2).toFloat()*100,'f',2));
caculatedReses.append(QString::number(resParams.at(i).at(2).toFloat()*100,'f',2));
caculatedReses.append(temperatureVal);
resCaculated.append(caculatedReses);
//23al; log; coefficientT; coefficient20;ratio; usedtime;starttime;stoptime;starthead;stophead;temperature;
}
}
resCaculatedForExp=resCaculated;
ui->lineEdit_2->setText(dtas.at(0).at(3));
ui->lineEdit_3->setText(expParams.value("areaA").toString());
ui->lineEdit_6->setText(expParams.value("pipearea").toString());
ui->lineEdit_10->setText(expParams.value("earthlength").toString());
ui->lineEdit_9->setText(id.trimmed());
}
QStringList XlsxExportor::caculate_permeability_t20(float currentTemper,float efficientT)
{
qDebug()<<__FUNCTION__;
QJsonObject tmpObj = get_efficient_params();
QStringList tmpList;
if(tmpObj.isEmpty()||efficientT==-1){
qDebug()<<__FUNCTION__<<QString("error: param is empty!");
return tmpList;
}
int lastNumOfTemper=(int)(currentTemper*10)%10;
float roundRes=0;
if(currentTemper<5){
roundRes=5;
}
else if(currentTemper>35){
roundRes=35;
}
else if(currentTemper<23){
if(lastNumOfTemper<2.5)
{
roundRes=qFloor(currentTemper);
}
else if(lastNumOfTemper<=7.5)
{
// int test=qFloor(currentTemper);
roundRes=qFloor(currentTemper)+0.5;
}
else {
roundRes=qCeil(currentTemper);
}
}
else
{
if(lastNumOfTemper<5)
{
roundRes=qFloor(currentTemper);
}
else {
roundRes=qCeil(currentTemper);
}
}
float tmpTemper=roundRes;
float coefficientT=0;
for(int i=0;i<tmpObj.count();++i){
if(tmpObj.value(QString::number(i+1)).toString().split("-").at(1).toFloat()==tmpTemper){
coefficientT=tmpObj.value(QString::number(i+1)).toString().split("-").at(3).toFloat();
break;
}
}
float cofficient20=-1;
cofficient20=efficientT*coefficientT;
qDebug()<<__FUNCTION__<<cofficient20<<efficientT<<coefficientT;
return QStringList()<<QString::number(cofficient20)<<QString::number(coefficientT);
}
QJsonObject XlsxExportor::get_efficient_params(void)
{
qDebug()<<__FUNCTION__;
QString efficients=QDir::currentPath()+ COEFFICIENTS ;
QJsonObject tmpEfficients;
QFileInfo tmpFil1(efficients);
if(tmpFil1.exists()){
tmpEfficients=Util::get_json_root_obj(efficients);
}
return tmpEfficients;
}
QStringList XlsxExportor::caculate_permeability(QStringList caculParams)
{
QStringList result;
if(caculParams.isEmpty())result ;
float waterHLevelPro,waterLLevelPro;
waterHLevelPro=caculParams.at(0).toFloat()*100;
waterLLevelPro=caculParams.at(1).toFloat()*100;
// expermentOutPut.insert("RealWaterH",QString::number(waterHLevelPro*100));
// expermentOutPut.insert("RealWaterL",QString::number(waterLLevelPro*100));
float res=0;
float num1,num2,num3,num4,num5,num6;
num1=2.3*caculParams.at(2).toFloat()*caculParams.at(3).toFloat()*qLn((float)waterHLevelPro/waterLLevelPro);
num2=caculParams.at(4).toFloat()*caculParams.at(5).toFloat()*qLn(10);
num3=2.3*caculParams.at(2).toFloat()*caculParams.at(3).toFloat();
num4=caculParams.at(4).toFloat()*caculParams.at(5).toFloat();
num5=qLn((float)waterHLevelPro/waterLLevelPro)/qLn(10);
num6=num3/num4;
result.append(QString::number(num6)); //23al
result.append(QString::number(num5)); //log
if(num2<=0){
result.append(QString("-1"));
return result;
}
res=num1/num2;
// qDebug()<<__FUNCTION__<<res<<num1<<num2<<pipeArea<<sampleLength<<waterHLevelPro<<waterLLevelPro<<qLn(waterHLevelPro/waterLLevelPro)<<waterDropUsedTime;
result.append(QString::number(res));
return result;
}
void XlsxExportor::closeExcel()
{
QProcess *process = new QProcess();
QString program = "taskkill";
QStringList arguments;
arguments << "/F" << "/IM" << "EXCEL.EXE";
process->start(program, arguments);
if (process->waitForFinished()) {
qDebug() << "Excel has been closed.";
} else {
qDebug() << "Failed to close Excel.";
}
delete process;
}
void XlsxExportor::setExcelBoreds(QAxObject* bordersRange)
{
//区域内部水平框线
QAxObject* borders_inside_hor = bordersRange->querySubObject("Borders(xlInsideHorizontal)");
//区域内部垂直框线
QAxObject* borders_inside_ver = bordersRange->querySubObject("Borders(xlInsideVertical)");
//左边框线
QAxObject* borders_left = bordersRange->querySubObject("Borders(xlEdgeLeft)");
//右边框线
QAxObject* borders_right = bordersRange->querySubObject("Borders(xlEdgeRight)");
//顶边框线
QAxObject* borders_top = bordersRange->querySubObject("Borders(xlEdgeTop)");
//下边框线
QAxObject* borders_bottom = bordersRange->querySubObject("Borders(xlEdgeBottom)");
QList<QAxObject*> border_list = {borders_left, borders_right, borders_top, borders_bottom};
for (auto& one : border_list) {
one->setProperty("Color", QColor(0, 0, 0));//设置颜色
one->setProperty("LineStyle",1);//设置线型实线
one->setProperty("Weight",2);//设置宽度
one->setProperty("ColorIndex",1);
}
}
void XlsxExportor::export_test_table(QStringList tableHeadInfo,QList<QStringList> recordToExport)
{
//qDebug()<<__FUNCTION__<<recordToExport<<tableHeadInfo;
closeExcel();
QString tmpXlsPath=QDir::currentPath()+QString(TESTTABLE)+"/"+tableHeadInfo.at(12)+".xlsx";//"-"+QDateTime::currentDateTime().toString("yyyy-MM-dd-hh-mm-ss")+".xlsx";
QFile tmpFile(tmpXlsPath);
if(tmpFile.exists()){
tmpFile.remove();
}
QFile::copy(QDir::currentPath()+EXCELTEMPLATE,tmpXlsPath);
QAxObject *excel=NULL;
excel = new QAxObject("Excel.Application");
if(!excel){
QMessageBox::warning(this,"警告","Excel未安装,请安装Excel!","确定");
return ;
}
// excel->setProperty("DisplayAlerts",false);
excel->dynamicCall("Quit()");
excel->setProperty("Visible", true);
QAxObject *workbooks = excel->querySubObject("Workbooks");
workbooks->dynamicCall("Open (const QString&)", tmpXlsPath);
QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
QAxObject *sheets = workbook->querySubObject("Sheets");
QAxObject *sheet = sheets->querySubObject("Item(int)", 1);
QAxObject *range15 = sheet->querySubObject("Range(const QString&)", QString("B2"));
range15->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(0));
QAxObject *range16 = sheet->querySubObject("Range(const QString&)", QString("G2"));
QStringList timeStr3=tableHeadInfo.at(1).split(" ");
QString tmpStr3;
tmpStr3=timeStr3.at(0)+QString("-")+timeStr3.at(1);
range16->dynamicCall("SetValue(const QVariant&)", tmpStr3);
QAxObject *range17 = sheet->querySubObject("Range(const QString&)", QString("M2"));
range17->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(3));
QAxObject *range18 = sheet->querySubObject("Range(const QString&)", QString("B3"));
range18->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(4));
QAxObject *range19 = sheet->querySubObject("Range(const QString&)", QString("G3"));
range19->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(5));
QAxObject *range20 = sheet->querySubObject("Range(const QString&)", QString("J3"));
range20->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(6));
QAxObject *range21 = sheet->querySubObject("Range(const QString&)", QString("M3"));
range21->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(7));
QAxObject *range22 = sheet->querySubObject("Range(const QString&)", QString("B4"));
range22->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(8));
QAxObject *range23 = sheet->querySubObject("Range(const QString&)", QString("G4"));
range23->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(9));
QAxObject *range24 = sheet->querySubObject("Range(const QString&)", QString("J4"));
range24->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(10));
QAxObject *range25 = sheet->querySubObject("Range(const QString&)", QString("M4"));
range25->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(11));
QAxObject *range26 = sheet->querySubObject("Range(const QString&)", QString("J2"));
range26->dynamicCall("SetValue(const QVariant&)", tableHeadInfo.at(2));
float tmpListForAver=0;
for(int i=0;i<recordToExport.count();++i){
QAxObject *range = sheet->querySubObject("Range(const QString&)", QString("A%1").arg(8+i));
QStringList timeStr1=recordToExport.at(i).at(6).split(" ");
QString tmpStr1;
tmpStr1=timeStr1.at(0)+QString("\n")+timeStr1.at(1);
range->dynamicCall("SetValue(const QVariant&)", tmpStr1);
setExcelBoreds(range);
QAxObject *range1 = sheet->querySubObject("Range(const QString&)", QString("B%1").arg(8+i));
QStringList timeStr2=recordToExport.at(i).at(7).split(" ");
QString tmpStr2;
tmpStr2=timeStr2.at(0)+QString("\n")+timeStr2.at(1);
range1->dynamicCall("SetValue(const QVariant&)", tmpStr2);
setExcelBoreds(range1);
QStringList tmpTime=Util::secondsToHMS(recordToExport.at(i).at(5).toFloat());
QAxObject *range2 = sheet->querySubObject("Range(const QString&)", QString("C%1").arg(8+i));
range2->dynamicCall("SetValue(const QVariant&)", tmpTime.at(0));
setExcelBoreds(range2);
QAxObject *range3 = sheet->querySubObject("Range(const QString&)", QString("D%1").arg(8+i));
range3->dynamicCall("SetValue(const QVariant&)", tmpTime.at(1));
setExcelBoreds(range3);
QAxObject *range4 = sheet->querySubObject("Range(const QString&)", QString("E%1").arg(8+i));
range4->dynamicCall("SetValue(const QVariant&)", recordToExport.at(i).at(8));
setExcelBoreds(range4);
QAxObject *range5 = sheet->querySubObject("Range(const QString&)", QString("F%1").arg(8+i));
range5->dynamicCall("SetValue(const QVariant&)", recordToExport.at(i).at(9));
setExcelBoreds(range5);
QAxObject *range6 = sheet->querySubObject("Range(const QString&)", QString("G%1").arg(8+i));
range6->dynamicCall("SetValue(const QVariant&)", QString::number(recordToExport.at(i).at(0).toFloat(),'E',3));
setExcelBoreds(range6);
QAxObject *range7 = sheet->querySubObject("Range(const QString&)", QString("H%1").arg(8+i));
range7->dynamicCall("SetValue(const QVariant&)", QString::number(recordToExport.at(i).at(1).toFloat(),'E',3));
setExcelBoreds(range7);
QAxObject *range8 = sheet->querySubObject("Range(const QString&)", QString("I%1").arg(8+i));
range8->dynamicCall("SetValue(const QVariant&)", QString::number(recordToExport.at(i).at(2).toFloat(),'E',3));
setExcelBoreds(range8);
QAxObject *range9 = sheet->querySubObject("Range(const QString&)", QString("J%1").arg(8+i));
range9->dynamicCall("SetValue(const QVariant&)", recordToExport.at(i).at(10));
setExcelBoreds(range9);
QAxObject *range10 = sheet->querySubObject("Range(const QString&)", QString("K%1").arg(8+i));
range10->dynamicCall("SetValue(const QVariant&)", recordToExport.at(i).at(4));
setExcelBoreds(range10);
QAxObject *range11 = sheet->querySubObject("Range(const QString&)", QString("L%1").arg(8+i));
if(recordToExport.count()>5){
if(i>recordToExport.count()-5-1)
tmpListForAver+=recordToExport.at(i).at(3).toFloat();
}
else{
if(i==recordToExport.count()-1)
tmpListForAver+=recordToExport.at(i).at(3).toFloat();
}
range11->dynamicCall("SetValue(const QVariant&)", QString::number(recordToExport.at(i).at(3).toFloat(),'E',3));
setExcelBoreds(range11);
QAxObject *range12 = sheet->querySubObject("Range(const QString&)", QString("M%1").arg(8+i));
// range12->dynamicCall("SetValue(const QVariant&)", QString("=AVERAGE(L8:L%1)").arg(8+i));
float averValue=0;
if(recordToExport.count()>5){
if(i>recordToExport.count()-5-1){
averValue = tmpListForAver/(i-recordToExport.count()+5+1);
range12->dynamicCall("SetValue(const QVariant&)", QString::number(averValue,'E',3));
setExcelBoreds(range12);
}
else{
range12->dynamicCall("SetValue(const QVariant&)", "");
setExcelBoreds(range12);
}
}
else{
if(i==recordToExport.count()-1){
averValue+=tmpListForAver;
range12->dynamicCall("SetValue(const QVariant&)", QString::number(averValue,'E',3));
setExcelBoreds(range12);
}
else{
range12->dynamicCall("SetValue(const QVariant&)", "");
setExcelBoreds(range12);
}
}
QAxObject *range13 = sheet->querySubObject("Range(const QString&)", QString("N%1").arg(8+i));
range13->dynamicCall("SetValue(const QVariant&)", "");
setExcelBoreds(range13);
}
// QFile tmpFile(tmpXlsPath);
// if(tmpFile.exists()){
// tmpFile.remove();
// }
// 保存 Excel 文件
workbook->dynamicCall("Save",true);
//关闭excel
if (excel != NULL)
{
excel->dynamicCall("Quit()");
delete excel;
excel = NULL;
}
// 打开对应的文件并显示于excell中
QString local_path=tmpXlsPath;
QString path=local_path;
bool isOpen=QDesktopServices::openUrl(QUrl("file:"+path,QUrl::TolerantMode)); //完成后打开报表
qDebug()<<__FUNCTION__<<isOpen<<path<<tmpXlsPath;
}
void XlsxExportor::on_pushButton_4_clicked()
{
// QStringList headeInfo;
// QLineEdit *tmpLineEdit;
// for(int i=0;i<13;++i){
// tmpLineEdit=window()->findChild<QLineEdit *>(QString("lineEdit_%1").arg(i+1));
// if(tmpLineEdit->text()==""){
// QMessageBox::warning(this,QString("警告"),QString("输入不能为空!"),QString("确定"));
// return ;
// }
// if(i==12&&tmpLineEdit->text().contains('-')){
// QMessageBox::warning(this,QString("警告"),QString("输入错误,请排除 '-'"),QString("确定"));
// return;
// }
// headeInfo.append(tmpLineEdit->text());
// }
//if(ui->dateTimeEdit_start->dateTime()>ui->dateTimeEdit_stop->dateTime()){
// QMessageBox::warning(this,QString("警告"),QString("开始时间大于结束时间,请做修改!"),QString("确定"));
// return;
//}
//headeInfo.append(ui->dateTimeEdit_start->dateTime().toString("yyyy-MM-dd hh:mm:ss"));
//headeInfo.append(ui->dateTimeEdit_stop->dateTime().toString("yyyy-MM-dd hh:mm:ss"));
//QList<QStringList> recordMsg=get_record_msg(ui->dateTimeEdit_start->dateTime().toString("yyyy-MM-dd hh:mm:ss"),ui->dateTimeEdit_stop->dateTime().toString("yyyy-MM-dd hh:mm:ss"));
//if(recordMsg.isEmpty()){
// QMessageBox::warning(this,QString("警告"),QString("查询数据为空,无法导出,请正确填写日期时间后重试!"),QString("确定"));
// return;
//}
//export_test_table(headeInfo,recordMsg);
//this->close();
QStringList headeInfo;
QLineEdit *tmpLineEdit;
for(int i=0;i<13;++i){
tmpLineEdit=window()->findChild<QLineEdit *>(QString("lineEdit_%1").arg(i+1));
if(tmpLineEdit->text()==""){
QMessageBox::warning(this,QString("警告"),QString("输入不能为空!"),QString("确定"));
return ;
}
// if(i==12&&tmpLineEdit->text().contains('-')){
// QMessageBox::warning(this,QString("警告"),QString("输入错误,请排除 '-'"),QString("确定"));
// return;
// }
headeInfo.append(tmpLineEdit->text());
}
qDebug()<<__FUNCTION__<<headeInfo<<"glmhh";
// if(ui->dateTimeEdit_start->dateTime()>ui->dateTimeEdit_stop->dateTime()){
// QMessageBox::warning(this,QString("警告"),QString("开始时间大于结束时间,请做修改!"),QString("确定"));
// return;
// }
// headeInfo.append(ui->dateTimeEdit_start->dateTime().toString("yyyy-MM-dd hh:mm:ss"));
// headeInfo.append(ui->dateTimeEdit_stop->dateTime().toString("yyyy-MM-dd hh:mm:ss"));
// QList<QStringList> recordMsg=get_record_msg(ui->dateTimeEdit_start->dateTime().toString("yyyy-MM-dd hh:mm:ss"),ui->dateTimeEdit_stop->dateTime().toString("yyyy-MM-dd hh:mm:ss"));
// if(recordMsg.isEmpty()){
// QMessageBox::warning(this,QString("警告"),QString("查询数据为空,无法导出,请正确填写日期时间后重试!"),QString("确定"));
// return;
// }
export_test_table(headeInfo,resCaculatedForExp);
this->close();
}
QJsonObject XlsxExportor::get_experment_params(void)
{
QJsonObject expParams;
QString paramsStr=QDir::currentPath()+EXPERMENTPARAMS;
QJsonObject tmpObj;
if(!QFileInfo(paramsStr).exists())
{
QMessageBox::warning(this,QString("警告"),QString("没有配置文件,请先在参数设置中设置参数!"),QString("确定"));
return expParams;
}
expParams=Util::get_json_root_obj(paramsStr);
// pressureObject=expParams.value("density").toString().toFloat()*expParams.value("g").toString().toFloat()*expParams.value("waterlevelh").toString().toFloat()/1000;
// warterDropObject=expParams.value("density").toString().toFloat()*expParams.value("g").toString().toFloat()*expParams.value("waterlevell").toString().toFloat()/1000;
emit_gas_flag=expParams.value("emitgasnum").toString().toInt();
// emitgasobject=warterDropObject;
// waterHLevel=expParams.value("waterlevelh").toString().toFloat();
// waterLLevel=expParams.value("waterlevell").toString().toFloat();
// pipeArea=expParams.value("pipearea").toString().toFloat();
// sampleLength=expParams.value("earthlength").toString().toFloat();
// sampleA=expParams.value("areaA").toString().toFloat();
return expParams;
}
void XlsxExportor::init_date_time_edit(void)
{
QDateTime tmpDate=QDateTime::currentDateTime();
ui->dateEdit_start->setDisplayFormat("yyyy-MM-dd");
ui->dateEdit_start->setDateTime(tmpDate);
ui->dateEdit_start->setCalendarPopup(true);
ui->dateEdit_stop->setDisplayFormat("yyyy-MM-dd");
ui->dateEdit_stop->setDateTime(tmpDate);
ui->dateEdit_stop->setCalendarPopup(true);
}
void XlsxExportor::get_already_params(void)
{
QString paramsStr=QDir::currentPath()+EXPERMENTPARAMS;
QJsonObject tmpObj;
if(!QFileInfo(paramsStr).exists())return;
tmpObj=Util::get_json_root_obj(paramsStr);
// ui->lineEdit->setText(tmpObj.value("density").toString());
// ui->lineEdit_2->setText(tmpObj.value("g").toString());
// ui->lineEdit_3->setText(tmpObj.value("waterlevelh").toString());
// ui->lineEdit_4->setText(tmpObj.value("waterlevell").toString());
// ui->lineEdit_5->setText(tmpObj.value("emitgasnum").toString());
ui->lineEdit_6->setText(tmpObj.value("pipearea").toString());
ui->lineEdit_10->setText(tmpObj.value("earthlength").toString());
ui->lineEdit_3->setText(tmpObj.value("areaA").toString());
}
QList<QStringList> XlsxExportor::get_record_msg( QString startDate,QString stopDate )
{
qDebug()<<__FUNCTION__<<startDate<<stopDate;
QString db_tablename = DBManager::GetInstance()->get_db_tablename( experment_record );
QList<QString> coulmnname;
coulmnname.append( DBManager::GetInstance()->get_db_column( db_tablename ) );
QList<QStringList> result;
if( coulmnname.count() == EXPERMENT_RECORD )
{
// startDate=startDate+QString(" 00:00:00");
// stopDate=stopDate+QString(" 23:59:59");
QString sqlWhere = QString("where %1 between '%2' and '%3'").arg(coulmnname.at(8)).arg(startDate).arg(stopDate);
DBManager::GetInstance()->select_datas(db_tablename, "select", result, sqlWhere);
}
return result;
}
void XlsxExportor::set_ui_controls(qint16 pageNo)
{
qDebug()<<__FUNCTION__<<pageNo;
if(pageNo==0){
ui->stackedWidget->setCurrentIndex(pageNo);
ui->pushButton_3->hide();
ui->radioButton->hide();
ui->radioButton_2->hide();
ui->lineEdit->hide();
ui->label_13->setVisible(true);
ui->label_14->setVisible(true);
ui->dateEdit_start->setVisible(true);
ui->dateEdit_stop->setVisible(true);
ui->radioButton->setChecked(true);
}
else if(pageNo==1){
ui->stackedWidget->setCurrentIndex(pageNo);
ui->pushButton_3->setVisible(true);
ui->radioButton->setVisible(true);
ui->radioButton_2->setVisible(true);
ui->lineEdit->setVisible(true);
ui->label_13->setVisible(true);
ui->label_14->setVisible(true);
ui->dateEdit_start->setVisible(true);
ui->dateEdit_stop->setVisible(true);
}
else if(pageNo==2){
ui->stackedWidget->setCurrentIndex(0);
ui->label_13->hide();
ui->label_14->hide();
ui->dateEdit_start->hide();
ui->dateEdit_stop->hide();
ui->pushButton_3->setVisible(false);
ui->radioButton->setVisible(false);
ui->radioButton_2->setVisible(false);
ui->lineEdit->setVisible(false);
}
}
void XlsxExportor::initListView(void)
{
QListView *listView = ui->listView;
QStandardItemModel * model = new QStandardItemModel( listView );
model->setHorizontalHeaderLabels( QStringList()<<"文件名" );
listView->setEditTriggers(QAbstractItemView::NoEditTriggers);
listView->setModel( model );
QDate stop_time=ui->dateEdit_stop->date();
QDate start_time=stop_time.addDays(-7);
ui->dateEdit_start->setDate(start_time);
get_docs_to_fill(start_time,stop_time);
}
void XlsxExportor::on_pushButton_clicked()
{
if(ui->listView->currentIndex().row()==-1)
{
QMessageBox::warning(this,QString("警告"),QString("未选中,请重试!"),QString("确定"));
return ;
}
QStandardItemModel * model =(QStandardItemModel *)ui->listView->model();
// ui->listView->currentIndex();
// qDebug()<<__FUNCTION__<<ui->listView->currentIndex()<<model->data(ui->listView->currentIndex(),Qt::DisplayRole);
//打开对应的文件并显示于excell中
QString local_path=QDir::currentPath()+QString(TESTTABLE)+"/"+model->data(ui->listView->currentIndex(),Qt::DisplayRole).toString();
qDebug()<<__FUNCTIONW__<<local_path;
QString path=local_path;
bool isOpen=QDesktopServices::openUrl(QUrl("file:"+path,QUrl::TolerantMode)); //完成后打开报表
this->close();
}
void XlsxExportor::get_docs_to_fill(QDate start_time,QDate stopTime)
{
if(start_time>stopTime){
QMessageBox::warning(this,"警告","开始时间不能超过结束时间!","确定");
return ;
}
qDebug()<<__FUNCTION__<<start_time<<stopTime;
QStandardItemModel * model =(QStandardItemModel *)ui->listView->model();
model->removeRows(0,model->rowCount());
QString tmpXlsPath=QDir::currentPath()+QString(TESTTABLE);
QDir testFormDocs(tmpXlsPath);
QFileInfoList filNameList=testFormDocs.entryInfoList(QStringList()<<"*.xlsx");
QFileInfoList filNameList1;
QStringList tmpStrList;
QStandardItem *tmpItem;
for(QFileInfo it:filNameList){
tmpStrList=it.fileName().split("\\");
QDate tmpDatetime;
QString tmpStr1=tmpStrList.last();
QString tmpStrUtil = tmpStr1;
QStringList tmpList2 = tmpStr1.split("-");
tmpList2.removeFirst();
tmpList2.removeFirst();
tmpStr1=tmpList2.join("-");
tmpStr1.chop(14);
tmpDatetime=QDate::fromString(tmpStr1,"yyyy-MM-dd");
qDebug()<<__FUNCTION__<<tmpDatetime<<tmpStr1<<tmpStrList.last();
if(tmpDatetime>=start_time&&tmpDatetime<=stopTime)
{
qDebug()<<__FUNCTION__<<tmpDatetime;
tmpItem=new QStandardItem(tmpStrUtil);
model->appendRow(tmpItem);
}
}
qDebug()<<__FUNCTION__<<filNameList;
}
void XlsxExportor::get_docs_to_fill(QString sampleUnit)
{
qDebug()<<__FUNCTION__<<sampleUnit;
QStandardItemModel * model =(QStandardItemModel *)ui->listView->model();
model->removeRows(0,model->rowCount());
QString tmpXlsPath=QDir::currentPath()+QString(TESTTABLE);
QDir testFormDocs(tmpXlsPath);
QFileInfoList filNameList=testFormDocs.entryInfoList(QStringList()<<"*.xlsx");
QFileInfoList filNameList1;
QStringList tmpStrList;
QStandardItem *tmpItem;
for(QFileInfo it:filNameList){
tmpStrList=it.fileName().split("\\");
// qDebug()<<it.fileName()<<it.filePath();
if(tmpStrList.last().contains(sampleUnit))
{
qDebug()<<__FUNCTION__<<tmpStrList;
tmpItem=new QStandardItem(tmpStrList.last());
model->appendRow(tmpItem);
}
}
qDebug()<<__FUNCTION__<<filNameList;
}
void XlsxExportor::on_pushButton_2_clicked()
{
get_docs_to_fill(ui->dateEdit_start->date(),QDate::currentDate());
}
void XlsxExportor::on_pushButton_3_clicked()
{
if(ui->radioButton->isChecked())
get_docs_to_fill(ui->dateEdit_start->date(),ui->dateEdit_stop->date());
else if(ui->radioButton_2->isChecked()){
if(ui->lineEdit->text()==""){
QMessageBox::warning(this,"警告","输入为空，请重新输入！","确定");
return;
}
get_docs_to_fill(ui->lineEdit->text());
}
}
void XlsxExportor::initial(void)
{
QJsonObject tmpParams = get_experment_params();
if(!tmpParams.isEmpty()){
ui->lineEdit_3->setText(tmpParams.value("areaA").toString());
ui->lineEdit_6->setText(tmpParams.value("pipearea").toString());
ui->lineEdit_10->setText(tmpParams.value("earthlength").toString());
}
}
void XlsxExportor::launchWpsWithPrintPreview( QString excelFilePath) {
qDebug()<<__FUNCTION__<<excelFilePath;
// 构建启动WPS Office并打开Excel文件的命令
QString wpsCommand = QString("C:/Program Files (x86)/Kingsoft Office Software/WPS Office/ksolaunch.exe") + " " + excelFilePath;
// 构建WPS Office中打开打印预览的命令
QString printPreviewCommand = QString("C:/Program Files (x86)/Kingsoft Office Software/WPS Office/11.1.0.12165/office6/wpp.exe") + " /p " + excelFilePath;
// 启动WPS Office打开Excel文件
QProcess::startDetached(wpsCommand);
// 启动WPS Office打印预览
// QProcess::startDetached(printPreviewCommand);
}
void XlsxExportor::bringExcelToFront(QAxObject *excel) {
// 确保Excel已经被初始化并打开
if (excel) {
qDebug()<<__FUNCTION__;
// 使用AppActivate方法激活Excel应用程序
excel->dynamicCall("AppActivate(const QString&)", QApplication::applicationName());
// 等待一会儿以让Excel窗口能够激活
// QTest::qSleep(500);
// // 发送Ctrl + F11键到键盘，这会切换到最前端
// QTest::keyClick(QApplication::focusWidget(), Qt::Key_F11, Qt::ControlModifier);
}
}
void XlsxExportor::printExcel(QString path)
{
qDebug()<<__FUNCTION__;
QAxObject *excel=NULL;
excel = new QAxObject("Excel.Application");
if(!excel){
QMessageBox::warning(this,"警告","Excel未安装,请安装Excel!","确定");
return ;
}
excel->setProperty("Visible", true);
QAxObject *workbooks = excel->querySubObject("Workbooks");
workbooks->dynamicCall("Open (const QString&)", path);
QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
QAxObject *sheets = workbook->querySubObject("Sheets");
QAxObject *sheet = sheets->querySubObject("Item(int)", 1);
int i=10;
while (i>0) {
i--;
HWND hWnd = NULL;
QString excelName= excel->dynamicCall("Caption").toString();
std::wstring wStr=excelName.toStdWString();
hWnd = ::FindWindow(NULL, wStr.c_str()); // 替换为你要查找的应用程序标题
if (hWnd) {
// 成功找到窗口句柄
qDebug()<<__FUNCTION__<<"excel"<<"ok";
SetForegroundWindow(hWnd);
break;
} else {
// 未找到窗口句柄
qDebug()<<__FUNCTION__<<"excel"<<"nok";
}
qDebug()<<__FUNCTION__<<"excel"<<i;
}
// 打开打印预览
sheet->dynamicCall("PrintPreview(bool)", true);
// sheet->dynamicCall("PrintOut");
workbook->dynamicCall("Close(bool)",false);//关闭工作簿
// myProcess->kill();
//关闭excel
if (excel != NULL)
{
excel->dynamicCall("Quit()");
delete excel;
excel = NULL;
}
emit sig_print_excel_finish();
}
void XlsxExportor::on_sig_is_experment_run(bool runOrNot)
{
if(runOrNot){
QMessageBox::warning(this,QString("警告"),QString("试验正在运行,禁止该项操作!"),QString("确定"));
return ;
}
if(ui->listView->currentIndex().row()==-1)
{
QMessageBox::warning(this,QString("警告"),QString("未选中,请重试!"),QString("确定"));
return ;
}
QStandardItemModel * model =(QStandardItemModel *)ui->listView->model();
// ui->listView->currentIndex();
// qDebug()<<__FUNCTION__<<ui->listView->currentIndex()<<model->data(ui->listView->currentIndex(),Qt::DisplayRole);
//打开对应的文件并显示于excell中
QString local_path=QDir::currentPath()+QString(TESTTABLE)+"/"+model->data(ui->listView->currentIndex(),Qt::DisplayRole).toString();
printExcel(local_path);
}
void XlsxExportor::on_pushButton_5_clicked()
{
emit sig_is_experment_run();
}
void XlsxExportor::set_ui_on_top(bool flag)
{
if(flag)
this->setWindowFlags(QWidget::windowFlags() | Qt::WindowStaysOnTopHint);
else
this->setWindowFlags(QWidget::windowFlags() & ~Qt::WindowStaysOnTopHint);
}
