laravel box/spout 处理excel大数据
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 $e) {
throw new IOException($e->getMessage());
} catch (WriterNotOpenedException $e) {
throw new IOException($e->getMessage());
} catch (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 $e) {
throw new IOException($e->getMessage());
} catch (ReaderNotOpenedException $e) {
throw new IOException($e->getMessage());
} catch (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)