How to read the contents of the table using PHPExcel

How to read the contents of the table using PHPExcel? Without further ado, the following article will give you a detailed introduction to the method of reading the contents of the table in PHPExcel.

First introduce the class IOFactory.php

require_once '../PHPExcel/IOFactory.php';
$filePath = "test.xlsx";  // 测试文件

Load the test file

1. ##$inputFileType = PHPExcel_IOFactory::identify($filePath ) Determine the file type

2. $objReader = PHPExcel_IOFactory::createReader($inputFileType); Instantiate the type object

3. $objPHPExcel = $objReader- >load($filePath); Load file

The following mainly determines the Excel2007 and Excel5 types, namely xlsx/xlsm/xltx/xltm and xls/xlt format files

try {
    $inputFileType = PHPExcel_IOFactory::identify($filePath);
    if ($inputFileType !== "Excel5" && $inputFileType !== "Excel2007" ) {
        unlink($filePath) && str_alert(-1,"请确保导入的文件格式正确!");
    }
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($filePath);
} catch(Exception $e) {
    unlink($filePath) && str_alert(-1,'加载文件发生错误：”'.pathinfo($filePath,PATHINFO_BASENAME).'”: '.$e->getMessage());
    }

Get the current worksheet

$sheet = $objPHPExcel->getSheet(0);
//或者
$sheet = $objPHPExcel->getActiveSheet();

Get the number of rows and columns of the worksheet

$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();

Note: Sometimes you will find that your table clearly has only 5 rows of content, but you get The $highestRow has 7, 8 or more rows. This may be because you accidentally clicked on other rows when operating your form. Although the content is not filled in, getHighestRow can also identify the row number;

I think To get the number of rows with content, you should use getHighestDataRow and getHighestDataColumn
The source code comments introduce the string Highest row number that contains data, that is, the highest row number of the string containing data.

Specific content of the cell

xlsx type table cells are obtained by similar xy-axis coordinates,

can obtain the content of the corresponding position through similar

 $sheet->getCell("A1")->getValue();
 $sheet->getCell("B2")->getValue();

,

If you don’t want to use letters To traverse and obtain, you can use the numerical index method

  $sheet->getCellByColumnAndRow(0,1);
  $sheet->getCellByColumnAndRow(1,2);

Note that the first parameter in the coordinates starts from 0, 0 represents A, 1 represents B..., and the second parameter starts from 1.

The following is to traverse the table to obtain all cell contents:

$dataSet=array();
for ($column = "A"; $column <= $highestColumn; $column++) {//列数是以A列开始
    for ($row = 4; $row <= $highestRow; $row++) { //行数是以第4行开始
        $cell = $sheet->getCell($column . $row)->getValue();
        if($cell instanceof PHPExcel_RichText) { //富文本转换字符串
            $cell = $cell->__toString();
        }
        $dataSet[$row][] = $cell;
    }
}

Among them, the rich text conversion string is to use $cell instanceof PHPExcel_RichText to determine whether it is rich text. After consulting the data, we found that if the string in your cell contains two The above font will be automatically set to rich text. At this time, __toString() conversion is required

To determine whether the merged cell is located in the upper left corner

When we loop through all cells and output them, we find that, Some merged cells only have content at the upper left coordinate, and the others are null

For example, if A4 and A5 are merged into one cell, getCell("A4") has normal content, but getCell("A5" ") is null.
isMergeRangeValueCell can be used to determine whether a specific cell is the upper left corner

$sheet->getCell('A' . $row)->isMergeRangeValueCell()

When $row is 4, it returns true, and when it is 5, it returns false

Convert time

To obtain the content of the time format in the table, PHPExcel_Shared_Date::ExcelToPHP() is required to convert it to a time format that can be recognized by PHP

date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($sometime);

Related recommendations:

Example of phpexcel reading excel table time                                                                            

PHPExcel correctly reading the excel table time cell

The above is the detailed content of How to read the contents of the table using PHPExcel. For more information, please follow other related articles on the PHP Chinese website!