phpexcel导出成绩表

效果图如下：

代码如下：代码注释不全，请大家参考phpexcel中文帮助手册

<?php
 require_once 'PHPExcel.php';
  function bfb($n)
    {
        return $n*100 . '%';
    }
 $objPHPExcel = new PHPExcel();// 创建一个处理对象实例
 $objPHPExcel->createSheet();//创建sheet
 $objPHPExcel->setActiveSheetIndex(0);
 $objActSheet = $objPHPExcel->getActiveSheet();
 $column_number = 6;
 //设置表格标题
 $objActSheet->setCellValue('A1', '2016级一班   学科学生成绩记载表');
 //设置字体大小
 $objActSheet->getStyle('A1')->getFont()->setName('宋体');

 $objActSheet->getStyle('A1')->getFont()->setSize(40);

 $objActSheet->getStyle('A1')->getFont()->setBold(true);

 //合并标题单元格

 $objActSheet->getStyle('B2')->getAlignment()->applyFromArray(
     array(

     'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,

     'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,

     'rotation' => 0,

     'wrap' => true

     )
 );
 $styleArray1 = array(
  'font' => array(
    'bold' => false,
    'size'=>12,
    'color'=>array(
      'argb' => '00000000',
    ),
  ),
  'alignment' => array(
    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  ),
);
// 将A1单元格设置为加粗，居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray1);
$objActSheet->mergeCells( 'A1:'.chr(ord('A')+$column_number-1).'1');//合并标题
$objPHPExcel->getActiveSheet()->mergeCells('A2:A3');
$objActSheet->setCellValue('A2', '姓名');
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->mergeCells('B2:C2');
$objActSheet->setCellValue('B2', '成绩');
$objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objActSheet->setCellValue('B3', '分数');
$objActSheet->setCellValue('C3', '等级');
$objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
###########################
$objPHPExcel->getActiveSheet()->mergeCells('D2:D3');
$objActSheet->setCellValue('D2', '姓名');
$objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->mergeCells('E2:F2');
$objActSheet->setCellValue('E2', '成绩');
$objPHPExcel->getActiveSheet()->getStyle('E2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objActSheet->setCellValue('E3', '分数');
$objActSheet->setCellValue('F3', '等级');
$objPHPExcel->getActiveSheet()->getStyle('E3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
#########################
$styleThinBlackBorderOutline = array(
    'borders' => array (
       'outline' => array (
          'style' => PHPExcel_Style_Border::BORDER_THIN,  //设置border样式
          //'style' => PHPExcel_Style_Border::BORDER_THICK, 另一种样式
          'color' => array ('argb' => 'FF000000'),     //设置border颜色
      ),
   ),
);
$lie = array('A','B','C','D','E','F');
foreach($lie as $l){
    $objPHPExcel->getActiveSheet()->getColumnDimension($l)->setWidth(15);
}
$data1 = array(
              array('name'=>'学生姓名1','code'=>90,'dj'=>'优'),
              array('name'=>'学生姓名2','code'=>91,'dj'=>'优'),
              array('name'=>'学生姓名3','code'=>92,'dj'=>'优'),
              array('name'=>'学生姓名4','code'=>93,'dj'=>'优'),
              array('name'=>'学生姓名5','code'=>94,'dj'=>'优'),
              array('name'=>'学生姓名6','code'=>95,'dj'=>'优'),
              array('name'=>'学生姓名7','code'=>96,'dj'=>'优')
);
$data2 = array(
              array('name'=>'学生姓名1','code'=>90,'dj'=>'优'),
              array('name'=>'学生姓名2','code'=>91,'dj'=>'优'),
              array('name'=>'学生姓名3','code'=>92,'dj'=>'优'),
              array('name'=>'学生姓名4','code'=>93,'dj'=>'优'),
              array('name'=>'学生姓名5','code'=>94,'dj'=>'优'),
              array('name'=>'学生姓名6','code'=>95,'dj'=>'优'),
              array('name'=>'学生姓名7','code'=>96,'dj'=>'优')
);
$j = $i = 4;
$code = 0;
foreach($data1 as $k=>$v){
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $v['name']);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $v['code']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $v['dj']);
        $objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(30);
        $code += $v['code'];
        $i++;
}
foreach($data2 as $k=>$v){
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $j, $v['name']);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . $j, $v['code']);
        $objPHPExcel->getActiveSheet()->setCellValue('F' . $j, $v['dj']);
        $objPHPExcel->getActiveSheet()->getStyle('D'.$j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D'.$j)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E'.$j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E'.$j)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F'.$j)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F'.$j)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getRowDimension($j)->setRowHeight(30);
        $code += $v['code'];
        $j++;
}
$count = count($data1)+count($data2);
$pjf = $code / $count ;
$hgl = bfb(1);
$ysl = bfb(1);
if($i != $j){
  die('导出成绩表出错');
}
$endindex1 = $i;
$endindex2 = $i+1;
$endindex3 = $i+2;
$objPHPExcel->getActiveSheet()->setCellValue('A' . $endindex1, '总人数');
$objPHPExcel->getActiveSheet()->getStyle('A'.$endindex1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A'.$endindex1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $endindex1, $count);
$objPHPExcel->getActiveSheet()->getStyle('B'.$endindex1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B'.$endindex1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $endindex1, '合格人数');
$objPHPExcel->getActiveSheet()->getStyle('C'.$endindex1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C'.$endindex1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $endindex1, $count);
$objPHPExcel->getActiveSheet()->getStyle('D'.$endindex1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D'.$endindex1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('E' . $endindex1, '优生人数');
$objPHPExcel->getActiveSheet()->getStyle('E'.$endindex1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E'.$endindex1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $endindex1, $count);
$objPHPExcel->getActiveSheet()->getStyle('F'.$endindex1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F'.$endindex1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('A' . $endindex2, '平均分');
$objPHPExcel->getActiveSheet()->getStyle('A'.$endindex2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A'.$endindex2)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $endindex2, $pjf);
$objPHPExcel->getActiveSheet()->getStyle('B'.$endindex2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B'.$endindex2)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $endindex2, '合格率');
$objPHPExcel->getActiveSheet()->getStyle('C'.$endindex2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C'.$endindex2)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $endindex2, $hgl);
$objPHPExcel->getActiveSheet()->getStyle('D'.$endindex2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D'.$endindex2)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('E' . $endindex2, '优生率');
$objPHPExcel->getActiveSheet()->getStyle('E'.$endindex2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E'.$endindex2)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $endindex2, $ysl);
$objPHPExcel->getActiveSheet()->getStyle('F'.$endindex2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F'.$endindex2)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//登分人签名
$writename = '杨捷成';
$objPHPExcel->getActiveSheet()->setCellValue('E' . $endindex3, '登分人签名');
$objPHPExcel->getActiveSheet()->getStyle('E'.$endindex3)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E'.$endindex3)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $endindex3, $writename);
$objPHPExcel->getActiveSheet()->getStyle('F'.$endindex3)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F'.$endindex3)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F'.$endindex3)->getFill()->getStartColor()->setARGB('FF808080'); // 将背景设置为浅粉色
//粗体下划线有背景色
$objPHPExcel->getActiveSheet()->getStyle('F'.$endindex3)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);

//***********************画出单元格边框*****************************  
$styleArray = array(  
    'borders' => array(  
        'allborders' => array(  
            //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
            'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
            //'color' => array('argb' => 'FFFF0000'),  
        ),  
    ),  
); 
$objPHPExcel->getActiveSheet()->getStyle('A1:F'.$endindex2)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪个格就把这个N改为那个字母替代 
//***********************画出单元格边框结束*****************************  
$path = 'title.xlsx';
  // 生成2007excel格式的xlsx文件
 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
 $objWriter->setOffice2003Compatibility(true);
 //echo $i;
 $objWriter->save($path);

?>

 代码存在冗余，后面会做封装！O(∩_∩)O哈哈~

posted @ 2016-12-04 21:06  yangboom  阅读(672)  评论(0)  编辑  收藏  举报
TOP