jucai/app/Http/Controllers/Api/Test/JobController.php

<?php

namespace App\Http\Controllers\Api\Test;

use App\Http\Controllers\Api\ApiBaseController;
use App\Models\Category;
use App\Models\Company;
use App\Models\Jobs;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;

class JobController extends ApiBaseController
{

    public function import(Request $request)
    {
        $spreadsheet = IOFactory::load(public_path() . '/job.xls');
        $sheet       = $spreadsheet->getActiveSheet();
        $rowCount    = $sheet->getHighestRow();
        $record      = [];
        for ($row = 5; $row <= $rowCount; $row++) {
            if (empty($sheet->getCell("D{$row}")->getValue())) {
                continue;
            }
            $record[] = [
                'company_name' => $sheet->getCell("D{$row}")->getValue(),
                'job_name'     => $sheet->getCell("G{$row}")->getValue(),
                'amount'       => $sheet->getCell("G{$row}")->getValue(),
                'max_age'      => $sheet->getCell("K{$row}")->getValue(),
                'sex'          => $sheet->getCell("L{$row}")->getValue(),
                'education'    => $sheet->getCell("O{$row}")->getValue(),
                'xuewei'       => $sheet->getCell("P{$row}")->getValue(),
                'major'        => $sheet->getCell("Q{$row}")->getValue(),
                'jobs_content' => $sheet->getCell("R{$row}")->getValue(),
            ];
        }
        $company_name_list = array_unique(array_column($record, 'company_name'));
        $company_list      = Company::whereIn('companyname', $company_name_list)->get()->keyBy('companyname');

        $education = Category::categoryTypeByDemand('AIX_education');
        foreach ($record as $k => $v) {
            if (empty($company_list[$v['company_name']])) {
                return $v['company_name'] . '还未创建';
            }

            $item                    = [];
            $item['valid']           = 1;
            $item['jobs_name']       = $v['job_name'];
            $item['company_id']      = $company_list[$v['company_name']]['id'];
            $item['company_name']    = $company_list[$v['company_name']]['companyname'];
            $item['company_addtime'] = strtotime($company_list[$v['company_name']]['created_at']);
            $item['company_audit']   = 1;
            $item['amount']          = $v['amount'];
            $item['topclass']        = 225;
            $item['category']        = 226;
            $item['subclass']        = 992;
            $item['trade']           = $company_list[$v['company_name']]['trade'];
            $item['scale']           = $company_list[$v['company_name']]['scale'];
            $item['district']        = $company_list[$v['company_name']]['district'];
            $item['education']       = $education[$v['education']];
            $item['wage']            = -1;
            $item['age']             = '18-' . $v['max_age'];
            $item['jobs_content']    = $v['jobs_content'] . "\n" . "学位要求：" . $v['xuewei'] . "；专业要求：" . $v['major'];
            $item['audit']           = 1;
            $item['setmeal_id']      = 1;
            $item['setmeal_name']    = "免费会员";
            $item['display']         = 1;
            $item['is_health']       = 1;
            $item['health_type']     = 1;
            Jobs::create($item);
        }

        return '完成';
    }
}