php使用phpoffice/phpspreadsheet获取表格数据

composer引入phpoffice/phpspreadsheet

composer require phpoffice/phpspreadsheet

具体实现类

以下是使用php8.2版本的代码，低版本请取消类型化属性(7.4开始支持类型化属性), mixed类型需要php8以上，请确认版本后修改掉

Excel读取类

<?php
namespace YourNameSpace;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

/**
 * 读取 excel 文件
 */
class ExcelReader
{
    /**
     * 文件名称
     * 
     * @var string|null 
     */
    private ?string $fileName;

    /**
     * 是否仅读取数据
     * 
     * @var bool 
     */
    private bool $readDataOnly = true;

    /**
     * 是否读取空单元格
     *
     * @var bool
     */
    private bool $readEmptyCell = true;

    /**
     * 获取excel表格内容
     * 
     * @return Spreadsheet
     */
    final public function read(): Spreadsheet
    {
        $reader = IOFactory::createReader($this->getExt());
        //设置只读
        $reader->setReadDataOnly($this->readDataOnly);
        //不读取空单元格 true 不读取  false 读取 默认true
        $reader->setReadEmptyCells($this->readEmptyCell);
        //加载文件
        return $reader->load($this->fileName);
    }

    /**
     * 获取文件后缀名称
     *
     * @return string
     */
    private function getExt(): string
    {
        return ucfirst(pathinfo($this->fileName, PATHINFO_EXTENSION));
    }

    /**
     * 设置文件名称
     * 
     * @return $this
     */
    final public function setFileName(string $fileName): self
    {
        $this->fileName = $fileName;
        return $this;
    }

    /**
     * 设置文件只读
     *
     * @return $this
     */
    final public function setReadDataOnly(bool $readDataOnly = true): self
    {
        $this->readDataOnly = $readDataOnly;
        return $this;
    }

    /**
     * 设置不读取空单元格
     *
     * @return $this
     */
    final public function setReadEmptyCell(bool $readEmptyCell = true): self
    {
        $this->readEmptyCell = $readEmptyCell;
        return $this;
    }
}

Excel数据处理类

<?php
namespace YourNameSpace;
use Generator;

/**
 * 处理 excel 数据
 */
class ExcelHandle
{
    /**
     * 生成器 获取数据 减少内存消耗
     *
     * @param string $fileName    文件名
     * @param array  $title       值对应字段
     *                               eg: [
     *                               "title"
     *                               ];
     *                               假设表格第一行第一列数据是 “我是标题” 最终结果就是 ['title' => '我是标题']
     * @param int    $sheetIndex  哪一个表格
     *
     * @return Generator
     */
    final public static function generatorData(
        string $fileName, 
        array $title = [], 
        int $sheetIndex = 0
    ): Generator
    {
        $excel = new ExcelReader();
        $spreadsheet = $excel->setFileName($fileName)->read();
        $worksheet = $spreadsheet->getSheet($sheetIndex);
        $empty = empty($title);
        //逐行读取
        foreach ($worksheet->getRowIterator() as $row) {
            $rowData = [];
            foreach ($row->getCellIterator() as $cell) {
                $rowData[] = $cell->getValue();
            }
            yield $empty ? $rowData : array_combine($title, $rowData); // 返回一行数据
        }
    }
}

具体使用

<?php
use YourNameSpace\ExcelHandle;
// 打开文件
$fileName = './test.xlsx';

foreach(ExcelHandle::generatorData($fileName,['title','a','b'],0) as $excelVal){
    $data[] = $excelVal;
}