excel - Laravel Excel 上传和进度条

Question

我有一个网站，可以在其中上传 .xlsx 文件，其中包含我的数据库的一些信息行。我阅读了 laravel-excel 的文档，但如果您使用控制台方法，它看起来只适用于进度条；我不这么认为。

我目前只使用纯 HTML 上传表单，还没有使用 ajax。

但是要为此创建这个进度条，我需要将其转换为 ajax，这并不麻烦，我可以做到。

但是，在上传文件并遍历 Excel 文件中的每一行时，如何创建进度条？

这是完成上传的 Controller 和方法:

/**
 * Import companies
 *
 * @param Import $request
 * @return \Illuminate\Routing\Redirector|\Illuminate\Http\RedirectResponse
 */
public function postImport(Import $request)
{
    # Import using Import class
    Excel::import(new CompaniesImport, $request->file('file'));

    return redirect(route('dashboard.companies.index.get'))->with('success', 'Import successfull!');
}

这是导入文件:

public function model(array $row)
{
    # Don't create or validate on empty rows
    # Bad workaround
    # TODO: better solution
    if (!array_filter($row)) {
        return null;
    }

    # Create company
    $company = new Company;
    $company->crn = $row['crn'];
    $company->name = $row['name'];
    $company->email = $row['email'];
    $company->phone = $row['phone'];
    $company->website = (!empty($row['website'])) ? Helper::addScheme($row['website']) : '';
    $company->save();

    # Everything empty.. delete address
    if (!empty($row['country']) || !empty($row['state']) || !empty($row['postal']) || !empty($row['address']) || !empty($row['zip'])) {

        # Create address
        $address = new CompanyAddress;
        $address->company_id = $company->id;
        $address->country = $row['country'];
        $address->state = $row['state'];
        $address->postal = $row['postal'];
        $address->address = $row['address'];
        $address->zip = $row['zip'];
        $address->save();

        # Attach
        $company->addresses()->save($address);

    }

    return $company;

}

我知道目前这还不算什么。我只需要一些帮助来弄清楚如何创建这个进度条，因为我陷入了困境。

我的想法是创建一个ajax上传表单，但从那里我不知道。

Answer 1

Just an idea, but you could use the Laravel session to store the total_row_count and processed_row_count during the import execution. Then, you could create a separate AJAX call on a setInterval() to poll those session values (e.g., once per second). This would allow you to calculate your progress as processed_row_count / total_row_count, and output to a visual progress bar. – matticustard

将@matticustard 评论付诸实践。以下只是如何实现的示例，也许还有需要改进的地方。

<强>1。路线
用于初始化 Excel 导入的 import 路由。
import-status 路由将用于获取最新的导入状态

Route::post('import', [ProductController::class, 'import']);
Route::get('import-status', [ProductController::class, 'status']);

<强>2。 Controller
import 操作将验证上传的文件，并将 $id 传递给 ProductsImport 类。由于它将在后台排队并运行，因此无法访问当前 session 。我们将在后台使用缓存。如果要处理更多并发导入，最好生成更随机的 $id，为了保持简单，现在只使用 unix 日期。

You currently cannot queue xls imports. PhpSpreadsheet's Xls reader contains some non-utf8 characters, which makes it impossible to queue.
XLS imports could not be queued

public function import()
{
    request()->validate([
        'file' => ['required', 'mimes:xlsx'],
    ]);

    $id = now()->unix()
    session([ 'import' => $id ]);

    Excel::queueImport(new ProductsImport($id), request()->file('file')->store('temp'));

    return redirect()->back();
}

从缓存中获取最新的导入状态，并从 session 中传递 $id。

public function status()
{
    $id = session('import');

    return response([
        'started' => filled(cache("start_date_$id")),
        'finished' => filled(cache("end_date_$id")),
        'current_row' => (int) cache("current_row_$id"),
        'total_rows' => (int) cache("total_rows_$id"),
    ]);
}

<强>3。导入类
使用WithEvents BeforeImport 我们将 excel 文件的总行数设置到缓存中。使用onRow我们将当前处理的行设置到缓存中。并且 AfterReset 清除所有数据。

<?php

namespace App\Imports;

use App\Models\Product;
use Maatwebsite\Excel\Row;
use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Events\AfterImport;
use Maatwebsite\Excel\Events\BeforeImport;
use Maatwebsite\Excel\Concerns\WithEvents;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class ProductsImport implements OnEachRow, WithEvents, WithChunkReading, ShouldQueue
{
    public $id;

    public function __construct(int $id)
    {
        $this->id = $id;
    }

    public function chunkSize(): int
    {
        return 100;
    }

    public function registerEvents(): array
    {
        return [
            BeforeImport::class => function (BeforeImport $event) {
                $totalRows = $event->getReader()->getTotalRows();

                if (filled($totalRows)) {
                    cache()->forever("total_rows_{$this->id}", array_values($totalRows)[0]);
                    cache()->forever("start_date_{$this->id}", now()->unix());
                }
            },
            AfterImport::class => function (AfterImport $event) {
                cache(["end_date_{$this->id}" => now()], now()->addMinute());
                cache()->forget("total_rows_{$this->id}");
                cache()->forget("start_date_{$this->id}");
                cache()->forget("current_row_{$this->id}");
            },
        ];
    }

    public function onRow(Row $row)
    {
        $rowIndex = $row->getIndex();
        $row      = array_map('trim', $row->toArray());
        cache()->forever("current_row_{$this->id}", $rowIndex);
        // sleep(0.2);

        Product::create([ ... ]);
    }
}

<强>4。前端
在前端方面，这只是如何处理事情的示例。这里我使用了 vuejs、ant-design-vue 和 lodash。

• 上传文件后调用handleChange方法
• 上传成功后，首次调用 trackProgress 方法
• trackProgress 方法是递归函数，完成后调用自身
• 与 lodash _.debounce我们可以防止过多调用它的方法

export default {
  data() {
    this.trackProgress = _.debounce(this.trackProgress, 1000);

    return {
      visible: true,
      current_row: 0,
      total_rows: 0,
      progress: 0,
    };
  },

  methods: {
    handleChange(info) {
      const status = info.file.status;

      if (status === "done") {
        this.trackProgress();
      } else if (status === "error") {
        this.$message.error(_.get(info, 'file.response.errors.file.0', `${info.file.name} file upload failed.`));
      }
    },

    async trackProgress() {
      const { data } = await axios.get('/import-status');

      if (data.finished) {
        this.current_row = this.total_rows
        this.progress = 100
        return;
      };

      this.total_rows = data.total_rows;
      this.current_row = data.current_row;
      this.progress = Math.ceil(data.current_row / data.total_rows * 100);
      this.trackProgress();
    },

    close() {
      if (this.progress > 0 && this.progress < 100) {
        if (confirm('Do you want to close')) {
          this.$emit('close')
          window.location.reload()
        }
      } else {
        this.$emit('close')
        window.location.reload()
      }
    }
  },
};

<template>
  <a-modal
    title="Upload excel"
    v-model="visible"
    cancel-text="Close"
    ok-text="Confirm"
    :closable="false"
    :maskClosable="false"
    destroyOnClose
  >
    <a-upload-dragger
      name="file"
      :multiple="false"
      :showUploadList="false"
      :action="`/import`"
      @change="handleChange"
    >
      <p class="ant-upload-drag-icon">
        <a-icon type="inbox" />
      </p>
      <p class="ant-upload-text">Click to upload</p>
    </a-upload-dragger>
    <a-progress class="mt-5" :percent="progress" :show-info="false" />
    <div class="text-right mt-1">{{ this.current_row }} / {{ this.total_rows }}</div>
    <template slot="footer">
      <a-button @click="close">Close</a-button>
    </template>
  </a-modal>
</template>

<script>
export default {
  data() {
    this.trackProgress = _.debounce(this.trackProgress, 1000);

    return {
      visible: true,
      current_row: 0,
      total_rows: 0,
      progress: 0,
    };
  },

  methods: {
    handleChange(info) {
      const status = info.file.status;

      if (status === "done") {
        this.trackProgress();
      } else if (status === "error") {
        this.$message.error(_.get(info, 'file.response.errors.file.0', `${info.file.name} file upload failed.`));
      }
    },

    async trackProgress() {
      const { data } = await axios.get('/import-status');

      if (data.finished) {
        this.current_row = this.total_rows
        this.progress = 100
        return;
      };

      this.total_rows = data.total_rows;
      this.current_row = data.current_row;
      this.progress = Math.ceil(data.current_row / data.total_rows * 100);
      this.trackProgress();
    },

    close() {
      if (this.progress > 0 && this.progress < 100) {
        if (confirm('Do you want to close')) {
          this.$emit('close')
          window.location.reload()
        }
      } else {
        this.$emit('close')
        window.location.reload()
      }
    }
  },
};
</script>