guanguans/export.md

## export.md

      
    Raw
  

              export.md
            
          
    Excel ajax 导出

Export

<?php

namespace App\Exports;

use App\Models\Product;
use App\Models\ProvinceCityArea;
use Illuminate\Support\Collection;
use Illuminate\Support\Stringable;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Spatie\QueryBuilder\AllowedFilter;
use Spatie\QueryBuilder\QueryBuilder;

class ProductExport implements FromCollection, WithMapping, WithHeadings
{
    use Exportable;

    private Collection $provincePluck;
    private Collection $cityPluck;

    public function __construct()
    {
        $this->provincePluck = ProvinceCityArea::query()->where('type', 'province')->pluck('name', 'id');
        $this->cityPluck = ProvinceCityArea::query()->where('type', 'city')->pluck('name', 'id');
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return QueryBuilder::for(Product::class)
            ->select('id', 'name', 'encoding', 'operator_id', 'supplier_id', 'province_id', 'city_id', 'is_required_idcard', 'is_required_idphoto', 'min_age_limit', 'max_age_limit', 'per_person_card_num_limit')
            ->with('operator', 'supplier', 'dontShipAddresses', 'province', 'city')
            ->allowedFilters(
                'name',
                AllowedFilter::exact('supplier_id'),
                AllowedFilter::exact('encoding'),
                AllowedFilter::exact('is_removed'),
            )
            ->defaultSort('-id')
            ->allowedSorts('id')
            ->get();
    }

    public function map($row): array
    {
        /* @var Product $row */
        return [
            $row->name,
            $row->encoding,
            $row->operator->name,
            $row->supplier->name,
            $row->province->name.$row->city->name,
            $row->is_required_idcard ? '是' : '否',
            $row->is_required_idphoto ? '是' : '否',
            $row->dontShipAddresses->reduce(function (Stringable $carry, $item) {
                return $carry->append($this->provincePluck[$item->province_id].$this->cityPluck[$item->city_id].'、');
            }, str('、'))->trim('、'),
            sprintf("$row->min_age_limit - $row->max_age_limit"),
            $row->per_person_card_num_limit
        ];
    }

    public function headings(): array
    {
        return [
            '产品名称',
            '产品编码',
            '运营商',
            '供应商',
            '归属地',
            '是否需要身份证号码',
            '是否需要身份证照片',
            '不发货区域',
            '开卡年龄限制',
            '开卡数量限制'
        ];
    }
}
Controller

<?php

namespace App\Http\Controllers\Admin;

use App\Exports\ProductExport;
use App\Models\Product;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use Spatie\QueryBuilder\AllowedFilter;
use Spatie\QueryBuilder\QueryBuilder;

class ProductController extends Controller
{
    public function index(Request $request)
    {
        if ($request->query('is_export')) {
            return Excel::download(
                new ProductExport(),
                $fileName = sprintf('产品-%s.xlsx', date('YmdHis')),
                \Maatwebsite\Excel\Excel::XLSX,
                [
                    'Access-Control-Expose-Headers' => 'X-File-Name',
                    'X-File-Name' => urlencode($fileName),
                ],
            );
        }

        $products = QueryBuilder::for(Product::class)
            ->allowedFilters(
                'name',
                AllowedFilter::exact('supplier_id'),
                AllowedFilter::exact('encoding'),
                AllowedFilter::exact('is_removed'),
            )
            ->defaultSort('-id')
            ->allowedSorts('id')
            ->paginate($request->get('per_page'));

        return $this->success($products);
    }
}
Html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>导出产品</title>
    <link rel="stylesheet" href="//cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css">
</head>
<body>
<div class="container text-center">
    <div class="row">
        <div class="col-sm-12">
            <button type="button" class="btn btn-primary" onclick="exportProduct();">导出产品</button>
        </div>
    </div>
</div>
</body>
<script src="//cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js"></script>
<script src="//cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.min.js"></script>
<script>
  function exportProduct() {
    var url = 'http://xxx.test/api/v1/admin/product/index?-sort=id&per_page=15&page=1';
    $.ajax({
      type: "GET",
      url: url,
      dataType: 'binary',
      xhrFields: {responseType: "blob"},
      contentType: "application/json",
      data: {
        'is_export': 1
      },
      headers: {
        'Authorization': 'Bearer 105|KYqNL9Axb2cz8zFkitFINs4Q32qRZaDaltzef',
        'timestamp': Math.round(new Date().getTime() / 1000).toString(),
        'nonce': randomStr(16),
      },
      success: function (ret, status, xhr) {
        console.log(ret)
        console.log(xhr.getAllResponseHeaders())

        var blob = new Blob([ret], {type: xhr.getResponseHeader('Content-Type')});
        var a = document.createElement('a');
        a.href = URL.createObjectURL(blob);
        a.download = decodeURI(xhr.getResponseHeader('X-File-Name'));
        a.click();
        URL.revokeObjectURL(a.href);
      }
    });
  }

  function randomStr(len) {
    var str = Math.random().toString(36).substring(2);
    var length = str.length;
    while (length < len) {
      str += Math.random().toString(36).substring(2);
      length = str.length;
    }

    return str.substring(0, len);
  }
</script>
</html>