How to query data from Excel using PHP

Excel is one of the widely used office software, which contains a large amount of data, such as product price lists, order information tables, etc. In many cases, we need to extract data from Excel for processing in order to conduct subsequent data analysis, data mining and other operations. This article explains how to query data from Excel using PHP.

1. Preparation

First, we need to install the PHPExcel library to read Excel files in PHP. PHPExcel is a PHP class library used to read and write Excel files and supports multiple Excel formats. To install the PHPExcel library, please execute the following command on the command line:

composer require phpoffice/phpexcel

After the installation is complete, introduce the PHPExcel class file into the PHP file:

require 'vendor/autoload.php';

Next, we need to prepare a Excel file, assuming its path is /var/www/excel/data.xlsx, which contains a worksheet sheet1, the data is as shown below:

2. Querying data

After we have the preparation work, we can use the PHPExcel library to query the data. The specific steps are as follows:

1. Instantiate the PHPExcel class:

$objPHPExcel = new PHPExcel();

2. Load the Excel file:

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load('/var/www/excel/data.xlsx');

Among them, Excel2007 means reading files of Excel 2007 and above. , if you need to read other versions of Excel files, please refer to the official documentation.

3. Get the worksheet:

$sheet = $objPHPExcel->getActiveSheet();

4. Get the number of rows and columns:

$rowCount = $sheet->getHighestRow(); //行数
$columnCount = $sheet->getHighestColumn(); //列数
$columnCount = PHPExcel_Cell::columnIndexFromString($columnCount);

Among them, the PHPExcel_Cell::columnIndexFromString() method is used to change the Excel column name Convert to column index to get the number of columns.

5. Traverse the worksheet and obtain data:

$data = array();

for ($rowIndex = 1; $rowIndex getCellByColumnAndRow($columnIndex, $rowIndex)->getValue();
        $data[$rowIndex-1][$columnIndex] = $cellValue;
    }
}

In the above code, we traverse each row and column of the worksheet through nested for loops, and then use the getCellByColumnAndRow() method to obtain The value of the cell and stores it in the $data array. The final $data array structure is as follows:

array(
    array('姓名', '年龄', '性别'),
    array('张三', 20, '男'),
    array('李四', 25, '女'),
    array('王五', 30, '男'),
)

In this way, we can query data from Excel.

3. Sample code

The above are the specific steps for querying data from Excel. The complete sample code is given below for students’ reference:

require 'vendor/autoload.php';

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load('/var/www/excel/data.xlsx');

$sheet = $objPHPExcel->getActiveSheet();

$rowCount = $sheet->getHighestRow();
$columnCount = $sheet->getHighestColumn();
$columnCount = PHPExcel_Cell::columnIndexFromString($columnCount);

$data = array();

for ($rowIndex = 1; $rowIndex getCellByColumnAndRow($columnIndex, $rowIndex)->getValue();
        $data[$rowIndex-1][$columnIndex] = $cellValue;
    }
}

var_dump($data);

The output results are as follows:

array(4) {
  [0]=>
  array(3) {
    [0]=>
    string(6) "姓名"
    [1]=>
    string(6) "年龄"
    [2]=>
    string(6) "性别"
  }
  [1]=>
  array(3) {
    [0]=>
    string(6) "张三"
    [1]=>
    float(20)
    [2]=>
    string(3) "男"
  }
  [2]=>
  array(3) {
    [0]=>
    string(6) "李四"
    [1]=>
    float(25)
    [2]=>
    string(3) "女"
  }
  [3]=>
  array(3) {
    [0]=>
    string(6) "王五"
    [1]=>
    float(30)
    [2]=>
    string(3) "男"
  }
}

4. Summary

This article introduces how to use the PHPExcel library to query data from Excel. The specific steps are: instantiate the PHPExcel class, load the Excel file, obtain the worksheet, obtain the number of rows and Number of columns, traverse the worksheet, and obtain data. Through the introduction of this article, students can master the basic methods of obtaining data from Excel.

The above is the detailed content of How to query data from Excel using PHP. For more information, please follow other related articles on the PHP Chinese website!