使用PHPExcel类导入excel表格数据,表中带图片导入
public function importPost(){
// 上传成功后的excel文件路径
$file = $this->request->param('video_url');
if (!$file) {
$this->error('上传文件不能为空');
}
// 文件的本地路径
$inputFileName = ROOT_PATH .'upload' .DS . $file;
if (!is_file($inputFileName)) {
$this->error('文件不存在');
}
//实例化reader
$ext = pathinfo($inputFileName, PATHINFO_EXTENSION);
if (!in_array($ext, ['xls'])) {
$this->error('文件类型不正确');
}
// 引用PHPExcel类
import('PHPExcel.PHPExcel', EXTEND_PATH);
$PHPExcel = new \PHPExcel();
$extension = strtolower( pathinfo($inputFileName, PATHINFO_EXTENSION));
if($extension =='xlsx'){
$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
}else if($extension =='xls'){
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
}
$obj_PHPExcel =$objReader->load($inputFileName, $encode = 'utf-8'); //加载文件内容,编码utf-8
$a = $obj_PHPExcel->getsheet(0);
$imgData=array();
$qr_url = 'portal/'.date('Ymd').'/';
$imageFilePath='upload/'.$qr_url;//图片保存目录
foreach($a->getDrawingCollection() as $img){
list ($startColumn, $startRow) = \PHPExcel_Cell::coordinateFromString($img->getCoordinates());//获取列与行号
$imageFileName=date('Ymd').$startRow.time();
/*表格解析后图片会以资源形式保存在对象中,可以通过getImageResource函数直接获取图片资源然后写入本地文件中*/
switch ($img->getMimeType()){//处理图片格式
case 'image/jpg':
case 'image/jpeg':
$imageFileName.='.jpg';
imagejpeg($img->getImageResource(),$imageFilePath.$imageFileName);
break;
case 'image/gif':
$imageFileName.='.gif';
imagegif($img->getImageResource(),$imageFilePath.$imageFileName);
break;
case 'image/png':
$imageFileName.='.png';
imagepng($img->getImageResource(),$imageFilePath.$imageFileName);
break;
}
$data[$startRow-2]['more']['thumbnail']= $qr_url.$imageFileName;//追加到数组中去
}
$excel_array=$obj_PHPExcel->getsheet(0)->toArray(); //转换为数组格式
array_shift($excel_array); //删除第一个数组(标题);
$i=0;
$portalPostModel = new PortalPostModel();
$published_time = date('Y-m-d');
$end_time = date('Y-m-d', strTotime('+7 day'));
foreach($excel_array as $k=>$v) {
$data[$k]['post_title'] = $v[0];
$data[$k]['qr_number'] = $v[1];
$data[$k]['post_excerpt'] = $v[2];
$data[$k]['page_keywords'] = $v[3];
$data[$k]['qr_type'] = $v[4];
}
// $data处理好的数据,然后使用thinkphp 批量插入
$this->success("导入完成", '');
}