PHP生成excel(3)
这一节主要是设置背景颜色、边框、字体大小、表格宽度
效果图
代码如下
<?php header("Content-Type:text/html;charset=utf-8"); require "./db.php"; require "./PHPExcel/PHPExcel.php"; //连接数据库 $db = Db::getInstance(); //实例化excel类 $objPHPExcel = new PHPExcel(); $objSheet = $objPHPExcel->getActiveSheet();//获取当前活动单元格 //设置水平居中 $objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置字体 $objSheet->getDefaultStyle()->getFont()->setName('微软雅黑')->setSize('13'); $objSheet->getStyle('A2:Z2')->getFont()->setSize('15')->setBold(True); $objSheet->getStyle('A3:Z3')->getFont()->setSize('15')->setBold(True); //设置背景颜色 //查询年级 $gradeInfo = $db->getResult("select `grade` from user group by grade order by grade asc"); //查询班级 $index = 0; foreach ($gradeInfo as $k=>$val) { $gradeIndex = getCells($index * 2); $objSheet->setCellValue($gradeIndex.'2','高'.$val['grade']); $classInfo = $db->getResult("select distinct(class) from user where grade = ".$val['grade']." order by class"); foreach ($classInfo as $j=>$vl) { $nameIndex = getCells($index * 2);//姓名列 $scoreIndex = getCells($index * 2 + 1);//分数列 $info = $db->getResult("select username,score from user where class=".$vl['class']." AND grade = ".$val['grade']." order by score desc"); $objSheet->mergeCells($nameIndex.'3:'.$scoreIndex.'3'); //设置班级背景颜色 $objSheet->getStyle($nameIndex.'3:'.$scoreIndex.'3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('51e351'); //设置班级边框 $classBorderStyle = getBorderStyle('e351ca'); $objSheet->getStyle($nameIndex.'3:'.$scoreIndex.'3')->applyFromArray($classBorderStyle); $objSheet->setCellValue($nameIndex.'3',$vl['class'].'班'); //设置自动换行,注意需要用双引号换行符 $objSheet->getStyle($nameIndex)->getAlignment()->setWrapText(true); $objSheet->setCellValue($nameIndex.'4',"姓名\n换行")->setCellValue($scoreIndex.'4','分数'); //超长数字设置格式 $objSheet->getStyle($scoreIndex)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); $m = 5; foreach ($info as $v) { //$objSheet->setCellValue($nameIndex.$m,$v['username'])->setCellValue($scoreIndex.$m,$v['score']);//填充学生信息 //->setValueExplicit('25', PHPExcel_Cell_DataType::TYPE_NUMERIC) //假如有超长数字 $objSheet->setCellValue($nameIndex.$m,$v['username'])->setCellValueExplicit ($scoreIndex.$m,$v['score'].'123123123213123',PHPExcel_Cell_DataType::TYPE_STRING); $m++; } $index++; } $endGradeIndex = getCells(($index*2-1));//获得每个年级终止单元格 //合并年级单元格 $objSheet->mergeCells($gradeIndex.'2:'.$endGradeIndex.'2');//('A2:F2') $objSheet->getStyle($gradeIndex.'2:'.$endGradeIndex.'2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('e36951'); //设置边框 $gradeBorderStyle = getBorderStyle('e3df51'); $objSheet->getStyle($gradeIndex.'2:'.$endGradeIndex.'2')->applyFromArray($gradeBorderStyle); } function getCells($index) { $arr = range('A','Z'); return $arr[$index]; } //获取不同的边框 function getBorderStyle($color) { $styleArray = array( 'borders'=>array( 'outline'=>array( 'style'=>PHPExcel_Style_Border::BORDER_THICK, 'color'=>array('rgb'=>$color) ) ) ); return $styleArray; } //按照指定格式生成excel文件 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//生成excel 2007 $objWriter->save('style.xlsx');