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;}
}