Detailed explanation of PHP reading Excel table and querying data in specific fields

PHP is a very common server-side programming language. During the development process, it is often necessary to read and manipulate data. Among them, Excel tables are a common data source. If the data in Excel tables can be queried and processed in PHP, it will be very convenient and efficient for developers. This article will introduce how to use PHP to read Excel tables and how to query data in specific fields in Excel.

1. Use PHP to read Excel tables

There are many ready-made libraries in PHP that can be used to read Excel tables, such as PHPExcel, PhpSpreadsheet, etc. In this article, we will use PhpSpreadsheet to read Excel tables.

1. Install the PhpSpreadsheet library

First, you need to download and install the PhpSpreadsheet library. The library can be downloaded on Github or installed using Composer. After the download is complete, unzip the file and place the Phpspreadsheet folder in the root directory of the PHP project.

2. Import Excel table

Before using PhpSpreadsheet to read an Excel table, you need to import the table first. You can use the load method in IOFactory provided by PhpSpreadsheet to import the Excel table. The code is as follows:

use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = IOFactory::load('file.xlsx');

3. Get the data in the Excel table

After importing the Excel table, you can Data is obtained through the methods provided by PhpSpreadsheet. For example, you can get all the data in the table through the following code:

$sheet = $spreadsheet->getActiveSheet();

$dataArray = $sheet->toArray();

The $sheet here represents a worksheet in the Excel table. Use the toArray method to convert all the data in the worksheet into binary. dimensional array. This $dataArray is all the data in the Excel table we imported.

2. Query the data of specific fields in Excel

After reading all the data in the Excel table, you can then query the data. Suppose we need to query data in a specific field. For example, we have a student information table with fields such as "name", "gender", and "age". We now need to query the information of all girls.

1. Get the header

First, you need to get the field name in the table, which is the header. You can get the first row of data through the following code:

$header = $dataArray[0];

2. Get all girl information

Then you can filter the data. According to the field that needs to be queried, such as gender, you can use the following code to obtain the data of the gender field column:

$sexCol = array_search('性别', $header);

$sexData = array_column($dataArray, $sexCol);

The array_search function is used here to find the field name and get the position of the field in the array. Next, use the array_column function to obtain the corresponding column data of this field in the entire Excel table array.

If you need to query the information of all girls, you can use the following code to achieve it:

$girlData = array();

for ($i = 1; $i < sizeof($dataArray); $i++) {
    if ($sexData[$i] === '女') {
        $girlData[] = $dataArray[$i];
    }
}

Here, by traversing all data rows, determine whether the row contains girl information based on the gender field. If so , then save the row data to the $girlData array.

3. Summary

Through this article, we learned how to read Excel tables in PHP, and realized data query for specific fields by using the PhpSpreadsheet library. If you encounter the problem of reading and querying Excel table data during actual development, you can consider using the method introduced in this article to achieve efficient and convenient data operations.

The above is the detailed content of Detailed explanation of PHP reading Excel table and querying data in specific fields. For more information, please follow other related articles on the PHP Chinese website!