Process Excel files using PHP and PhpSpreadsheet

With the advent of the digital age, spreadsheets have become an indispensable part of many people's daily work. Especially for people who need to process large amounts of data, Excel files are an indispensable tool. However, manually processing Excel files can be tedious and error-prone, so automated processing of Excel files has become the choice of many people. The emergence of PHP and PhpSpreadsheet makes processing Excel files more convenient.

PHP is a popular open source server-side scripting language for writing dynamic web applications, and PhpSpreadsheet is a PHP library for reading and writing Excel files. PhpSpreadsheet is an upgraded version of PHPExcel designed to provide better performance and maintainability. The following will explore how to use PHP and PhpSpreadsheet to process Excel files.

1. Installing PhpSpreadsheet

Before officially using PhpSpreadsheet, we need to install it first. It can be installed through Composer, just enter the following command in the terminal:

composer require phpoffice/phpspreadsheet

After the installation is complete, we can start using PhpSpreadsheet to process Excel files.

2. Read Excel files

PhpSpreadsheet can read Excel files in various formats, including ".xls" and ".xlsx". The following is a simple code example that demonstrates how to read an Excel file:

use PhpOfficePhpSpreadsheetIOFactory;

$reader = IOFactory::createReader('Xlsx'); // 先创建一个Reader对象
$spreadsheet = $reader->load('example.xlsx'); // 载入文件到Spreadsheet对象中

$worksheet = $spreadsheet->getActiveSheet(); // 获取活动工作表

$highestRow = $worksheet->getHighestRow(); // 获取最大行数
$highestColumn = $worksheet->getHighestColumn(); // 获取最大列数

// 从第1行开始遍历每一行
for ($row = 1; $row <= $highestRow; ++$row) {
    // 从A列开始遍历每一列
    for ($col = 'A'; $col <= $highestColumn; ++$col) {
        $cell = $worksheet->getCell($col . $row); // 获取单元格对象
        $value = $cell->getValue(); // 获取单元格值
        echo "$col$row: $value
";
    }
}

The above code will read the Excel file named "example.xlsx" and traverse each row and column to output the cell's value. The traversed range can be modified as needed.

3. Write Excel files

In addition to reading Excel files, PhpSpreadsheet can also write Excel files. The following is a sample code that demonstrates how to write data in an Excel file:

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;

$spreadsheet = new Spreadsheet(); // 创建一个Spreadsheet对象

$worksheet = $spreadsheet->getActiveSheet(); // 获取活动工作表

// 写入数据
$worksheet->setCellValue('A1', '姓名')
          ->setCellValue('B1', '分数')
          ->setCellValue('A2', '张三')
          ->setCellValue('B2', 80)
          ->setCellValue('A3', '李四')
          ->setCellValue('B3', 90);

$writer = new Xlsx($spreadsheet); // 创建一个Writer对象，指定文件类型为“xlsx”

$writer->save('example.xlsx'); // 保存Excel文件

The above code will write data to an Excel file named "example.xlsx". In actual situations, the cell value and file name can be modified as needed.

Summary

This article introduces how to use PHP and PhpSpreadsheet to process Excel files. PhpSpreadsheet is powerful and can be used to read and write Excel files in various formats. By using PhpSpreadsheet, developers can easily automate the processing of Excel files and improve work efficiency. If you need to work with large amounts of data, it is recommended to try PHP and PhpSpreadsheet to make Excel processing easier and more efficient.

The above is the detailed content of Process Excel files using PHP and PhpSpreadsheet. For more information, please follow other related articles on the PHP Chinese website!