PHP implements reading and writing operations of Excel files

Excel file is a commonly used table file format and is often used in the development process. As a commonly used programming language, PHP also supports reading and writing operations on Excel files. In this article, we will introduce how to use PHP to read and write Excel files.

1. Install PHPExcel library

PHPExcel is an open source PHP library that can easily read and write Excel files. It can be installed through Composer or you can download the source code directly from GitHub.

Use Composer to install the PHPExcel library. You only need to execute the following command in the project root directory:

composer require phpoffice/phpexcel

After the installation is complete, you can load the PHPExcel library through the following code:

require 'vendor/autoload.php';

2. Reading Excel files

The PHPExcel library provides a rich API that can be used to read Excel files. The first thing to do is to open an Excel file. The following code demonstrates how to open an Excel file:

<?php
require 'vendor/autoload.php';

$inputFileName = 'example.xlsx';
$reader = PhpOfficePhpSpreadsheetIOFactory::createReaderForFile($inputFileName);
$spreadsheet = $reader->load($inputFileName);

In the above code, the $inputFileName variable holds the path to the Excel file to be read. createReaderForFile()The method can automatically select the corresponding reader based on the file extension. The load() method reads the file content into a PhpSpreadsheet object.

After reading the Excel file, we can get all the worksheets in the file through the following code:

$worksheetNames = $spreadsheet->getSheetNames();

foreach ($worksheetNames as $worksheetName) {
    $worksheet = $spreadsheet->getSheetByName($worksheetName);
    // 对工作表进行操作
}

In the above code, the getSheetNames() method returns a Array of worksheet names. We can traverse this array and use the getSheetByName() method to obtain the specific worksheet object.

After obtaining the worksheet object, you can use the following code to obtain the value of the cell:

$value = $worksheet->getCell('A1')->getValue();

Among them, the getCell() method is used to obtain the specified cell Object, getValue() method is used to get the value of the cell.

3. Writing Excel files

Similarly, the PHPExcel library also provides an API to facilitate us to write Excel files. The following code shows how to create a new Excel file and add worksheets and data to it:

$spreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet();

// 创建工作表并设置名称
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setTitle('Sheet1');

// 设置单元格的值
$worksheet->setCellValue('A1', '姓名');
$worksheet->setCellValue('B1', '年龄');
$worksheet->setCellValue('C1', '性别');
$worksheet->setCellValue('A2', '张三');
$worksheet->setCellValue('B2', 25);
$worksheet->setCellValue('C2', '男');

// 保存Excel文件
$writer = PhpOfficePhpSpreadsheetIOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('output.xlsx');

In the above code, a PhpSpreadsheet object is first created, and then an empty worksheet, set the name of the worksheet and the value of the cell, and finally use the PhpSpreadsheet_IOFactory::createWriter() method to create a writer object, and use the save() method to Data is written to the file.

4. Summary

In this article, we introduced how to use PHP to read and write Excel files. By using the PHPExcel library, we can easily operate Excel files and read and write data. Hope this article can be helpful to you!

The above is the detailed content of PHP implements reading and writing operations of Excel files. For more information, please follow other related articles on the PHP Chinese website!