huahuibao/console/controllers/ItemController.php

<?php

namespace console\controllers;

use biz\item\classes\ItemClass;
use biz\item\classes\ItemClassClass;
use biz\item\classes\UnitClass;
use yii\console\Controller;
use Yii;

/**
 * 花材初始化 shish 2021.2.18
 */
class ItemController extends Controller
{

    //花材初始化 ./yii item/init
    public function actionInit()
    {
        $phpExcelFile = Yii::getAlias("@vendor/phpoffice/phpexcel/");
        require_once($phpExcelFile . 'Classes/PHPExcel/IOFactory.php');//查询接口
        $dir = Yii::$app->basePath;
        $filename = $dir . '/goods.xls';

        $fileType = \PHPExcel_IOFactory::identify($filename); //文件名自动判断文件类型

        $excelReader = \PHPExcel_IOFactory::createReader($fileType);

        $objPHPExcel = $excelReader->load($filename);//$file_url即Excel文件的路径

        $sheet = $objPHPExcel->getSheet(0);//获取第一个工作表

        $highestRow = $sheet->getHighestRow();//取得总行数

        $highestColumn = $sheet->getHighestColumn(); //取得总列数

        $changeData = [];
        for ($j = 2; $j <= $highestRow; $j++) {
            $str = '';
            for ($k = 'A'; $k <= $highestColumn; $k++) {
                $str .= $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue() . '\\';
            }
            //explode:函数把字符串分割为数组。
            $string = explode("\\", $str);
            array_pop($string);
            array_push($changeData, $string);
        }
        if (!empty($changeData)) {

            $classList = ItemClassClass::getAllByCondition([], null, '*', 'name');

            $unitList = UnitClass::getAllByCondition([], null, '*', 'name');

            foreach ($changeData as $key => $item) {
                $name = $item[1];
                $alias = $item[2];
                $className = $item[3];
                $classId = isset($classList[$className]['id']) ? $classList[$className]['id'] : 0;
                $py = strtolower($item[4]);
                $big = $item[5] ?? '';
                $big = $big == '枝' ? '支' : $big;
                $bigId = isset($unitList[$big]['id']) ? $unitList[$big]['id'] : 0;
                $small = $item[6] ?? '';
                $small = $small == '枝' ? '支' : $small;
                $smallId = isset($unitList[$small]['id']) ? $unitList[$small]['id'] : 0;
                $ratio = isset($item[7]) && !empty($item[7]) ? $item[7] : 0;
                if(empty($name)){
                    continue;
                }
                $data = [
                    'name'=>$name,
                    'alias'=>$alias,
                    'py'=>$py,
                    'classId'=>$classId,
                    'unitNum'=>$ratio,
                    'bigUnit'=>$bigId,
                    'smallUnit'=>$smallId,
                ];
                print_r($data);
                ItemClass::add($data);
            }
        }
    }

}