Home > Article > Backend Development > 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
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();
$highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn();
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.
$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 requiredTo determine whether the merged cell is located in the upper left cornerWhen 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 timeTo 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 cellThe 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!