How to use PHP for Excel file processing and manipulation

How to use PHP for Excel file processing and operation

Excel file is a tool widely used in the office for managing data, creating reports and charts, etc. When developing web applications, sometimes you need to process and operate Excel files, such as importing data, reading data, exporting data, etc. As a powerful server-side scripting language, PHP provides a wealth of functions and classes to process Excel files. In this article, we will introduce how to use PHP for Excel file processing and operations.

1. Install the PHPExcel library
PHPExcel is a popular PHP third-party library that provides a set of powerful classes and methods to process Excel files. First, we need to download and install the PHPExcel library. You can download the latest version at the following URL:
https://github.com/PHPOffice/PHPExcel/releases

After the download is complete, copy the PHPExcel directory and the files in it to your web application.

2. Reading Excel files
Reading Excel files using PHPExcel is very simple. The following is a sample code that demonstrates how to read an Excel file and display it on a web page:

<?php
// 引入PHPExcel库
require_once 'PHPExcel/PHPExcel.php';

// 创建PHPExcel对象
$objPHPExcel = new PHPExcel();

// 加载Excel文件
$objPHPExcel = PHPExcel_IOFactory::load("test.xlsx");

// 获取第一个工作表
$sheet = $objPHPExcel->getActiveSheet();

// 遍历每一行，并获取每一列的值
foreach ($sheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(FALSE);
    
    foreach ($cellIterator as $cell) {
        echo $cell->getValue() . "    ";
    }
    
    echo "<br>";
}
?>

3. Import data into an Excel file
Importing data into an Excel file is also a common operation. In PHP, we can use the relevant methods of PHPExcel to achieve this. The following is a sample code that demonstrates how to import database query results into an Excel file:

<?php
// 引入PHPExcel库
require_once 'PHPExcel/PHPExcel.php';

// 创建PHPExcel对象
$objPHPExcel = new PHPExcel();

// 创建一个工作表
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();

// 设置表头
$sheet->setCellValue('A1', '编号');
$sheet->setCellValue('B1', '姓名');
$sheet->setCellValue('C1', '年龄');

// 查询数据库，获取数据
$conn = mysqli_connect("localhost", "root", "password", "database");
$result = mysqli_query($conn, "SELECT * FROM users");

// 将查询结果写入Excel文件
$i = 2;
while ($row = mysqli_fetch_array($result)) {
    $sheet->setCellValue('A' . $i, $row['id']);
    $sheet->setCellValue('B' . $i, $row['name']);
    $sheet->setCellValue('C' . $i, $row['age']);
    $i++;
}

// 保存Excel文件
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('output.xlsx');
?>

4. Export data to Excel files
Exporting data to Excel files is also a common operation. PHPExcel provides a method to create an Excel file and export data to the file. Below is a sample code that demonstrates how to export array data to an Excel file:

<?php
// 引入PHPExcel库
require_once 'PHPExcel/PHPExcel.php';

// 创建PHPExcel对象
$objPHPExcel = new PHPExcel();

// 创建一个工作表
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();

// 设置表头
$sheet->setCellValue('A1', '编号');
$sheet->setCellValue('B1', '姓名');
$sheet->setCellValue('C1', '年龄');

// 数组数据
$data = array(
    array('1', '张三', '20'),
    array('2', '李四', '25'),
    array('3', '王五', '30')
);

// 将数组数据写入Excel文件
$i = 2;
foreach ($data as $row) {
    $sheet->setCellValue('A' . $i, $row[0]);
    $sheet->setCellValue('B' . $i, $row[1]);
    $sheet->setCellValue('C' . $i, $row[2]);
    $i++;
}

// 保存Excel文件
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('output.xlsx');
?>

The above code sample demonstrates how to use the PHPExcel library to process and manipulate Excel files. By reading this article, you should be able to understand how to use PHP to read, import, and export Excel files to add more functionality to your web applications. Hope this article helps you!

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