public function dcsbExport(){
$param = Request::instance()->param();
$begin = strtotime($param['day1']);
$end = strtotime($param['day2']);
$stime = date('Y-m-d 00:00:01', $begin);
$etime = date('Y-m-d H:00:00', $end + 86410);
// halt($etime);
//引入PHPExcel导出类
vendor("PHPExcel.PHPExcel");
$sheetIndex=0;
$objPHPExcel = new \PHPExcel();
$sheet = $objPHPExcel->setActiveSheetIndex($sheetIndex++);
$sheet->setTitle('同比水量日明细');
$objPHPExcel->getProperties()->setCreator("ctos")
->setLastModifiedBy("ctos")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 水平居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);// 垂直居中
$sheet->getDefaultColumnDimension()->setWidth(15);//设置默认列宽度
$sheet->getColumnDimension('A')->setWidth(30);//设置某列宽度
// AutoSize
$objPHPExcel->getActiveSheet()->SetCellValue('A1', '电磁水表在装表统计');
$objPHPExcel->getActiveSheet()->mergeCells('A1:J1');//合并单元格
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);//设置默认行高度
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);//设置某一行高度
$objPHPExcel->getActiveSheet()->SetCellValue('A2', '单位');
$objPHPExcel->getActiveSheet()->mergeCells('A2:A3');//合并单元格
$objPHPExcel->getActiveSheet()->SetCellValue('B2', '电磁水表在装'.substr($stime,0,7).'前');
$objPHPExcel->getActiveSheet()->mergeCells('B2:D2');//合并单元格
$objPHPExcel->getActiveSheet()->SetCellValue('B3', '肯特');
$objPHPExcel->getActiveSheet()->SetCellValue('C3', '拓安信');
$objPHPExcel->getActiveSheet()->SetCellValue('D3', '小计');
$objPHPExcel->getActiveSheet()->SetCellValue('E2', '电磁水表新装'.substr($stime,0,7).'-'.substr($etime,0,7));
$objPHPExcel->getActiveSheet()->mergeCells('E2:G2');//合并单元格
$objPHPExcel->getActiveSheet()->SetCellValue('E3', '肯特');
$objPHPExcel->getActiveSheet()->SetCellValue('F3', '拓安信');
$objPHPExcel->getActiveSheet()->SetCellValue('G3', '小计');
$objPHPExcel->getActiveSheet()->SetCellValue('H2', '电磁水表在装新装总计');
$objPHPExcel->getActiveSheet()->mergeCells('H2:J2');//合并单元格
$objPHPExcel->getActiveSheet()->SetCellValue('H3', '肯特');
$objPHPExcel->getActiveSheet()->SetCellValue('I3', '拓安信');
$objPHPExcel->getActiveSheet()->SetCellValue('J3', '小计');
//查找在选择时间之前的表
//查出组织机构
$zzjg=db('management')->whereIn('id',[7,10,11,12,13,14,15])->select();
// $title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q',
// 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH'];
$title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'];
$line=4;//从第四行开始写数据
$zong=[];
foreach ($zzjg as $key => $value) {
$lineData=[];
$lineData['A']=$value['names'];
$lineData['B']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','肯特')->where('a.AddTime','<',$stime)->count();
$lineData['C']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','拓安信')->where('a.AddTime','<',$stime)->count();
$lineData['D']=$lineData['B']+$lineData['C'];
$lineData['E']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','肯特')
->where('a.AddTime','>=',$stime)->where('a.AddTime','<=',$etime)->count();
$lineData['F']=db('meter_equip2')->alias('a')->join('meter_info m','a.id = m.id')->where('m.manageunit',$value['id'])->where('m.brand','拓安信')
->where('a.AddTime','>=',$stime)->where('a.AddTime','<=',$etime)->count();
$lineData['G']=$lineData['E']+$lineData['G'];
$lineData['H']=$lineData['B']+$lineData['E'];
$lineData['I']=$lineData['C']+$lineData['F'];
$lineData['J']=$lineData['D']+$lineData['G'];
foreach($title_array as $zimu){
$objPHPExcel->getActiveSheet()->SetCellValue($zimu.$line, $lineData[$zimu]);//计算值所在的位置
if($zimu!='A'){
$zong[$zimu]=$zong[$zimu]+$lineData[$zimu];//计算总计
}
}
$line++;
}
$zong['A']='总计';
foreach($title_array as $zimu){
$objPHPExcel->getActiveSheet()->SetCellValue($zimu.$line, $zong[$zimu]);//计算值所在的位置
}
//=====================================================================================================================================================
foreach($zzjg as $zzjgInfo){
//创建新sheet表
$objPHPExcel->createSheet($sheetIndex);
$sheet = $objPHPExcel->setActiveSheetIndex($sheetIndex++);
$sheet->setTitle($zzjgInfo['names']);//单位名称
$sheet->getDefaultColumnDimension()->setWidth(20);//设置默认列宽度
$sheet->getColumnDimension('F')->setWidth(40);//设置默认列宽度
$objPHPExcel->getActiveSheet()->SetCellValue('A1', '序号');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', '仪表管理号');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', '点位名称');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', '仪表口径');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', '计量性质');
$objPHPExcel->getActiveSheet()->SetCellValue('F1', '安装地址');
$objPHPExcel->getActiveSheet()->SetCellValue('G1', '安装时间');
$objPHPExcel->getActiveSheet()->SetCellValue('H1', '仪表分类');
$objPHPExcel->getActiveSheet()->SetCellValue('I1', '管理单位');
$objPHPExcel->getActiveSheet()->SetCellValue('J1', '是否在用');
$title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'];
$meters=db('meter_equip2')
->alias('a')
->join('meter_info m','a.id = m.id')
->where('m.manageunit',$zzjgInfo['id'])
// ->where('m.manageunit',11)
->whereIn('brand',['肯特','拓安信'])
->field('m.*,a.staname,a.AddTime')
->select();
$meterData=[];
$line=2;//从第二行开始写
foreach($meters as $meter){
$meterData['A']=$meter['ROW_NUMBER'];
$meterData['B']=$meter['id'];
$meterData['C']=$meter['staname'];
$meterData['D']=$meter['caliber'];
$meterData['E']=$meter['measurecon'];
$meterData['F']=$meter['installpl'];
$meterData['G']=$meter['AddTime'];
$meterData['H']=$meter['brand'];
$meterData['I']=$zzjgInfo['names'];
$dic_status=[""=>"使用","0"=>"使用","1"=>"停用","2"=>"报废"];
$meterData['J']=$dic_status[$meter['ybstatus']];
foreach($title_array as $zimu){
$objPHPExcel->getActiveSheet()->SetCellValue($zimu.$line, $meterData[$zimu]);//计算值所在的位置
}
$line++;
}
}
//======================================================================================================================================================
$objPHPExcel->setActiveSheetIndex(0); //设置sheet起始位置
ob_clean();
header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;');
header('Content-Disposition:attachment;filename=电磁水表在装表统计'. date('Y-m-d', $begin) . '-' . date('Y-m-d', $end) .'.xlsx');
$save_object = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$save_object->save('php://output');
}
版权属于：
Ezra
作品采用：
《
署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)
》许可协议授权
评论 (0)