526互联

laravel box/spout 处理excel大数据

发布时间 2023-05-29 09:39:50作者: pine007

1、简介

laravel导入导出常规使用maatwebsite/excel，在处理大数据时效率低、占用过多内存。

而box/spout效率高、占用内存少，更适合处理大数据的导入导出。

2、安装

composer require box/spout

"box/spout": "^3.1"

3、封装

<?php

namespace App\Services\Common;

use Box\Spout\Common\Entity\Row;
use Box\Spout\Common\Exception\IOException;
use Box\Spout\Common\Exception\UnsupportedTypeException;
use Box\Spout\Common\Type;
use Box\Spout\Reader\Common\Creator\ReaderFactory;
use Box\Spout\Reader\Exception\ReaderNotOpenedException;
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Writer\Common\Creator\WriterFactory;
use Box\Spout\Writer\Exception\WriterNotOpenedException;

/**
 * box spout 导入导出Excel
 * Class ExcelSpout
 * @package App\Services\Common
 */
class ExcelSpout
{
    /**
     * 导出
     * @param string $path 路径
     * @param array $header 标题
     * @param array $data 数据
     * @param string $type 导出格式
     * @throws IOException
     */
    public function export(string $path, array $header, array $data, string $type = Type::XLSX)
    {
        try {
            $writer = WriterFactory::createFromType($type);
            $writer->openToFile($path);

            $headerRow = WriterEntityFactory::createRowFromArray($header);

            // 创建样式
            $style = (new StyleBuilder())
                ->setFontBold()
                ->build();

            // 给标题行添加样式
            foreach ($headerRow->getCells() as $cell) {
                $cell->setStyle($style);
            }

            $writer->addRow($headerRow);
            foreach ($data as $row) {
                $writer->addRow(WriterEntityFactory::createRowFromArray($row));
            }

            $writer->close();
        } catch (IOException | WriterNotOpenedException | UnsupportedTypeException $e) {
            throw new IOException($e->getMessage());
        }
    }


    /**
     * 导入
     * @param string $path 路径
     * @param int $startRow 数据开始行数
     * @return array
     * @throws IOException
     */
    public function import(string $path, int $startRow = 1)
    {
        try {
            $reader = ReaderFactory::createFromType(Type::XLSX);
            $reader->open($path);

            $data = [];
            foreach ($reader->getSheetIterator() as $sheet) {
                foreach ($sheet->getRowIterator() as $index => $row) {
                    if ($index >= $startRow) {
                        $data[] = $this->parseRow($row);
                    }
                }
            }

            $reader->close();
            return $data;
        } catch (UnsupportedTypeException | ReaderNotOpenedException | IOException $e) {
            throw new IOException($e->getMessage());
        }
    }

    /**
     * 读取一行数据
     * @param Row $row
     * @return array
     */
    protected function parseRow(Row $row)
    {
        $data = [];
        foreach ($row->getCells() as $cell) {
            $data[] = $cell->getValue();
        }
        return $data;
    }
}

4、调用

  • 导出
$filePath = storage_path('app/export/ExcelSpout-export-'.time().'.xlsx');
$header = ['标题','内容'];
$exportData = [['title1','content1'],['title2','content2']];
app(ExcelSpout::class)->export($filePath, $header, $exportData);
  • 导入
$filePath = 'xxx.xlsx';
$data = app(ExcelSpout::class)->import($filePath, 1);
dd($data)