phpExcel reads excel files-PHP Tutorial-php.cn

phpExcel reads excel files

巴扎黑

Release： 2016-11-22 16:56:32

Original

2266 people have browsed it

The previous blog post has given a brief summary of the excel file exported by PHPExcel. Now I will make the following summary of how to read excel. (If the amount of data is not very large, you can use the web to read it directly using this method. If the amount of data is very large, it is recommended that the web only performs the upload function, and the reading and processing should be left in the background. PHPExcel is still relatively time-consuming. , memory.)

Example code:

//首先导入PHPExcel
require_once &#39;PHPExcel.php&#39;;
$filePath = "test.xlsx";
//建立reader对象
$PHPReader = new PHPExcel_Reader_Excel2007();
if(!$PHPReader->canRead($filePath)){
    $PHPReader = new PHPExcel_Reader_Excel5();
    if(!$PHPReader->canRead($filePath)){
        echo &#39;no Excel&#39;;
        return ;
    }
}
//建立excel对象，此时你即可以通过excel对象读取文件，也可以通过它写入文件
$PHPExcel = $PHPReader->load($filePath);
/**读取excel文件中的第一个工作表*/
$currentSheet = $PHPExcel->getSheet(0);
/**取得最大的列号*/
$allColumn = $currentSheet->getHighestColumn();
/**取得一共有多少行*/
$allRow = $currentSheet->getHighestRow();
//循环读取每个单元格的内容。注意行从1开始，列从A开始
for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){
    for($colIndex=&#39;A&#39;;$colIndex<=$allColumn;$colIndex++){
        $addr = $colIndex.$rowIndex;
$cell = $currentSheet->getCell($addr)->getValue();
        if($cell instanceof PHPExcel_RichText)     //富文本转换字符串
$cell = $cell->__toString();
             
        echo $cell;
     
    }
}

Copy after login

What needs to be explained here is the "rich text conversion string" in the above comment.

When PHPExcel reads an EXCEl file, if the content in the cell has two fonts, it will read a rich text object:

For example: there is content in the cell: "Test 1", the first half of which is " The font of "Test" is Song Dynasty, and the font of "1" in the second half is Calibri. At this time, the value of the cell is obtained through

$cell = $sheet->getCell($addr)->getValue();

. And print:

PHPExcel_RichText Object(
[_richTextElements:private] => Array
 (
           [0] => PHPExcel_RichText_TextElement Object ([_text:private] => 测试)
                     [1] => PHPExcel_RichText_Run Object
                     (
                     [_font:private] => PHPExcel_Style_Font Object
                               (
                                [_name:private] => Calibri
                                        [_size:private] => 11
                                        [_bold:private] => 
                                        [_italic:private] => 
                                        [_superScript:private] => 
                                        [_subScript:private] => 
                                        [_underline:private] => none
                                        [_strikethrough:private] => 
                                        [_color:private] => PHPExcel_Style_Color Object
                                        (
                                            [_argb:private] => FF000000
                                            [_isSupervisor:private] => 
                                            [_parent:private] => 
                                            [_parentPropertyName:private] => 
                                        )
                                        [_parentPropertyName:private] => 
                                        [_isSupervisor:private] => 
                                        [_parent:private] => 
                                        [colorIndex] => 8
                                    )
                                    [_text:private] => 1
                                )
                        )
                )

Copy after login

You can see that the text content of the cell cannot be read directly for such a cell. (Note: The rich text here is my own translation, I don’t know if it is correct).

In addition, the functions for reading cells are:

//Columns start from 0, rows start from 1

$currentSheet ->getCellByColumnAndRow($colIndex,$rowIndex)->getValue();