朱纯树博客
VPS测评推荐网站
cloudacead cloudacead

Laravel6 配合 Maatwebsite\Excel 实现 Excel 导出

相比导入,项目中导出场景更多,估摸着现在有十多个导出了,之前写了导入,这会才把导出补上。

安装之前说过,这里说一下配置,虽然已有默认配置,但还是有修改配置的场景,所以建议生成配置文件。

配置

//生成config/excel.php
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

配置只提一个,其他注释蛮细的,

'csv' => [
    'delimiter'              => ',',
    'enclosure'              => '"',
    'line_ending'            => PHP_EOL,
    // 导出csv中文乱码,把use_bom设为true即可
    'use_bom'                => true,
    'include_separator_line' => false,
    'excel_compatibility'    => false,
],

接下来,来完成一个导出的 demo 说明下常用的一些点。

DEMO

php artisan make:export MultiExport

生成文件如下:

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

class MultiExport implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        //
    }
}
  • 自定义 sheet,增加 WithTitle
  • 自定义列名,增加 WithHeadings
  • 不想使用 Collection,替换 FromCollection 使用 FromArray
  • 多个 sheet,替换 FromCollection 使用 WithMultipleSheets

经过改造:

<?php
/**
 * 多重导出
 */
namespace App\Exports;

use App\Exports\MultiExportA;
use App\Exports\MultiExportB;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class MultiExport implements WithMultipleSheets
{
    private $date;

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


    public function sheets(): array
    {
        $sheets = [];

        $sheets[] = new MultiExportA($this->date);
        $sheets[] = new MultiExportB($this->date);

        return $sheets;
    }
}

---
// MultiExportA,MultiExportB类比即可
<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithTitle;
use App\Models\ExportA;

class MultiExportA implements FromArray, WithTitle, WithHeadings
{
    private $date;

    public function __construct($date, $cityId)
    {
        $this->date = $date;
    }

    public function headings(): array
    {
        return [
            'ID',
            '名称',
            '价格',
            '手机'
        ];
    }


    /**
    * @return array
    */
    public function array() : array
    {
        $data = ExportA::where('date', $this->date)
            ->get()
            ->toArray();

        $ret = [];
        foreach ($data as $val) {
            // 一段神奇的代码计算出了价格
            $price = ...;
            $ret[] = [
                'id' => $val['id']."\t",
                'name' => $val['name'],
                'price' => $price,
                // 转换为文本,编码excel使用了科学计数法
                'mobile' => $val['mobile']."\t",
            ];
        }
        return $ret;
    }

    /**
     * @return string
     */
    public function title(): string
    {
        return '表格A';
    }
}

使用

// 保存
$obj = new MultiExport($date);
Excel::store($obj, 'MultiExport'.$date.'.xlsx');

// 下载csv
Excel::download($obj, 'MultiExport'.$date.'.csv', \Maatwebsite\Excel\Excel::CSV, ['Content-Type' => 'text/csv']);

问题思考

当数据量过大的时候,导出时很可能会内存溢出了。建议:

  • 使用其他高性能的组件,或者使用原生代码流式输出到浏览器,也可以直接使用其他语言(比如 go)编写
  • 文件过大,Excel 打开大数据量文件也很鸡肋,容易卡死甚至崩溃,尝试分文件导出,比如 1w 一个文件
  • 部分导出过程可能有计算,可以提前计算好,导出时直接读表,使用 LazyCollection, 使用 Lazy Collections 来提高 laravel Excel 读取的性能(轻松支持百万数据)
赞(0) 打赏
未经允许不得转载:VPS测评推荐网站 - 朱纯树博客 » Laravel6 配合 Maatwebsite\Excel 实现 Excel 导出

评论 9

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
  1. #0

    Informative article, just what I was looking for.

    Fran10个月前 (10-29)回复
  2. #0

    Good answer back in return of this difficulty with
    genuine arguments and telling all concerning that.

    Adalberto10个月前 (10-26)回复
  3. #0

    Amazing! This blog looks exactly like my old one! It’s on a entirely different subject but it has pretty
    much the same layout and design. Superb choice of colors!

    Rueben10个月前 (10-26)回复
  4. #0

    What’s up, I read your blogs regularly. Your story-telling style is awesome, keep doing what you’re doing!

    Herman10个月前 (10-26)回复
  5. #0

    I like it when people get together and share ideas.
    Great website, stick with it!

    Milagros10个月前 (10-26)回复
  6. #0

    At this time it sounds like Movable Type is the top blogging platform out there right now.
    (from what I’ve read) Is that what you’re using on your blog?

    Connie10个月前 (10-26)回复
  7. #0

    Heya i am for the first time here. I found this board and I
    find It really useful & it helped me out a lot. I hope to give something back and aid others like you helped me.

    Dani10个月前 (10-26)回复
  8. #0

    Touche. Outstanding arguments. Keep up the great effort.

    Rudy10个月前 (10-26)回复
  9. #0

    Good day! Do you know if they make any plugins to help with Search Engine Optimization? I’m trying to get my blog to rank for
    some targeted keywords but I’m not seeing very good results.
    If you know of any please share. Appreciate
    it!

    Elijah10个月前 (10-26)回复