phpexcel使用方法示例

jerry PHP 2015年11月23日收藏

官方的PHPExcel源程序实在太大了，大概有17M。里面很多功能是我们不需要的。所以我筛选了一下。把excel常用的功能提取出来。详细见附件。文件大小889k。点击下载PHPExcel精简版如果你想下载完整版，可以到http://phpexcel.codeplex.com/releases/view/45412 下载。

以下是excel功能的基本用法代码示例

/**
 * 下载Excel
 * @since 2012.04.17
 * @version 1.0
 * @copyright http://www.tmper.com
 * @author 干草 
 */
require_once(dirname(__FILE__).'/phpexcel/PHPExcel.php');//加载PHPExcel
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition:attachment; filename=demo.xls");
header('Cache-Control: max-age=0');
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'demo');//把demo写入A1
$objPHPExcel->getActiveSheet()->SetCellValue('A2', 'demo2');//把demo2写入A2
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);//加粗A2
//
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save('php://output');//输出到浏览器
//$objWriter->save(dirname(__FILE__).'/demo.xls');//下载到文件

/**
 * 读取Excel 
 */
$Obj = new PHPExcel_Reader_Excel5();
$Obj->setReadDataOnly(true);
$phpExcel = $Obj->load(dirname(__FILE__).'/demo.xls'); //读取demo.xls
$objWorksheet = $phpExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$data = array();
for($row = 1; $row < = $highestRow; ++$row) {
    for($col = 0; $col <= $highestColumnIndex; ++$col) {
        $val = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
        $data[$row][$col] = trim($val);
    }
}
print_r($data);//print出数据.

使用PHPExcel会占用很大的内存, 有的空间可能会用不起. 之前就试过几次. 程序执行之后页面时空白或500错误. 所以被迫优化了一下读取xls的程序. 如下:

/**
 * 经过优化后读取Excel的程序
 * 该程序比上面的读取excel程序占内存更少些.
 */
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;//缓存
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
$PHPReader = PHPExcel_IOFactory::createReader("Excel5");
$PHPReader->setReadDataOnly(true);
$PHPExcel_ReadFilter =  new PHPExcel_ReadFilter(); //自定义的类, 用于过滤读取xls的最大行. 该类是本人自添加到PHPExcel.php中的.
$PHPExcel_ReadFilter->maxRow = 300;//设置只读取xls的前300行
$PHPReader->setReadFilter( $PHPExcel_ReadFilter );
$PHPexcel = $PHPReader->load(dirname(__FILE__).'/demo.xls');
unset($PHPReader);
//$PHPReader = new PHPExcel_Reader_Excel5();
//$PHPexcel = $PHPReader->load($fn);
$workSheet = $PHPexcel->getActiveSheet();
//
$rt = array();
$highestRow = $workSheet->getHighestRow();//获取行
$highestCol = $workSheet->getHighestColumn();//获取列
$highestColIndex = PHPExcel_Cell::columnIndexFromString($highestCol);//获取有效列
for($row = 1; $row < = $highestRow; $row++){
    for($col = 0; $col < $highestColIndex; $col++){
        $cell = $workSheet->getCellByColumnAndRow($col,$row);
        $val = $cell->getValue();
        $rt[$row][$col] = trim($val);
    }
}
$PHPexcel->disconnectWorksheets();//清空缓存
unset($PHPReader, $PHPexcel, $workSheet, $highestColIndex);
print_r($rt);

涉及到的代码文件: phpexcel-demo

php