xyzshop/helper/bank/execl_loader.php

<?php

namespace bank;

require_once(BASE_ROOT_PATH . '/vendor/autoload.php');

use PhpOffice;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder;
use Log;

function file_generator($path)
{
    if (!file_exists($path)) {
        return;
    }

    $fd = opendir($path);
    if ($fd)
    {
        while (($item = @readdir($fd)) !== false)
        {
            $sub = $path . DIRECTORY_SEPARATOR . $item;
            if (is_dir($sub) && $item != '.' && $item != '..')
            {
                $files = file_generator($sub);
                foreach ($files as $file) {
                    yield $file;
                }
            }
            elseif (is_file($sub)) {
                yield $sub;
            }
        }
    }
    closedir($fd);
}


class CustomValueBinder extends AdvancedValueBinder
{
    public static function dataTypeForValue($value): string
    { //只重写dataTypeForValue方法，去掉一些不必要的判断
        if (is_null($value)) {
            return DataType::TYPE_NULL;
        } elseif ($value instanceof PhpOffice\PhpSpreadsheet\RichText\RichText) {
            return DataType::TYPE_INLINE;
        } elseif (is_string($value) && $value[0] === '=' && strlen($value) > 1) {
            return DataType::TYPE_FORMULA;
        } elseif (is_bool($value)) {
            return DataType::TYPE_BOOL;
        } elseif (is_float($value) || is_int($value)) {
            return DataType::TYPE_NUMERIC;
        }
        return DataType::TYPE_STRING;
    }
}


class execl_loader
{
    public function load_dirs($dirs)
    {
        foreach ($dirs as $dir) {
            $this->load_dir($dir);
        }
    }

    public function load_dir($dir)
    {
        $files = file_generator($dir);
        foreach ($files as $file) {
            Log::record("$file",Log::WARING);
            $this->load_file($file);
        }
    }

    public function load_file($file)
    {
        $line_reader = function ($file)
        {
            $ext = pathinfo($file,PATHINFO_EXTENSION);
            if(!in_array($ext,['xls','xlsx'])) {
                return;
            }

            $reader = PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($file);
            $reader->setReadDataOnly(true);
            $spreadsheet = PhpOffice\PhpSpreadsheet\IOFactory::load($file);

            yield 'start' => 1;

            foreach ($spreadsheet->getWorksheetIterator() as $worksheet)
            {
                foreach ($worksheet->getRowIterator() as $row)
                {
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set

                    $item = [];
                    foreach ($cellIterator as $cell)
                    {
                        if ($cell !== null) {
                            $item[] = trim($cell->getValue());
                        }
                    }

                    yield 'line' => $item;
                }
            }

            yield 'start' => 0;
        };

        $mod_record = Model();
        $inserter = function ($line) use ($mod_record){
            $mod_record->table('bank_record')->insert($line);
        };

        $lines = $line_reader($file);
        $convert = new converter();
        foreach ($lines as $key => $val)
        {
            if($key === 'start') {
                $convert->start();
            }
            elseif($key === 'line')
            {
                $line = $convert->convert($val);
                if(!empty($line) && !empty($line['trade_no'])) {
                    $inserter($line);
                }
                else {
                    Log::record(json_encode($val,JSON_UNESCAPED_UNICODE),Log::WARING);
                }
            }
            else {
                $convert->end();
            }
        }
    }
}