PHPEXCEL实例-导出EXCEL

 

 

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. ?> 
posted @ 2014-01-14 09:41  Beyond it  阅读(630)  评论(0)  编辑  收藏  举报