PHP development skills: How to use PHPExcel to operate MySQL database

PHP development skills: How to use PHPExcel to operate MySQL database

With the vigorous development of the Internet, a large amount of data is stored in the database, and operations such as import, export, and processing are required. In PHP development, PHPExcel is a powerful library that can simplify the interaction with Excel files and realize the import and export of data. This article will introduce how to use PHPExcel to operate the MySQL database and implement data import and export functions.

Installation and configuration of PHPExcel:

First, we need to download the PHPExcel library and extract it into our project directory. Then, introduce the PHPExcel library into our project:

require_once 'PHPExcel/PHPExcel.php';

Database connection:

First, we need to establish a connection with the database to read and write data, here we use the MySQL database As an example. The following is a code example to establish a connection to a MySQL database:

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

Read database data and export to Excel:

The following is an example showing how to read data from a MySQL database and export it to Excel Export to Excel file:

$query = "SELECT * FROM mytable";
$result = $conn->query($query);

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'ID');
$objPHPExcel->getActiveSheet()->setCellValue('B1', '姓名');
$objPHPExcel->getActiveSheet()->setCellValue('C1', '年龄');

$row = 2;
while ($row_data = $result->fetch_assoc()) {
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $row_data['id']);
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $row_data['name']);
    $objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $row_data['age']);
    $row++;
}

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

In this code, a SELECT statement is first defined to obtain data from the mytable table. Then, use the fetch_assoc() method to read the data row by row and write it to the corresponding cell in the PHPExcel object. Finally, use the createWriter() and save() methods to save the PHPExcel object as an Excel file.

Import Excel data into the database:

Here is an example showing how to import data from an Excel file into a MySQL database:

require_once 'PHPExcel/IOFactory.php';

$file = 'data_import.xlsx';
$objPHPExcel = PHPExcel_IOFactory::load($file);

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

for ($row = 2; $row <= $highestRow; $row++) {
    $id = $worksheet->getCellByColumnAndRow(0, $row)->getValue();
    $name = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
    $age = $worksheet->getCellByColumnAndRow(2, $row)->getValue();

    $query = "INSERT INTO mytable (id, name, age) VALUES ('$id', '$name', '$age')";
    $conn->query($query);
}

echo "数据导入成功！";

Through the PHPExcel library With the load() method of the IOFactory class, we can load data from Excel files. Then, read the data row by row through the getCellByColumnAndRow() method, and then insert it into the MySQL database.

Summary:

In this article, we introduced how to use the PHPExcel library to operate the MySQL database and implement the data import and export functions. In this way, we can easily export the data in the database to Excel files, and import the data in the Excel files into the database. This approach can greatly simplify the work of developers and improve efficiency. I hope this article can help everyone, thank you for reading!

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