How to use PhpSpreadsheet in php to read Excel and CSV files (with examples)

The content of this article is about how to use PhpSpreadsheet in php to read Excel and CSV files (with examples). It has certain reference value. Friends in need can refer to it. I hope it will help You helped.

There are many libraries for PHP to read excel and csv files, but the more commonly used ones are: PHPOffice/PHPExcel, PHPOffice/PhpSpreadsheet. Now PHPExcel is no longer maintained. The latest one The first submission was on December 25, 2017. It is recommended to use PhpSpreadsheet directly, and the two projects are maintained by the same organization. This article introduces the use of PhpSpreadsheet.

Introduction to PhpSpreadsheet

PhpSpreadsheet is a library written in pure PHP, provides a very rich class and method, and supports many file formats:

Environmental requirements

• PHP >= 5.6

• Openphp_zipExtension

• Openphp_xmlExtension

• Openphp_gd2Extension

Get started

We write a simple demo to learn the use of PhpSpreadsheet, which is probably a simple file upload page , upload the Excel file we want to read, PHP receives the file, and calls PhpSpreadsheet to read the content in Excel.

0. Configure the environment

Slightly..., configure it yourself
My current PHP version is7.2.13

1. Create new A project

mkdir demo
cd demo

2. Installation

Use composer to install:

composer require phpoffice/phpspreadsheet

The latest stable version (1.5) is installed by default. If you want to install the dev version, you can execute The following command:

composer require phpoffice/phpspreadsheet:develop

After the above steps are executed, the directory structure is as follows:

3. Create a new simple html file, used to upload Excel files. The content in

vim index.html

index.html is very simple, as follows:

Be careful here Next: The enctype of the form form must be multipart/form-data

This is just a simple demo, a form form is enough. After running, it will look like the following :)

4. How to use PhpSpreadsheet?

Before processing the Excel file passed from the front end, let’s first introduce how to use PhpSpredsheet.

4.1 Reading files

There are many ways to read files in PhpSpreadsheet. There are different reading methods for files in different formats, such as: xlsx format, use \PhpOffice\PhpSpreadsheet\Reader\Xlsx(), csv format, use \PhpOffice\PhpSpreadsheet\Reader\Csv(), at first glance there are so many categories It feels a bit complicated. In fact, these classes implement the \PhpOffice\PhpSpreadsheet\Reader\IReader and \PhpOffice\PhpSpreadsheet\Writer\IWriter interfaces, which specify the file type to be loaded. We can directly use the factory class \PhpOffice\PhpSpreadsheet\IOFactory:

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

If you want to set some properties when reading and writing files, such as read and write properties, you can set them like this:

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

The advantage of using this factory class is that you don’t need to care about the format of the file upload. It can automatically help identify it. In fact, this factory class does some identification on the file you upload. If it is identified as xls format, it will return The reader of xls, if it is csv, returns the reader of csv. By analyzing the code, we can see that this IOFactory can produce the following readers and writers:

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,
    ];
...

You can see the supported There are quite a few types, but many of them are not commonly used.

In the IOFactory factory, you can also specify the file type for reading and writing, and return the corresponding reader, which eliminates the need to identify the file type, as follows:

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

4.2 Compare the two reading and writing methods from the source code

First, let’s take a look at the factory class IOFactory. When we do not specify the reader type, we directly load. The code inside is To do an operation of identifying the format:

// 源码解析
// 不指定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');
}

From the above code, you can see that before loading, file detection and type judgment operations are performed, and then the corresponding reader is returned. Next, let’s take a look. After we specified the type, what operations did we do:

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

The above is relatively simple. We directly create the reader and then load it. We just do some instantiation operations. Compared with these two methods, the second method has better performance, of course, the prerequisite is to know the file format.

5. 读取Excel文件内容

让我们接着继续上面的index.html，我们需要编写一个PHP文件来处理请求：

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);

我们先引入autoload，接着创建了一个Xlsx的reader，然后load我们上传的文件，因为在excel中，内容都是按sheet区分的，每一个sheet中都由行和列组成，我们获取到当前使用的sheet，通过sheet获取到行的迭代对象，再针对每一行得到每一列对象，在PhpSpreadsheet中，cell是一个最小的单元，对应着第几行第几列，数据都是存在cell中，得到cell对象我们就能获取到数据。

当我们上传如下内容后：

返回结果如下：

因为我们在读取时，是从第二行开始的，所以第一行的内容就不显示了。

这里说一下，在Excel中第三列是一个时间，PhpSpreadsheet对时间的处理有点特殊。在PhpSpreadsheet中date和time在存储时都是作为数字类型，当要区分数字是时间格式时，需要用到format mask，默认情况下，format mask是开启了的，但如果设置setReadDataOnly等于true的话，就不能使用format mask，从而就区分不了数字和时间格式，PhpSpreatsheet将会全部作为数字处理。

此时，我们开启只读模式看一下，

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

输出结果如下：

第三列就变成了奇怪的数字，当初这个问题还困扰了我半天。

5. PhpSpreadsheet读取文件时的一些常用方法

1. 如果一个Excel中有多个sheet，只想操作其中的某几个sheet，可以设置setLoadSheetsOnly

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

1. 读取指定行和列的数据

class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    public function readCell($column, $row, $worksheetName = '') {
        // 只读取A1:E7的数据
        if ($row >= 1 && $row 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 endRow) {
            if (in_array($column,$this->columns)) {
                return true;
            }
        }
        return false;
    }
}

$myFilter = new MyReadFilter(9,15,['A', 'B', 'D']);

1. 列出Excel中所有sheet的名字

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

1. 列出一个sheet的信息，包括多少列、多少行

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

PhpSpreadsheet的学习与使用就到这，真的很强大，几乎满足了日常的所有需求，是读取Excel、CSV文件的利器。

The above is the detailed content of How to use PhpSpreadsheet in php to read Excel and CSV files (with examples). For more information, please follow other related articles on the PHP Chinese website!