您的位置:澳门402永利com > 计算机 网络 > 如何写入

如何写入

发布时间:2019-09-23 20:45编辑:计算机 网络浏览(65)

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

     

    具体操作步骤如下:

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

    图片 1图片 2

     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

     

    (一)导入Excel

    用PHPExcel读取excel并导入数据库

    图片 3图片 4

     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

     

    第一,在前台html页面进行上传文件:如:

    以下内容是转载的:

    很多文章都有提到关于使用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包附在最后。

     

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

    phpexccel类:

    图片 5图片 6

       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

     

     

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

     if (! empty ( $_FILES ['file_stu'] ['name'] )) 
     {
      $tmp_file = $_FILES ['file_stu'] ['tmp_name'];
      $file_types = explode ( ".", $_FILES ['file_stu'] ['name'] );
      $file_type = $file_types [count ( $file_types ) - 1];
       /*判别是不是.xls文件,判别是不是excel文件*/
       if (strtolower ( $file_type ) != "xls")       
      {
         $this->error ( '不是Excel文件,重新上传' );
       }
      /*设置上传路径*/
       $savePath = SITE_PATH . '/public/upfile/Excel/';
      /*以时间来命名上传的文件*/
       $str = date ( 'Ymdhis' ); 
       $file_name = $str . "." . $file_type;
       /*是否上传成功*/
       if (! copy ( $tmp_file, $savePath . $file_name )) 
       {
         $this->error ( '上传失败' );
       }
      /*
        *对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中
    
       注意:这里调用执行了第三步类里面的read函数,把Excel转化为数组并返回给$res,再进行数据库写入
      */
     $res = Service ( 'ExcelToArray' )->read ( $savePath . $file_name );
      /*
        重要代码 解决Thinkphp M、D方法不能调用的问题  
        如果在thinkphp中遇到M 、D方法失效时就加入下面一句代码
      */
      //spl_autoload_register ( array ('Think', 'autoload' ) );
      /*对生成的数组进行数据库的写入*/
      foreach ( $res as $k => $v ) 
      {
        if ($k != 0) 
       {
          $data ['uid'] = $v [0];
          $data ['password'] = sha1 ( '111111' );
          $data ['email'] = $v [1];
          $data ['uname'] = $v [3];
         $data ['institute'] = $v [4];
         $result = M ( 'user' )->add ( $data );
         if (! $result) 
         {
           $this->error ( '导入数据库失败' );
         }
       }
      }
    }
    

    第三:ExcelToArrary类,用来引用phpExcel并处理Excel数据的
    此处注意:ExcelToArrary类建在根目录下的 addons /services/ExcelToArrary.class.php中

    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本站下载地址

    本文由澳门402永利com发布于计算机 网络,转载请注明出处:如何写入

    关键词:

上一篇:Script文件加载相比较

下一篇:没有了