PHP is a popular programming language used for developing web applications and websites. In web applications, it is often necessary to import data from Excel (.xls or .xlsx) files into a database. This article will introduce how to use PHP to read Excel data and import it into the database.
PHPExcel is a PHP library for reading and processing Excel files. You can download the PHPExcel library from the official website (https://phpexcel.codeplex.com/) or install it using Composer. If you use Composer, you only need to add the following code to the composer.json file:
{ "require": { "phpoffice/phpexcel": "1.8.*" } }
Execute the composer update
command on the command line, and Composer will automatically install the PHPExcel library.
Before importing Excel data, you need to have an Excel file. You can create an Excel file through software such as Microsoft Excel. Note that you need to save the Excel file in .xls or .xlsx format. In addition, the first line of the Excel file should be the header, which is the column name. For example:
ID | Name | Age | |
---|---|---|---|
1 | John | 25 | john@gmail.com |
2 | Mary | 30 | mary@yahoo.com |
David | 35 | david@outlook.com |
require_once 'vendor/autoload.php'; // 引入PHPExcel库 $path = 'example.xlsx'; // Excel文件路径 // 创建PHPExcel对象 $excel = PHPExcel_IOFactory::load($path); // 获取Excel的第一个工作表 $sheet = $excel->getActiveSheet(); // 获取工作表中的行数和列数 $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); // 遍历每一行 for ($row = 2; $row <= $highestRow; $row++) { // 获取每一行的数据 $id = $sheet->getCellByColumnAndRow(0, $row)->getValue(); $name = $sheet->getCellByColumnAndRow(1, $row)->getValue(); $age = $sheet->getCellByColumnAndRow(2, $row)->getValue(); $email = $sheet->getCellByColumnAndRow(3, $row)->getValue(); // 在此处可以将行数据插入到数据库中 }
load() method of the PHPExcel library to create a PHPExcel object and load data from an Excel file. Then, we get the first worksheet of the Excel file and use the
getHighestRow() and
getHighestColumn() methods to get the number of rows and columns. Finally, we iterate through each row, use the
getCellByColumnAndRow() method to obtain the cell data of each row, and insert it into the database.
// 连接到数据库 $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // 插入数据到数据库中 for ($row = 2; $row <= $highestRow; $row++) { $id = $sheet->getCellByColumnAndRow(0, $row)->getValue(); $name = $sheet->getCellByColumnAndRow(1, $row)->getValue(); $age = $sheet->getCellByColumnAndRow(2, $row)->getValue(); $email = $sheet->getCellByColumnAndRow(3, $row)->getValue(); $sql = "INSERT INTO myTable (id, name, age, email) VALUES ('$id', '$name', '$age', '$email')"; if ($conn->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } } $conn->close(); // 关闭数据库连接
Summary
It is easy to import data from Excel using PHP. You just need to use the PHPExcel library to read the Excel file and insert the data into the database. In the above example, we demonstrated how to import data into a MySQL database using PHPExcel library and SQL INSERT statement.The above is the detailed content of How to read Excel data in php and import it into the database. For more information, please follow other related articles on the PHP Chinese website!