Use PHP and XML to process and import Excel data

Use PHP and XML to process and import Excel data

1. Introduction
Excel is a very commonly used tool in the Microsoft Office suite. It provides a simple and convenient way to process data. Input, output and editing. In some web applications, we often need to import Excel data into the database for further processing or display. This article explains how to use PHP and XML to process and import Excel data.

2. Use PHPExcel library
PHPExcel is a popular PHP library that can be used to process Excel files. It provides many functions and methods to read, write, and modify Excel files, and supports Excel files in multiple formats, including .xls and .xlsx. First, you need to download and install the PHPExcel library. You can download the latest version of the library file from the official website (https://phpexcel.codeplex.com/).

3. Reading Excel data
The following is a code example for reading Excel data using the PHPExcel library:

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 above code first loads the Excel file and saves it in $objPHPExcel in variables. Then, by getting an instance of active sheet $sheet, we can iterate through each row and get the value of each cell, saving it in a 2D array $data.

4. Import data into the database
Once we successfully read the Excel data, the next step is to import the data into the database. Here, we will use the MySQL database as an example to explain. First, you need to create a database and tables to store the imported data. Here, we create a table named students, which contains two fields: name and age of students.

The following is a code example for importing Excel data into a 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();

The above code first connects to the database and uses a foreach loop to iterate through the read Excel data. Then, insert each row of data into the students table.

5. Summary
This article introduces how to use PHP and XML to process and import Excel data. By using PHPExcel library we can easily read data from Excel files and import it into database for further processing. By adding appropriate error handling and data validation, we can ensure that the imported data is accurate.

In practical applications, this method can be used to import some basic data from Excel tables, such as user information, product lists, etc. This is a very convenient and efficient way to perform data entry and management. I hope this article helps you get better at using PHP and XML to process and import Excel data.

The above is the detailed content of Use PHP and XML to process and import Excel data. For more information, please follow other related articles on the PHP Chinese website!