php把excel变成数组,PHP excel读取excel文件转换为数组

/*

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

*/

1.[代码][PHP]代码

/*

* 将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

)

)