phpspreadsheet导出数据和图片到excel

仅作记录，废话不多说

前提是已经安装了phpspreadsheet  （ composer require phpoffice/phpspreadsheet  ）

一、 数据拼装，调用excel类

<?php
/**
 * 电子台账
 * Date: 2023/4/20
 * Time: 17:28
 */

namespace app\store\controller;
use app\common\controller\Excel;
use app\common\model\ModelRedis;
use app\store\model\ModelSaleStoreInput;


class Ledger extends \app\ApiCommon
{
    /**
     * 导出数据拼装
     * @param $ids string 出库id 列表  1,2,3,4
     */
    public function export_input($ids,$start,$end){
        $where = [];
        if(!empty($start) && !empty($end)){
            $where[] = ['ctime','>=',strtotime($start)];
            $where[] = ['ctime','<=',strtotime($end)];
        }else{
            $where[] = ['id','in',$ids];
        }
        $list = ModelSaleStoreInput::where($where)->field($field)->select()->toArray();
        if(empty($list)){
            return $this->apiError('数据不存在');
        }
        
        $data = $list;
        //此处说明：解决数字太长尾数变000的问题
        //由于数字超过15位，会被显示成0或者加小数点处理。造成这种情况是由于Excel内置的数值有效范围是15位。超过15位，如果要显示的话，就需要转换成非数字格式。比如文本格式。
        foreach ($data as $key => $value) {
            $tmp = [];
            $explode_no = self::decode_explode_no($value['explode_no']);
            array_push($tmp,"\t".$value['id']."\t");
            array_push($tmp,"\t".$value['ctime']."\t");
            array_push($tmp,"\t".$value['store_name']."\t");
            array_push($tmp,"\t".$value['name']."\t");
            array_push($tmp,"\t".$value['spec_name']."\t");
            array_push($tmp,"\t".$value['amount']."\t");
            array_push($tmp,"\t".$explode_no."\t");
            array_push($tmp,"\t".$value['supply_company']."\t");
            array_push($tmp,"\t".$value['deliver']."\t");            
            array_push($tmp,"\t".self::desensitizedIdCard($value['deliver_idcard'])."\t");
            array_push($tmp,"\t".$value['carry_no']."\t");
            array_push($tmp,"\t".$value['buy_no']."\t");
            array_push($tmp,"\t".$value['storeman_names']."\t");
            array_push($tmp,"\t".$value['storeman_signs']."\t");
            array_push($tmp,"\t".$value['safety_name']."\t");
            array_push($tmp,"\t".$value['safety_sign']."\t");
            $data[$key] = $tmp;
        }
        //保存到本地临时目录
        $path = './uploads/tmp/';
        $excel_name = '数据盘点';
        $title = ['id','时间','仓库名','物品名称','品种规格','物品数量','编号','供货单位','送货人','身份证号','运输证号','购买证号','库管员','库管员签字','安全员','安全员签字'];
        $file_name = date('Y-m-d').rand(1000,9999).$excel_name.'.xlsx';
        $res = Excel::export($title,$data,$path,$file_name);
        return $res;
    }
}

二、封装好的Excel类：注意远程图片必须base64后缓存到本地再写入excel。

<?php
/**
 * excel导入导出公共类
 * Date: 2023/4/27
 * Time: 18:39
 */

namespace app\common\controller;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use think\exception\ValidateException;
use think\facade\Filesystem;

class Excel
{
     /**
     * 数据导出Excel
     * @param array $title 表头
     * @param array $data 数据源
     * @param string $path 目录
     * @param string $file_name 文件名称
     * @return array
     */
    public static function export($title = [], $data = [],$path,$file_name="")
    {
        try{
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            // 表头单元格内容 第一行
            $titCol = 'A';
            foreach ($title as $value) {
                // 单元格内容写入
                $sheet->setCellValue($titCol . '1', $value);
                $titCol++;
            }
            //单元格内容居中
            $sheet->getDefaultRowDimension()->setRowHeight(60);//默认行高60
            $sheet->getDefaultColumnDimension()->setAutoSize(true);//列宽自适应
            $sheet->getStyle('A:'.$titCol)->getAlignment()->setVertical('center');//内容容垂居中
            $sheet->getStyle('A:'.$titCol)->getAlignment()->setHorizontal('center');//内水平直居中
            // 从第二行开始写入数据
            $row = 2;
            foreach ($data as $item) {
                $dataCol = 'A';
                foreach ($item as $value) {
                    // 单元格内容写入
                    $values = explode(".",$value);
                    $ext = trim(end($values));
                    if(in_array($ext,['jpg','png','jpeg'])){ //多图导出
                        $num = 10;
                        $images = explode(',', $value);
                        foreach ($images as $k => $v) {
                            $drawings[$k] = new Drawing();
                            $img = self::img_resource(trim($v),$path,$k);
                            //图片路径,项目目录下就行
                            $drawings[$k]->setResizeProportional(false);
                            $drawings[$k]->setName('手动签名图片');
                            $drawings[$k]->setDescription('手动签名图片展示');
                            $drawings[$k]->setPath($img,true);
                            $drawings[$k]->setWidth(60);
                            $drawings[$k]->setHeight(60);
                            $drawings[$k]->setOffsetX($num);
                            $drawings[$k]->setOffsetY(10);
                            $drawings[$k]->setCoordinates($dataCol . $row);
                            $drawings[$k]->setWorksheet($sheet);
                            $num = $num + 70; // 增加每张图之间的间距
                        }
                    }else{
                        $sheet->setCellValue($dataCol . $row, $value);
                    }
                    //自适应列宽
                    $len = strlen($value);
                    if($len>1){ //空数据不做处理避免影响到有数据的列宽度
                        $sheet->getColumnDimension($dataCol)->setWidth($len);
                    }
                    $dataCol++;
                }
                $row++;
            }
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            if(is_file($path.$file_name)){
                file_put_contents($path.$file_name,'');;
            }
            $writer->save($path.$file_name);
            return ['code'=>0,'url'=>trim($path.$file_name,'.')];
        }catch (\Exception $e){
            return ['code'=>1,'msg'=>$e->getMessage()];
        }
    }

    /**
     * 图片缓存到本地
     * @param $url string 远程图片地址
     * @param $dir string 本地缓存目录
     * @param $i int 图片序号
     * @return mixed
     */
    public static function img_resource($url,$dir,$i){
        $data = file_get_contents($url);
        $path = $dir.time().'-'.$i;
        file_put_contents($path, $data);
        return $path;
    }
}