How to read Excel data in php and import it into the database

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.

1. Installation PHPExcel

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.

2. Create Excel file

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:

##3David35david@outlook.com

ID	Name	Age	Email
1	John	25	john@gmail.com
2	Mary	30	mary@yahoo.com

3. Read Excel file

Read using PHPExcel Excel files are simple. Just follow these steps:

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();

    // 在此处可以将行数据插入到数据库中
}

In the above example, we use the

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.

4. Database Insertion

To insert data from an Excel file into the database, you can use the SQL INSERT statement. So you need to connect to the database first and then insert the data 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(); // 关闭数据库连接

In the above example, we use mysqli to connect to the MySQL database. In each row of data, we use SQL INSERT statement to insert the data into the myTable table. If the insertion is successful, we will see the message "New record created successfully", otherwise an error message will be output.

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!