PHP development tips: How to use PHPExcel and PHPExcel_IOFactory to operate MySQL database

PHP Development Tips: How to use PHPExcel and PHPExcel_IOFactory to operate MySQL database

Overview:
In web development, processing Excel files is a common and important task. PHPExcel is a powerful and easy-to-use PHP library that can help us read and write Excel files. This article will introduce how to use PHPExcel and PHPExcel_IOFactory libraries to operate MySQL database.

Step 1: Preparation
First, make sure that the PHPExcel and PHPExcel_IOFactory libraries are installed on your PHP server. You can download the latest versions from the official website and extract them into your project directory.

Step 2: Create a database connection
Before using PHPExcel, you need to establish a connection with the MySQL database. A database connection can be created using the following code example:

<?php
$servername = "localhost";
$username = "yourusername";
$password = "yourpassword";
$dbname = "yourdbname";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
?>

Step 3: Read data from MySQL database and generate Excel file
Next, we will use the PHPExcel library to read data from the MySQL database and Generate the corresponding Excel file. The following is a sample code:

<?php
require 'PHPExcel/PHPExcel.php';

// 创建一个新的Excel对象
$objPHPExcel = new PHPExcel();

// 选择活动工作表
$objPHPExcel->setActiveSheetIndex(0);

// 查询数据库中的数据
$sql = "SELECT * FROM your_table_name";
$result = $conn->query($sql);

// 设置Excel表头
$objPHPExcel->getActiveSheet()->setCellValue('A1', '列1标题');
$objPHPExcel->getActiveSheet()->setCellValue('B1', '列2标题');
$objPHPExcel->getActiveSheet()->setCellValue('C1', '列3标题');

// 将数据库查询结果填充到Excel中
if ($result->num_rows > 0) {
    $row = 2;
    while($row_data = $result->fetch_assoc()) {
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $row_data['column1']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $row_data['column2']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $row_data['column3']);
        $row++;
    }
}

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

In the code sample, we first create a new Excel object and select the active worksheet. Then get the data by querying the database and populate the data into Excel using the setCellValue method. Finally, we save the Excel file as output.xlsx.

Step 4: Import data from Excel file to MySQL database
In addition to exporting data from the database, PHPExcel can also help us import data from Excel files into the MySQL database. The following is a sample code:

<?php
require 'PHPExcel/PHPExcel.php';

// Load Excel文件
$objPHPExcel = PHPExcel_IOFactory::load('input.xlsx');

// 选择活动工作表
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();

// 循环遍历Excel行
foreach ($sheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);

    // 获取每个单元格的值
    $column1 = $cellIterator->current()->getValue();
    $cellIterator->next();
    $column2 = $cellIterator->current()->getValue();
    $cellIterator->next();
    $column3 = $cellIterator->current()->getValue();

    // 将数据插入到数据库中
    $sql = "INSERT INTO your_table_name (column1, column2, column3) VALUES ('$column1', '$column2', '$column3')";
    $conn->query($sql);
}
?>

In the above code, we first load the Excel file and select the active worksheet. Then by looping over the rows and cells, we get the value of each cell and insert it into the MySQL database.

Conclusion:
By using PHPExcel and PHPExcel_IOFactory libraries, we can easily process Excel files and interact with MySQL database data. This article describes how to use these two libraries to read data from a MySQL database and generate an Excel file, and how to import data from an Excel file into a MySQL database. I hope these sample codes will be helpful to your Excel operations in PHP development.

The above is the detailed content of PHP development tips: How to use PHPExcel and PHPExcel_IOFactory to operate MySQL database. For more information, please follow other related articles on the PHP Chinese website!