Simple tutorial about PhpSpreadsheet

I encountered a problem today involving data conversion between php and excel. I have been using PHPExcel before, but their development team no longer updates it. But PhpSpreadsheet was found.

1. Introduction

A library written in pure PHP that provides a set of classes that allow you to read and write different spreadsheet file formats

Supported formats

Environment requirements

php 5.6 and above

php_zip Support and enable

php_xml Support and enable

php_gd2 Support and enable

Installation

> composer require phpoffice/phpspreadsheet

hello world

<?php
require &#39;vendor/autoload.php&#39;;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue(&#39;A1&#39;, &#39;Hello World !&#39;);
$writer = new Xlsx($spreadsheet);
$writer->save(&#39;hello world.xlsx&#39;);

Example

The package contains example code, located under vendor/phpoffice/phpspreadsheet/samples

> php -S localhost:8000 -t vendor/phpoffice/phpspreadsheet/samples

2. Reading

No need to worry about file type loading, IOFactory

// 文件路径
$inputFileName = './sampleData/example1.xls';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

Note:

This is not for loading files The most efficient method, and it lacks the flexibility to configure the loader in any way before actually reading the file into the Spreadsheet object.

Knowing the file type, you can decide which reader to use

// 文件路径
$inputFileName = './sampleData/example1.xls';
/** 创建读取器  **/
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xml();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Ods();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Slk();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Gnumeric();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
$spreadsheet = $reader->load($inputFileName);

When you only need to read data without formatting, the readDataOnly attribute in the instance reader is as follows

$inputFileType = 'Xls';
$inputFileName = './sampleData/example1.xls';
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  只要数据  **/
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($inputFileName);

Note: csv reader does not have this attribute

Multiple files are merged into one object

$inputFileType = 'Csv';
$inputFileNames = [
    './sampleData/example1.csv',
    './sampleData/example2.csv'
    './sampleData/example3.csv'
];
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/** 拿到第一个  **/
$inputFileName = array_shift($inputFileNames);
$spreadsheet = $reader->load($inputFileName);
$spreadsheet->getActiveSheet()
    ->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME));
/**  循环读取  **/
foreach($inputFileNames as $sheet => $inputFileName) {
    /**  重新设置工作表索引  **/
    $reader->setSheetIndex($sheet+1);
    /**  把文件当做一个新的工作表载入  **/
    $reader->loadIntoExisting($inputFileName,$spreadsheet);
    /**  设置工作表标题  **/
    $spreadsheet->getActiveSheet()
        ->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME));
}

Note: For multiple worksheets Using the same worksheet index does not append the files to the same worksheet but overwrites the previously loaded results. You cannot load multiple CSV files into the same worksheet.

Convert to array

The file will finally be loaded into an object, which I call the spreadsheet worksheet object. This object stores information about all worksheet collections. (Data information, format information, worksheet information, etc.)

$spreadsheet = PhpOffice\PhpSpreadsheet\IOFactory::load("new.xls");
$data = $spreadsheet
            ->getSheet(0) // 指定第一个工作表为当前
            ->toArray();  // 转为数组
// 或者得到全部工作表的数据数组           
$cells=array();
// 工作表对象有迭代器实现
foreach ( $spreadsheet->getWorksheetIterator() as $data ) {
    $cells = $data->toArray();
}

Conclusion

PhpSpreadsheet is a very good php spreadsheet processing tool class. I will add it when I have time. Write and export.

For more PHP related knowledge, please visit PHP Chinese website!

The above is the detailed content of Simple tutorial about PhpSpreadsheet. For more information, please follow other related articles on the PHP Chinese website!