在使用 方法获取列的个数的时候，居然 有 【16385 】列数据；即：XFD 列；
这就显然不对了，然而实际上Excel中只有【 71 】列；
以上问题，则会造成下面现象：
- 1.解析8K左右的Excel文件，居然报内存溢出错误（实际上已经分配了1G的内存 ini_set("memory_limit", "2000M");）
- 2.进行内存分析，读取第一行数据，虽然内存仅仅从2.53MB 上升到 63.68MB，但是居然取出来 16385 列数据（71列之后的的值是空）
- 3.造成内存溢出的原因很显然是获取的列出太大，造成动态获取Excel数组太大了；
- 4.经过改造后内存获取整个文件的数据后，对应的内存变化为：从2.53MB 上升到 63.80MB
对应关于Excel处理的实现，已经发布到github上，地址：
https://github.com/jasonyqwang/Utils.git
原获取的方式
$this->PHPReader = \PHPExcel_IOFactory::load($filePath);
$sheet = $this->PHPReader->getActiveSheet();
$this->highestRow = $sheet->getHighestRow();
$this->highestColumm = $sheet->getHighestColumn();
改造后获取方式
$this->PHPReader = \PHPExcel_IOFactory::load($filePath);
$sheet = $this->PHPReader->getActiveSheet();
$this->highestRow = $sheet->getHighestDataRow();
$this->highestColumm = $sheet->getHighestDataColumn();
原来方式，打印的结果
改造后，打印的结果
总结
代码主要是修改
getHighestRow（获取最大行）改为 getHighestDataRow
getHighestColumn（获取最大列）改为 getHighestDataColumn
详细代码
<?php
/**
* User: Jason Wang
* Excel Data
*/
namespace Jsyqw\Utils\Excels;
use Jsyqw\Utils\Exceptions\UtileExcelException;
use Jsyqw\Utils\FileHelper;
class ExcelData
{
/**
* @var ExcelHeader
*/
public $excelHeader;
/**
* @var \PHPExcel
*/
private $PHPReader = null;
/**
* @var \PHPExcel_Worksheet
*/
private $sheet = null;
/**
* @var int
*/
protected $highestRow;
/**
* @var A、B ...
*/
protected $highestColumm;
/**
* @var int
*/
protected $maxRow = 0;
/**
* @var int
*/
protected $maxCol = 0;
/**
* @var file path
*/
protected $filePath;
/**
* Is return excel index num
* @var bool
*/
public $isReturnExcelIndex = true;
/**
* @var string
*/
public $excelRowIndexCulumn = 'row_index';
/**
* Should formulas be calculated
* 是否应计算公式
* @var $calculateFormulas boolean default true
*/
public $calculateFormulas = true;
/**
* Should formatting be applied to cell values
* 是否应将格式应用于单元格值
* @var $formatData boolean default true
*/
public $formatData = true;
/**
* set excel file path
* @param $filePath
* @throws UtileExcelException
*/
private function setFilePath($filePath){
if(!file_exists($filePath)){
throw new UtileExcelException("{$filePath} not exist!");
}
$ext = strtolower(FileHelper::getExt($filePath));
if(!in_array($ext, ['xls', 'xlsx'])){
throw new UtileExcelException("the file ext '{$ext}' error!");
}
$this->filePath = $filePath;
}
/**
* set excel header
* @param $excelHeader ExcelHeader
*/
public function setExcelHeader($excelHeader){
$headerData = $this->getRowData($excelHeader->headerIndex);
if(!$headerData){
throw new UtileExcelException("The Excel header data is empty, index is {$excelHeader->headerIndex}");
}
$excelHeader->initExcelCellMapIndex($headerData);
$this->excelHeader = $excelHeader;
}
/**
* 加载文件
* @param $filePath
* @throws UtileExcelException
*/
public function loadFile($filePath){
$this->setFilePath($filePath);
$this->PHPReader = \PHPExcel_IOFactory::load($filePath);
$sheet = $this->PHPReader->getActiveSheet();
//init hight row and column
// $this->highestRow = $sheet->getHighestRow();
// $this->highestColumm = $sheet->getHighestColumn();
$this->highestRow = $sheet->getHighestDataRow();
$this->highestColumm = $sheet->getHighestDataColumn();
//init max row and max column
$pRange = 'A1:'.$this->highestColumm.$this->highestRow;
list($rangeStart, $rangeEnd) = \PHPExcel_Cell::rangeBoundaries($pRange);
$maxCol = $rangeEnd[0];
$maxRow = $rangeEnd[1];
$this->maxCol = $maxCol;
$this->maxRow = $maxRow;
//init sheet
$this->sheet = $sheet;
}
/**
* Get data
* @param int $startRow
* @return array
*/
public function getData($startRow = 2, $maxRow = null){
if(!$maxRow){
$maxRow = $this->maxRow;
}
$data = [];
for ($rowIndex = $startRow; $rowIndex <= $maxRow; $rowIndex++){
$data[] = $this->getRowData($rowIndex);
}
return $data;
}
/**
* 获取行数据
* @param $row start 0
* @return array
* @throws \PHPExcel_Exception
*/
public function getRowData($row, $isTrim = true){
$data = [];
for ($column = 0; $column <= $this->maxCol; $column++) {
$cell = $this->sheet->getCellByColumnAndRow($column, $row);
$columnLetter = \PHPExcel_Cell::stringFromColumnIndex($column);
$value = null;
if ($cell->getValue() !== null) {
if ($cell->getValue() instanceof \PHPExcel_RichText) {
$value = $cell->getValue()->getPlainText();
} else {
if ($this->calculateFormulas) {
$value = $cell->getCalculatedValue();
} else {
$value = $cell->getValue();
}
}
if ($this->formatData) {
//this is important for show date
$style = $this->PHPReader->getCellXfByIndex($cell->getXfIndex());
$value = \PHPExcel_Style_NumberFormat::toFormattedString(
$value,
($style && $style->getNumberFormat()) ? $style->getNumberFormat()->getFormatCode() : \PHPExcel_Style_NumberFormat::FORMAT_GENERAL
);
}
}
$data[$columnLetter] = $value;
}
if($this->isReturnExcelIndex){
$data[$this->excelRowIndexCulumn] = $row;
}
//is return excel header
if($this->excelHeader){
$this->excelHeader->rowToMapIndex($data);
}
return $data;
}
}
