PHPExcel 是相当强大的 MS Office Excel 文档生成类库，当需要输出比较复杂格式数据的时候，PHPExcel 是个不错的选择。

Php代码  

1.   

Php代码  

1. <?php  
2.  /* 
3.   * 导出EXCEL 
4.   * 程序：刘兴 
5.   * 时间:2010-6 
6.   */  
7. ?>  
8. <?php  
9. require_once("connDB.php");//引入数据库连接参数  
10. require_once("PExcel/PHPExcel.php");  
11. ?>  
12. <?php  
13. $year=$_POST[year];   
14. $jd=$_POST[jd];  
15.   
16. set_time_limit(900);  
17. /** Error reporting */  
18. error_reporting(E_ALL);  
19. /** PHPExcel */  
20.   
21. $sqlxm="select count(*) as  cxm from khxm where lb=1 and isyx=0";      
22. $resultxm=mysql_query($sqlxm) or die("SQL语句执行错误！");  
23. $rowxm = mysql_fetch_array($resultxm);   
24. $c1=$rowxm[‘cxm‘];  
25.   
26. $sqlxm="select count(*) as  cxm from khxm where lb=2 and isyx=0";      
27. $resultxm=mysql_query($sqlxm) or die("SQL语句执行错误！");  
28. $rowxm = mysql_fetch_array($resultxm);   
29. $c2=$rowxm[‘cxm‘];  
30.   
31. $sqlxm="select count(*) as  cxm from khxm where lb=3 and isyx=0";      
32. $resultxm=mysql_query($sqlxm) or die("SQL语句执行错误！");  
33. $rowxm = mysql_fetch_array($resultxm);   
34. $c3=$rowxm[‘cxm‘];  
35.   
36. // Create new PHPExcel object  
37. $objPHPExcel = new PHPExcel();  
38. // Set properties  
39. $objPHPExcel->getProperties()->setCreator("gxds");  
40. $objPHPExcel->getProperties()->setLastModifiedBy("gxds");  
41. $objPHPExcel->getProperties()->setTitle("gxdskhtj");  
42. $objPHPExcel->getProperties()->setSubject("gxdskhtj");  
43. $objPHPExcel->getProperties()->setDescription("gxdskhtj, gxds.");  
44. $objPHPExcel->getProperties()->setKeywords("gxdskhtj");  
45. $objPHPExcel->getProperties()->setCategory("gxtj result file");  
46. // Add some data  
47. $noshow = array("year", "time", "peopleid", "bm","kbm");  
48. $objPHPExcel->setActiveSheetIndex(0);  
49.       
50. $objActSheet = $objPHPExcel->getActiveSheet();  
51. $objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(2, 3);  
52.   
53.    
54.                 $db=new mysqli("localhost","ssss","aaaaa","bbbb");    
55.                 mysqli_query($db,"SET NAMES utf8");                    
56.                 $result=$db->query("call gxtj($year,$jd)");  
57.     $i=4;  
58.     while( $row = $result->fetch_array(MYSQLI_ASSOC))  
59.       {     
60.        $c=0;  
61.        $zt=1;  
62.           $totalfs=100;   
63.        $tfs=0;   
64.        $isstart=false;   
65.        $j=65;  
66.        $asc_cell=chr($j);  
67.   
68.      
69.            while ($key=key($row)){   
70.              $ex_title=$asc_cell."3";   
71.           $ex_cell=$asc_cell.$i;            
72.        $value=current($row);   
73.        if (in_array($key, $noshow)) {  
74.              next($row);  
75.        continue;   
76.        }  
77.        if (is_numeric($value) or  is_null($value) ){  
78.            if (is_null($value)) $value=‘0‘;  
79.         if (!$isstart) {  
80.            $isstart=true;  
81.            $startasc=$asc_cell;  
82.           $endasc=$asc_cell;   
83.      
84.   
85.               }  
86.        }  
87.        if ($isstart){  
88.            $c++;  
89.         if ($zt==1){  
90.            if ($c>$c1) {  
91.             $zt++;   
92.          $ge=$startasc.‘2:‘.$endasc.‘2‘;  
93.          $objActSheet->mergeCells("$ge");   
94.             $objActSheet->setCellValue($startasc.‘2‘,"岗位职责、效能考核");  
95.          $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()  
96. ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
97.          $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()  
98. ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);   
99.                   $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()  
100.                    ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);         
101.                   $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()  
102.                   ->getStartColor()->setARGB(‘0DB0E59FF‘);   
103.                    $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)  
104.                    ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);   
105.            $objPHPExcel->getActiveSheet()->getStyle($ex_title)  
106.            ->getFont()->setBold(true);   
107.             $startasc=$asc_cell;  
108.          $endasc=$asc_cell;   
109.          $totalfs-=$tfs;  
110.          $tfs=0;  
111.          $c=1;                   
112.          }  
113.          else{  
114.             $endasc=$asc_cell;   
115.          }  
116.                               $tfs+=$value;  
117.             if ($tfs>50) {  
118.                 $tfs=50;  
119.             }   
120.            }                                   
121.         if ($zt==2){  
122.             if ($c>$c2) {         
123.             $zt++;   
124.          $ge=$startasc.‘2:‘.$endasc.‘2‘;  
125.          $objActSheet->mergeCells("$ge");   
126.                $objActSheet->setCellValue($startasc.‘2‘,"考试");  
127.          $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()  
128. ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
129.          $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()  
130. ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
131.                   $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()  
132.                    ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);         
133.                   $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()  
134.                   ->getStartColor()->setARGB(‘0DB0E59FF‘);  
135.                    $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)  
136.                       ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);   
137.            $objPHPExcel->getActiveSheet()->getStyle($ex_title)  
138.            ->getFont()->setBold(true);   
139.           $startasc=$asc_cell;  
140.          $endasc=$asc_cell;   
141.          $totalfs-=$tfs;  
142.          $tfs=0;  
143.          $c=1;  
144.         }  
145.          else{  
146.             $endasc=$asc_cell;         
147.          }  
148.                               $tfs+=$value;  
149.             if ($tfs>30) {  
150.                 $tfs=30;  
151.             }   
152.            }    
153.         if ($zt==3){  
154.             if ($c=$c3) {  
155.             $zt++;   
156.          $ge=$startasc.‘2:‘.$endasc.‘2‘;  
157.          $objActSheet->mergeCells("$ge");   
158.             $objActSheet->setCellValue($startasc.‘2‘,"科长考核");  
159.          $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()  
160. ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
161.          $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getAlignment()  
162. ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
163.                   $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()  
164.                    ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);         
165.                   $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)->getFill()  
166.                   ->getStartColor()->setARGB(‘0DB0E59FF‘);   
167.                    $objPHPExcel->getActiveSheet()->getStyle($startasc.‘2‘)  
168.                    ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);   
169.            $objPHPExcel->getActiveSheet()->getStyle($ex_title)  
170.            ->getFont()->setBold(true);                          
171.            $startasc=$asc_cell;  
172.          $endasc=$asc_cell;   
173.          $totalfs-=$tfs;  
174.          $tfs=0;  
175.          $c=0;          
176.          }  
177.          else{  
178.             $endasc=$asc_cell;   
179.            
180.          }  
181.                               $tfs+=$value;  
182.             if ($tfs>20) {  
183.                 $tfs=20;  
184.             }   
185.            }               
186.        }    
187.          
188.        
189.        $objActSheet->setCellValue($ex_title,$key);  
190.        $objActSheet->setCellValue($ex_cell,$value);  
191. //设置单元格宽度         
192.                 $objActSheet->getColumnDimension($asc_cell)->setWidth(16);   
193.          
194.        $objPHPExcel->getActiveSheet()->getStyle($ex_title)->getAlignment()  
195.        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
196.        $objPHPExcel->getActiveSheet()->getStyle($ex_title)->getAlignment()  
197.        ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);          
198.        $objPHPExcel->getActiveSheet()->getStyle($ex_cell)->getAlignment()  
199.        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
200.        $objPHPExcel->getActiveSheet()->getStyle($ex_cell)->getAlignment()  
201.        ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);   
202. //设置填充格式及字体颜色          
203.        $objPHPExcel->getActiveSheet()->getStyle($ex_cell)  
204.        ->getNumberFormat()->setFormatCode(‘#,##0.00‘);         
205.        $objPHPExcel->getActiveSheet()->getStyle($ex_title)->getFill()  
206.        ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);         
207.        $objPHPExcel->getActiveSheet()->getStyle($ex_title)->getFill()  
208.        ->getStartColor()->setARGB(‘0DB0E50A1‘);  
209.          
210.        $objPHPExcel->getActiveSheet()->getStyle($ex_title)  
211.        ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);  
212. //设置字体粗体         
213.        $objPHPExcel->getActiveSheet()->getStyle($ex_title)  
214.        ->getFont()->setBold(true);  
215.   
216.   
217.          
218.                           
219.        $j++;  
220.        if ($j>90) {  
221.                   $j=65;  
222.                           $asc_cell="AA" ;  
223.        }  
224.        elseif (strlen($asc_cell)>1) {  
225.                $asc_cell=substr($asc_cell,0,strlen($asc_cell)-1).chr($j);          
226.        }  
227.        else{  
228.             $asc_cell=chr($j);   
229.        }       
230.           next($row);          
231.        }  
232.          
233.        
234.      $ge=$asc_cell.‘2:‘.$asc_cell.‘3‘;  
235.      $objActSheet->mergeCells("$ge");  
236.      $objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)->getAlignment()  
237.      ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
238.      $objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)->getAlignment()  
239.      ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);   
240.                     
241.         $objActSheet->setCellValue($asc_cell.‘2‘,"总分");  
242.          
243.        $objActSheet->setCellValue($ex_cell,$totalfs);  
244. //设置填充格式及字体颜色         
245.        $objPHPExcel->getActiveSheet()->getStyle($ex_cell)         
246.        ->getNumberFormat()->setFormatCode(‘#,##0.00‘);         
247.       $objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)->getFill()  
248.       ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);         
249.       $objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)->getFill()  
250.       ->getStartColor()->setARGB(‘0DB0E59FF‘);  
251. //设置字体颜色        
252.       $objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)  
253.       ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);  
254.       $objPHPExcel->getActiveSheet()->getStyle($asc_cell.‘2‘)  
255.       ->getFont()->setBold(true);  
256.                 
257.        $i++;          
258.           }    
259. //标题栏合并单元格，并设置居中  
260. $objActSheet->setCellValue(‘A1‘,$year."年".$jd."季度绩效考核情况统计表");  
261. $value=$objPHPExcel->getActiveSheet()->getCell(‘A1‘)->getValue();;       
262. $ge=‘A1:‘.$asc_cell.‘1‘;  
263. $objActSheet->mergeCells("$ge");  
264. $objActSheet->setCellValue(‘A1‘,$value);  
265. $objPHPExcel->getActiveSheet()->getStyle(‘A1‘)->getAlignment()  
266. ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
267. $objPHPExcel->getActiveSheet()->getStyle(‘A1‘)->getAlignment()  
268. ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);   
269. $objPHPExcel->getActiveSheet()->getStyle(‘A1‘)  
270. ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);  
271. $objPHPExcel->getActiveSheet()->getStyle(‘A1‘)  
272. ->getFont()->setBold(true);  
273. $objPHPExcel->getActiveSheet()->getStyle(‘A1‘)  
274. ->getFont()->setSize(26);  
275.       
276. //姓名栏合并单元格，并设置居中       
277. $value=$objPHPExcel->getActiveSheet()->getCell(‘A3‘)->getValue();;       
278. $ge=‘A2:A3‘;  
279. $objActSheet->mergeCells("$ge");  
280. $objActSheet->setCellValue(‘A2‘,$value);  
281. $objPHPExcel->getActiveSheet()->getStyle(‘A2‘)->getAlignment()  
282. ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
283. $objPHPExcel->getActiveSheet()->getStyle(‘A2‘)->getAlignment()  
284. ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);   
285. $objPHPExcel->getActiveSheet()->getStyle(‘A2‘)->getFill()  
286. ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);         
287. $objPHPExcel->getActiveSheet()->getStyle(‘A2‘)->getFill()  
288. ->getStartColor()->setARGB(‘0DB0E59FF‘);  
289. $objPHPExcel->getActiveSheet()->getStyle(‘A2‘)  
290. ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);  
291. $objPHPExcel->getActiveSheet()->getStyle(‘A2‘)  
292. ->getFont()->setBold(true);  
293. //科室栏合并单元格，并设置居中  
294. $value=$objPHPExcel->getActiveSheet()->getCell(‘B3‘)->getValue();;       
295. $ge=‘B2:B3‘;  
296. $objActSheet->mergeCells("$ge");  
297. $objActSheet->setCellValue(‘B2‘,$value);  
298. $objPHPExcel->getActiveSheet()->getStyle(‘B2‘)->getAlignment()  
299. ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
300. $objPHPExcel->getActiveSheet()->getStyle(‘B2‘)->getAlignment()  
301. ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
302. $objPHPExcel->getActiveSheet()->getStyle(‘B2‘)->getFill()  
303. ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);         
304. $objPHPExcel->getActiveSheet()->getStyle(‘B2‘)->getFill()  
305. ->getStartColor()->setARGB(‘0DB0E59FF‘);  
306. $objPHPExcel->getActiveSheet()->getStyle(‘B2‘)  
307. ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);  
308. $objPHPExcel->getActiveSheet()->getStyle(‘B2‘)  
309. ->getFont()->setBold(true);  
310.   
311.   
312. //单元格边框及颜色  
313. $objPHPExcel->getActiveSheet()->getStyle(‘A2:‘.$ex_cell)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
314. $objPHPExcel->getActiveSheet()->getStyle(‘A2:‘.$ex_cell)->getBorders()->getAllBorders()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);  
315.   
316. // sheet名称  
317. $objPHPExcel->getActiveSheet()->setTitle(‘绩效考核统计‘);  
318. // Set active sheet index to the first sheet, so Excel opens this as the first sheet  
319. $objPHPExcel->setActiveSheetIndex(0);  
320. // Redirect output to a clients web browser (Excel5)通知下载  
321. $fn="gxtj-$year-$jd.xls";  
322. header(‘Content-Type: application/vnd.ms-excel; charset=utf-8‘);  
323. header("Content-Disposition: attachment;filename=$fn");  
324. header(‘Cache-Control: max-age=0‘);  
325. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5‘);  
326. $objWriter->save(‘php://output‘);  
327. exit;  
328. ?> 
     

PHPEXCEL实例-导出EXCEL