PHP之路——PHPExcel使用

 

<?php
	$dir = dirname(__FILE__);
	require $dir.'/PHPExcel/PHPExcel.php';
	$excelObject = new PHPExcel();
	//获得当前活动页
	$objSheet = $excelObject->getActiveSheet();
	//设置当前活动页的名称
	$objSheet->setTitle('demo');
	$data = [
		[],					//第一行为空
		['','姓名','年龄'],	//第一列为空
		['','张三','17'],
		['','李四','18'],
	];
	//从数组中读取数据
	$objSheet->fromArray($data);
	$objWrite = PHPExcel_IOFactory::createWriter($excelObject,'Excel2007');
	$objWrite->save($dir.'/demo.xls');

 

使用PHPExcel版本为1.8.0

如果PHP版本为7及以上 

　　出现错误：Fatal error: 'break' not in the 'loop' or 'switch' context in <mypath>\PHPExcel\PHPExcel\Calculation\Functions.php on line 581

　　打开PHPExcel\Calculation\Functions.php文件，删除掉581行的break即可

PHP7以下版本没事

 

读取Excel：

require_once app_path()."/../libs/PHPExcel/PHPExcel/IOFactory.php";
$reader = \PHPExcel_IOFactory::createReader('Excel5');  //设置excel版本
$PHPExcel = $reader->load("d:/1.xls");  //加载excel文件
$sheet = $PHPExcel->getSheet(0);    //设置sheet
$highestRow = $sheet->getHighestRow();  //获取总行数
$highestColumm = $sheet->getHighestColumn();    //获取总列数
$highestColumm= \PHPExcel_Cell::columnIndexFromString($highestColumm);  //字母列转换为数字列 如:AA变为27
var_dump($highestColumm);
$data = [];
for ($row = 1; $row <= $highestRow; $row++){
    for ($column = 0; $column < $highestColumm; $column++) {
        if ($column == 1) {
            $data[$row-1] = $sheet->getCellByColumnAndRow($column, $row)->getValue();
        }
    }
}

　　

//获取单元格的值
$sheet->getCellByColumnAndRow($column, $row)->getValue();
//数字转为字母  0->A 26->AA
\PHPExcel_Cell::stringFromColumnIndex(0)
//字母转为数字
\PHPExcel_Cell::columnIndexFromString($highestColumm);