TP5 实现Excle导入导出 带图片
首先确保框架已下载 PHPExcle类,若没有如下操作
Git下载:https://github.com/PHPOffice/PHPExcel
下载好的phpExcle类 放到 vender 下
导出带图片
PHP代码:
1 public function excelPhoto() 2 { 3 $res = Db::table('text')->select(); 4 vendor("PHPExcel.Classes.PHPExcel"); 5 $resultPHPExcel = new \PHPExcel(); 6 //设置参数 7 //设值 8 $resultPHPExcel->getActiveSheet()->setCellValue('A1', 'id'); 9 $resultPHPExcel->getActiveSheet()->setCellValue('B1', '描述'); 10 $resultPHPExcel->getActiveSheet()->setCellValue('C1', '图片'); 11 $i = 2; 12 foreach ($res as $item) { 13 $resultPHPExcel->getActiveSheet()->setCellValue('A' . $i, $item['id']); 14 $resultPHPExcel->getActiveSheet()->setCellValue('B' . $i, $item['desn']); 15 $objDrawing = new \PHPExcel_Worksheet_Drawing(); 16 $objDrawing->setPath('./uploads/' . $item['pic']);//这里拼接 . 是因为要在根目录下获取 17 // 设置宽度高度 18 $objDrawing->setHeight(50);//照片高度 19 $objDrawing->setWidth(50); //照片宽度 20 /*设置图片要插入的单元格*/ 21 $objDrawing->setCoordinates('C' . $i); 22 // 图片偏移距离 23 $objDrawing->setOffsetX(0); 24 $objDrawing->setOffsetY(0); 25 $objDrawing->setWorksheet($resultPHPExcel->getActiveSheet()); 26 $i++; 27 } 28 //设置导出文件名 29 30 $outputFileName = 'total.xls'; 31 32 $xlsWriter = new \PHPExcel_Writer_Excel5($resultPHPExcel); 33 34 ob_end_clean(); 35 header("Content-Type: application/force-download"); 36 37 header("Content-Type: application/octet-stream"); 38 39 header("Content-Type: application/download"); 40 41 header('Content-Disposition:inline;filename="' . $outputFileName . '"'); 42 43 header("Content-Transfer-Encoding: binary"); 44 45 header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); 46 47 header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 48 49 header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 50 51 header("Pragma: no-cache"); 52 53 $xlsWriter->save("php://output"); 54 }
导入带图片:
1 public function importPhoto(Request $request) 2 { 3 4 vendor('PHPExcel.PHPExcel'); 5 vendor('PHPExcel.PHPExcel.PHPExcel_IOFactory'); 6 vendor('PHPExcel.PHPExcel.PHPExcel_Cell'); 7 8 //从前台获取excel文件 9 $file = request()->file('excel'); 10 11 //上传该文件 12 $path = ROOT_PATH . 'public' . DS . 'excel'.DS; 13 $info = $file->move(ROOT_PATH . 'public' . DS . 'excel');//上传位置 14 if($info){ 15 echo '文件上传成功'.'<br/>'; 16 } 17 else{ 18 echo '文件上传失败'.'<br/>'; 19 } 20 21 //上传后的EXCEL路径 22 $file_path = $path . $info->getSaveName(); 23 //图片配置****************** 24 //设置一个存放图片的路径 25 $imgPath = ROOT_PATH . 'public' . DS .'images'.DS; 26 if(!file_exists($imgPath)){ 27 mkdir($imgPath); 28 } 29 30 //获取文件后缀:xls、xlsx等 31 $extension = strtolower(pathinfo($file_path, PATHINFO_EXTENSION) ); 32 //加上这个判断的目的就是防止报错,但目前只支持Excel5 33 if($extension =='xls'){ 34 $objReader = \PHPExcel_IOFactory::createReader('Excel5'); 35 }else{ 36 $objReader = \PHPExcel_IOFactory::createReader('excel2007'); 37 } 38 39 //载入文件 40 $objPHPExcel = $objReader->load($file_path); 41 foreach ($objPHPExcel->getSheet(0)->getDrawingCollection() as $k => $drawing) { 42 $filename = $drawing->getIndexedFilename(); //文件名 43 44 //如果多张图片,就存入数组 45 $filenames[]= '/images/'.$filename; 46 ob_start(); 47 call_user_func( 48 $drawing->getRenderingFunction(), 49 $drawing->getImageResource() 50 ); 51 $imageContents = ob_get_contents(); 52 file_put_contents($imgPath.$filename,$imageContents); //把图片保存到本地(上方自定义的路径) 53 ob_end_clean(); 54 } 55 //图片配置****************** 56 // 57 // halt($filenames); 58 foreach($objPHPExcel->getSheet(0)->getDrawingCollection() as $img) { 59 list($startColumn,$startRow)= \PHPExcel_Cell::coordinateFromString($img->getCoordinates());//获取图片所在行和列 60 $imageFileName = $img->getCoordinates() . mt_rand(100, 999); 61 $imageFileName.='.'.substr($img->getMimeType(),6); 62 imagepng($img->getImageResource(),$imgPath.$imageFileName); 63 $startColumn = $this->ABC2decimal($startColumn);//由于图片所在位置的列号为字母,转化为数字 64 $data[$startRow-1][$startColumn]=$imgPath.$imageFileName;//把图片插入到数组中 65 66 } 67 ksort($data); 68 halt($data); 69 70 } 71 function ABC2decimal($abc){ 72 $ten = 0; 73 $len = strlen($abc); 74 for($i=1;$i<=$len;$i++){ 75 $char = substr($abc,0-$i,1);//反向获取单个字符 76 77 $int = ord($char); 78 $ten += ($int-65)*pow(26,$i-1); 79 } 80 return $ten; 81 }