大家好，欢迎来到IT知识分享网。

准备工作

环境条件

根据官方文档显示，使用Box/Spout组件需要满足：

• PHP version 7.1 or higher
• PHP extension ext-zip enabled
• PHP extension ext-xmlreader enabled

即PHP需要7.1及以上、开启zip拓展、开启xmlreader拓展

MacOS开启拓展的详细教程可点击此处查看。

安装Composer

安装前务必保证已经正确安装了PHP，MacOS已经自带PHP，目前最新版本为7.3.9

• 打开终端并依次执行下列命令安装最新版本的 Composer：

php -r "copy('https://install.phpcomposer.com/installer', 'composer-setup.php');"

php composer-setup.php

php -r "unlink('composer-setup.php');"

• 执行后将会在当前目录下生成一个composer.phar的文件，我们需要全局安装composer，执行下列命令：

sudo mv composer.phar /usr/local/bin/composer

• 现在就可以全局运行Composer了，但是由于众所周知的原因，我们需要使用Composer的中国镜像，在终端中执行：

composer config -g repo.packagist composer https://packagist.phpcomposer.com

使用Composer安装 Box/Spout

• 在你的Laravel工程根目录下执行命令：

composer require box/spout

本操作需要提前开启zip拓展和xmlreader拓展，否则会安装失败

导入Excel

目前在网络上难以搜索到真正可以使用的最新版教程，所以我将会分为官方教程和我个人的代码来进行分享，以达到准确性和实用性

官方描述

Regardless of the file type, the interface to read a file is always the same:

use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;

$reader = ReaderEntityFactory::createReaderFromFile('/path/to/file.ext');

$reader->open($filePath);

foreach ($reader->getSheetIterator() as $sheet) { 
   
    foreach ($sheet->getRowIterator() as $row) { 
   
        // do stuff with the row
        $cells = $row->getCells();
        ...
    }
}

$reader->close();

If there are multiple sheets in the file, the reader will read all of them sequentially.

Note that Spout guesses the reader type based on the file extension. If the extension is not standard (.csv, .ods, .xlsx – lower/uppercase), a specific reader can be created directly:

use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;

$reader = ReaderEntityFactory::createXLSXReader();
// $reader = ReaderEntityFactory::createODSReader();
// $reader = ReaderEntityFactory::createCSVReader();

个人分享

• 以一个简单的网络应用——上传Excel表格，返回表格内数据——为例，此块代码可以将所有sheet中的所有行全部输出，建议新手在学习时使用dd();函数调试，以便于清晰的掌握其中原理。

$filePath = "/path/".$File_name;
$reader = ReaderEntityFactory::createReaderFromFile($filePath);
$reader->open($filePath);
foreach ($reader->getSheetIterator() as $sheet) { 
   
    foreach ($sheet->getRowIterator() as $RowKey => $row) { 
   
        $cell = $row->getCells();
        foreach($cell as $CellKey => $value){ 
   
            $data[$RowKey][$CellKey] = $value->getValue();
        }
    }
}
$reader->close();
return ['code' => 200, 'data' => $data];

导出Excel

目前在网络上难以搜索到真正可以使用的最新版教程，所以我将会分为官方教程和我个人的代码来进行分享，以达到准确性和实用性

官方描述

As with the reader, there is one common interface to write data to a file:

use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Common\Entity\Row;

$writer = WriterEntityFactory::createXLSXWriter();
// $writer = WriterEntityFactory::createODSWriter();
// $writer = WriterEntityFactory::createCSVWriter();

$writer->openToFile($filePath); // write data to a file or to a PHP stream
//$writer->openToBrowser($fileName); // stream data directly to the browser

$cells = [
    WriterEntityFactory::createCell('Carl'),
    WriterEntityFactory::createCell('is'),
    WriterEntityFactory::createCell('great!'),
];

/** add a row at a time */
$singleRow = WriterEntityFactory::createRow($cells);
$writer->addRow($singleRow);

/** add multiple rows at a time */
$multipleRows = [
    WriterEntityFactory::createRow($cells),
    WriterEntityFactory::createRow($cells),
];
$writer->addRows($multipleRows); 

/** Shortcut: add a row from an array of values */
$values = ['Carl', 'is', 'great!'];
$rowFromValues = WriterEntityFactory::createRowFromArray($values);
$writer->addRow($rowFromValues);

$writer->close();

Similar to the reader, if the file extension of the file to be written is not standard, specific writers can be created this way:

use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Common\Entity\Row;

$writer = WriterEntityFactory::createXLSXWriter();
// $writer = WriterEntityFactory::createODSWriter();
// $writer = WriterEntityFactory::createCSVWriter();

For XLSX and ODS files, the number of rows per sheet is limited to 1,048,576. By default, once this limit is reached, the writer will automatically create a new sheet and continue writing data into it.

个人分享

• 以最简单的浏览器输出Excel为例，新版的Box/Spout不允许直接使用数组，必须转换为Cell，直接上代码，若有其他更加复杂的代码需求请参阅官方文档。

$cells = [
     WriterEntityFactory::createCell('序号'),
     WriterEntityFactory::createCell('姓名'),
     WriterEntityFactory::createCell('工号'),
];
$writer = WriterEntityFactory::createXLSXWriter()->openToBrowser('Example.xlsx');
$singleRow = WriterEntityFactory::createRow($cells);
$writer->addRow($singleRow);
$values = ['1', 'Mar0ew', 'G-19030001'];
$rowFromValues = WriterEntityFactory::createRowFromArray($values);
$writer->addRow($rowFromValues);

$writer->close();