Laravel-Excel数据导入到数据库使用小结

1、Laravel-Excel组件网址

3.0暂时还不支持Excel-import

• github.com/Maatwebsite…
• laravel-excel.maatwebsite.nl/docs/2.1/ge…

2、安装及配置Laravel-Excel

• 安装

composer require maatwebsite/excel:~2.1.0
复制代码

• 配置

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
复制代码

中文导入需要将其配置文件导出并修改to_ascii = false

'to_ascii' => false,//config/excel.php Line:431; default: true
复制代码

3、生成Excel-import-command

php artisan make:command ExcelImport --command=tqsq2005:excel-import
复制代码

4、修改ExcelImport代码

App\Entities\Backend\Rcyw\Upload模型用到Laravel-medialibrary

<?php

namespace App\Console\Commands;

use App\Entities\Backend\Jsxt\Check;
use App\Entities\Backend\Rcyw\Upload;
use Carbon\Carbon;
use Excel;
use Illuminate\Console\Command;

class ExcelImport extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'tqsq2005:excel-import {uuid}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Import into database from a excel file[Upload::uuid]';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //获取uuid
        $uuid = $this->argument('uuid');
        $start = microtime(true);

        //execute
        $this->append_data($uuid);

        $time = microtime(true) - $start;
        $memory = memory_get_peak_usage(true) / 1024 / 1024;

        $this->output->writeln(sprintf('time: %f memory: %f MB', $time, $memory));
    }

    /**
     * 数据插入
     * @param $uuid
     */
    private function append_data($uuid)
    {
        $upload = Upload::findOrFail($uuid);
        $media  = $upload->getMedia('uploads')->first();
        $path   = $media->getPath();
        $i_insert = 0;
        //只获取sheet1的数据
        Excel::filter('chunk')->selectSheetsByIndex(0)->load($path)->chunk(100, function($results) use ($upload)
        {
            $i_insert = 0;
            $results->each(function ($row, $key) use (&$i_insert) {
                $item                   = $row->toArray();
                $date                   = str_replace('.', '-', $item['检查日期']);
                $check_jcdate           = strtotime($date) ? (new Carbon($date))->format('Y-m-d') : Carbon::now()->format('Y-m-d');
                $data['check_jcdate']   = $check_jcdate;
                $data['check_hyjg']     = $item['检查情况1'];
                $data['check_jyjg']     = $item['检查情况2'];
                $data['check_cbjg']     = $item['检查情况3'];
                $data['check_bunit']    = $item['医院'];
                //TODO: fetch puuid
                $data['puuid']          = 'tmp';
                //TODO: 获取到Puuid的时候请用 updateOrCreate
                Check::create($data);
                $i_insert++;
            });
            $upload = $upload->fresh();
            $upload->import_records = $upload->import_records + $i_insert;
            $upload->save();
        });
        $upload = $upload->fresh();
        $upload->import_result = 1;
        $upload->save();
        $this->info($upload->import_records.' record inserted success!');
    }
}

复制代码

5、Controller中的代码

window环境下Symfony\Component\Process\Process用异步的方法start()不起作用，只能用run()。Linux环境请使用异步的方法start()

use App\Entities\Backend\Rcyw\Upload;
use DB;
use Symfony\Component\Process\Process;
use Validator;
/**
 * 文件上传
 * @param Request $request
 * @return \Illuminate\Http\JsonResponse
 * @throws \Exception
 * @throws \Throwable
 */
public function upload(Request $request)
{
    //设置php页面最大执行时间：默认30s
    ini_set('max_execution_time', 300);
    //返回信息
    $data = [];
    //设置文件后缀白名单
    $allowExt   = ["csv", "xls", "xlsx"];
    //获取文件
    $file = $request->file('file');
    //简单验证必须有文件上传上来
    $validator = Validator::make($request->all(), [
        'file' => 'required'
    ]);
    //验证文件后缀
    $validator->after(function($validator) use ($file, $allowExt) {
        if (!in_array($file->guessClientExtension(), $allowExt)) {
            return $this->fail(400, '不允许的文件类型：请上传Excel文件！');
        }
    });
    if ($validator->fails()) {
        return $this->fail(400, '未发现有效文件：请上传Excel文件！');
    }
    $upload = '';
    DB::transaction(function () use ($file, &$data, &$upload) {
        //存储文件信息到数据库
        $upload = Upload::create([
            'file_name' => $file->getClientOriginalName(),
            'file_ext'  => $file->getClientOriginalExtension(),
            'category'  => 'check',
        ]);
        //存储到Laravel-Medialibrary
        $media  = $upload->addMediaFromRequest('file')
            ->toMediaCollection('uploads');
        $tmp['filename']    = $media->file_name;
        $tmp['url']         = $media->getFullUrl();
        $data               = $tmp;
    });

    //数据导入
    $process = new Process('php '. dirname(app_path()) .'/artisan tqsq2005:excel-import ' . $upload->uuid);
    //运行时间限制：默认60s
    $process->setTimeout(3600);
    //空闲时间限制
    //$process->setIdleTimeout(30);
    $process->run();
    //返回插入的数据数
    $upload             = $upload->fresh();
    $data['inserts']    = $upload->import_records;

    if (is_array($data) && $data['inserts'])
        return $this->success_without_index('成功导入'.$data['inserts'].'条数据！', $data);
    return $this->fail(400, '文件导入失败');
}
复制代码