How to use PHPExcel to process Excel files?

PHPExcel is an open source PHP library for processing Microsoft Excel (.xls and .xlsx) files. It can read, write and operate Excel files, and provides a wealth of functions and methods.

Using the PHPExcel library in PHP projects can quickly and easily process Excel files and implement functions such as data import, export and data processing. This article will introduce how to use PHPExcel to process Excel files.

1. Install PHPExcel

To use PHPExcel, you need to download and install the library first. The latest version of PHPExcel can be downloaded from the official website (https://github.com/PHPOffice/PHPExcel). After decompressing the compressed package, place the PHPExcel folder into the project, and then introduce PHPExcel's automatic loading file into the PHP file:

require_once 'PHPExcel/Classes/PHPExcel.php';

2. Create Excel files

Use PHPExcel to quickly create Excel file and write data. The following is a sample code to create an Excel document containing three rows and three columns and write data:

$excel = new PHPExcel();

$excel->setActiveSheetIndex(0);

$excel->getActiveSheet()->setCellValue('A1', '姓名')
                        ->setCellValue('B1', '年龄')
                        ->setCellValue('C1', '邮箱');

$excel->getActiveSheet()->setCellValue('A2', '张三')
                        ->setCellValue('B2', 20)
                        ->setCellValue('C2', 'zhangsan@example.com');

$excel->getActiveSheet()->setCellValue('A3', '李四')
                        ->setCellValue('B3', 25)
                        ->setCellValue('C3', 'lisi@example.com');

$excel->getActiveSheet()->setCellValue('A4', '王五')
                        ->setCellValue('B4', 30)
                        ->setCellValue('C4', 'wangwu@example.com');

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="example.xls"');
header('Cache-Control: max-age=0');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$writer->save('php://output');

In this code, a PHPExcel object named $excel is first created and then defaulted to The worksheet is set to the first worksheet (index 0). Then write the data to the Excel worksheet through the setCellValue() method.

Finally set the file type, file name, cache control and other attributes of the Excel file to be output, and create an instance object $writer of PHPExcel_IOFactory to write the Excel object $excel to the output.

3. Reading Excel files

Use PHPExcel to easily read data in Excel files. The following is a sample code for reading an Excel file:

$objReader = PHPExcel_IOFactory::createReader('Excel5');

$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load("example.xls");

$worksheet = $objPHPExcel->getActiveSheet();

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);

    $columns = [];

    foreach ($cellIterator as $cell) {
        $columns[] = $cell->getValue();
    }

    if (count($columns) > 0) {
        echo implode('|', $columns) . "
";
    }
}

In this code, first use the createReader() method of the PHPExcel_IOFactory class to create an Excel reader object $objReader, and then use the load() method to load the Excel The file example.xls is loaded into memory and finally the first worksheet in the Excel file is obtained.

Then traverse the cells in each row and column, use the getValue() method to obtain the value in each cell, and concatenate all the data in each row into a string.

Finally output the data of each row.

4. Export Excel file

Use PHPExcel to export data to an Excel file. The following is a sample code to export data in the MySQL database to an Excel file:

$pdo = new PDO('mysql:host=localhost;dbname=mydatabase', 'username', 'password');

$sql = "SELECT name, age, email FROM users";

$stmt = $pdo->query($sql);

$excel = new PHPExcel();

$excel->setActiveSheetIndex(0);

$excel->getActiveSheet()->setCellValue('A1', '姓名')
                        ->setCellValue('B1', '年龄')
                        ->setCellValue('C1', '邮箱');

$i = 2;

while ($row = $stmt->fetch()) {
    $excel->getActiveSheet()->setCellValue('A' . $i, $row['name'])
                            ->setCellValue('B' . $i, $row['age'])
                            ->setCellValue('C' . $i, $row['email']);
    $i++;
}

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="users.xls"');
header('Cache-Control: max-age=0');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$writer->save('php://output');

In this code, first connect to the MySQL database through the PDO object and query the data in the users table. Then create a PHPExcel object named $excel and write the data of each row into the Excel object in turn.

Finally set the file type, file name, cache control and other attributes of the Excel file to be output, and create an instance object $writer of PHPExcel_IOFactory to write the Excel object $excel to the output.

5. Conclusion

Through the introduction of this article, readers should understand how to use PHPExcel to process Excel files. Using PHPExcel, you can easily read, write and operate Excel files, and at the same time, you can export data from other data sources (such as MySQL database) to Excel files.

Although PHPExcel has ceased maintenance, as a mature and stable Excel processing library, it can still be used in many PHP projects and provides rich sample codes and documentation for reference.

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