laravel+phpoffice+easyexcel实现导入

QiZong__BK

已于 2024-07-02 10:50:22 修改

阅读量243

点赞数 1

文章标签： laravel php phpoffice

于 2024-07-01 14:47:13 首次发布

本文链接：https://blog.csdn.net/QiZong__BK/article/details/140100724

版权

资源包下载地址

https://download.csdn.net/download/QiZong__BK/89503486

easy-excel下载：

"dcat/easy-excel": "^1.0",

命令行：

composer require dcat/easy-excel

前端代码

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
    <!-- jQuery文件。务必在bootstrap.min.js 之前引入 -->
    <script src="https://cdn.staticfile.net/jquery/3.2.1/jquery.min.js"></script>
    <!-- 新 Bootstrap4 核心 CSS 文件 -->
    <link rel="stylesheet" href="https://cdn.staticfile.net/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
    <!-- bootstrap.bundle.min.js 用于弹窗、提示、下拉菜单，包含了 popper.min.js -->
    <script src="https://cdn.staticfile.net/popper.js/1.15.0/umd/popper.min.js"></script>

    <!-- 最新的 Bootstrap4 核心 JavaScript 文件 -->
    <script src="https://cdn.staticfile.net/twitter-bootstrap/4.3.1/js/bootstrap.min.js"></script>

</head>
<body>


<form action="excel_import" method="post" enctype="multipart/form-data">
    @csrf
    <div class="form-group">
        <label for="exampleFormControlFile1">Example file input</label>
        <input type="file" class="form-control-file" id="exampleFormControlFile1" name="file">
    </div>
    <button type="submit" class="btn btn-primary">Primary</button>
</form>

</body>
</html>

后端代码实现

    public function excel_import()
    {
        $file_name = request()->file('file');
        $filename = Str::random(20) . '.' . $file_name->getClientOriginalExtension();
        $path_name = $file_name->storeAs('/public/uploads/', $filename, 'public');
//        dd($path);
        $file = public_path('storage/'.$path_name);
        $path = $this->real_file_path($path_name);

        try {
            $import_log = new ImportLog();
            $import_log->item = get_class(new TaskBomModel());
            $import_log->operator = Admin::user()->id;
            $import_log->save();
            $type_map = TaskBomModel::TYPE_MAP;
            $reader = new Xlsx();
            $spreadsheet = $reader->load($file);
            //获取c列2行单元格
            $sku_sn = $spreadsheet->getSheet(0)->getCell('C2')->getValue();
            //获取C列3行单元格的内容
            $bom_type = $spreadsheet->getSheet(0)->getCell('C3')->getValue();

            $type = array_search($bom_type, $type_map);
            //成品表数据
            $bom = GoodsSkuModel::query()->where('sku_sn', $sku_sn)->first();
            if (empty($bom)) {
                throw new Exception('关联成品编码' . $sku_sn . '不存在，请确认编码是否真实存在或前往产品界面添加');
            }
            $task_bom = TaskBomModel::where('sku_id', $bom->id)->get()->toArray();
            if (!empty($task_bom)) {
                throw new Exception('该BOM已存在');
            }
            if (!empty($bom)) {
                $task_bom = new TaskBomModel();
                $task_bom->bom_name = $bom->goods_name ?? '';
                $task_bom->bom_code = $bom->sku_sn ?? '';
                $task_bom->type = $type;
                $task_bom->bom_sku_name = $bom->sku_name ?? '';
                $task_bom->product_id = $bom->goods_id ?? '';
                $task_bom->sku_id = $bom->id ?? '';
                if ($task_bom->save()){
                    $bom_success= true;
                }
            }

            if ($bom_success) {
                $row_map = Excel::import($path)->headingRow(10)->first()->toArray();

                foreach ($row_map as $map) {
                    $complete_bom_sku_sn = trim($map['完整BOM存货编码']);
                    $complete_bom_num = trim($map['完整BOM消耗数量']);
                    $easy_bom_sku_sn = trim($map['简易BOM存货编码']);
                    $easy_bom_num = trim($map['简易BOM消耗数量']);
                    //判断完整BOM有值更新完整BOM字段
                    if ($complete_bom_sku_sn) {
                        //配料表数据
                        $complete_bom_map = GzGoodsSkuModel::query()->where('sku_sn', $complete_bom_sku_sn)->first();
                        if ($complete_bom_map == null) {
                            $fail++;//记录错误编码
                            ImportLogDetail::query()->create([
                                'log_id' => $import_log->id,
                                'log' => '物料编码' . $complete_bom_sku_sn . '不存在'
                            ]);
                        } else {
                            $complete_data[] = [
                                'sku_id' => $complete_bom_map->id,
                                'num' => $complete_bom_num,
                                'type' => 1,
                                'remark' => null
                            ];
//                            dd($complete_data);
                            TaskBomModel::where('id', $task_bom->id)->update([
                                'full_map' => $complete_data //完整BOM
                            ]);
                            ImportLogDetail::query()->create([
                                'log_id' => $import_log->id,
                                'status' => 1,
                                'log' => '物料编码' . $complete_bom_sku_sn . '导入成功'
                            ]);
                            $success++;
                        }
                    }
                    //判断简易BOM有值更新简易BOM字段
                    if ($easy_bom_sku_sn) {
                        //配料表数据
                        $easy_bom_map = GzGoodsSkuModel::query()->where('sku_sn', $easy_bom_sku_sn)->first();
                        if ($easy_bom_map == null) {
                            $fail++;//记录错误编码
                            ImportLogDetail::query()->create([
                                'log_id' => $import_log->id,
                                'log' => '物料编码' . $easy_bom_sku_sn . '不存在'
                            ]);
                        } else {
                            $easy_data[] = [
                                'sku_id' => $easy_bom_map->id,
                                'num' => $easy_bom_num,
                                'type' => 1,
                                'remark' => null
                            ];
                            TaskBomModel::where('id', $task_bom->id)->update([
                                'basic_map' => $easy_data //简易BOM
                            ]);
                            ImportLogDetail::query()->create([
                                'log_id' => $import_log->id,
                                'status' => 1,
                                'log' => '物料编码' . $easy_bom_sku_sn . '导入成功'
                            ]);
                            $success++;
                        }
                    }
                }

            }
            $import_log->succeed = $success;
            $import_log->failed = $fail;
            $import_log->save();
            $url = admin_url('import_logs');
            $content = <<<EOF
库存导入成功:{$success}，导入失败：{$fail}
<br>请在菜单<a target="_blank" href="{$url}" >【导入日志】</a>中查看记录
EOF;
            return  $this->response()
                ->success($content)
                ->refresh();
        } catch (Exception $e) {
            return $this->response()->error($e->getMessage());
        }


    }

    function real_file_path($cover = ""): string
    {
        if ($cover) {
            if (Str::contains($cover, '//')) {
                return $cover;
            }
            return Storage::disk('public')->path($cover);
        } else {
            return '';
        }
    }