How to use PHP for Excel file processing?

With the continuous popularity and application of Excel files in the business field and daily life, we often need to use PHP to process Excel files, such as data import and export, data filtering and sorting, etc. Therefore, this article will introduce how to use PHP for Excel file processing.

1. Install PHPExcel library

PHPExcel is a powerful open source library for PHP to operate Excel files. It supports reading and writing Excel files, and provides many conveniences method of operation. You need to install the PHPExcel library before using it. Use Composer to install, the command is as follows:

composer require phpoffice/phpexcel

Or download and unzip it manually, and copy the PHPExcel folder to your project directory.

2. Reading Excel files

To read Excel files, use the PHPExcel_IOFactory class, which can be achieved through the following methods:

//加载Excel文件
$objPHPExcel = PHPExcel_IOFactory::load('excel_file.xlsx');
//获取当前活动的工作表
$sheet = $objPHPExcel->getActiveSheet();
//获取单元格数据
$data = $sheet->getCell('A1')->getValue();

In the above code, first Load the Excel file through the load() method, then use the getActiveSheet() method to get the currently active worksheet, and finally use the getCell() method to get the cell data.

3. Write to Excel file

To write data to Excel file, also use the PHPExcel class, which can be achieved by the following method:

//创建一个Excel文件对象
$objPHPExcel = new PHPExcel();
//设置工作表标题
$objPHPExcel->getActiveSheet()->setTitle('test sheet');
//添加数据
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'data1')
                              ->setCellValue('B1', 'data2')
                              ->setCellValue('C1', 'data3');
//保存Excel文件
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('excel_file.xlsx');

The above code , first create an Excel file object, then set the worksheet title, add data, and finally use the createWriter() method and save() method to save the Excel file.

4. Data filtering and sorting

When processing Excel files, data filtering and sorting are often required. You can use the filter class and sorting class provided by PHPExcel to operate.

//加载Excel文件
$objPHPExcel = PHPExcel_IOFactory::load('excel_file.xlsx');
//获取当前活动的工作表
$sheet = $objPHPExcel->getActiveSheet();
//筛选数据
$autoFilter = $sheet->setAutoFilter('A1:C1');
//按照A列排序
$sort = $sheet->sort('A1', 'desc');
//保存Excel文件
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('excel_file.xlsx');

In the above code, the setAutoFilter() method can set the first row as the filter column, and the sort() method can sort according to a certain column.

5. Summary

Using PHP to process Excel files requires the PHPExcel library, which provides a wealth of methods and functions, covering reading, writing, filtering and Sorting and other operations. The above is a brief introduction to using PHPExcel for Excel file processing. I hope it will be helpful to everyone.

The above is the detailed content of How to use PHP for Excel file processing?. For more information, please follow other related articles on the PHP Chinese website!