PHPexcel之读取表格(三)

一,其实针对文件的读取,PHPExcel提供了很多格式的文件,可以看下底层封装,支持各种常用和不常用的文件类型支持。,针对不同的类型都有相应的文件处理类 PHPExcel_Reader_xxxx.php

  

 1 public static function createReaderForFile($pFilename)
 2     {
 3         // First, lucky guess by inspecting file extension
 4         $pathinfo = pathinfo($pFilename);
 5 
 6         $extensionType = null;
 7         if (isset($pathinfo['extension'])) {
 8             switch (strtolower($pathinfo['extension'])) {
 9                 case 'xlsx':            //    Excel (OfficeOpenXML) Spreadsheet
10                 case 'xlsm':            //    Excel (OfficeOpenXML) Macro Spreadsheet (macros will be discarded)
11                 case 'xltx':            //    Excel (OfficeOpenXML) Template
12                 case 'xltm':            //    Excel (OfficeOpenXML) Macro Template (macros will be discarded)
13                     $extensionType = 'Excel2007';
14                     break;
15                 case 'xls':                //    Excel (BIFF) Spreadsheet
16                 case 'xlt':                //    Excel (BIFF) Template
17                     $extensionType = 'Excel5';
18                     break;
19                 case 'ods':                //    Open/Libre Offic Calc
20                 case 'ots':                //    Open/Libre Offic Calc Template
21                     $extensionType = 'OOCalc';
22                     break;
23                 case 'slk':
24                     $extensionType = 'SYLK';
25                     break;
26                 case 'xml':                //    Excel 2003 SpreadSheetML
27                     $extensionType = 'Excel2003XML';
28                     break;
29                 case 'gnumeric':
30                     $extensionType = 'Gnumeric';
31                     break;
32                 case 'htm':
33                 case 'html':
34                     $extensionType = 'HTML';
35                     break;
36                 case 'csv':
37                     // Do nothing
38                     // We must not try to use CSV reader since it loads
39                     // all files including Excel files etc.
40                     break;
41                 default:
42                     break;
43             }
44 
45             if ($extensionType !== null) {
46                 $reader = self::createReader($extensionType);
47                 // Let's see if we are lucky
48                 if (isset($reader) && $reader->canRead($pFilename)) {
49                     return $reader;
50                 }
51             }
52         }

 

二,以xlsx为例,读取单个表格只有一个sheet

  

 1 require_once dirname(__FILE__) . '/PHPExcel/Classes/PHPExcel/IOFactory.php';
 2 
 3 $objReader = PHPExcel_IOFactory::createReader('Excel2007');
 4 $objPHPExcel = $objReader->load("test.xlsx");        //返回PHPExcel对象
 5 $activeSheet = $objPHPExcel->getActiveSheet();
 6 $data = $activeSheet->toArray();
 7 print_r($data);
 8 
 9 //针对data进行业务处理
10 .......

 

三,针对多个sheet,PHPExcel也提供了迭代读取的办法

  

//针对多个sheet的读取
foreach($objPHPExcel->getWorksheetIterator() as $workSheet){
       //可以直接读取整个sheet的结果
       $data = $workSheet->toArray();
        //业务处理
       ........
    
    /*
   * 按行循环迭代 每一行,然后再每一个列 
  echo 'Worksheet - ' , $workSheet->getTitle() , PHP_EOL; foreach($workSheet->getRowIterator() as $row){ echo ' Row number - ' , $row->getRowIndex() , PHP_EOL; $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); foreach ($cellIterator as $cell) { if (!is_null($cell)) { echo ' Cell - ' , $cell->getCoordinate() , ' - ' , $cell->getCalculatedValue() , PHP_EOL; } } }
  */

  /** 按列循环,一列一旬
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    echo 'Worksheet - ' , $worksheet->getTitle() , EOL;

    foreach ($worksheet->getColumnIterator() as $column) {
        echo '    Column index - ' , $column->getColumnIndex() , EOL;

        $cellIterator = $column->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(true); // Loop all cells, even if it is not set
        foreach ($cellIterator as $cell) {
            if (!is_null($cell)) {
                echo '        Cell - ' , $cell->getCoordinate() , ' - ' , $cell->getCalculatedValue() , EOL;
            }
        }
    }
}
*
}

 

上一篇:ThinkPHP3.2导出Excel表格


下一篇:PHPExcel编写Excel并保存到服务器吗?