php Excelb 循环工作表

require_once ROOT_PATH . 'simplewind/vendor/phpoffice/phpexcel/Classes/PHPExcel.php';
require_once ROOT_PATH . 'simplewind/vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php';
require_once ROOT_PATH . 'simplewind/vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel5.php';
$objPHPExcel = new \PHPExcel();
$begin = 0;
$each_id  = 1;
$i = 5;
$styleThinBlackBorderOutline = array(
'borders' => array(
'allborders' => array( //设置全部边框
'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
),
),
);
foreach($daochu_arr as $excelkey=>$excellist){
/*新加一张表格统计*/
if($begin >= 1){
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($begin);
}
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11);//字体大小
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()
->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中
//设置行高
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(40);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(30);
$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(30);
$objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(30);
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
$objPHPExcel->getActiveSheet()->getStyle('B') ->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中
$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
$objPHPExcel->getActiveSheet()->getStyle('D4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中
$objPHPExcel->getActiveSheet()->mergeCells('A1:F1');//设置合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A2:C2');//设置合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('D2:F2');//设置合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A3:C3');//设置合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('D3:F3');//设置合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('D4:F4');//设置合并单元格
//设置单元格宽
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6.5);	
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10.5);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(16.77);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18.33);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(18.33);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(18.33);
//设置格式内容
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('A1', '垃圾分类现场督导记录表');
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('A2', '时间:'.$excelkey);
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('D2', '小区名称:'.$uptown['name']);
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('A3', '投放点名称:'.$classpoint['name']);
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('D3', '督导员签名:'.$signname);
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('A4', '序号');
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('B4', '投放时间');
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('C4', '房号');
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('D4', '投放类别');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('宋体')->setBold(true); //字体加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);//字体大小
//处理添加内容
foreach($excellist as $item=>$va){
//处理签名
if(!empty($va['sign'])){
$signname = $va['sign'];
}else{
//默认签名
$signname = $user['user_nickname'];
}
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('D3', '督导员签名:'.$signname);
$objPHPExcel->getActiveSheet()->mergeCells('D'.$i.':F'.$i);//设置合并单元格
$objPHPExcel->getActiveSheet()->getStyle('D'.$i)
->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中
$objPHPExcel->getActiveSheet()->getStyle('D'.$i)
->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('A'.$i,$each_id);
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('B'.$i,date('H:i:s',$va['times']));
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('C'.$i,$va['roomname']);
$objPHPExcel->setActiveSheetIndex($begin)->setCellValue('D'.$i,$va['type_list_info']);
$i++;
$each_id++;
}
//处理循环多出的一个
$i = $i-1;
$objPHPExcel->getActiveSheet()->getStyle('A1:F'.$i)->applyFromArray($styleThinBlackBorderOutline);
$each_id  = 1;
$i = 5;
$objPHPExcel->setActiveSheetIndex($begin);//导出第一个结束
$begin +=1;
//处理工作表名称
$objPHPExcel->getActiveSheet()->setTitle($excelkey);
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

点赞

发表评论