当前位置：首页 > article >正文

PHP导出EXCEL含合计行，设置单元格格式

article 2024/11/25 4:36:08

PHP导出EXCEL含合计行，设置单元格格式，水平居中垂直居中

public function exportSalary(Request $request)
    {
        //水平居中 垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical'   => Alignment::VERTICAL_CENTER
            ],
        ];
        //细边框
        $styleArray1 = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' =>  Border::BORDER_THIN
                ]
            ]
        ];
        //右对齐 垂直居中
        $styleArray2 = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_RIGHT,
                'vertical'   => Alignment::VERTICAL_CENTER
            ],
        ];
        $ym = $request->get('ym');
        $start_date = $ym . '-01 00:00:00';
        $month = date("Y年m月", strtotime($start_date));
        $paper_type = $request->paper_type;
        $type_id = isset($paper_type)?$paper_type:1;
        $flag = $type_id==2?'打样':'板房';
        $yyyymm = date('Y-m', strtotime($start_date));
        $data = $this->get_salary_data($yyyymm,$type_id);
        if($data){
            $name ='XXXX有限公司 (技术部)';
            $filename = $flag.'计件工资汇总表'.$ym;
            $title = $flag.'计件工资汇总表';
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle($title);
            $sheet->getColumnDimension('A')->setWidth(6);
            $sheet->getColumnDimension('B')->setWidth(8);
            $sheet->getColumnDimension('C')->setWidth(10);
            $sheet->getColumnDimension('D')->setWidth(10);
            $sheet->getColumnDimension('E')->setWidth(10);
            $sheet->getColumnDimension('F')->setWidth(10);
            $sheet->getColumnDimension('G')->setWidth(10);
            $sheet->getColumnDimension('H')->setWidth(10);
            $sheet->getColumnDimension('I')->setWidth(10);
            $sheet->getColumnDimension('J')->setWidth(10);
            $sheet->getColumnDimension('K')->setWidth(10);
            $sheet->getColumnDimension('L')->setWidth(10);
            $sheet->getColumnDimension('M')->setWidth(10);
            $sheet->getColumnDimension('N')->setWidth(10);
            $sheet->getColumnDimension('O')->setWidth(10);
            $sheet->getColumnDimension('P')->setWidth(15);
            //第一行
            $sheet->setCellValue('A1', $name);
            $sheet->mergeCells('A1:P1');
            $sheet->getStyle('A1:P1')->getFont()->setBold(true)->setName('Arial')->setSize(20);
            //第二行
            $sheet->setCellValue('A2', $month);
            $sheet->mergeCells('A2:B2');
            $sheet->setCellValue('C2', $title);
            $sheet->mergeCells('C2:P2');
            $sheet->getStyle('C2:P2')->getFont()->setBold(true)->setName('Arial')->setSize(18);
            $sheet->getStyle('A1:P2')->applyFromArray($styleArray);
            //第三行
            $sheet->setCellValue('A3', '序号');
            $sheet->mergeCells('A3:A4');
            $sheet->setCellValue('B3', '姓名');
            $sheet->mergeCells('B3:B4');
            $sheet->setCellValue('C3', '工资基数');
            $sheet->mergeCells('C3:F3');
            $sheet->setCellValue('G3', '薪资结算明细');
            $sheet->mergeCells('G3:L3');
            $sheet->setCellValue('M3', '出勤管理');
            $sheet->mergeCells('M3:N3');
            $sheet->setCellValue('O3', '发放合计');
            $sheet->mergeCells('O3:O4');
            $sheet->setCellValue('P3', '备注');
            $sheet->mergeCells('P3:P4');
            //第四行
            $sheet->setCellValue('C4', '底薪');
            $sheet->setCellValue('D4', '产量考核');
            $sheet->setCellValue('E4', '绩效考核');
            $sheet->setCellValue('F4', '综合收入');
            $sheet->setCellValue('G4', '及时率');
            $sheet->setCellValue('H4', '产量薪资');
            $sheet->setCellValue('I4', '绩效得分');
            $sheet->setCellValue('J4', '绩效薪资');
            $sheet->setCellValue('K4', '超产奖励');
            $sheet->setCellValue('L4', '点工');
            $sheet->setCellValue('M4', '出勤天数');
            $sheet->setCellValue('N4', '请假天数');

            $sheet->getStyle('G4')->getAlignment()->setWrapText(true);
            $sheet->getStyle('I4')->getAlignment()->setWrapText(true);
            $sheet->getRowDimension('1')->setRowHeight(28);
            $sheet->getRowDimension('2')->setRowHeight(25);
            $sheet->getRowDimension('3')->setRowHeight(23);
            $sheet->getRowDimension('4')->setRowHeight(30);
            //开始装数据
            $count = 5;
            foreach ($data as $key =>$value){
                $xh = $key + 1;
                $sheet->setCellValue('A'.$count, $xh);
                $sheet->setCellValue('B'.$count, $value['name']);//姓名
                $sheet->setCellValue('C'.$count, $value['basic_salary']);//底薪
                $sheet->setCellValue('D'.$count, $value['yield_basic_salary']);//产量考核
                $sheet->setCellValue('E'.$count, $value['merit_basic_salary']);//绩效考核
                $sheet->setCellValue('F'.$count, $value['all_basic_salary']);//综合收入
                $sheet->setCellValue('G'.$count, $value['timely_rate']/100); //及时率
                $sheet->setCellValue('H'.$count, $value['yield_salary']);//产量薪资
                $sheet->setCellValue('I'.$count, $value['merit_score']);//绩效得分
                $sheet->setCellValue('J'.$count, $value['merit_salary']); //绩效薪资
                $sheet->setCellValue('K'.$count, $value['over_salary']); //超产奖励
                $sheet->setCellValue('L'.$count, $value['timing_cut_salary']); //点工扣款
                $sheet->setCellValue('M'.$count, ""); //出勤天数
                $sheet->setCellValue('N'.$count, ""); //请假天数
                $sheet->setCellValue('O'.$count, $value['all_salary']); //发放合计
                $sheet->setCellValue('P'.$count, $value['remark']); //备注
                $sheet->getRowDimension($count)->setRowHeight(25);
                $count++;
            }

            $sheet->getStyle('A3:P'.($count))->applyFromArray($styleArray1)->applyFromArray($styleArray); //画上几线
            $sheet->getStyle('C5:O'.($count))->getNumberFormat()->setFormatCode("0.00");//保留小数2位
            $sheet->getStyle('A1:P2')->applyFromArray($styleArray);
            $sheet->getStyle('C5:O'.($count))->applyFromArray($styleArray2);//右对齐 垂直居中
            $sheet->getStyle('J5:O'.($count))->applyFromArray($styleArray2);//右对齐 垂直居中
            $sheet->getStyle('A3:P4')->getFont()->setBold(true);

            //合计行
            $sheet->setCellValue('A'.$count,'合计');
            $sheet->mergeCells('A'.$count.':B'.$count);
            $sheet->setCellValue('C'.$count,'=SUM(C5:C'.($count-1).')');
            $sheet->setCellValue('D'.$count,'=SUM(D5:D'.($count-1).')');
            $sheet->setCellValue('E'.$count,'=SUM(E5:E'.($count-1).')');
            $sheet->setCellValue('F'.$count,'=SUM(F5:F'.($count-1).')');
            $sheet->setCellValue('G'.$count,'=AVERAGE(G5:G'.($count-1).')');
            $sheet->setCellValue('H'.$count,'=SUM(H5:H'.($count-1).')');
            $sheet->setCellValue('I'.$count,'=AVERAGE(I5:I'.($count-1).')');
            $sheet->setCellValue('J'.$count,'=SUM(J5:J'.($count-1).')');
            $sheet->setCellValue('K'.$count,'=SUM(K5:K'.($count-1).')');
            $sheet->setCellValue('L'.$count,'=SUM(L5:L'.($count-1).')');
            $sheet->setCellValue('O'.$count,'=SUM(O5:O'.($count-1).')');
            //设置G列单元格格式为百分比0.00%
            $sheet->getStyle('G5:G'.$count)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);

            //最后一行
            $last_count = $count + 1;
            $sheet->setCellValue('A'.$last_count, '审批：');
            $sheet->mergeCells('A'.$last_count.':F'.$last_count);
            $sheet->setCellValue('G'.$last_count, '审核：');
            $sheet->mergeCells('G'.$last_count.':K'.$last_count);
            $sheet->setCellValue('L'.$last_count, '制表：');
            $sheet->mergeCells('L'.$last_count.':P'.$last_count);

            header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="'.$filename.'.xlsx"');
            header('Cache-Control: max-age=0');
            ob_end_clean();
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save('php://output');
            exit;
        }
    }