来源：https://blog.csdn.net/qq_34625397/article/details/136225972 前提是已经安装了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; } } ``` 效果：