How to Efficiently Import Excel Data into Database Using PHP and XML

Introduction

Excel is a commonly used tool in the Microsoft Office suite, widely used for data input, output, and editing. In web applications, we often need to import data from Excel into a database for processing or display. This article will introduce how to use PHP and XML to achieve this goal.

Using the PHPExcel Library

PHPExcel is a powerful PHP library that helps you handle various Excel file formats, including .xls and .xlsx. First, you need to download and install the PHPExcel library. After installation, you can use the rich functionality provided by PHPExcel to process Excel files.

Reading Excel Data

Next, we will introduce how to use the PHPExcel library to read data from an Excel file. Below is a code example:

require_once 'PHPExcel/PHPExcel.php';
$filename = 'data.xls';
$objPHPExcel = PHPExcel_IOFactory::load($filename);
$sheet = $objPHPExcel->getActiveSheet();
$data = array();
foreach ($sheet->getRowIterator() as $row) {
    $rowData = array();
    foreach ($row->getCellIterator() as $cell) {
        $rowData[] = $cell->getValue();
    }
    $data[] = $rowData;
}
print_r($data);

The code above demonstrates how to load an Excel file and read its content into a two-dimensional array. The data from each row is extracted and stored in the $data array.

Importing Data into a Database

After successfully reading the Excel data, the next step is to import this data into a database. Below is a code example that demonstrates how to import data into a MySQL database:

require_once 'PHPExcel/PHPExcel.php';
$filename = 'data.xls';
$objPHPExcel = PHPExcel_IOFactory::load($filename);
$sheet = $objPHPExcel->getActiveSheet();
$data = array();
foreach ($sheet->getRowIterator() as $row) {
    $rowData = array();
    foreach ($row->getCellIterator() as $cell) {
        $rowData[] = $cell->getValue();
    }
    $data[] = $rowData;
}
$conn = new mysqli('localhost', 'username', 'password', 'database');
if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}
foreach ($data as $row) {
    $name = $row[0];
    $age = $row[1];
    $sql = "INSERT INTO students (name, age) VALUES ('$name', $age)";
    if ($conn->query($sql) === TRUE) {
        echo 'Record inserted successfully';
    } else {
        echo 'Error inserting record: ' . $conn->error;
    }
}
$conn->close();

This code connects to a MySQL database and inserts the name and age data from each row in the Excel file into the `students` table.

Conclusion

This article demonstrated how to use PHP and XML to read data from Excel and import it into a database. With the PHPExcel library, developers can easily handle various Excel files and quickly implement data import functionality. This method is highly suitable for importing basic data like user information or product lists from Excel, greatly improving data entry efficiency.