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

PHPz
Release: 2023-03-24 11:43:56
Original
2487 people have browsed it

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.*"
    }
}
Copy after login

Execute the composer update command on the command line, and Composer will automatically install the PHPExcel library.

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

    // 在此处可以将行数据插入到数据库中
}
Copy after login
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.

  1. 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(); // 关闭数据库连接
Copy after login
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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template