laravel将excel水平分割成多张表

在Laravel中，可以使用PhpSpreadsheet库来读取Excel文件，并将其水平分割成多个表格。

首先，通过Composer安装了PhpSpreadsheet库：

composer require phpoffice/phpspreadsheet

使用

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class ExcelChunk extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'excel_chunk {fileName} {size}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '分割excel文件';
    protected $dir = '';
    protected $size = 1000;
    protected $excelName = '';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        set_time_limit(0);
        parent::__construct();
        $this->dir = storage_path('app/public/excel/');
        if (!file_exists($this->dir)) {
            mkdir($this->dir);
        }
    }

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $fileName = $this->argument('fileName', '');
        $index = stripos($fileName, '.');
        if ($index !== false) {
            $this->excelName = substr($fileName, 0, $index);
        } else {
            $this->error('文件名错误');
            return;
        }
        $this->size = $this->argument('size', 1000);
        $this->chunkExcelSheet($fileName);
    }

    public function chunkExcel($fileName)
    {
        // 读取 Excel 文件
        $spreadsheet = IOFactory::load($this->dir . $fileName);
        $worksheet = $spreadsheet->getActiveSheet();

        // 获取数据集
        $data = $worksheet->toArray();
        $head = $data[0];
        unset($data[0]);

        // 假设我们按照行数来分割文件，每100行为一个新文件
        $fileCount = ceil(count($data) / $this->size);

        // 遍历数据并创建新的工作簿和工作表
        for ($i = 0; $i < $fileCount; $i++) {
            // 创建一个新的 Spreadsheet 对象作为新文件的基础
            $newSpreadsheet = new Spreadsheet();

            // 创建第一个工作表
            $newSheet = $newSpreadsheet->getActiveSheet();
            $newSheet->setTitle("Sheet$i");

            // 将当前批次的数据写入新工作表
            $startIndex = $i * $this->size;
            $endIndex = min(($i + 1) * $this->size - 1, count($data) - 1);
            $dataSlice = array_slice($data, $startIndex, $endIndex - $startIndex + 1);
            array_unshift($dataSlice, $head);
            $newSheet->fromArray($dataSlice);

            // 保存为新的 Excel 文件
            $writer = IOFactory::createWriter($newSpreadsheet, 'Xlsx');
            $writer->save($this->dir . $this->excelName . '_' . ($i + 1) . '.xlsx');
        }
    }

    public function chunkExcelSheet($fileName)
    {
        // 读取 Excel 文件
        $spreadsheet = IOFactory::load($this->dir . $fileName);
        $originalWorksheet = $spreadsheet->getActiveSheet();
        $data = $originalWorksheet->toArray();
        $head = $data[0];
        unset($data[0]);
        // 假设我们按照行数来分割到不同的工作表，每100行为一个新工作表
        $sheetCount = ceil(count($data) / $this->size);

        // 在同一个工作簿中创建多个工作表
        for ($i = 0; $i < $sheetCount; $i++) {
            // 创建新工作表
            $newSheet = new Worksheet($spreadsheet);
            $spreadsheet->addSheet($newSheet, $i);
            $newSheet->setTitle("Sheet$i");

            // 将当前批次的数据写入新工作表
            $startIndex = $i * $this->size;
            $endIndex = min(($i + 1) * $this->size - 1, count($data) - 1);
            $dataSlice = array_slice($data, $startIndex, $endIndex - $startIndex + 1);
            array_unshift($dataSlice, $head);
            $newSheet->fromArray($dataSlice);
        }

        // 保存修改后的工作簿
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save($this->dir . $this->excelName . "_output.xlsx");
    }
}

调用

php artisan excel_chunk aaa.excel 1000