php技术博客

记录php相关学习知识及开发示例、疑难问题等

[原创] 使用php代码读取表格方法(一)PHPExcel类使用

读取表格这个开发中经常遇到的事情,下面来介绍下使用PHPExcel来读取表格数据的例子,这里主要写下php处理表格那块的代码

<?php
$excel_file='./demo.xlsx'; //表格文件
$ext=strtolower(pathinfo($excel_file,PATHINFO_EXTENSION));
if(!in_array($ext,array('xls','xlsx'))){
    echo '表格格式不正确,请先转换为xls或xlsx格式';
    die;
}
if(!file_exists($excel_file)){
    echo '表格文件不存在!';die;
}

require './vendor/phpoffice/phpexcel/Classes/PHPExcel.php';//根据实际PHPExcel路径引用

if($ext=='xls'){
    $reader = \PHPExcel_IOFactory::createReader('Excel5'); //设置以Excel5格式(Excel97-2003工作簿)
}else{
    $reader = new \PHPExcel_Reader_Excel2007();
}

$PHPExcel = $reader->load($excel_file); // 载入excel文件

$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数,类似 B、AA这种格式
$colsArr=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK','DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ','EA','EB','EC','ED','EE','EF','EG','EH','EI','EJ','EK','EL','EM','EN','EO','EP','EQ','ER','ES','ET','EU','EV','EW','EX','EY','EZ','FA','FB','FC','FD','FE','FF','FG','FH','FI','FJ','FK','FL','FM','FN','FO','FP','FQ','FR','FS','FT','FU','FV','FW','FX','FY','FZ','GA','GB','GC','GD','GE','GF','GG','GH','GI','GJ','GK','GL','GM','GN','GO','GP','GQ','GR','GS','GT','GU','GV','GW','GX','GY','GZ','HA','HB','HC','HD','HE','HF','HG','HH','HI','HJ','HK','HL','HM','HN','HO','HP','HQ','HR','HS','HT','HU','HV','HW','HX','HY','HZ','IA','IB','IC','ID','IE','IF','IG','IH','II','IJ','IK','IL','IM','IN','IO','IP','IQ','IR','IS','IT','IU','IV');
$highestColumm_num=array_search($highestColumm,$colsArr);//将列数字符转换为对应的数字

$dataset=array();
//循环读取每个单元格的数据
for ($row = 1; $row <= $highestRow; $row++){//行数是以第1行开始
    for ($j=0; $j <= $highestColumm_num; $j++) {//列数是以A列开始
        $curval=@trim($sheet->getCell($colsArr[$j].$row)->getValue());
        $dataset[$row][$j]=$curval;
    }
}

var_dump($dataset);


版权声明:本站原创文章,转载请注明出处:php技术博客,否则将追究法律责任