Complete Guide: How to process Excel files using php extension PHPExcel

Complete Guide: How to use the PHP extension PHPExcel to process Excel files

Introduction:
Excel files are often used as a common format for data storage and exchange when processing large amounts of data and statistical analysis. Using the PHP extension PHPExcel, we can easily read, write and modify Excel files to effectively process Excel data. This article will introduce how to use the PHP extension PHPExcel to process Excel files and provide code examples.

1. Install PHPExcel:
PHPExcel is an open source PHP library used to read and process Excel files. We first need to install the PHPExcel library. It can be installed in the following two ways:

1. Manual download
   You can manually download the PHPExcel zip package from the official website of PHPExcel (https://github.com/PHPOffice/PHPExcel), and Unzip it into the project directory.

2. Use Composer
   Add the following dependencies in the project's composer.json file:

   "require": {
    "phpoffice/phpexcel": "^1.8"
   }

   Then use composer to execute "composer install" to install PHPExcel.

2. Reading Excel files:
It is very simple to use PHPExcel to read Excel files. We can use PHPExcel's IOFactory class to load and parse Excel files. Here is an example of reading an Excel file:

require 'path_to_phpexcel/PHPExcel/IOFactory.php';

$inputFileName = 'path_to_excel_file/example.xlsx';
$excelReader = PHPExcel_IOFactory::createReaderForFile($inputFileName);
$excelReader->setReadDataOnly(true);
$objPHPExcel = $excelReader->load($inputFileName);

$worksheet = $objPHPExcel->getActiveSheet();
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();

for ($row = 1; $row <= $highestRow; $row++) {
    $rowData = $worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
    // 处理rowData
    foreach ($rowData[0] as $cell) {
        echo $cell . " ";
    }
    echo "<br>";
}

This example code reads an Excel file named example.xlsx and iterates through all rows and columns. During the traversal process, we can use the $rowData array to save the data of each row, which can then be processed further.

3. Writing Excel files:
It is also very simple to use PHPExcel to write Excel. We can create and edit cells in PHPExcel’s workbook object (PHPExcel object). The following is an example of writing to an Excel file:

require 'path_to_phpexcel/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$worksheet = $objPHPExcel->getActiveSheet();

$worksheet->setCellValue('A1', 'Hello');
$worksheet->setCellValue('B1', 'World!');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$filename = 'path_to_save_file/example.xlsx';
$objWriter->save($filename);

echo "Excel file has been created successfully!";

This example code creates a new Excel file, writes the "Hello" and "World!" characters to cells A1 and B1, and The Excel file is saved to a file named example.xlsx. We can use other functions of PHPExcel, such as setting cell styles, merging cells, etc., to customize Excel files.

4. Modify Excel files:
Using PHPExcel, you can also modify existing Excel files. We can load an existing Excel file and modify the cells in it. The following is an example of modifying an Excel file:

require 'path_to_phpexcel/PHPExcel/IOFactory.php';

$inputFileName = 'path_to_excel_file/example.xlsx';
$excelReader = PHPExcel_IOFactory::createReaderForFile($inputFileName);
$objPHPExcel = $excelReader->load($inputFileName);

$worksheet = $objPHPExcel->getActiveSheet();
$worksheet->setCellValue('C1', 'Modified');
$worksheet->setCellValue('D1', 'Data');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($inputFileName);

echo "Excel file has been modified successfully!";

This sample code loads an Excel file named example.xlsx, then modifies the data in cells C1 and D1 and saves it to the same file. We can use other functions of PHPExcel to make more complex modifications according to our needs.

Conclusion:
This article introduces how to use the PHP extension PHPExcel to process Excel files, and provides code examples for reading, writing, and modifying Excel files. Using PHPExcel, we can process Excel data more conveniently and improve the efficiency of data processing. I hope this article will help you use Excel file processing in application development and data analysis.

The above is the detailed content of Complete Guide: How to process Excel files using php extension PHPExcel. For more information, please follow other related articles on the PHP Chinese website!