excel/lib/Import.php

<?php
namespace Excel\Lib;
use Dever;
Dever::apply('autoload', 'excel', 'vendor');
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class Import extends Core
{
    /**
     * 流式读取 Excel，每次返回一行
     * @param string $file Excel 文件路径
     * @param int $sheetIndex sheet 下标（默认 0）
     * @param int $offset 跳过前几行
     * @return \Generator
     */
    public function act($file = '', $sheetIndex = 0, $offset = 0, $head = [])
    {
        $file = mb_convert_encoding($file, "UTF-8", "gbk");
        if (empty($file) || !file_exists($file)) {
            Dever::error('file not exists!');
        }

        try {
            $type = IOFactory::identify($file);
            $reader = IOFactory::createReader($type);
            //$reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($file);
        } catch (\Exception $e) {
            Dever::error('加载文件发生错误："'.$file.'": ' . $e->getMessage());
        }

        $sheet = $spreadsheet->getSheet($sheetIndex);
        $highestRow = $sheet->getHighestRow();
        $highestCol = $sheet->getHighestColumn();
        $columnCnt = array_search($highestCol, $this->cell) ?: 0;

        $draws = $sheet->getDrawingCollection();
        $pictures = [];
        foreach ($draws as $img) {
            list($col, $row) = Coordinate::coordinateFromString($img->getCoordinates());
            $pictures[$row][$col][] = Dever::load('save', 'upload')->init(1)->act($img->getPath())['url'];
        }
        for ($row = $offset + 1; $row <= $highestRow; $row++) {
            $rowData = [];
            for ($col = 0; $col <= $columnCnt; $col++) {
                $key = $this->cell[$col];
                if (isset($pictures[$row][$key])) {
                    $cellValue = $pictures[$row][$key];
                } else {
                    $cellId = $key . $row;
                    $cellValue = $sheet->getCell($cellId)->getCalculatedValue();
                    if ($cellValue instanceof RichText) {
                        $cellValue = $cellValue->__toString();
                    }
                }
                if ($head && $key = Dever::issets($head, $col)) {
                    if (is_array($key)) {
                        $rowData[$key[0]][$key[1]] = $cellValue;
                    } else {
                        $rowData[$key] = $cellValue;
                    }
                } else {
                    $rowData[$key] = $cellValue;
                }
            }
            yield $rowData;
        }
    }
}

/*
<?php namespace Excel\Lib;
use Dever;
Dever::apply('autoload', 'excel', 'vendor');
class Import extends Core
{
    public function act($file = '', $sheet = 0, $offset = 0)
    {
        $file = mb_convert_encoding($file, "UTF-8", "gbk");
        if(empty($file) OR !file_exists($file)) {
            Dever::error('file not exists!');
        }
        try {
            $type = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file);
            $read = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($type);
            //$read->setReadDataOnly(true);
            $excel = $read->load($file);
        } catch (\Exception $e) {
            Dever::error('加载文件发生错误："'.$file.'": '. $e->getMessage());
        }
        $sheet = $excel->getSheet($sheet);
        $columnH = $sheet->getHighestColumn();
        $columnCnt = array_search($columnH, $this->cell);
        if (!$columnCnt) {
            $columnCnt = $offset - 1;
        }
        if ($columnCnt < 0) {
            $columnCnt = 0;
        }
        $rowCnt = $sheet->getHighestRow();
      
        $data = array();
        for ($_row = 1; $_row <= $rowCnt; $_row++) {
            for ($_column = 0; $_column <= $columnCnt; $_column++) {
                $cellId = $this->cell[$_column].$_row;
                //$cellValue = $sheet->getCell($cellId)->getValue();
                $cellValue = $sheet->getCell($cellId)->getCalculatedValue();
                if ($cellValue instanceof \PhpOffice\PhpSpreadsheet\RichText\RichText) {
                    $cellValue = $cellValue->__toString();
                }
                $data[$_row][$this->cell[$_column]] = $cellValue;
            }
        }
        $draws = $sheet->getDrawingCollection();
        //处理图片
        foreach ($draws as $img) {
            list($startColumn, $startRow) = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($img->getCoordinates());//获取图片所在行和列
            $pic = Dever::load('save', 'upload')->act(1, $img->getPath());
            //插入代码
            //$startColumn = $this->decimal($startColumn);//由于图片所在位置的列号为字母，转化为数字
            $data[$startRow][$startColumn] = $pic['url'];
        }
        return $data;  
    }
}
*/