如何使用php中的PhpSpreadsheet读取Excel、CSV文件（附示例）

mkdir demo cd demo

composer require phpoffice/phpspreadsheet

composer require phpoffice/phpspreadsheet:develop

vim index.html

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('demo.xlsx');

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile("demo.xlsx"); $reader->setReadDataOnly(true); $reader->load("demo.xlsx");

abstract class IOFactory { private static $readers = [ 'Xlsx' => Reader\Xlsx::class, 'Xls' => Reader\Xls::class, 'Xml' => Reader\Xml::class, 'Ods' => Reader\Ods::class, 'Slk' => Reader\Slk::class, 'Gnumeric' => Reader\Gnumeric::class, 'Html' => Reader\Html::class, 'Csv' => Reader\Csv::class, ]; private static $writers = [ 'Xls' => Writer\Xls::class, 'Xlsx' => Writer\Xlsx::class, 'Ods' => Writer\Ods::class, 'Csv' => Writer\Csv::class, 'Html' => Writer\Html::class, 'Tcpdf' => Writer\Pdf\Tcpdf::class, 'Dompdf' => Writer\Pdf\Dompdf::class, 'Mpdf' => Writer\Pdf\Mpdf::class, ]; ...

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx"); // 指定为xlsx格式 $spreadsheet = $reader->load("demo.xlsx");

// 源码解析 // 不指定reader，直接获取上传的文件创建 $reader = \PhpOffice\PhpSpreadsheet\IOFactory::load($_FILES['file']['tmp_name']); // IOFactory::load() public static function load($pFilename) { // 这步棋室就是创建reader，免去了你手动创建 $reader = self::createReaderForFile($pFilename); return $reader->load($pFilename); } // IOFactory::createReaderForFile() // 这步就是返回一个reader，具体返回什么reader，是根据文件名来的 public static function createReaderForFile($filename) { // 判断文件是否存在并且可读，会抛出InvalidArgumentException File::assertFile($filename); // 根据文件后缀猜测类型 $guessedReader = self::getReaderTypeFromExtension($filename); if ($guessedReader !== null) { $reader = self::createReader($guessedReader); // Let's see if we are lucky if (isset($reader) && $reader->canRead($filename)) { return $reader; } } // 如果没有检测到类型，就会遍历默认的reader数组，直到找到可以使用的那个reader foreach (self::$readers as $type => $class) { if ($type !== $guessedReader) { $reader = self::createReader($type); if ($reader->canRead($filename)) { return $reader; } } } throw new Reader\Exception('Unable to identify a reader for this file'); }

// 指定reader $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $spreadsheet = $reader->load($_FILES['file']['tmp_name']);

require 'vendor/autoload.php'; $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); try { $spreadsheet = $reader->load($_FILES['file']['tmp_name']); } catch (\PhpOffice\PhpSpreadsheet\Reader\Exception $e) { die($e->getMessage()); } $sheet = $spreadsheet->getActiveSheet(); $res = array(); foreach ($sheet->getRowIterator(2) as $row) { $tmp = array(); foreach ($row->getCellIterator() as $cell) { $tmp[] = $cell->getFormattedValue(); } $res[$row->getRowIndex()] = $tmp; } echo json_encode($res);

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $reader->setReadDataOnly(true);

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); // 参数支持字符串或一个数组 $reader->setLoadSheetsOnly(['sheet1','sheet3']);

class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter { public function readCell($column, $row, $worksheetName = '') { // 只读取A1:E7的数据 if ($row >= 1 && $row <= 7) { if (in_array($column,range('A','E'))) { return true; } } return false; } } $myFilter = new MyReadFilter(); $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $reader->setReadFilter($filterSubset); $spreadsheet = $reader->load('demo.xlsx');

class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter { private $startRow = 0; private $endRow = 0; private $columns = []; public function __construct($startRow, $endRow, $columns) { $this->startRow = $startRow; $this->endRow = $endRow; $this->columns = $columns; } public function readCell($column, $row, $worksheetName = '') { if ($row >= $this->startRow && $row <= $this->endRow) { if (in_array($column,$this->columns)) { return true; } } return false; } } $myFilter = new MyReadFilter(9,15,['A', 'B', 'D']);

$reader->listWorksheetNames('demo.xlsx');

$reader->listWorksheetInfo('demo.xlsx');