PHPExcel读取excel文件2

PHPExcel读取excel文件

PHPExcel是个很强大的类库，以前只使用过它生成Excel文件，非常方便。

今天接到个项目要读取Excel的文件，以前也做过Excel转换写入数据库的工作， 

不过相对简单一些，是转换成CSV格式再进行解析的。

首先下载PHPExcel类库。http://phpexcel.codeplex.com/

包含PHPExcel类库文件，如果不能确定文件类型的话可以使用PHPExcel_IOFactory::identify方法返回文件的类型，传递给该函数一个文件名就可以。

然后根据返回的文件类型创建该类型的读取对象，进行文件的load。

之后就可以进行数据的读取了，
具体代码如下所示：

1. <?php
2.     require_once(‘include/common.inc.php’);
3.     require_once(ROOTPATH . ‘include/phpExcel/PHPExcel/IOFactory.php’);
4.     
5.     $filePath = ‘./file/xls/110713.xls’; 
6.     
7.     $fileType = PHPExcel_IOFactory::identify($filePath); //文件名自动判断文件类型
8.     $objReader = PHPExcel_IOFactory::createReader($fileType);
9.     $objPHPExcel = $objReader->load($filePath);
10.     
11.     $currentSheet = $objPHPExcel->getSheet(0); //第一个工作簿
12.     $allRow = $currentSheet->getHighestRow(); //行数
13.     $output = array();
14.     $preType = ”;
15.     
16.     $qh = $currentSheet->getCell(‘A4′)->getValue();
17.     //按照文件格式从第7行开始循环读取数据
18.     for($currentRow = 7;$currentRow<=$allRow;$currentRow++){ 
19.         //判断每一行的B列是否为有效的序号，如果为空或者小于之前的序号则结束
20.         $xh = (int)$currentSheet->getCell(‘B’.$currentRow)->getValue();
21.         if(empty($xh))break;
22.         
23.         $tmpType = (string)$currentSheet->getCell(‘C’.$currentRow)->getValue(); //赛事类型
24.         if(!empty($tmpType))$preType = $tmpType;
25.         $output[$xh][‘type’] = $preType;
26.         $output[$xh][‘master’] = $currentSheet->getCell(‘F’.$currentRow)->getValue(); //主队
27.         $output[$xh][‘guest’] = $currentSheet->getCell(‘H’.$currentRow)->getValue(); //客队    
28.     }
29.     
30.     //从当前行开始往下循环，取出第一个不为空的行
31.     for( ; ; $currentRow++){
32.         $xh = (int)$currentSheet->getCell(‘B’.$currentRow)->getValue();
33.         if(!empty($xh))break;
34.     }
35.     
36.     for( ; $currentRow <= $allRow; $currentRow++){
37.         $xh = (int)$currentSheet->getCell(‘B’.$currentRow)->getValue();
38.         if(empty($xh))break;
39.         
40.         $output[$xh][‘rq’] = $currentSheet->getCell(‘I’.$currentRow)->getValue();
41.     }
42.     header(“content-type:text/html; charset=utf-8″);
43.     
44.     echo ‘期号：’ . $qh . “\n\n”;
45.     if(!empty($output)){
46.         printf(“%-5s\t%-15s\t%-40s\t%-40s\t%-5s\n”, ‘序号’, ‘赛事类型’, ‘主队’, ‘客队’, ‘让球值’);
47.         foreach($output as $key => $row){
48.             $format = “%-5d\t%-15s\t%-40s\t%-40s\t%-5s\n”;
49.             printf($format, $key, $row[‘type’], $row[‘master’], $row[‘guest’], $row[‘rq’]);
50.         }
51.     }
52. ?>
53. ?

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183

    /* * 将excel转换为数组 by aibhsc * */ require (ROOT_PATH . 'includes/PHPExcel.php' ); //引入PHP EXCEL类 function format_excel2array( $filePath = '' , $sheet =0){          if ( empty ( $filePath ) or ! file_exists ( $filePath )){ die ( 'file not exists' );}          $PHPReader = new PHPExcel_Reader_Excel2007();        //建立reader对象          if (! $PHPReader ->canRead( $filePath )){                  $PHPReader = new PHPExcel_Reader_Excel5();                  if (! $PHPReader ->canRead( $filePath )){                          echo 'no Excel' ;                          return ;                  }          }          $PHPExcel = $PHPReader ->load( $filePath );        //建立excel对象          $currentSheet = $PHPExcel ->getSheet( $sheet );        //**读取excel文件中的指定工作表*/          $allColumn = $currentSheet ->getHighestColumn();        //**取得最大的列号*/          $allRow = $currentSheet ->getHighestRow();        //**取得一共有多少行*/          $data = array ();          for ( $rowIndex =1; $rowIndex <= $allRow ; $rowIndex ++){        //循环读取每个单元格的内容。注意行从1开始，列从A开始                  for ( $colIndex = 'A' ; $colIndex <= $allColumn ; $colIndex ++){                          $addr = $colIndex . $rowIndex ;                          $cell = $currentSheet ->getCell( $addr )->getValue();                          if ( $cell instanceof PHPExcel_RichText){ //富文本转换字符串                                  $cell = $cell ->__toString();                          }                          $data [ $rowIndex ][ $colIndex ] = $cell ;                  }          }          return $data ; }       使用方法：                          $filePath = ROOT_PATH. 'data/diamondStock.xlsx' ;        //钻石库存文件                          $data = format_excel2array( $filePath );                          print_r( $data ); die ;    输出结果示例：    Array (      [1] => Array          (              [A] => 商品编号              [B] => 商品名称              [C] => 总重量              [D] => 进货价格              [E] => 销售价格              [F] => 4C备注          )         [2] => Array          (              [A] => 10001              [B] => GIA-2156685995              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [3] => Array          (              [A] => 10002              [B] => GIA-2156685996              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [4] => Array          (              [A] => 10003              [B] => GIA-2156685997              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [5] => Array          (              [A] => 10004              [B] => GIA-2156685998              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [6] => Array          (              [A] => 10005              [B] => GIA-2156685999              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [7] => Array          (              [A] => 10006              [B] => GIA-2156686000              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [8] => Array          (              [A] => 10007              [B] => GIA-2156686001              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [9] => Array          (              [A] => 10008              [B] => GIA-2156686002              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [10] => Array          (              [A] => 10009              [B] => GIA-2156686003              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [11] => Array          (              [A] => 10010              [B] => GIA-2156686004              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [12] => Array          (              [A] => 10011              [B] => GIA-2156686005              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [13] => Array          (              [A] => 10012              [B] => GIA-2156686006              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )         [14] => Array          (              [A] => 10013              [B] => GIA-2156686007              [C] => 0.7              [D] => 1760              [E] => 1848              [F] => G色、0.7ct、SI1、FR          )    )