tp6.0+PhpSpreadsheet+Excel表导入导出带图片方法

前段时间刚好有个项目需要用到，所以在这里记录一下，话不多说直接上代码

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\cell\Coordinate;
/**
 * 提交EXECL表格带图片导入导入数据
 */
public function upload_field_excel_post()
{
 $file_url = $this->request->param('file_url');  //文件路径
 if (!empty($file_url)) {
  $filePath = $_SERVER['DOCUMENT_ROOT'] . '/upload/' . $file_url;
  //如果文件存在
  if (file_exists($filePath)) { //检查文件是否存在
   $exts = pathinfo($filePath, PATHINFO_EXTENSION);  //文件类型
   // 有Xls和Xlsx格式两种
   if ($exts == 'xlsx') {
    $objReader = IOFactory::createReader('Xlsx');
   } else {
    //$objReader = IOFactory::createReader('Xls');
    $info = "请使用'Xlsx'格式文件进行带图片上传,否则图片上传失败！";
    $this->error(['code' => 0, 'msg' => $info]);
   }
   $imageFilePath1 = root_path() . '/public/upload/';//图片保存目录
   $imageFilePath2 = 'admin/images/' . date("Ymd") . '/';
   $imageFilePath = $imageFilePath1 . $imageFilePath2;
   if (!file_exists($imageFilePath)) {
    mkdir("$imageFilePath", 0777, true);
   }
   $objSpreadsheet = $objReader->load($filePath);
   $objWorksheet = $objSpreadsheet->getActiveSheet(0);  //getSheet(0)
   $highestRow = $objWorksheet->getHighestRow(); // 取得总行数
   $data = $objWorksheet->toArray();
   //$drawing 为 PhpOffice\PhpSpreadsheet\Worksheet\Drawing类的实例;   仅仅支持xlsx格式文件
   foreach ($objWorksheet->getDrawingCollection() as $drawing) {
    list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());
    $imageFileName = $drawing->getIndexedFilename(); //获取文件名称
    switch ($drawing->getExtension()) {
     case 'jpg':
     case 'jpeg':
      // $imageFileName .= '.jpg';
      $source = imagecreatefromjpeg($drawing->getPath());
      imagejpeg($source, $imageFilePath . $imageFileName);
      break;
     case 'gif':
      $imageFileName .= '.gif';
      $source = imagecreatefromgif($drawing->getPath());
      imagegif($source, $imageFilePath . $imageFileName);
      break;
     case 'png':
      $imageFileName .= '.png';
      $source = imagecreatefrompng($drawing->getPath());
      imagepng($source, $imageFilePath . $imageFileName);
      break;
    }
    $startColumn = $this->ABC2decimal($startColumn);
    $data[$startRow - 1][$startColumn] = $imageFilePath2 . $imageFileName;
   }
   //从第二行开始
   for ($i = 1; $i <= $highestRow - 1; $i++) {
    $add_data[$i]['name'] = $data[$i][0];
    $add_data[$i]['image'] = $data[$i][1];
   }
   $success_count = Db::name('test')->insertAll($add_data);
   //unlink($filePath);  //删除文件
   //判断导入成功数量
   if ($success_count == $highestRow - 1) {
    $info = '导入成功！本次成功导入数量：' . $success_count . '条';
     $this->success($info);
   } else {
    $info = '导入成功！本次成功导入字段数量：' . $success_count . '条,无效数据或已重复上传' . ($highestRow - 1) - $success_count . '条,与目标数不符！';
    $this->success($info);
   }
  } else {
   $info = "文件不存在,文件上传失败！";
   $this->error(['code' => 0, 'msg' => $info]);
  }
 } else {
  $info = "请上传文件！";
  $this->error(['code' => 0, 'msg' => $info]);
 }
}

/**
 * 字母序列化为数字
 */
public function ABC2decimal($abc)
{
 $ten = 0;
 $len = strlen($abc);
 for ($i = 1; $i <= $len; $i++) {
  $char = substr($abc, 0 - $i, 1);//反向获取单个字符
  $int = ord($char);
  $ten += ($int - 65) * pow(26, $i - 1);
 }
 return $ten;
}




/**
  * 下载数据EXECL表格导出
  */
 public function export()
 {
  $newExcel = new Spreadsheet();  //创建一个新的excel文档
  $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
  //设置宽度为true,不然太窄了
  $newExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);
  $newExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);

  //获取数据库的数据。
  $List = Db::name('user')->select();
  $objSheet->setTitle('班级表');  //设置当前sheet的标题
  //设置第一栏的标题
  $objSheet->setCellValue('A1', 'ID')
   ->setCellValue('B1', '图片');
  foreach ($List as $k => $val) {
   $k = $k + 2;
   $objSheet->setCellValue('A' . $k, $val['id']);    //数据行

   // 获取本地文件夹路径
   $dir = $_SERVER['DOCUMENT_ROOT'] . '/upload/';
   $file_info = pathinfo($val['avatar']);
   // 过滤非文件类型
   if (!empty($file_info['basename'])) {
    $basename = $file_info['basename'];
    // 进行检测文件是否存在
    is_dir($dir) OR mkdir($dir, 0777, true);
    file_put_contents($dir . $basename, $val['avatar']);

    // 引入操作图片类
    $drawing[$k] = new Drawing();
    $drawing[$k]->setName('GoodsCode');
    $drawing[$k]->setDescription('GoodsCode');
    $drawing[$k]->setPath($dir . $val['avatar']);
    $drawing[$k]->setWidth(80);
    $drawing[$k]->setHeight(80);
    $drawing[$k]->setCoordinates('B' . $k);
    $drawing[$k]->setOffsetX(10);
    $drawing[$k]->setOffsetY(10);
    $drawing[$k]->setWorksheet($newExcel->getActiveSheet());
   }

// 每行高度设置
   $objSheet->getRowDimension($k)->setRowHeight(80);
  }
  $this->downloadExcel($newExcel, '表1', 'Xls');
 }


 /**
  * 下载数据EXECL表格导出
  * 'newExcel'   => 'EXECL表格',
  * 'filename' => '文件名称',
  * 'format'=> 文件后缀,
  */
 function downloadExcel($newExcel, $filename, $format)
 {
  // $format只能为 Xlsx 或 Xls
  if ($format == 'Xlsx') {
   header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  } elseif ($format == 'Xls') {
   header('Content-Type: application/vnd.ms-excel');
  }
  header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format));
  header('Cache-Control: max-age=0');
  $objWriter = IOFactory::createWriter($newExcel, $format);
  $objWriter->save('php://output');
  //通过php保存在本地的时候需要用到
  //$objWriter->save($dir.'/demo.xlsx');
  //以下为需要用到IE时候设置
  // If you're serving to IE 9, then the following may be needed
  //header('Cache-Control: max-age=1');
  //If you're serving to IE over SSL, then the following may be needed
  //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
  //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
  //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
  //header('Pragma: public'); // HTTP/1.0
  exit;
 }

本文链接：http://so.lmcjl.com/news/206/