PHPExcel生成自定义表格

上代码

$objPHPExcel = new \PHPExcel();
// 设置表头 固定部分内容
$objPHPExcel->getActiveSheet()->mergeCells('A1:G1');//合并单元格 从A1到G1
$objPHPExcel->getActiveSheet()->setCellValue('A1',$fileName);//单元格内容
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('宋体');//A1单元格字体类型
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(17);//A1单元格字体大学
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);//A1单元格字体加粗
// 序号
$objPHPExcel->getActiveSheet()->mergeCells('A2:A3');//合并单元格 从A2到A3
$objPHPExcel->getActiveSheet()->setCellValue('A2','序号');//A2单元格内容
// 姓名
$objPHPExcel->getActiveSheet()->mergeCells('B2:B3');//合并单元格 从B2到B3
$objPHPExcel->getActiveSheet()->setCellValue('B2','姓名');//B2单元格内容
// 早餐
$objPHPExcel->getActiveSheet()->mergeCells('C2:D2');//合并单元格 从C2到D2
$objPHPExcel->getActiveSheet()->setCellValue('C2','早餐(8元/餐)');//C2单元格内容
$objPHPExcel->getActiveSheet()->setCellValue('C3','餐数');//C3单元格内容
$objPHPExcel->getActiveSheet()->setCellValue('D3','单位补助(4.8元/餐)');//D3单元格内容
// 午餐
$objPHPExcel->getActiveSheet()->mergeCells('E2:F2');
$objPHPExcel->getActiveSheet()->setCellValue('E2','午餐(14元/餐)');
$objPHPExcel->getActiveSheet()->setCellValue('E3','餐数');
$objPHPExcel->getActiveSheet()->setCellValue('F3','单位补助(8.4元/餐)');
//备注
$objPHPExcel->getActiveSheet()->mergeCells('G2:G3');
$objPHPExcel->getActiveSheet()->setCellValue('G2','备注');

//固定列
$start_row = 4;
foreach ($data['list'] as $key => $val) {
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$start_row,$key + 1);//No.
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$start_row,$val['name']);// 姓名
    $objPHPExcel->getActiveSheet()->setCellValue('C'.$start_row,$val['breakfast']);  // 早餐数
    $objPHPExcel->getActiveSheet()->setCellValue('D'.$start_row,$val['breakfast_sum']);  // 补助金额
    $objPHPExcel->getActiveSheet()->getStyle('D'.$start_row)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
    $objPHPExcel->getActiveSheet()->setCellValue('E'.$start_row,$val['lunch']);  // 晚餐数
    $objPHPExcel->getActiveSheet()->setCellValue('F'.$start_row,$val['lunch_sum']);  // 补助金额
    $objPHPExcel->getActiveSheet()->getStyle('F'.$start_row)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
    $start_row ++;
}
//        $start_row++;
$objPHPExcel->getActiveSheet()->mergeCells('A'.$start_row.':B'.$start_row);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$start_row,'合计');
$objPHPExcel->getActiveSheet()->setCellValue('C'.$start_row, '=SUM(C4:C'.($start_row-1).')');
$objPHPExcel->getActiveSheet()->setCellValue('D'.$start_row, '=SUM(D4:D'.($start_row-1).')');
$objPHPExcel->getActiveSheet()->setCellValue('E'.$start_row, '=SUM(E4:E'.($start_row-1).')');
$objPHPExcel->getActiveSheet()->setCellValue('F'.$start_row, '=SUM(F4:F'.($start_row-1).')');
$objPHPExcel->getActiveSheet()->getStyle('D'.$start_row)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
$objPHPExcel->getActiveSheet()->getStyle('F'.$start_row)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
//列+1
$start_row++;

$objPHPExcel->getActiveSheet()->mergeCells('A'.$start_row.':B'.$start_row);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$start_row,'总计');
$objPHPExcel->getActiveSheet()->mergeCells('C'.$start_row.':F'.$start_row);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$start_row, '=SUM(D'.($start_row-1).',F'.($start_row-1).')');
$objPHPExcel->getActiveSheet()->getStyle('C'.$start_row)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

$start_row++;

$objPHPExcel->getActiveSheet()->mergeCells('A'.$start_row.':B'.$start_row);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$start_row,'税额(8.5%)');
$objPHPExcel->getActiveSheet()->mergeCells('C'.$start_row.':F'.$start_row);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$start_row, '=C'.($start_row-1).'*0.085');
$objPHPExcel->getActiveSheet()->getStyle('C'.$start_row)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

$start_row++;

$objPHPExcel->getActiveSheet()->mergeCells('A'.$start_row.':B'.$start_row);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$start_row,'税后总计');
$objPHPExcel->getActiveSheet()->mergeCells('C'.$start_row.':F'.$start_row);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$start_row, '=SUM(C'.($start_row-2).':C'.($start_row-1).')');
$objPHPExcel->getActiveSheet()->getStyle('C'.$start_row)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);


// 居中和水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1:G'.$start_row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:G'.$start_row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
// 设置边框
$objPHPExcel->getActiveSheet()->getStyle('A2:G'.$start_row)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
// 落款
$start_row++;
$objPHPExcel->getActiveSheet()->setCellValue('A'.$start_row,'制表人:');
$objPHPExcel->getActiveSheet()->setCellValue('D'.$start_row,'审核人:');
$objPHPExcel->getActiveSheet()->getStyle('A2:G'.$start_row)->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('A2:G'.$start_row)->getFont()->setSize(12);
// 设置宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(21);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(21);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);

$fileName = iconv("utf-8", "gb2312", $fileName.'.xls');
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"$fileName\"");
header('Cache-Control: max-age=0');

//创建
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); //文件通过浏览器下载
exit;