读取EXCEL 

 1 /**
 2  * 读取excel
 3  * @param $filename
 4  * @param int $sheet
 5  * @return array
 6  */
 7 function readExcel($filename,$sheet=0)
 8 {
 9     //导入PHPExcel
10     vendor("PHPExcel.PHPExcel");
11     //$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
12     //$cacheSettings = array('memoryCacheSize'=>'100MB');
13     //\PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
14     $filePath = $filename;
15     $data = array();
16     if (strpos($filename, '.csv')) {
17         $objReader = \PHPExcel_IOFactory::createReader('CSV')
18             ->setDelimiter(',')
19             ->setInputEncoding('gb2312') //不设置将导致中文列内容返回boolean(false)或乱码
20             ->setEnclosure('"');
21         $PHPExcel = $objReader->load($filename);
22     } else {
23         $PHPExcel = \PHPExcel_IOFactory::load($filename);
24     }
25 
26     if ($sheet) {
27         $currentSheet = $PHPExcel->getSheet($sheet);
28     } else {
29         $currentSheet = $PHPExcel->getActiveSheet();//当前工作表
30     }
31 
32     $columns = $currentSheet->getHighestColumn(); // 总列数
33     $columns++;
34     $rows = $currentSheet->getHighestRow(); // 总行数
35 
36     //\PHPExcel_Cell::columnIndexFromString($highestColumn); //字母列转换为数字列 如:AA变为26
37     //$cell = $currentSheet->getCellByColumnAndRow(数字列, 数字行)->getValue();
38     //$columnName = \PHPExcel_Cell::stringFromColumnIndex(27);
39     $data = array();
40     //循环读取每个单元格的内容。注意行从1开始，列从A开始
41     for($row=1;$row<=$rows;$row++){
42         for($column='A';$column!=$columns;$column++){
43             //$cell = \PHPExcel_Shared_Date::ExcelToPHP($currentSheet->getCell($column.$row)->getValue())-8*3600;//把中国时间当做格林威治时间来转时间戳,所以+8时区
44             $cell = $currentSheet->getCell($column.$row)->getValue();
45             if($cell instanceof \PHPExcel_RichText) {//富文本转换字符串
46                 $cell = $cell->__toString();
47             }
48             $data[$row][]=$cell;
49             if($column == $columns) {
50                 break;
51             }
52         }
53     }
54     return $data;
55 }

 

posted @ 2017-12-29 15:47  个人小站  阅读(257)  评论(0)    收藏  举报