1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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;
                }
            }
        }
    }
}