php excel PhpSpreadsheet 处理大量数据 读取
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class Xlsx
{
public static function load($filePath)
{
$inputFileType = IOFactory::identify($filePath);
$excelReader = IOFactory::createReader($inputFileType);
$PHPExcel = $excelReader->load($filePath);
return $PHPExcel;
}
/**
* 迭代每一行
* @param string $filePath
* @param callable $callback 参数 $rowData 行数据, $rowKey行号2开始
*/
public static function iterator(
$filePath,
callable $callback,
$hasHeader = true,
$sheetIndex = 0,
$onlyExistingCell = true,
$startColumn = null,
$endColumn = null
) {
$excel = static::load($filePath);
$sheet = $excel->getSheet($sheetIndex);
$rows = $sheet->getRowIterator();
$header = [];
foreach ($rows as $rowKey => $row) {
$rowData = [];
$cellIterator = $row->getCellIterator();
// 程序自己判断存在的cell可能不准
if ($onlyExistingCell === true) {
$cellIterator->setIterateOnlyExistingCells($onlyExistingCell);
} else {
$cellIterator->resetStart($startColumn);
$cellIterator->resetEnd($endColumn);
}
foreach ($cellIterator as $colKey => $cell) {
if ($hasHeader) {
if ($rowKey == 1) {
$header[$colKey] = trim($cell->getValue());
} else {
if (!array_key_exists($colKey, $header)) {
continue;
}
$rowData[$header[$colKey]] = $cell->getValue();
}
}
}
if ($rowKey > 1) {
$res = $callback($rowData, $rowKey);
if ($res === false) {
return;
}
}
}
}
}