项目中需要用到导入excel文件的功能,网上找了找相关的插件,先找的一个很简易的,结果下下来连demo都跑不起来。又选了phpexcel,看网上介绍,功能相当强大。下下来一看,确实如此,不过demo有几十个,看起来眼花缭乱。项目中主要需要用到的是导入功能,找了半天也没找着导入的demo,后来上网搜,倒腾了一天,功能已经完全实现了。下面介绍下用phpexcel导入excel的用法:
$check_file = pathinfo($_FILES['excel']['name'], PATHINFO_EXTENSION); //获取上传文件类型
require_once _ROOT_ . '/libs/phpexcel/PHPExcel.php'; //引入phpexcel类库
if ($check_file == 'xls') { //根据文件类型选择要实例化的类
$Obj = new PHPExcel_Reader_Excel5();
$Obj->setReadDataOnly(true);
}
if ($check_file == 'xlsx') {
$Obj = new PHPExcel_Reader_Excel2007();
$Obj->setReadDataOnly(true);
}
if ($check_file == 'csv') {
$Obj = new PHPExcel_Reader_CSV();
}
$phpExcel = $Obj->load($_FILES['excel']['tmp_name']); //读取文件
$objWorksheet = $phpExcel->getActiveSheet(); //读取数据
$highestRow = $objWorksheet->getHighestRow(); //有多少列
$highestColumn = $objWorksheet->getHighestColumn(); //有多少行
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //
$data = array();
for($row = 1; $row <= $highestRow; ++$row) {
$excel = new ImportExcel();
for($col = 0; $col < $highestColumnIndex; ++$col) {
$val = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); //读取单元格内容
$excel->memberId(memberid());
if($col == 0){
$excel->name(trim($val));
}
if($col == 1){
$excel->phone(trim($val));
}
if($col == 2){
$excel->email(trim($val));
}
}
$data[] = $excel; //读取到数组内
}
$return = $excel->batchInsert($data); //插入数据库