PHPExcel之生成表格汇总列（二）

一，针对某些表格列算计算汇总数据的操作：

 1 //模拟数据
 2 $data = array(
 3     array("id"=>1,"name"=>"张一","age"=>15,"address"=>"广东1","salary"=>8000),
 4     array("id"=>2,"name"=>"张二","age"=>23,"address"=>"广东2","salary"=>5000),
 5     array("id"=>3,"name"=>"张三","age"=>36,"address"=>"广东3","salary"=>3000),
 6     array("id"=>4,"name"=>"张四","age"=>40,"address"=>"广东4","salary"=>18000),
 7     array("id"=>5,"name"=>"张五","age"=>55,"address"=>"广东5","salary"=>28000),
 8 );
 9 
10 //设置标题行
11 $objExcel->setActiveSheetIndex(0)
12             ->setCellValue('A1','ID')
13             ->setCellValue('B1','姓名')
14             ->setCellValue('C1','年龄')
15             ->setCellValue('D1','住址')
16             ->setCellValue('E1','工资');
17 
18 //内容循环操作即可 从第二行开始
19 $j = 2;
20 
21 foreach($data as $key=>$value){
22     //设置某列为富文本 加粗 倾斜 颜色
23     $objRichText = new PHPExcel_RichText();
24     $objPayable = $objRichText->createTextRun($value['address']);
25     $objPayable->getFont()->setBold(true);
26     $objPayable->getFont()->setItalic(true);
27     $objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
28     
29     $objExcel->getActiveSheet()                
30             ->setCellValue('A'.$j, $value['id'])
31             ->setCellValue('B'.$j, $value['name'])
32             ->setCellValue('C'.$j, intval($value['age']))
33             ->setCellValue('D'.$j, $objRichText)
34             ->setCellValue('E'.$j, intval($value['salary']));
35             
36     $j++;
37 }
38 
39 //对年龄列和工资列进行求和
40 $objExcel->getActiveSheet()
41             ->setCellValue('A'.$j,'汇总')
42             ->setCellValue('C'.$j,"=SUM(C2:C6)")
43             ->setCellValue('E'.$j,"=SUM(E2:E6)");
44             
45 $next = $j+1;
46 
47 //求年龄 工资最小值
48 $objExcel->getActiveSheet()
49             ->setCellValue('A'.$next,'最小值')
50             ->setCellValue('C'.$next,"=MIN(C2:C6)")
51             ->setCellValue('E'.$next,"=MIN(E2:E6)");
52             
53 $next = $next + 1;
54 //求年龄 工资最大值
55 $objExcel->getActiveSheet()
56             ->setCellValue('A'.$next,'最大值')
57             ->setCellValue('C'.$next,"=MAX(C2:C6)")
58             ->setCellValue('E'.$next,"=MAX(E2:E6)");
59             
60 $next = $next + 1;
61 //求年龄 工资平均值
62 $objExcel->getActiveSheet()
63             ->setCellValue('A'.$next,'平均值')
64             ->setCellValue('C'.$next,"=AVERAGE(C2:C6)")
65             ->setCellValue('E'.$next,"=AVERAGE(E2:E6)");

导出的效果如下：

PHPExcel之生成表格并下载（一）

二，可以针对表格设置一些打印属性：

 1 $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HPlease treat this document as confidential!');
 2 $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
 3 
 4 // Add a drawing to the header
 5  6 $objDrawing = new PHPExcel_Worksheet_HeaderFooterDrawing();
 7 $objDrawing->setName('PHPExcel logo');
 8 $objDrawing->setPath('./images/phpexcel_logo.gif');
 9 $objDrawing->setHeight(36);
10 $objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing, PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_LEFT);
11 
12 // Set page orientation and size
13 14 $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
15 $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
　　//设置多少行一页
16 $objPHPExcel->getActiveSheet()->setBreak( 'A' . $i, PHPExcel_Worksheet::BREAK_ROW );

PHP中常见的问题点，知识点，及盲点。