No Comments

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;
                }
            }
        }
    }
}

最后更新于 2021-03-24 17:14:43 并被添加「PHP 处理大量数据 读取」标签，已有 1138 位童鞋阅读过。

相关文章

此处评论已关闭