php 如何写入、读取word，excel文档

如何在php写入、读取word文档

 1 <?
 2 
 3 //如何在php写入、读取word文档
 4 
 5 // 建立一个指向新COM组件的索引
 6 $word = new COM("word.application") or die("Can’t start Word!");
 7 // 显示目前正在使用的Word的版本号
 8 //echo “Loading Word, v. {$word->Version}<br>”;
 9 // 把它的可见性设置为0（假），如果要使它在最前端打开，使用1（真）
10 // to open the application in the forefront, use 1 (true)
11 //$word->Visible = 0;
12 
13 //打?一个文档
14 $word->Documents->OPen("d:mywebmuban.doc");
15 //读取文档内容
16 
17 $test= $word->ActiveDocument->content->Text;
18 
19 echo $test;
20 echo "<br>";
21 
22 //将文档中需要换的变量更换一下
23 $test=str_replace("<{变量}>","这是变量",$test);
24 
25 echo $test;
26 $word->Documents->Add();
27 // 在新文档中添加文字
28 $word->Selection->TypeText("$test");
29 //把文档保存在目录中
30 $word->Documents[1]->SaveAs("d:/myweb/comtest.doc");
31 // 关闭与COM组件之间的连接
32 $word->Quit();
33 
34 ?>

View Code

用PHPExcel读取excel并导入数据库

 1 <?php
 2 set_time_limit(20000);
 3 ini_set('memory_limit','-1');
 4 // by www.phpddt.com
 5 require_once './PHPExcel.php';
 6 require_once './PHPExcel/IOFactory.php';
 7 require_once './PHPExcel/Reader/Excel5.php';
 8  
 9 //使用pdo连接数据库
10 $dsn = "mysql:host=localhost;dbname=alumni;";
11 $user = "root";
12 $password = "";
13 try{
14     $dbh = new PDO($dsn,$user,$password);
15     $dbh->query('set names utf8;'); 
16 }catch(PDOException $e){
17     echo "连接失败".$e->getMessage();
18 }
19 //pdo绑定参数操作
20 $stmt = $dbh->prepare("insert into alumni(gid,student_no,name) values (:gid,:student_no,:name) ");
21 $stmt->bindParam(":gid", $gid,PDO::PARAM_STR);
22 $stmt->bindParam(":student_no", $student_no,PDO::PARAM_STR);
23 $stmt->bindParam(":name", $name,PDO::PARAM_STR);
24  
25 $objReader = new PHPExcel_Reader_Excel5(); //use excel2007
26 $objPHPExcel = $objReader->load('bks.xls'); //指定的文件
27 $sheet = $objPHPExcel->getSheet(0);
28 $highestRow = $sheet->getHighestRow(); // 取得总行数
29 $highestColumn = $sheet->getHighestColumn(); // 取得总列数
30  
31 for($j=1;$j<=10;$j++)
32 {
33  
34 $student_no = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//第一列学号
35 $name = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//第二列姓名
36 $gid = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//第三列gid
37 }
38 //将获取的excel内容插入到数据库
39 $stmt->execute();
40 ?>

View Code

以下内容是转载的：

很多文章都有提到关于使用phpExcel实现Excel数据的导入导出，大部分文章都差不多，或者就是转载的，都会出现一些问题，下面是本人研究phpExcel的使用例程总结出来的使用方法，接下来直接进入正题。

首先先说一下，本人的这段例程是使用在Thinkphp的开发框架上，要是使用在其他框架也是同样的方法，很多人可能不能正确的实现Excel的导入导出，问题基本上都是phpExcel的核心类引用路径出错，如果有问题大家务必要对路劲是否引用正确进行测试。

（一）导入Excel

第一，在前台html页面进行上传文件：如：

<form method="post" action="php文件" enctype="multipart/form-data">
         <h3>导入Excel表：</h3><input  type="file" name="file_stu" />
           <input type="submit"  value="导入" />
</form>

第二，在对应的php文件进行文件的处理

 1  if (! empty ( $_FILES ['file_stu'] ['name'] ))
 2  {
 3     $tmp_file = $_FILES ['file_stu'] ['tmp_name'];
 4     $file_types = explode ( ".", $_FILES ['file_stu'] ['name'] );
 5     $file_type = $file_types [count ( $file_types ) - 1];
 6      /*判别是不是.xls文件，判别是不是excel文件*/
 7      if (strtolower ( $file_type ) != "xls")              
 8     {
 9           $this->error ( '不是Excel文件，重新上传' );
10      }
11     /*设置上传路径*/
12      $savePath = SITE_PATH . '/public/upfile/Excel/';
13     /*以时间来命名上传的文件*/
14      $str = date ( 'Ymdhis' ); 
15      $file_name = $str . "." . $file_type;
16      /*是否上传成功*/
17      if (! copy ( $tmp_file, $savePath . $file_name )) 
18       {
19           $this->error ( '上传失败' );
20       }
21     /*
22        *对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中
23       注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入
24     */
25   $res = Service ( 'ExcelToArray' )->read ( $savePath . $file_name );
26    /*
27         重要代码 解决Thinkphp M、D方法不能调用的问题  
28         如果在thinkphp中遇到M 、D方法失效时就加入下面一句代码
29     */
30    //spl_autoload_register ( array ('Think', 'autoload' ) );
31    /*对生成的数组进行数据库的写入*/
32    foreach ( $res as $k => $v ) 
33    {
34        if ($k != 0) 
35       {
36            $data ['uid'] = $v [0];
37            $data ['password'] = sha1 ( '111111' );
38            $data ['email'] = $v [1];
39            $data ['uname'] = $v [3];
40           $data ['institute'] = $v [4];
41          $result = M ( 'user' )->add ( $data );
42          if (! $result) 
43          {
44               $this->error ( '导入数据库失败' );
45           }
46       }
47    }
48 }

第三：ExcelToArrary类,用来引用phpExcel并处理Excel数据的

class ExcelToArrary extends Service{
 public function __construct() {
     /*导入phpExcel核心类    注意 ：你的路径跟我不一样就不能直接复制*/
     include_once('./Excel/PHPExcel.php');
 }
/**
* 读取excel $filename 路径文件名 $encode 返回数据的编码 默认为utf8
*以下基本都不要修改
*/
public function read($filename,$encode='utf-8'){
          $objReader = PHPExcel_IOFactory::createReader('Excel5');
          $objReader->setReadDataOnly(true);
          $objPHPExcel = $objReader->load($filename);
          $objWorksheet = $objPHPExcel->getActiveSheet();
　　　 $highestRow = $objWorksheet->getHighestRow(); 
　　　 $highestColumn = $objWorksheet->getHighestColumn(); 
　　    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); 
 　　   $excelData = array(); 
 　　　for ($row = 1; $row <= $highestRow; $row++) { 
    　　  for ($col = 0; $col < $highestColumnIndex; $col++) { 
                 $excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
           } 
         } 
        return $excelData;
    }    
 }

第四，以上就是导入的全部内容，phpExcel包附在最后。

（二）Excel的导出（相对于导入简单多了）

第一，先查出数据库里面要生成Excel的数据，如：

$data= M('User')->findAll(); //查出数据
$name='Excelfile'; //生成的Excel文件文件名
$res=service('ExcelToArrary')->push($data,$name);

第二，ExcelToArrary类,用来引用phpExcel并处理数据的

class ExcelToArrary extends Service{
       public function __construct() {
              /*导入phpExcel核心类    注意 ：你的路径跟我不一样就不能直接复制*/
               include_once('./Excel/PHPExcel.php');
       }
     /* 导出excel函数*/
    public function push($data,$name='Excel'){
          error_reporting(E_ALL);
          date_default_timezone_set('Europe/London');
         $objPHPExcel = new PHPExcel();
        /*以下是一些设置 ，什么作者  标题啊之类的*/
         $objPHPExcel->getProperties()->setCreator("转弯的阳光")
                               ->setLastModifiedBy("转弯的阳光")
                               ->setTitle("数据EXCEL导出")
                               ->setSubject("数据EXCEL导出")
                               ->setDescription("备份数据")
                               ->setKeywords("excel")
                              ->setCategory("result file");
         /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
        foreach($data as $k => $v){
             $num=$k+1;
             $objPHPExcel->setActiveSheetIndex(0)
                         //Excel的第A列，uid是你查出数组的键值，下面以此类推
                          ->setCellValue('A'.$num, $v['uid'])    
                          ->setCellValue('B'.$num, $v['email'])
                          ->setCellValue('C'.$num, $v['password'])
            }
            $objPHPExcel->getActiveSheet()->setTitle('User');
            $objPHPExcel->setActiveSheetIndex(0);
             header('Content-Type: application/vnd.ms-excel');
             header('Content-Disposition: attachment;filename="'.$name.'.xls"');
             header('Cache-Control: max-age=0');
             $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
             $objWriter->save('php://output');
             exit;
      }

第三，以上就是导出的全部内容，phpExcel包附在最后。

phpexccel类：

   1 <?php
   2 /**
   3  * PHPExcel
   4  *
   5  * Copyright (c) 2006 - 2014 PHPExcel
   6  *
   7  * This library is free software; you can redistribute it and/or
   8  * modify it under the terms of the GNU Lesser General Public
   9  * License as published by the Free Software Foundation; either
  10  * version 2.1 of the License, or (at your option) any later version.
  11  *
  12  * This library is distributed in the hope that it will be useful,
  13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  15  * Lesser General Public License for more details.
  16  *
  17  * You should have received a copy of the GNU Lesser General Public
  18  * License along with this library; if not, write to the Free Software
  19  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  20  *
  21  * @category   PHPExcel
  22  * @package    PHPExcel
  23  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  24  * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
  25  * @version    1.8.0, 2014-03-02
  26  */
  27 
  28 
  29 /** PHPExcel root directory */
  30 if (!defined('PHPEXCEL_ROOT')) {
  31     define('PHPEXCEL_ROOT', dirname(__FILE__) . '/');
  32     require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  33 }
  34 
  35 
  36 /**
  37  * PHPExcel
  38  *
  39  * @category   PHPExcel
  40  * @package    PHPExcel
  41  * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  42  */
  43 class PHPExcel
  44 {
  45     /**
  46      * Unique ID
  47      *
  48      * @var string
  49      */
  50     private $_uniqueID;
  51 
  52     /**
  53      * Document properties
  54      *
  55      * @var PHPExcel_DocumentProperties
  56      */
  57     private $_properties;
  58 
  59     /**
  60      * Document security
  61      *
  62      * @var PHPExcel_DocumentSecurity
  63      */
  64     private $_security;
  65 
  66     /**
  67      * Collection of Worksheet objects
  68      *
  69      * @var PHPExcel_Worksheet[]
  70      */
  71     private $_workSheetCollection = array();
  72 
  73     /**
  74      * Calculation Engine
  75      *
  76      * @var PHPExcel_Calculation
  77      */
  78     private $_calculationEngine = NULL;
  79 
  80     /**
  81      * Active sheet index
  82      *
  83      * @var int
  84      */
  85     private $_activeSheetIndex = 0;
  86 
  87     /**
  88      * Named ranges
  89      *
  90      * @var PHPExcel_NamedRange[]
  91      */
  92     private $_namedRanges = array();
  93 
  94     /**
  95      * CellXf supervisor
  96      *
  97      * @var PHPExcel_Style
  98      */
  99     private $_cellXfSupervisor;
 100 
 101     /**
 102      * CellXf collection
 103      *
 104      * @var PHPExcel_Style[]
 105      */
 106     private $_cellXfCollection = array();
 107 
 108     /**
 109      * CellStyleXf collection
 110      *
 111      * @var PHPExcel_Style[]
 112      */
 113     private $_cellStyleXfCollection = array();
 114 
 115     /**
 116     * _hasMacros : this workbook have macros ?
 117     *
 118     * @var bool
 119     */
 120     private $_hasMacros = FALSE;
 121 
 122     /**
 123     * _macrosCode : all macros code (the vbaProject.bin file, this include form, code,  etc.), NULL if no macro
 124     *
 125     * @var binary
 126     */
 127     private $_macrosCode=NULL;
 128     /**
 129     * _macrosCertificate : if macros are signed, contains vbaProjectSignature.bin file, NULL if not signed
 130     *
 131     * @var binary
 132     */
 133     private $_macrosCertificate=NULL;
 134 
 135     /**
 136     * _ribbonXMLData : NULL if workbook is'nt Excel 2007 or not contain a customized UI
 137     *
 138     * @var NULL|string
 139     */
 140     private $_ribbonXMLData=NULL;
 141 
 142     /**
 143     * _ribbonBinObjects : NULL if workbook is'nt Excel 2007 or not contain embedded objects (picture(s)) for Ribbon Elements
 144     * ignored if $_ribbonXMLData is null
 145     *
 146     * @var NULL|array
 147     */
 148     private $_ribbonBinObjects=NULL;
 149 
 150     /**
 151     * The workbook has macros ?
 152     *
 153     * @return true if workbook has macros, false if not
 154     */
 155     public function hasMacros(){
 156         return $this->_hasMacros;
 157     }
 158 
 159     /**
 160     * Define if a workbook has macros
 161     *
 162     * @param true|false
 163     */
 164     public function setHasMacros($hasMacros=false){
 165         $this->_hasMacros=(bool)$hasMacros;
 166     }
 167 
 168     /**
 169     * Set the macros code
 170     *
 171     * @param binary string|null
 172     */
 173     public function setMacrosCode($MacrosCode){
 174         $this->_macrosCode=$MacrosCode;
 175         $this->setHasMacros(!is_null($MacrosCode));
 176     }
 177 
 178     /**
 179     * Return the macros code
 180     *
 181     * @return binary|null
 182     */
 183     public function getMacrosCode(){
 184         return $this->_macrosCode;
 185     }
 186 
 187     /**
 188     * Set the macros certificate
 189     *
 190     * @param binary|null
 191     */
 192     public function setMacrosCertificate($Certificate=NULL){
 193         $this->_macrosCertificate=$Certificate;
 194     }
 195 
 196     /**
 197     * Is the project signed ?
 198     *
 199     * @return true|false
 200     */
 201     public function hasMacrosCertificate(){
 202         return !is_null($this->_macrosCertificate);
 203     }
 204 
 205     /**
 206     * Return the macros certificate
 207     *
 208     * @return binary|null
 209     */
 210     public function getMacrosCertificate(){
 211         return $this->_macrosCertificate;
 212     }
 213 
 214     /**
 215     * Remove all macros, certificate from spreadsheet
 216     *
 217     * @param none
 218     * @return void
 219     */
 220     public function discardMacros(){
 221         $this->_hasMacros=false;
 222         $this->_macrosCode=NULL;
 223         $this->_macrosCertificate=NULL;
 224     }
 225 
 226     /**
 227     * set ribbon XML data
 228     *
 229     */
 230     public function setRibbonXMLData($Target=NULL, $XMLData=NULL){
 231         if(!is_null($Target) && !is_null($XMLData)){
 232             $this->_ribbonXMLData=array('target'=>$Target, 'data'=>$XMLData);
 233         }else{
 234             $this->_ribbonXMLData=NULL;
 235         }
 236     }
 237 
 238     /**
 239     * retrieve ribbon XML Data
 240     *
 241     * return string|null|array
 242     */
 243     public function getRibbonXMLData($What='all'){//we need some constants here...
 244         $ReturnData=NULL;
 245         $What=strtolower($What);
 246         switch($What){
 247         case 'all':
 248             $ReturnData=$this->_ribbonXMLData;
 249             break;
 250         case 'target':
 251         case 'data':
 252             if(is_array($this->_ribbonXMLData) && array_key_exists($What,$this->_ribbonXMLData)){
 253                 $ReturnData=$this->_ribbonXMLData[$What];
 254             }//else $ReturnData stay at null
 255             break;
 256         }//default: $ReturnData at null
 257         return $ReturnData;
 258     }
 259 
 260     /**
 261     * store binaries ribbon objects (pictures)
 262     *
 263     */
 264     public function setRibbonBinObjects($BinObjectsNames=NULL, $BinObjectsData=NULL){
 265         if(!is_null($BinObjectsNames) && !is_null($BinObjectsData)){
 266             $this->_ribbonBinObjects=array('names'=>$BinObjectsNames, 'data'=>$BinObjectsData);
 267         }else{
 268             $this->_ribbonBinObjects=NULL;
 269         }
 270     }
 271     /**
 272     * return the extension of a filename. Internal use for a array_map callback (php<5.3 don't like lambda function)
 273     *
 274     */
 275     private function _getExtensionOnly($ThePath){
 276         return pathinfo($ThePath, PATHINFO_EXTENSION);
 277     }
 278 
 279     /**
 280     * retrieve Binaries Ribbon Objects
 281     *
 282     */
 283     public function getRibbonBinObjects($What='all'){
 284         $ReturnData=NULL;
 285         $What=strtolower($What);
 286         switch($What){
 287         case 'all':
 288             return $this->_ribbonBinObjects;
 289             break;
 290         case 'names':
 291         case 'data':
 292             if(is_array($this->_ribbonBinObjects) && array_key_exists($What, $this->_ribbonBinObjects)){
 293                 $ReturnData=$this->_ribbonBinObjects[$What];
 294             }
 295             break;
 296         case 'types':
 297             if(is_array($this->_ribbonBinObjects) && array_key_exists('data', $this->_ribbonBinObjects) && is_array($this->_ribbonBinObjects['data'])){
 298                 $tmpTypes=array_keys($this->_ribbonBinObjects['data']);
 299                 $ReturnData=array_unique(array_map(array($this,'_getExtensionOnly'), $tmpTypes));
 300             }else
 301                 $ReturnData=array();//the caller want an array... not null if empty
 302             break;
 303         }
 304         return $ReturnData;
 305     }
 306 
 307     /**
 308     * This workbook have a custom UI ?
 309     *
 310     * @return true|false
 311     */
 312     public function hasRibbon(){
 313         return !is_null($this->_ribbonXMLData);
 314     }
 315 
 316     /**
 317     * This workbook have additionnal object for the ribbon ?
 318     *
 319     * @return true|false
 320     */
 321     public function hasRibbonBinObjects(){
 322         return !is_null($this->_ribbonBinObjects);
 323     }
 324 
 325     /**
 326      * Check if a sheet with a specified code name already exists
 327      *
 328      * @param string $pSheetCodeName  Name of the worksheet to check
 329      * @return boolean
 330      */
 331     public function sheetCodeNameExists($pSheetCodeName)
 332     {
 333         return ($this->getSheetByCodeName($pSheetCodeName) !== NULL);
 334     }
 335 
 336     /**
 337      * Get sheet by code name. Warning : sheet don't have always a code name !
 338      *
 339      * @param string $pName Sheet name
 340      * @return PHPExcel_Worksheet
 341      */
 342     public function getSheetByCodeName($pName = '')
 343     {
 344         $worksheetCount = count($this->_workSheetCollection);
 345         for ($i = 0; $i < $worksheetCount; ++$i) {
 346             if ($this->_workSheetCollection[$i]->getCodeName() == $pName) {
 347                 return $this->_workSheetCollection[$i];
 348             }
 349         }
 350 
 351         return null;
 352     }
 353 
 354      /**
 355      * Create a new PHPExcel with one Worksheet
 356      */
 357     public function __construct()
 358     {
 359         $this->_uniqueID = uniqid();
 360         $this->_calculationEngine    = PHPExcel_Calculation::getInstance($this);
 361 
 362         // Initialise worksheet collection and add one worksheet
 363         $this->_workSheetCollection = array();
 364         $this->_workSheetCollection[] = new PHPExcel_Worksheet($this);
 365         $this->_activeSheetIndex = 0;
 366 
 367         // Create document properties
 368         $this->_properties = new PHPExcel_DocumentProperties();
 369 
 370         // Create document security
 371         $this->_security = new PHPExcel_DocumentSecurity();
 372 
 373         // Set named ranges
 374         $this->_namedRanges = array();
 375 
 376         // Create the cellXf supervisor
 377         $this->_cellXfSupervisor = new PHPExcel_Style(true);
 378         $this->_cellXfSupervisor->bindParent($this);
 379 
 380         // Create the default style
 381         $this->addCellXf(new PHPExcel_Style);
 382         $this->addCellStyleXf(new PHPExcel_Style);
 383     }
 384 
 385     /**
 386      * Code to execute when this worksheet is unset()
 387      *
 388      */
 389     public function __destruct() {
 390         PHPExcel_Calculation::unsetInstance($this);
 391         $this->disconnectWorksheets();
 392     }    //    function __destruct()
 393 
 394     /**
 395      * Disconnect all worksheets from this PHPExcel workbook object,
 396      *    typically so that the PHPExcel object can be unset
 397      *
 398      */
 399     public function disconnectWorksheets()
 400     {
 401         $worksheet = NULL;
 402         foreach($this->_workSheetCollection as $k => &$worksheet) {
 403             $worksheet->disconnectCells();
 404             $this->_workSheetCollection[$k] = null;
 405         }
 406         unset($worksheet);
 407         $this->_workSheetCollection = array();
 408     }
 409 
 410     /**
 411      * Return the calculation engine for this worksheet
 412      *
 413      * @return PHPExcel_Calculation
 414      */
 415     public function getCalculationEngine()
 416     {
 417         return $this->_calculationEngine;
 418     }    //    function getCellCacheController()
 419 
 420     /**
 421      * Get properties
 422      *
 423      * @return PHPExcel_DocumentProperties
 424      */
 425     public function getProperties()
 426     {
 427         return $this->_properties;
 428     }
 429 
 430     /**
 431      * Set properties
 432      *
 433      * @param PHPExcel_DocumentProperties    $pValue
 434      */
 435     public function setProperties(PHPExcel_DocumentProperties $pValue)
 436     {
 437         $this->_properties = $pValue;
 438     }
 439 
 440     /**
 441      * Get security
 442      *
 443      * @return PHPExcel_DocumentSecurity
 444      */
 445     public function getSecurity()
 446     {
 447         return $this->_security;
 448     }
 449 
 450     /**
 451      * Set security
 452      *
 453      * @param PHPExcel_DocumentSecurity    $pValue
 454      */
 455     public function setSecurity(PHPExcel_DocumentSecurity $pValue)
 456     {
 457         $this->_security = $pValue;
 458     }
 459 
 460     /**
 461      * Get active sheet
 462      *
 463      * @return PHPExcel_Worksheet
 464      */
 465     public function getActiveSheet()
 466     {
 467         return $this->_workSheetCollection[$this->_activeSheetIndex];
 468     }
 469 
 470     /**
 471      * Create sheet and add it to this workbook
 472      *
 473      * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
 474      * @return PHPExcel_Worksheet
 475      * @throws PHPExcel_Exception
 476      */
 477     public function createSheet($iSheetIndex = NULL)
 478     {
 479         $newSheet = new PHPExcel_Worksheet($this);
 480         $this->addSheet($newSheet, $iSheetIndex);
 481         return $newSheet;
 482     }
 483 
 484     /**
 485      * Check if a sheet with a specified name already exists
 486      *
 487      * @param  string $pSheetName  Name of the worksheet to check
 488      * @return boolean
 489      */
 490     public function sheetNameExists($pSheetName)
 491     {
 492         return ($this->getSheetByName($pSheetName) !== NULL);
 493     }
 494 
 495     /**
 496      * Add sheet
 497      *
 498      * @param  PHPExcel_Worksheet $pSheet
 499      * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
 500      * @return PHPExcel_Worksheet
 501      * @throws PHPExcel_Exception
 502      */
 503     public function addSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = NULL)
 504     {
 505         if ($this->sheetNameExists($pSheet->getTitle())) {
 506             throw new PHPExcel_Exception(
 507                 "Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename this worksheet first."
 508             );
 509         }
 510 
 511         if($iSheetIndex === NULL) {
 512             if ($this->_activeSheetIndex < 0) {
 513                 $this->_activeSheetIndex = 0;
 514             }
 515             $this->_workSheetCollection[] = $pSheet;
 516         } else {
 517             // Insert the sheet at the requested index
 518             array_splice(
 519                 $this->_workSheetCollection,
 520                 $iSheetIndex,
 521                 0,
 522                 array($pSheet)
 523                 );
 524 
 525             // Adjust active sheet index if necessary
 526             if ($this->_activeSheetIndex >= $iSheetIndex) {
 527                 ++$this->_activeSheetIndex;
 528             }
 529         }
 530 
 531         if ($pSheet->getParent() === null) {
 532             $pSheet->rebindParent($this);
 533         }
 534 
 535         return $pSheet;
 536     }
 537 
 538     /**
 539      * Remove sheet by index
 540      *
 541      * @param  int $pIndex Active sheet index
 542      * @throws PHPExcel_Exception
 543      */
 544     public function removeSheetByIndex($pIndex = 0)
 545     {
 546 
 547         $numSheets = count($this->_workSheetCollection);
 548 
 549         if ($pIndex > $numSheets - 1) {
 550             throw new PHPExcel_Exception(
 551                 "You tried to remove a sheet by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
 552             );
 553         } else {
 554             array_splice($this->_workSheetCollection, $pIndex, 1);
 555         }
 556         // Adjust active sheet index if necessary
 557         if (($this->_activeSheetIndex >= $pIndex) &&
 558             ($pIndex > count($this->_workSheetCollection) - 1)) {
 559             --$this->_activeSheetIndex;
 560         }
 561 
 562     }
 563 
 564     /**
 565      * Get sheet by index
 566      *
 567      * @param  int $pIndex Sheet index
 568      * @return PHPExcel_Worksheet
 569      * @throws PHPExcel_Exception
 570      */
 571     public function getSheet($pIndex = 0)
 572     {
 573 
 574         $numSheets = count($this->_workSheetCollection);
 575 
 576         if ($pIndex > $numSheets - 1) {
 577             throw new PHPExcel_Exception(
 578                 "Your requested sheet index: {$pIndex} is out of bounds. The actual number of sheets is {$numSheets}."
 579                );
 580         } else {
 581             return $this->_workSheetCollection[$pIndex];
 582         }
 583     }
 584 
 585     /**
 586      * Get all sheets
 587      *
 588      * @return PHPExcel_Worksheet[]
 589      */
 590     public function getAllSheets()
 591     {
 592         return $this->_workSheetCollection;
 593     }
 594 
 595     /**
 596      * Get sheet by name
 597      *
 598      * @param  string $pName Sheet name
 599      * @return PHPExcel_Worksheet
 600      */
 601     public function getSheetByName($pName = '')
 602     {
 603         $worksheetCount = count($this->_workSheetCollection);
 604         for ($i = 0; $i < $worksheetCount; ++$i) {
 605             if ($this->_workSheetCollection[$i]->getTitle() === $pName) {
 606                 return $this->_workSheetCollection[$i];
 607             }
 608         }
 609 
 610         return NULL;
 611     }
 612 
 613     /**
 614      * Get index for sheet
 615      *
 616      * @param  PHPExcel_Worksheet $pSheet
 617      * @return Sheet index
 618      * @throws PHPExcel_Exception
 619      */
 620     public function getIndex(PHPExcel_Worksheet $pSheet)
 621     {
 622         foreach ($this->_workSheetCollection as $key => $value) {
 623             if ($value->getHashCode() == $pSheet->getHashCode()) {
 624                 return $key;
 625             }
 626         }
 627 
 628         throw new PHPExcel_Exception("Sheet does not exist.");
 629     }
 630 
 631     /**
 632      * Set index for sheet by sheet name.
 633      *
 634      * @param  string $sheetName Sheet name to modify index for
 635      * @param  int $newIndex New index for the sheet
 636      * @return New sheet index
 637      * @throws PHPExcel_Exception
 638      */
 639     public function setIndexByName($sheetName, $newIndex)
 640     {
 641         $oldIndex = $this->getIndex($this->getSheetByName($sheetName));
 642         $pSheet = array_splice(
 643             $this->_workSheetCollection,
 644             $oldIndex,
 645             1
 646         );
 647         array_splice(
 648             $this->_workSheetCollection,
 649             $newIndex,
 650             0,
 651             $pSheet
 652         );
 653         return $newIndex;
 654     }
 655 
 656     /**
 657      * Get sheet count
 658      *
 659      * @return int
 660      */
 661     public function getSheetCount()
 662     {
 663         return count($this->_workSheetCollection);
 664     }
 665 
 666     /**
 667      * Get active sheet index
 668      *
 669      * @return int Active sheet index
 670      */
 671     public function getActiveSheetIndex()
 672     {
 673         return $this->_activeSheetIndex;
 674     }
 675 
 676     /**
 677      * Set active sheet index
 678      *
 679      * @param  int $pIndex Active sheet index
 680      * @throws PHPExcel_Exception
 681      * @return PHPExcel_Worksheet
 682      */
 683     public function setActiveSheetIndex($pIndex = 0)
 684     {
 685             $numSheets = count($this->_workSheetCollection);
 686 
 687         if ($pIndex > $numSheets - 1) {
 688             throw new PHPExcel_Exception(
 689                 "You tried to set a sheet active by the out of bounds index: {$pIndex}. The actual number of sheets is {$numSheets}."
 690             );
 691         } else {
 692             $this->_activeSheetIndex = $pIndex;
 693         }
 694         return $this->getActiveSheet();
 695     }
 696 
 697     /**
 698      * Set active sheet index by name
 699      *
 700      * @param  string $pValue Sheet title
 701      * @return PHPExcel_Worksheet
 702      * @throws PHPExcel_Exception
 703      */
 704     public function setActiveSheetIndexByName($pValue = '')
 705     {
 706         if (($worksheet = $this->getSheetByName($pValue)) instanceof PHPExcel_Worksheet) {
 707             $this->setActiveSheetIndex($this->getIndex($worksheet));
 708             return $worksheet;
 709         }
 710 
 711         throw new PHPExcel_Exception('Workbook does not contain sheet:' . $pValue);
 712     }
 713 
 714     /**
 715      * Get sheet names
 716      *
 717      * @return string[]
 718      */
 719     public function getSheetNames()
 720     {
 721         $returnValue = array();
 722         $worksheetCount = $this->getSheetCount();
 723         for ($i = 0; $i < $worksheetCount; ++$i) {
 724             $returnValue[] = $this->getSheet($i)->getTitle();
 725         }
 726 
 727         return $returnValue;
 728     }
 729 
 730     /**
 731      * Add external sheet
 732      *
 733      * @param  PHPExcel_Worksheet $pSheet External sheet to add
 734      * @param  int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last)
 735      * @throws PHPExcel_Exception
 736      * @return PHPExcel_Worksheet
 737      */
 738     public function addExternalSheet(PHPExcel_Worksheet $pSheet, $iSheetIndex = null) {
 739         if ($this->sheetNameExists($pSheet->getTitle())) {
 740             throw new PHPExcel_Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first.");
 741         }
 742 
 743         // count how many cellXfs there are in this workbook currently, we will need this below
 744         $countCellXfs = count($this->_cellXfCollection);
 745 
 746         // copy all the shared cellXfs from the external workbook and append them to the current
 747         foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) {
 748             $this->addCellXf(clone $cellXf);
 749         }
 750 
 751         // move sheet to this workbook
 752         $pSheet->rebindParent($this);
 753 
 754         // update the cellXfs
 755         foreach ($pSheet->getCellCollection(false) as $cellID) {
 756             $cell = $pSheet->getCell($cellID);
 757             $cell->setXfIndex( $cell->getXfIndex() + $countCellXfs );
 758         }
 759 
 760         return $this->addSheet($pSheet, $iSheetIndex);
 761     }
 762 
 763     /**
 764      * Get named ranges
 765      *
 766      * @return PHPExcel_NamedRange[]
 767      */
 768     public function getNamedRanges() {
 769         return $this->_namedRanges;
 770     }
 771 
 772     /**
 773      * Add named range
 774      *
 775      * @param  PHPExcel_NamedRange $namedRange
 776      * @return PHPExcel
 777      */
 778     public function addNamedRange(PHPExcel_NamedRange $namedRange) {
 779         if ($namedRange->getScope() == null) {
 780             // global scope
 781             $this->_namedRanges[$namedRange->getName()] = $namedRange;
 782         } else {
 783             // local scope
 784             $this->_namedRanges[$namedRange->getScope()->getTitle().'!'.$namedRange->getName()] = $namedRange;
 785         }
 786         return true;
 787     }
 788 
 789     /**
 790      * Get named range
 791      *
 792      * @param  string $namedRange
 793      * @param  PHPExcel_Worksheet|null $pSheet Scope. Use null for global scope
 794      * @return PHPExcel_NamedRange|null
 795      */
 796     public function getNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
 797         $returnValue = null;
 798 
 799         if ($namedRange != '' && ($namedRange !== NULL)) {
 800             // first look for global defined name
 801             if (isset($this->_namedRanges[$namedRange])) {
 802                 $returnValue = $this->_namedRanges[$namedRange];
 803             }
 804 
 805             // then look for local defined name (has priority over global defined name if both names exist)
 806             if (($pSheet !== NULL) && isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
 807                 $returnValue = $this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange];
 808             }
 809         }
 810 
 811         return $returnValue;
 812     }
 813 
 814     /**
 815      * Remove named range
 816      *
 817      * @param  string  $namedRange
 818      * @param  PHPExcel_Worksheet|null  $pSheet  Scope: use null for global scope.
 819      * @return PHPExcel
 820      */
 821     public function removeNamedRange($namedRange, PHPExcel_Worksheet $pSheet = null) {
 822         if ($pSheet === NULL) {
 823             if (isset($this->_namedRanges[$namedRange])) {
 824                 unset($this->_namedRanges[$namedRange]);
 825             }
 826         } else {
 827             if (isset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange])) {
 828                 unset($this->_namedRanges[$pSheet->getTitle() . '!' . $namedRange]);
 829             }
 830         }
 831         return $this;
 832     }
 833 
 834     /**
 835      * Get worksheet iterator
 836      *
 837      * @return PHPExcel_WorksheetIterator
 838      */
 839     public function getWorksheetIterator() {
 840         return new PHPExcel_WorksheetIterator($this);
 841     }
 842 
 843     /**
 844      * Copy workbook (!= clone!)
 845      *
 846      * @return PHPExcel
 847      */
 848     public function copy() {
 849         $copied = clone $this;
 850 
 851         $worksheetCount = count($this->_workSheetCollection);
 852         for ($i = 0; $i < $worksheetCount; ++$i) {
 853             $this->_workSheetCollection[$i] = $this->_workSheetCollection[$i]->copy();
 854             $this->_workSheetCollection[$i]->rebindParent($this);
 855         }
 856 
 857         return $copied;
 858     }
 859 
 860     /**
 861      * Implement PHP __clone to create a deep clone, not just a shallow copy.
 862      */
 863     public function __clone() {
 864         foreach($this as $key => $val) {
 865             if (is_object($val) || (is_array($val))) {
 866                 $this->{$key} = unserialize(serialize($val));
 867             }
 868         }
 869     }
 870 
 871     /**
 872      * Get the workbook collection of cellXfs
 873      *
 874      * @return PHPExcel_Style[]
 875      */
 876     public function getCellXfCollection()
 877     {
 878         return $this->_cellXfCollection;
 879     }
 880 
 881     /**
 882      * Get cellXf by index
 883      *
 884      * @param  int $pIndex
 885      * @return PHPExcel_Style
 886      */
 887     public function getCellXfByIndex($pIndex = 0)
 888     {
 889         return $this->_cellXfCollection[$pIndex];
 890     }
 891 
 892     /**
 893      * Get cellXf by hash code
 894      *
 895      * @param  string $pValue
 896      * @return PHPExcel_Style|false
 897      */
 898     public function getCellXfByHashCode($pValue = '')
 899     {
 900         foreach ($this->_cellXfCollection as $cellXf) {
 901             if ($cellXf->getHashCode() == $pValue) {
 902                 return $cellXf;
 903             }
 904         }
 905         return false;
 906     }
 907 
 908     /**
 909      * Check if style exists in style collection
 910      *
 911      * @param  PHPExcel_Style $pCellStyle
 912      * @return boolean
 913      */
 914     public function cellXfExists($pCellStyle = null)
 915     {
 916         return in_array($pCellStyle, $this->_cellXfCollection, true);
 917     }
 918 
 919     /**
 920      * Get default style
 921      *
 922      * @return PHPExcel_Style
 923      * @throws PHPExcel_Exception
 924      */
 925     public function getDefaultStyle()
 926     {
 927         if (isset($this->_cellXfCollection[0])) {
 928             return $this->_cellXfCollection[0];
 929         }
 930         throw new PHPExcel_Exception('No default style found for this workbook');
 931     }
 932 
 933     /**
 934      * Add a cellXf to the workbook
 935      *
 936      * @param PHPExcel_Style $style
 937      */
 938     public function addCellXf(PHPExcel_Style $style)
 939     {
 940         $this->_cellXfCollection[] = $style;
 941         $style->setIndex(count($this->_cellXfCollection) - 1);
 942     }
 943 
 944     /**
 945      * Remove cellXf by index. It is ensured that all cells get their xf index updated.
 946      *
 947      * @param  int $pIndex Index to cellXf
 948      * @throws PHPExcel_Exception
 949      */
 950     public function removeCellXfByIndex($pIndex = 0)
 951     {
 952         if ($pIndex > count($this->_cellXfCollection) - 1) {
 953             throw new PHPExcel_Exception("CellXf index is out of bounds.");
 954         } else {
 955             // first remove the cellXf
 956             array_splice($this->_cellXfCollection, $pIndex, 1);
 957 
 958             // then update cellXf indexes for cells
 959             foreach ($this->_workSheetCollection as $worksheet) {
 960                 foreach ($worksheet->getCellCollection(false) as $cellID) {
 961                     $cell = $worksheet->getCell($cellID);
 962                     $xfIndex = $cell->getXfIndex();
 963                     if ($xfIndex > $pIndex ) {
 964                         // decrease xf index by 1
 965                         $cell->setXfIndex($xfIndex - 1);
 966                     } else if ($xfIndex == $pIndex) {
 967                         // set to default xf index 0
 968                         $cell->setXfIndex(0);
 969                     }
 970                 }
 971             }
 972         }
 973     }
 974 
 975     /**
 976      * Get the cellXf supervisor
 977      *
 978      * @return PHPExcel_Style
 979      */
 980     public function getCellXfSupervisor()
 981     {
 982         return $this->_cellXfSupervisor;
 983     }
 984 
 985     /**
 986      * Get the workbook collection of cellStyleXfs
 987      *
 988      * @return PHPExcel_Style[]
 989      */
 990     public function getCellStyleXfCollection()
 991     {
 992         return $this->_cellStyleXfCollection;
 993     }
 994 
 995     /**
 996      * Get cellStyleXf by index
 997      *
 998      * @param  int $pIndex
 999      * @return PHPExcel_Style
1000      */
1001     public function getCellStyleXfByIndex($pIndex = 0)
1002     {
1003         return $this->_cellStyleXfCollection[$pIndex];
1004     }
1005 
1006     /**
1007      * Get cellStyleXf by hash code
1008      *
1009      * @param  string $pValue
1010      * @return PHPExcel_Style|false
1011      */
1012     public function getCellStyleXfByHashCode($pValue = '')
1013     {
1014         foreach ($this->_cellXfStyleCollection as $cellStyleXf) {
1015             if ($cellStyleXf->getHashCode() == $pValue) {
1016                 return $cellStyleXf;
1017             }
1018         }
1019         return false;
1020     }
1021 
1022     /**
1023      * Add a cellStyleXf to the workbook
1024      *
1025      * @param PHPExcel_Style $pStyle
1026      */
1027     public function addCellStyleXf(PHPExcel_Style $pStyle)
1028     {
1029         $this->_cellStyleXfCollection[] = $pStyle;
1030         $pStyle->setIndex(count($this->_cellStyleXfCollection) - 1);
1031     }
1032 
1033     /**
1034      * Remove cellStyleXf by index
1035      *
1036      * @param int $pIndex
1037      * @throws PHPExcel_Exception
1038      */
1039     public function removeCellStyleXfByIndex($pIndex = 0)
1040     {
1041         if ($pIndex > count($this->_cellStyleXfCollection) - 1) {
1042             throw new PHPExcel_Exception("CellStyleXf index is out of bounds.");
1043         } else {
1044             array_splice($this->_cellStyleXfCollection, $pIndex, 1);
1045         }
1046     }
1047 
1048     /**
1049      * Eliminate all unneeded cellXf and afterwards update the xfIndex for all cells
1050      * and columns in the workbook
1051      */
1052     public function garbageCollect()
1053     {
1054         // how many references are there to each cellXf ?
1055         $countReferencesCellXf = array();
1056         foreach ($this->_cellXfCollection as $index => $cellXf) {
1057             $countReferencesCellXf[$index] = 0;
1058         }
1059 
1060         foreach ($this->getWorksheetIterator() as $sheet) {
1061 
1062             // from cells
1063             foreach ($sheet->getCellCollection(false) as $cellID) {
1064                 $cell = $sheet->getCell($cellID);
1065                 ++$countReferencesCellXf[$cell->getXfIndex()];
1066             }
1067 
1068             // from row dimensions
1069             foreach ($sheet->getRowDimensions() as $rowDimension) {
1070                 if ($rowDimension->getXfIndex() !== null) {
1071                     ++$countReferencesCellXf[$rowDimension->getXfIndex()];
1072                 }
1073             }
1074 
1075             // from column dimensions
1076             foreach ($sheet->getColumnDimensions() as $columnDimension) {
1077                 ++$countReferencesCellXf[$columnDimension->getXfIndex()];
1078             }
1079         }
1080 
1081         // remove cellXfs without references and create mapping so we can update xfIndex
1082         // for all cells and columns
1083         $countNeededCellXfs = 0;
1084         foreach ($this->_cellXfCollection as $index => $cellXf) {
1085             if ($countReferencesCellXf[$index] > 0 || $index == 0) { // we must never remove the first cellXf
1086                 ++$countNeededCellXfs;
1087             } else {
1088                 unset($this->_cellXfCollection[$index]);
1089             }
1090             $map[$index] = $countNeededCellXfs - 1;
1091         }
1092         $this->_cellXfCollection = array_values($this->_cellXfCollection);
1093 
1094         // update the index for all cellXfs
1095         foreach ($this->_cellXfCollection as $i => $cellXf) {
1096             $cellXf->setIndex($i);
1097         }
1098 
1099         // make sure there is always at least one cellXf (there should be)
1100         if (empty($this->_cellXfCollection)) {
1101             $this->_cellXfCollection[] = new PHPExcel_Style();
1102         }
1103 
1104         // update the xfIndex for all cells, row dimensions, column dimensions
1105         foreach ($this->getWorksheetIterator() as $sheet) {
1106 
1107             // for all cells
1108             foreach ($sheet->getCellCollection(false) as $cellID) {
1109                 $cell = $sheet->getCell($cellID);
1110                 $cell->setXfIndex( $map[$cell->getXfIndex()] );
1111             }
1112 
1113             // for all row dimensions
1114             foreach ($sheet->getRowDimensions() as $rowDimension) {
1115                 if ($rowDimension->getXfIndex() !== null) {
1116                     $rowDimension->setXfIndex( $map[$rowDimension->getXfIndex()] );
1117                 }
1118             }
1119 
1120             // for all column dimensions
1121             foreach ($sheet->getColumnDimensions() as $columnDimension) {
1122                 $columnDimension->setXfIndex( $map[$columnDimension->getXfIndex()] );
1123             }
1124 
1125             // also do garbage collection for all the sheets
1126             $sheet->garbageCollect();
1127         }
1128     }
1129 
1130     /**
1131      * Return the unique ID value assigned to this spreadsheet workbook
1132      *
1133      * @return string
1134      */
1135     public function getID() {
1136         return $this->_uniqueID;
1137     }
1138 
1139 }

View Code