Excel模板填充：从minio上获取模板使用easyExcel填充

最近工作中有个excel导出的功能，要求导出的模板和客户提供的模板一致，而客户提供的模板有着复杂的表头和独特列表风格，像以往使用poi去画是非常耗时间的，比如需要考虑字体大小，单元格合并，单元格的格式等问题；

所有给大家推荐使用easyExcel填充，我们只需要把客户的模板进行简单处理，上传到文件服务器，需要导出的的时候，就从文件服务器拿模板进行填充返回前端，这样非常便捷和效率高。

easyExcel官方文档：填充Excel | Easy Excel

?一、流程展示

我准备了一个简单的模板进行测试

1.1 模板准备

1.2 模板文件上传到minio

1.3?填充后的文件

?

?二、关键代码

1.1 引入依赖minio、easyExcel

            <dependency>
                <groupId>io.minio</groupId>
                <artifactId>minio</artifactId>
                <version>7.1.0</version>
            </dependency>

            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.1.0</version>
            </dependency>

?1.2 minio配置及工具类准备

?1.2.1 minio yml文件配置

minio:
  endpoint: http://xxx.xxx.xxx.xxx:xxx
  accesskey: xxx
  secretKey: xxx
  bucketName: xxx
  expires: 3

?1.2.2?minio 配置类

package com.ruoyi.common.config;


import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.MinioUtils;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;

/**
 * @author qujingye
 */
@Data
@Configuration
@Component
@ConfigurationProperties(prefix = "minio")
public class MinioConfig {

    /**
     * 地址
     */
    private String endpoint;

    /**
     * 桶的名字
     */
    private static String bucketName;

    /**
     * 访问key
     */
    private String accessKey;

    /**
     * 秘钥key
     */
    private String secretKey;

    /**
     * 临时文件的过期时间(天)
     */
    private static Integer expires;

    @Bean
    public MinioUtils creatMinioClient() {
        return new MinioUtils(endpoint, bucketName, accessKey, secretKey);
    }

    /**
     * 临时文件的过期时间
     *
     * @param expires Xml内容
     */
    public void setExpires(Integer expires) {
        MinioConfig.expires = DateUtils.getDaySecond(expires);
    }

    public static Integer getExpires() {
        return expires;
    }

    public static String getBucketName() {
        return bucketName;
    }

    public void setBucketName(String bucketName) {
        MinioConfig.bucketName = bucketName;
    }
}

?1.2.3?minio 工具类?

package com.ruoyi.common.utils;


import com.alibaba.fastjson2.JSONObject;
import com.ruoyi.common.exception.file.FileNameLengthLimitExceededException;
import com.ruoyi.common.exception.file.FileSizeLimitExceededException;
import com.ruoyi.common.exception.file.InvalidExtensionException;
import com.ruoyi.common.utils.file.MimeTypeUtils;
import com.ruoyi.common.utils.uuid.Seq;
import io.minio.*;
import io.minio.errors.*;
import io.minio.messages.Bucket;
import io.minio.messages.DeleteObject;
import io.minio.messages.Item;
import org.apache.commons.io.FilenameUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.time.ZonedDateTime;
import java.util.*;

/**
 * Minio工具类
 *
 * @author qujingye
 */
public class MinioUtils {

    private static final Logger log = LoggerFactory.getLogger(MinioUtils.class);

    private static MinioClient minioClient;

    private static String endpoint;
    private static String bucketName;
    private static String accessKey;
    private static String secretKey;

    private static final String SEPARATOR = "/";

    /**
     * 默认大小 200M
     */
    public static final long DEFAULT_MAX_SIZE = 200 * 1024 * 1024;

    /**
     * 默认的文件名最大长度 100
     */
    public static final int DEFAULT_FILE_NAME_LENGTH = 100;


    private MinioUtils() {
    }

    public MinioUtils(String endpoint, String bucketName, String accessKey, String secretKey) {
        MinioUtils.endpoint = endpoint;
        MinioUtils.bucketName = bucketName;
        MinioUtils.accessKey = accessKey;
        MinioUtils.secretKey = secretKey;
        createMinioClient();
    }

    /**
     * 创建minioClient
     */
    public void createMinioClient() {
        try {
            if (null == minioClient) {
                log.info("minioClient create start");
                minioClient = MinioClient.builder().endpoint(endpoint).credentials(accessKey, secretKey)
                        .build();
                createBucket();
                log.info("minioClient create end");
            }
        } catch (Exception e) {
            log.error("连接MinIO服务器异常：{}", e);
        }
    }

    /**
     * 获取上传文件的基础路径
     *
     * @return url
     */
    public static String getBasisUrl() {
        return endpoint + SEPARATOR + bucketName + SEPARATOR;
    }

    /**
     * 初始化Bucket
     *
     * @throws Exception 异常
     */
    private static void createBucket()
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException, RegionConflictException {
        if (!minioClient.bucketExists(BucketExistsArgs.builder().bucket(bucketName).build())) {
            minioClient.makeBucket(MakeBucketArgs.builder().bucket(bucketName).build());
        }
    }

    /**
     * 验证bucketName是否存在
     *
     * @return boolean true:存在
     */
    public static boolean bucketExists()
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient.bucketExists(BucketExistsArgs.builder().bucket(bucketName).build());
    }

    /**
     * 创建bucket
     *
     * @param bucketName bucket名称
     */
    public static void createBucket(String bucketName)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException, RegionConflictException {
        if (!minioClient.bucketExists(BucketExistsArgs.builder().bucket(bucketName).build())) {
            minioClient.makeBucket(MakeBucketArgs.builder().bucket(bucketName).build());
        }
    }

    /**
     * 获取存储桶策略
     *
     * @param bucketName 存储桶名称
     * @return json
     */
    private JSONObject getBucketPolicy(String bucketName)
            throws IOException, InvalidKeyException, InvalidResponseException, BucketPolicyTooLargeException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, InsufficientDataException, ErrorResponseException {
        String bucketPolicy = minioClient
                .getBucketPolicy(GetBucketPolicyArgs.builder().bucket(bucketName).build());
        return JSONObject.parseObject(bucketPolicy);
    }

    /**
     * 获取全部bucket
     * <p>
     * https://docs.minio.io/cn/java-client-api-reference.html#listBuckets
     */
    public static List<Bucket> getAllBuckets()
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient.listBuckets();
    }

    /**
     * 根据bucketName获取信息
     *
     * @param bucketName bucket名称
     */
    public static Optional<Bucket> getBucket(String bucketName)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient.listBuckets().stream().filter(b -> b.name().equals(bucketName)).findFirst();
    }

    /**
     * 根据bucketName删除信息
     *
     * @param bucketName bucket名称
     */
    public static void removeBucket(String bucketName)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        minioClient.removeBucket(RemoveBucketArgs.builder().bucket(bucketName).build());
    }

    /**
     * 判断文件是否存在
     *
     * @param bucketName 存储桶
     * @param objectName 对象
     * @return true：存在
     */
    public static boolean doesObjectExist(String bucketName, String objectName) {
        boolean exist = true;
        try {
            minioClient
                    .statObject(StatObjectArgs.builder().bucket(bucketName).object(objectName).build());
        } catch (Exception e) {
            exist = false;
        }
        return exist;
    }

    /**
     * 判断文件夹是否存在
     *
     * @param bucketName 存储桶
     * @param objectName 文件夹名称（去掉/）
     * @return true：存在
     */
    public static boolean doesFolderExist(String bucketName, String objectName) {
        boolean exist = false;
        try {
            Iterable<Result<Item>> results = minioClient.listObjects(
                    ListObjectsArgs.builder().bucket(bucketName).prefix(objectName).recursive(false).build());
            for (Result<Item> result : results) {
                Item item = result.get();
                if (item.isDir() && objectName.equals(item.objectName())) {
                    exist = true;
                }
            }
        } catch (Exception e) {
            exist = false;
        }
        return exist;
    }

    /**
     * 根据文件前置查询文件
     *
     * @param bucketName bucket名称
     * @param prefix     前缀
     * @param recursive  是否递归查询
     * @return MinioItem 列表
     */
    public static List<Item> getAllObjectsByPrefix(String bucketName, String prefix,
                                                   boolean recursive)
            throws ErrorResponseException, InsufficientDataException, InternalException, InvalidBucketNameException, InvalidKeyException, InvalidResponseException,
            IOException, NoSuchAlgorithmException, ServerException, XmlParserException {
        List<Item> list = new ArrayList<>();
        Iterable<Result<Item>> objectsIterator = minioClient.listObjects(
                ListObjectsArgs.builder().bucket(bucketName).prefix(prefix).recursive(recursive).build());
        if (objectsIterator != null) {
            for (Result<Item> o : objectsIterator) {
                Item item = o.get();
                list.add(item);
            }
        }
        return list;
    }

    /**
     * 获取文件流
     *
     * @param bucketName bucket名称
     * @param objectName 文件名称
     * @return 二进制流
     */
    public static InputStream getObject(String bucketName, String objectName)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient
                .getObject(GetObjectArgs.builder().bucket(bucketName).object(objectName).build());
    }

    /**
     * 断点下载
     *
     * @param bucketName bucket名称
     * @param objectName 文件名称
     * @param offset     起始字节的位置
     * @param length     要读取的长度
     * @return 流
     */
    public InputStream getObject(String bucketName, String objectName, long offset, long length)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient.getObject(
                GetObjectArgs.builder().bucket(bucketName).object(objectName).offset(offset).length(length)
                        .build());
    }

    /**
     * 获取路径下文件列表
     *
     * @param bucketName bucket名称
     * @param prefix     文件名称
     * @param recursive  是否递归查找，如果是false,就模拟文件夹结构查找
     * @return 二进制流
     */
    public static Iterable<Result<Item>> listObjects(String bucketName, String prefix,
                                                     boolean recursive) {
        return minioClient.listObjects(
                ListObjectsArgs.builder().bucket(bucketName).prefix(prefix).recursive(recursive).build());
    }

    /**
     * 通过MultipartFile，上传文件
     *
     * @param bucketName 存储桶
     * @param file       文件
     * @param objectName 对象名
     */
    public static ObjectWriteResponse putObject(String bucketName, MultipartFile file,
                                                String objectName, String contentType)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        InputStream inputStream = file.getInputStream();


        return minioClient.putObject(
                PutObjectArgs.builder().bucket(bucketName).object(objectName).contentType(contentType)
                        .stream(
                                inputStream, inputStream.available(), -1)
                        .build());
    }

    /**
     * 上传本地文件
     *
     * @param bucketName 存储桶
     * @param objectName 对象名称
     * @param fileName   本地文件路径
     */
    public static ObjectWriteResponse putObject(String bucketName, String objectName,
                                                String fileName)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient.uploadObject(
                UploadObjectArgs.builder()
                        .bucket(bucketName).object(objectName).filename(fileName).build());
    }

    /**
     * 通过流上传文件
     *
     * @param bucketName  存储桶
     * @param objectName  文件对象
     * @param inputStream 文件流
     */
    public static ObjectWriteResponse putObject(String bucketName, String objectName,
                                                InputStream inputStream)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient.putObject(
                PutObjectArgs.builder().bucket(bucketName).object(objectName).stream(
                        inputStream, inputStream.available(), -1)
                        .build());
    }

    /**
     * 创建文件夹或目录
     *
     * @param bucketName 存储桶
     * @param objectName 目录路径
     */
    public static ObjectWriteResponse putDirObject(String bucketName, String objectName)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient.putObject(
                PutObjectArgs.builder().bucket(bucketName).object(objectName).stream(
                        new ByteArrayInputStream(new byte[]{}), 0, -1)
                        .build());
    }

    /**
     * 获取文件信息, 如果抛出异常则说明文件不存在
     *
     * @param bucketName bucket名称
     * @param objectName 文件名称
     */
    public static ObjectStat statObject(String bucketName, String objectName)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient
                .statObject(StatObjectArgs.builder().bucket(bucketName).object(objectName).build());
    }

    /**
     * 拷贝文件
     *
     * @param bucketName    bucket名称
     * @param objectName    文件名称
     * @param srcBucketName 目标bucket名称
     * @param srcObjectName 目标文件名称
     */
    public static ObjectWriteResponse copyObject(String bucketName, String objectName,
                                                 String srcBucketName, String srcObjectName)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient.copyObject(
                CopyObjectArgs.builder()
                        .source(CopySource.builder().bucket(bucketName).object(objectName).build())
                        .bucket(srcBucketName)
                        .object(srcObjectName)
                        .build());
    }

    /**
     * 删除文件
     *
     * @param bucketName bucket名称
     * @param objectName 文件名称
     */
    public static void removeObject(String bucketName, String objectName)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, NoSuchAlgorithmException, ServerException, InternalException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        minioClient
                .removeObject(RemoveObjectArgs.builder().bucket(bucketName).object(objectName).build());
    }

    /**
     * 批量删除文件
     *
     * @param bucketName bucket
     * @param keys       需要删除的文件列表
     * @return
     */
  /*public static Iterable<Result<DeleteError>> removeObjects(String bucketName, List<String> keys) {
    List<DeleteObject> objects = new LinkedList<>();
    keys.forEach(s -> {
      objects.add(new DeleteObject(s));
    });
    return minioClient.removeObjects(
        RemoveObjectsArgs.builder().bucket(bucketName).objects(objects).build());
  }*/
    public static void removeObjects(String bucketName, List<String> keys) {
        List<DeleteObject> objects = new LinkedList<>();
        keys.forEach(s -> {
            objects.add(new DeleteObject(s));
            try {
                removeObject(bucketName, s);
            } catch (Exception e) {
                log.error("批量删除失败！error:{}", e);
            }
        });
    }

    /**
     * 获取文件外链
     *
     * @param bucketName bucket名称
     * @param objectName 文件名称
     * @param expires    过期时间 <=7 秒级
     * @return url
     */
    public static String getPresignedObjectUrl(String bucketName, String objectName,
                                               Integer expires)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, InvalidExpiresRangeException, ServerException, InternalException, NoSuchAlgorithmException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        return minioClient.presignedGetObject(bucketName, objectName, expires);
    }

    /**
     * 给presigned URL设置策略
     *
     * @param bucketName 存储桶
     * @param objectName 对象名
     * @param expires    过期策略
     * @return map
     */
    public static Map<String, String> presignedGetObject(String bucketName, String objectName,
                                                         Integer expires)
            throws IOException, InvalidKeyException, InvalidResponseException, InsufficientDataException, InvalidExpiresRangeException, ServerException, InternalException, NoSuchAlgorithmException, XmlParserException, InvalidBucketNameException, ErrorResponseException {
        PostPolicy policy = new PostPolicy(bucketName, objectName,
                ZonedDateTime.now().plusDays(7));
        policy.setContentType("image/png");
        return minioClient.presignedPostPolicy(policy);
    }

    /**
     * 获得上传的URL
     *
     * @param bucketName 存储桶
     * @param objectName 对象名
     * @param expires    过期策略（秒）
     * @return
     */
    public static String presignedPutObject(String bucketName, String objectName, Integer expires) throws ServerException, InvalidBucketNameException, InsufficientDataException, ErrorResponseException, InvalidExpiresRangeException, IOException, NoSuchAlgorithmException, InvalidKeyException, InvalidResponseException, XmlParserException, InternalException {
        if (expires == null) {
            // 7天
            return minioClient.presignedPutObject(bucketName, objectName);
        }
        return minioClient.presignedPutObject(bucketName, objectName, expires);
    }


    /**
     * 将URLDecoder编码转成UTF8
     *
     * @param str
     * @return
     * @throws UnsupportedEncodingException
     */
    public static String getUtf8ByURLDecoder(String str) throws UnsupportedEncodingException {
        String url = str.replaceAll("%(?![0-9a-fA-F]{2})", "%25");
        return URLDecoder.decode(url, "UTF-8");
    }

    /**
     * 获取上传地址并做文件校验
     *
     * @param file allowedExtension
     * @return String
     */
    public static Map<String, Object> getUploadPath(MultipartFile file, String basePath, String[] allowedExtension)
            throws FileSizeLimitExceededException, IOException, FileNameLengthLimitExceededException,
            InvalidExtensionException {
        int fileNamelength = Objects.requireNonNull(file.getOriginalFilename()).length();
        if (fileNamelength > MinioUtils.DEFAULT_FILE_NAME_LENGTH) {
            throw new FileNameLengthLimitExceededException(MinioUtils.DEFAULT_FILE_NAME_LENGTH);
        }
        assertAllowed(file, allowedExtension);
        return extractFilename(file, basePath);
    }

    /**
     * 文件大小校验
     *
     * @param file 上传的文件
     * @return
     * @throws FileSizeLimitExceededException 如果超出最大大小
     * @throws InvalidExtensionException
     */
    public static final void assertAllowed(MultipartFile file, String[] allowedExtension)
            throws FileSizeLimitExceededException, InvalidExtensionException
    {
        long size = file.getSize();
        if (size > DEFAULT_MAX_SIZE)
        {
            throw new FileSizeLimitExceededException(DEFAULT_MAX_SIZE / 1024 / 1024);
        }

        String fileName = file.getOriginalFilename();
        String extension = getExtension(file);
        if (allowedExtension != null && !isAllowedExtension(extension, allowedExtension))
        {
            if (allowedExtension == MimeTypeUtils.IMAGE_EXTENSION)
            {
                throw new InvalidExtensionException.InvalidImageExtensionException(allowedExtension, extension,
                        fileName);
            }
            else if (allowedExtension == MimeTypeUtils.FLASH_EXTENSION)
            {
                throw new InvalidExtensionException.InvalidFlashExtensionException(allowedExtension, extension,
                        fileName);
            }
            else if (allowedExtension == MimeTypeUtils.MEDIA_EXTENSION)
            {
                throw new InvalidExtensionException.InvalidMediaExtensionException(allowedExtension, extension,
                        fileName);
            }
            else if (allowedExtension == MimeTypeUtils.VIDEO_EXTENSION)
            {
                throw new InvalidExtensionException.InvalidVideoExtensionException(allowedExtension, extension,
                        fileName);
            }
            else
            {
                throw new InvalidExtensionException(allowedExtension, extension, fileName);
            }
        }
    }

    /**
     * 判断MIME类型是否是允许的MIME类型
     *
     * @param extension
     * @param allowedExtension
     * @return
     */
    public static final boolean isAllowedExtension(String extension, String[] allowedExtension)
    {
        for (String str : allowedExtension)
        {
            if (str.equalsIgnoreCase(extension))
            {
                return true;
            }
        }
        return false;
    }

    /**
     * 编码文件名
     */
    public static final Map<String, Object> extractFilename(MultipartFile file, String basePath)
    {
        Map<String, Object> map=new HashMap<>();
        map.put("uploadPath",StringUtils.format("{}/{}/{}.{}",basePath, DateUtils.datePathMonth(), Seq.getId(Seq.uploadSeqType), getExtension(file)));
        map.put("newFileName",StringUtils.format("{}.{}", Seq.getId(Seq.uploadSeqType), getExtension(file)));
        return map;
    }

    public static final Map<String, Object> extractFilenameText(String fileName, String basePath)
    {
        Map<String, Object> map=new HashMap<>();
        map.put("uploadPath",StringUtils.format("{}/{}/{}.{}",basePath, DateUtils.datePathMonth(), Seq.getId(Seq.uploadSeqType), fileName));
        map.put("newFileName",StringUtils.format("{}.{}", Seq.getId(Seq.uploadSeqType),fileName));
        return map;
    }

    /**
     * 获取文件名的后缀
     *
     * @param file 表单文件
     * @return 后缀名
     */
    public static final String getExtension(MultipartFile file)
    {
        String extension = FilenameUtils.getExtension(file.getOriginalFilename());
        if (StringUtils.isEmpty(extension))
        {
            extension = MimeTypeUtils.getExtension(Objects.requireNonNull(file.getContentType()));
        }
        return extension;
    }


}

?1.3?模板填充导出实现接口

?1.3.1 controller

package com.ruoyi.web.controller.common;

import com.ruoyi.system.service.ExcelTemplateDemoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;

/**
 * @author qujingye
 * @Classname ExcelTemplateDemoController
 * @Description 测试 从minio获取文件模板-easyExcel模板填充
 * @Date 2023/12/31 12:37
 */
@RestController
@RequestMapping("/excelTemplateDemo")
public class ExcelTemplateDemoController {

    @Autowired
    private ExcelTemplateDemoService excelTemplateDemoService;

    @GetMapping("/test")
    public void exportChangeDetail(String resource, HttpServletResponse response) {
        excelTemplateDemoService.excelTemplateDemoImport(response);
    }

}

?1.3.2?service

package com.ruoyi.system.service;

import javax.servlet.http.HttpServletResponse;

/**
 * @author qujingye
 * @title ExcelTemplateDemoService
 * @date 2023/12/31 12:40
 * @description TODO
 */
public interface ExcelTemplateDemoService {
    void excelTemplateDemoImport(HttpServletResponse response);
}

package com.ruoyi.system.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.ruoyi.common.config.MinioConfig;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.MinioUtils;
import com.ruoyi.system.domain.ProductInfo;
import com.ruoyi.system.service.ExcelTemplateDemoService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * @author qujingye
 * @Classname ExcelTemplateDemoServiceImpl
 * @Description TODO
 * @Date 2023/12/31 12:42
 */
@Service
public class ExcelTemplateDemoServiceImpl implements ExcelTemplateDemoService {

    public static final String EXCEL_TEMPLATE_PATH = "/exportExcelTemplate/商品出库单.xlsx";

    private static final Logger log = LoggerFactory.getLogger(ExcelTemplateDemoServiceImpl.class);

    /**
     * 从minio获取模板进行填充
     *
     * @param response
     * @return void
     */
    @Override
    public void excelTemplateDemoImport(HttpServletResponse response) {
        //输入流
        InputStream inputStream = null;
        ServletOutputStream outputStream = null;
        ExcelWriter excelWriter = null;
        List<ProductInfo> result = new ArrayList<>();
        //组装测试商品列表数据 编号 名称及规格 单位	数量 单价 金额 备注
        getProductInfoTestData(result);
        //组装测试其他数据
        HashMap<String, Object> totalMap = new HashMap<>();
        getOtheroTestData(totalMap);
        try {
            inputStream = MinioUtils.getObject(MinioConfig.getBucketName(), EXCEL_TEMPLATE_PATH);
            //输出流
            // 获取文件名并转码
//            String name = URLEncoder.encode("商品出库单.xlsx", "UTF-8");
//            response.setHeader("Content-Disposition", "attachment;filename=" + name);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            outputStream = response.getOutputStream();
            // 创建填充配置
            FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
            // 创建写对象
            excelWriter = EasyExcel.write(outputStream)
                    .withTemplate(inputStream).build();
            // 创建Sheet对象
            WriteSheet sheet = EasyExcel.writerSheet(0).build();
            sheet.setSheetName("商品出库单");
            // 多组填充excel
            excelWriter.fill(totalMap, sheet);
            excelWriter.fill(result, fillConfig, sheet);
        } catch (Exception e) {
            log.info("导出失败={}", e.getMessage());
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            //关闭流
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private void getOtheroTestData(HashMap<String, Object> totalMap) {
        //客户名称
        totalMap.put("name","小张");
        //客户编码
        totalMap.put("code","001XP");
        //日期
        totalMap.put("date", DateUtils.getChineseDate());
        //总数量
        totalMap.put("sumNums",20);
        //合计：
        totalMap.put("sumMoney",2000);
        //主管：
        totalMap.put("governor","王主管");
        //财务：
        totalMap.put("finance","李财务");
        //保管员
        totalMap.put("guardian","王保管员");
        //保管员
        totalMap.put("HandlerName","刘经手人");
    }


    private void getProductInfoTestData(List<ProductInfo> result) {
        result.add(new ProductInfo("001", "商品1", "单位1", 2, BigDecimal.valueOf(100), BigDecimal.valueOf(200), "无"));
        result.add(new ProductInfo("002", "商品2", "单位1", 2, BigDecimal.valueOf(100), BigDecimal.valueOf(200), "无"));
        result.add(new ProductInfo("003", "商品3", "单位1", 2, BigDecimal.valueOf(100), BigDecimal.valueOf(200), "无"));
        result.add(new ProductInfo("004", "商品4", "单位1", 2, BigDecimal.valueOf(100), BigDecimal.valueOf(200), "无"));
        result.add(new ProductInfo("005", "商品5", "单位1", 2, BigDecimal.valueOf(100), BigDecimal.valueOf(200), "无"));
        result.add(new ProductInfo("006", "商品6", "单位1", 2, BigDecimal.valueOf(100), BigDecimal.valueOf(200), "无"));
        result.add(new ProductInfo("007", "商品7", "单位1", 2, BigDecimal.valueOf(100), BigDecimal.valueOf(200), "无"));
        result.add(new ProductInfo("008", "商品8", "单位1", 2, BigDecimal.valueOf(100), BigDecimal.valueOf(200), "无"));
        result.add(new ProductInfo("009", "商品9", "单位1", 2, BigDecimal.valueOf(100), BigDecimal.valueOf(200), "无"));
        result.add(new ProductInfo("010", "商品10", "单位1", 2, BigDecimal.valueOf(100), BigDecimal.valueOf(200), "无"));
    }
}

📝结束语：

总结不易😄觉得有用的话，还请各位大佬动动发财的小手
给小瞿扣个三连：?? 点赞??收藏 ?? 关注
这个对我真的非常重要，拜托啦！
本人水平有限，如有纰漏，欢迎各位大佬评论指正！💞