PHP excel读取excel文件转换为数组

备注：先去下载PHP EXCEL——http://download-codeplex.sec.s-msft.com/Download/Release?ProjectName=phpexcel&DownloadId=809023&FileTime=130382506270130000&Build=209

/*

* 将excel转换为数组 by aibhsc

* */

require (ROOT_PATH . 'includes/PHPExcel.php' ); //引入PHP EXCEL类

function format_excel2array( $filePath = '' , $sheet =0){

         if ( empty ( $filePath ) or ! file_exists ( $filePath )){ die ( 'file not exists' );}

         $PHPReader = new PHPExcel_Reader_Excel2007();        //建立reader对象

         if (! $PHPReader ->canRead( $filePath )){

                 $PHPReader = new PHPExcel_Reader_Excel5();

                 if (! $PHPReader ->canRead( $filePath )){

                         echo 'no Excel' ;

                         return ;

                 }

         }

         $PHPExcel = $PHPReader ->load( $filePath );        //建立excel对象

         $currentSheet = $PHPExcel ->getSheet( $sheet );        //**读取excel文件中的指定工作表*/

         $allColumn = $currentSheet ->getHighestColumn();        //**取得最大的列号*/

         $allRow = $currentSheet ->getHighestRow();        //**取得一共有多少行*/

         $data = array ();

         for ( $rowIndex =1; $rowIndex <= $allRow ; $rowIndex ++){        //循环读取每个单元格的内容。注意行从1开始，列从A开始

                 for ( $colIndex = 'A' ; $colIndex <= $allColumn ; $colIndex ++){

                         $addr = $colIndex . $rowIndex ;

                         $cell = $currentSheet ->getCell( $addr )->getValue();

                         if ( $cell instanceof PHPExcel_RichText){ //富文本转换字符串

                                 $cell = $cell ->__toString();

                         }

                         $data [ $rowIndex ][ $colIndex ] = $cell ;

                 }

         }

         return $data ;

}

 

 

使用方法：

                         $filePath = ROOT_PATH. 'data/diamondStock.xlsx' ;        //钻石库存文件

                         $data = format_excel2array( $filePath );

                         print_r( $data ); die ;

 

输出结果示例：

 

Array

(

     [1] => Array

         (

             [A] => 商品编号

             [B] => 商品名称

             [C] => 总重量

             [D] => 进货价格

             [E] => 销售价格

             [F] => 4C备注

         )

 

     [2] => Array

         (

             [A] => 10001

             [B] => GIA-2156685995

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [3] => Array

         (

             [A] => 10002

             [B] => GIA-2156685996

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [4] => Array

         (

             [A] => 10003

             [B] => GIA-2156685997

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [5] => Array

         (

             [A] => 10004

             [B] => GIA-2156685998

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [6] => Array

         (

             [A] => 10005

             [B] => GIA-2156685999

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [7] => Array

         (

             [A] => 10006

             [B] => GIA-2156686000

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [8] => Array

         (

             [A] => 10007

             [B] => GIA-2156686001

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [9] => Array

         (

             [A] => 10008

             [B] => GIA-2156686002

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [10] => Array

         (

             [A] => 10009

             [B] => GIA-2156686003

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [11] => Array

         (

             [A] => 10010

             [B] => GIA-2156686004

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [12] => Array

         (

             [A] => 10011

             [B] => GIA-2156686005

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [13] => Array

         (

             [A] => 10012

             [B] => GIA-2156686006

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

     [14] => Array

         (

             [A] => 10013

             [B] => GIA-2156686007

             [C] => 0.7

             [D] => 1760

             [E] => 1848

             [F] => G色、0.7ct、SI1、FR

         )

 

)