记录一次PHP写多sheet的Excel

记录一次PHP写多sheet的Excel

Ezra
2023-03-29 / 0 评论 / 171 阅读 / 正在检测是否收录...
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');

    }
0

评论 (0)

取消