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

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;}}