PHPEXCEL实例-导出EXCEL

 

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

 

 

 

 

声明：本文为刘兴(http://deepfuture.iteye.com/)原创，如转载请注明来源

 

<?php
 /*
  * 导出EXCEL
  * 程序：刘兴
  * 时间:2010-6
  */
?>
<?php
require_once("connDB.php");//引入数据库连接参数
require_once("PExcel/PHPExcel.php");
?>
<?php
$year=$_POST[year]; 
$jd=$_POST[jd];

set_time_limit(900);
/** Error reporting */
error_reporting(E_ALL);
/** PHPExcel */

$sqlxm="select count(*) as  cxm from khxm where lb=1 and isyx=0";    
$resultxm=mysql_query($sqlxm) or die("SQL语句执行错误！");
$rowxm = mysql_fetch_array($resultxm); 
$c1=$rowxm['cxm'];

$sqlxm="select count(*) as  cxm from khxm where lb=2 and isyx=0";    
$resultxm=mysql_query($sqlxm) or die("SQL语句执行错误！");
$rowxm = mysql_fetch_array($resultxm); 
$c2=$rowxm['cxm'];

$sqlxm="select count(*) as  cxm from khxm where lb=3 and isyx=0";    
$resultxm=mysql_query($sqlxm) or die("SQL语句执行错误！");
$rowxm = mysql_fetch_array($resultxm); 
$c3=$rowxm['cxm'];

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("gxds");
$objPHPExcel->getProperties()->setLastModifiedBy("gxds");
$objPHPExcel->getProperties()->setTitle("gxdskhtj");
$objPHPExcel->getProperties()->setSubject("gxdskhtj");
$objPHPExcel->getProperties()->setDescription("gxdskhtj, gxds.");
$objPHPExcel->getProperties()->setKeywords("gxdskhtj");
$objPHPExcel->getProperties()->setCategory("gxtj result file");
// Add some data
$noshow = array("year", "time", "peopleid", "bm","kbm");
$objPHPExcel->setActiveSheetIndex(0);
    
$objActSheet = $objPHPExcel->getActiveSheet();
$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(2, 3);

 
                $db=new mysqli("localhost","ssss","aaaaa","bbbb");  
                mysqli_query($db,"SET NAMES utf8");                  
                $result=$db->query("call gxtj($year,$jd)");
    $i=4;
    while( $row = $result->fetch_array(MYSQLI_ASSOC))
      {   
       $c=0;
       $zt=1;
          $totalfs=100; 
       $tfs=0; 
       $isstart=false; 
       $j=65;
       $asc_cell=chr($j);

   
           while ($key=key($row)){ 
             $ex_title=$asc_cell."3"; 
          $ex_cell=$asc_cell.$i;          
       $value=current($row); 
       if (in_array($key, $noshow)) {
             next($row);
       continue; 
       }
       if (is_numeric($value) or  is_null($value) ){
           if (is_null($value)) $value='0';
        if (!$isstart) {
           $isstart=true;
           $startasc=$asc_cell;
          $endasc=$asc_cell; 
   

              }
       }
       if ($isstart){
           $c++;
        if ($zt==1){
           if ($c>$c1) {
            $zt++; 
         $ge=$startasc.'2:'.$endasc.'2';
         $objActSheet->mergeCells("$ge"); 
            $objActSheet->setCellValue($startasc.'2',"岗位职责、效能考核");
         $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
         $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
                  $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getFill()
                   ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);       
                  $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getFill()
                  ->getStartColor()->setARGB('0DB0E59FF'); 
                   $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')
                   ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); 
           $objPHPExcel->getActiveSheet()->getStyle($ex_title)
           ->getFont()->setBold(true); 
            $startasc=$asc_cell;
         $endasc=$asc_cell; 
         $totalfs-=$tfs;
         $tfs=0;
         $c=1;                 
         }
         else{
            $endasc=$asc_cell; 
         }
                              $tfs+=$value;
            if ($tfs>50) {
                $tfs=50;
            } 
           }                                 
        if ($zt==2){
            if ($c>$c2) {       
            $zt++; 
         $ge=$startasc.'2:'.$endasc.'2';
         $objActSheet->mergeCells("$ge"); 
               $objActSheet->setCellValue($startasc.'2',"考试");
         $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
         $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getFill()
                   ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);       
                  $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getFill()
                  ->getStartColor()->setARGB('0DB0E59FF');
                   $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')
                      ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); 
           $objPHPExcel->getActiveSheet()->getStyle($ex_title)
           ->getFont()->setBold(true); 
          $startasc=$asc_cell;
         $endasc=$asc_cell; 
         $totalfs-=$tfs;
         $tfs=0;
         $c=1;
        }
         else{
            $endasc=$asc_cell;       
         }
                              $tfs+=$value;
            if ($tfs>30) {
                $tfs=30;
            } 
           }  
        if ($zt==3){
            if ($c=$c3) {
            $zt++; 
         $ge=$startasc.'2:'.$endasc.'2';
         $objActSheet->mergeCells("$ge"); 
            $objActSheet->setCellValue($startasc.'2',"科长考核");
         $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
         $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getFill()
                   ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);       
                  $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')->getFill()
                  ->getStartColor()->setARGB('0DB0E59FF'); 
                   $objPHPExcel->getActiveSheet()->getStyle($startasc.'2')
                   ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); 
           $objPHPExcel->getActiveSheet()->getStyle($ex_title)
           ->getFont()->setBold(true);                        
           $startasc=$asc_cell;
         $endasc=$asc_cell; 
         $totalfs-=$tfs;
         $tfs=0;
         $c=0;        
         }
         else{
            $endasc=$asc_cell; 
         
         }
                              $tfs+=$value;
            if ($tfs>20) {
                $tfs=20;
            } 
           }             
       }  
       
     
       $objActSheet->setCellValue($ex_title,$key);
       $objActSheet->setCellValue($ex_cell,$value);
//设置单元格宽度       
                $objActSheet->getColumnDimension($asc_cell)->setWidth(16); 
       
       $objPHPExcel->getActiveSheet()->getStyle($ex_title)->getAlignment()
       ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
       $objPHPExcel->getActiveSheet()->getStyle($ex_title)->getAlignment()
       ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);        
       $objPHPExcel->getActiveSheet()->getStyle($ex_cell)->getAlignment()
       ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
       $objPHPExcel->getActiveSheet()->getStyle($ex_cell)->getAlignment()
       ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
//设置填充格式及字体颜色        
       $objPHPExcel->getActiveSheet()->getStyle($ex_cell)
       ->getNumberFormat()->setFormatCode('#,##0.00');       
       $objPHPExcel->getActiveSheet()->getStyle($ex_title)->getFill()
       ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);       
       $objPHPExcel->getActiveSheet()->getStyle($ex_title)->getFill()
       ->getStartColor()->setARGB('0DB0E50A1');
       
       $objPHPExcel->getActiveSheet()->getStyle($ex_title)
       ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
//设置字体粗体       
       $objPHPExcel->getActiveSheet()->getStyle($ex_title)
       ->getFont()->setBold(true);


       
                        
       $j++;
       if ($j>90) {
                  $j=65;
                          $asc_cell="AA" ;
       }
       elseif (strlen($asc_cell)>1) {
               $asc_cell=substr($asc_cell,0,strlen($asc_cell)-1).chr($j);        
       }
       else{
            $asc_cell=chr($j); 
       }     
          next($row);        
       }
       
     
     $ge=$asc_cell.'2:'.$asc_cell.'3';
     $objActSheet->mergeCells("$ge");
     $objPHPExcel->getActiveSheet()->getStyle($asc_cell.'2')->getAlignment()
     ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
     $objPHPExcel->getActiveSheet()->getStyle($asc_cell.'2')->getAlignment()
     ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
                  
        $objActSheet->setCellValue($asc_cell.'2',"总分");
       
       $objActSheet->setCellValue($ex_cell,$totalfs);
//设置填充格式及字体颜色       
       $objPHPExcel->getActiveSheet()->getStyle($ex_cell)       
       ->getNumberFormat()->setFormatCode('#,##0.00');       
      $objPHPExcel->getActiveSheet()->getStyle($asc_cell.'2')->getFill()
      ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);       
      $objPHPExcel->getActiveSheet()->getStyle($asc_cell.'2')->getFill()
      ->getStartColor()->setARGB('0DB0E59FF');
//设置字体颜色      
      $objPHPExcel->getActiveSheet()->getStyle($asc_cell.'2')
      ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
      $objPHPExcel->getActiveSheet()->getStyle($asc_cell.'2')
      ->getFont()->setBold(true);
              
       $i++;        
          }  
//标题栏合并单元格，并设置居中
$objActSheet->setCellValue('A1',$year."年".$jd."季度绩效考核情况统计表");
$value=$objPHPExcel->getActiveSheet()->getCell('A1')->getValue();;     
$ge='A1:'.$asc_cell.'1';
$objActSheet->mergeCells("$ge");
$objActSheet->setCellValue('A1',$value);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
$objPHPExcel->getActiveSheet()->getStyle('A1')
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle('A1')
->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')
->getFont()->setSize(26);
    
//姓名栏合并单元格，并设置居中     
$value=$objPHPExcel->getActiveSheet()->getCell('A3')->getValue();;     
$ge='A2:A3';
$objActSheet->mergeCells("$ge");
$objActSheet->setCellValue('A2',$value);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID);       
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFill()
->getStartColor()->setARGB('0DB0E59FF');
$objPHPExcel->getActiveSheet()->getStyle('A2')
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle('A2')
->getFont()->setBold(true);
//科室栏合并单元格，并设置居中
$value=$objPHPExcel->getActiveSheet()->getCell('B3')->getValue();;     
$ge='B2:B3';
$objActSheet->mergeCells("$ge");
$objActSheet->setCellValue('B2',$value);
$objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
$objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID);       
$objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()
->getStartColor()->setARGB('0DB0E59FF');
$objPHPExcel->getActiveSheet()->getStyle('B2')
->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
$objPHPExcel->getActiveSheet()->getStyle('B2')
->getFont()->setBold(true);


//单元格边框及颜色
$objPHPExcel->getActiveSheet()->getStyle('A2:'.$ex_cell)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A2:'.$ex_cell)->getBorders()->getAllBorders()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);

// sheet名称
$objPHPExcel->getActiveSheet()->setTitle('绩效考核统计');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a clients web browser (Excel5)通知下载
$fn="gxtj-$year-$jd.xls";
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header("Content-Disposition: attachment;filename=$fn");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>

 

楼主经历不容易

您好，我碰到一个问题，不知道是否能帮忙，谢谢哦
///
$objWriter->save('php://output');  ////这句没任何反应

//*************************************************************

set_include_path(get_include_path() . PATH_SEPARATOR . LIB_PATH.'PHPExcel'.DS.'Classes'.DS);

include 'PHPExcel.php';
include 'PHPExcel/IOFactory.php';
require_once 'PHPExcel/RichText.php';


// 创建一个处理对象实例
$objExcel = new PHPExcel();

//$objWriter = new PHPExcel_Writer_Excel5($objExcel);
//$objWriter = new PHPExcel_Writer_Excel2007($objExcel);
//*************************************
//设置文档基本属性
$objProps = $objExcel->getProperties();
$objProps->setCreator("Zeal Li");
$objProps->setLastModifiedBy("Zeal Li");
$objProps->setTitle("Office XLS Test Document");
$objProps->setSubject("Office XLS Test Document, Demo");
$objProps->setDescription("Test document, generated by PHPExcel.");
$objProps->setKeywords("office excel PHPExcel");
$objProps->setCategory("Test");


//*************************************
//设置当前的sheet索引，用于后续的内容操作。
//一般只有在使用多个sheet的时候才需要显示调用。
//缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0
$objExcel->setActiveSheetIndex(0);

$objActSheet = $objExcel->getActiveSheet();

//设置当前活动sheet的名称
$objActSheet->setTitle('测试Sheet');  

$outputFileName = "output.xls";

$fn="gxtj-$year-$jd.xls";
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header("Content-Disposition: attachment;filename=$fn");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');

echo "1111";//到这步都还没什么问题，，

$objWriter->save('php://output');   //到这步就不行了，没任何反应了。

你这个办法，支持多种浏览器吗？好像在Firefox下有问题。

生成的是biff8的格式，还是xml+xlst的？

生成的是标准的excel2003的xls

office97 2000 xp/2002 2003都是biff8，变动很小

2007是oxml的压缩文件格式
------------------------------
phpexcel都支持了

Output your spreadsheet object to different file formats
Excel 2007 (spreadsheetML)
BIFF8 (Excel 97 and higher)
PHPExcel Serialized Spreadsheet
CSV (Comma Separated Values)
HTML
PDF
Read different file formats into your spreadsheet object
Excel 2007 (spreadsheetML)
BIFF5 (Excel 5.0 / Excel 95), BIFF8 (Excel 97 and higher)
PHPExcel Serialized Spreadsheet
Excel 2003 XML format
Symbolic Link (SYLK)
CSV (Comma Separated Values)