phpoffice/phpspreadsheet读取excel表格中的图片并保存到本地

我们开发导入excel表格功能时，会遇到表格中带有图片的情况，怎样获取到表格的图片并且把图片保存到本地，这样就可以展示你导入的数据和图片，实现此功能。这里介绍一下分别使用phpspreadsheet和PHPExcel扩展库来实现读取Excel内图片的功能：表格是这样的。

开始使用

composer require phpoffice/phpspreadsheet

部分代码：


use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;


use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
 
 
// 获取excel表格的图片
public function readexcel1()
{
        $imageFilePath = ROOT_PATH . 'public/uploads/imgs/'; //图片本地存储的路径
        if (!file_exists($imageFilePath)) { //如果目录不存在则递归创建
                mkdir($imageFilePath, 0777, true);
        }
        try {
                $file = ROOT_PATH . 'public/excel/2.xlsx';; //包含图片的Excel文件
                $objRead = IOFactory::createReader('Xlsx');
                $objSpreadsheet = $objRead->load($file);
                $objWorksheet = $objSpreadsheet->getSheet(0);
                $data = $objWorksheet->toArray();
                foreach ($objWorksheet->getDrawingCollection() as $drawing) {
                        list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());
                        $imageFileName = $drawing->getCoordinates() . mt_rand(1000, 9999);
                        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;
                        }
                        $data[$startRow-1][$startColumn] = '/uploads/imgs/' . $imageFileName;
                }
                dump($data);die();
        } catch (\Exception $e) {
                throw $e;
        }
}

输出表格的数据：

array(3) {
          [0] => array(3) {
            [0] => string(6) "编码"
            [1] => string(6) "名称"
            [2] => string(6) "图片"
          }
          [1] => array(4) {
            [0] => string(4) "B101"
            [1] => string(9) "票据夹"
            [2] => NULL
            ["C"] => string(25) "./uploads/imgs/C26829.png"
          }
          [2] => array(4) {
            [0] => string(4) "B102"
            [1] => string(6) "油笔"
            [2] => NULL
            ["C"] => string(25) "./uploads/imgs/C39403.png"
          }
        }

图片在第三列也就是C列，但是这样的数据不方便保存到数据库。所以我们将图片数据重新保存在一个图片数组中，然后再和获取的表格数据合并下，最后得到的数组就可以直接拿来保存到数据库了。

public function readexcel2()
{
        $imageFilePath = ROOT_PATH . 'public/uploads/imgs/'; //图片本地存储的路径
        if (!file_exists($imageFilePath)) { //如果目录不存在则递归创建
                mkdir($imageFilePath, 0777, true);
        }
        try {
                $file = ROOT_PATH . 'public/excel/2.xlsx';; //包含图片的Excel文件
                $objRead = IOFactory::createReader('Xlsx');
                $objSpreadsheet = $objRead->load($file);
                $objWorksheet = $objSpreadsheet->getSheet(0);
                $data = $objWorksheet->toArray();
                $img_data = [];// 图片数组
                foreach ($objWorksheet->getDrawingCollection() as $drawing) {
                        list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());
                        $imageFileName = $drawing->getCoordinates() . mt_rand(1000, 9999);
                        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;
                        }
                        // 单图片上传
                        $img_data[$startRow-1][$startColumn] = '/uploads/imgs/' . $imageFileName;
                        // 多图片上传
                        // $img_data[$startRow-1][$startColumn][] = '/uploads/imgs/' . $imageFileName;
                }
                dump($img_data);
                // 合并表格数据
                if(!empty($img_data)){
                        foreach ($img_data as $key => $value){
                                //$data[$key][2] 是导入表格图片的哪一行
                $data[$key][2] = $value['C'];
                        }
                }
                dump($data);
        } catch (\Exception $e) {
                throw $e;
        }
}

再看看最后输出的数据：

// 图片数组
array(2) {
  [1] => array(1) {
    ["C"] => string(24) "/uploads/imgs/C27788.png"
  }
  [2] => array(1) {
    ["C"] => string(24) "/uploads/imgs/C35897.png"
  }
}
// 合并的表格数据数组
array(3) {
  [0] => array(3) {
    [0] => string(6) "编码"
    [1] => string(6) "名称"
    [2] => string(6) "图片"
  }
  [1] => array(3) {
    [0] => string(4) "B101"
    [1] => string(9) "票据夹"
    [2] => string(24) "/uploads/imgs/C27788.png"
  }
  [2] => array(3) {
    [0] => string(4) "B102"
    [1] => string(6) "油笔"
    [2] => string(24) "/uploads/imgs/C35897.png"
  }
}

@小关CSDN

0 likes