注：文章皆为个人纪录，可用性请以最终结果为准，若有错还请大佬们指出，谢谢！

 程序入口

package com.jxz.owner.controller.excel;

import com.jxz.owner.entity.CsIssue;
import com.jxz.owner.entity.excel.CsIssueExcel;
import com.jxz.owner.sdk.oss.service.IOssService;
import com.jxz.owner.utils.DateTimeUtils;
import com.jxz.owner.utils.ExcelUtils;
import com.sun.istack.internal.NotNull;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Description: Excel 文件的导入导出
 *
 * @Date: 2022/1/5
 * @Author: jiangXueZhi
 */
@RestController(value = "c1") // 当有两个控制器名称相同时做value的区分
@RequestMapping("/api/excel")
public class ExcelController {
    @Resource
    private IOssService iOssService;

    /**
     * 文件上传获取下载地址，并可解析上传的文件生成一个List<T>
     *
     * @param file 上传的文件
     *
     */
    @RequestMapping(value = "/import", method = RequestMethod.POST)
    public void upload(MultipartFile file) {
        String cloudFile = iOssService.upload(file, "excel", file.getOriginalFilename()); // 相对路径
        String path = iOssService.getDownLoadUrl(cloudFile); // 绝对路径
        System.out.println("###########  PATH : " + path);

        /* 解析文件 */
        List<CsIssueExcel> csIssues = ExcelUtils.resolveExcel(file, CsIssueExcel.class, null);
        if (csIssues != null) {
            System.out.println(csIssues.size());
        }
        System.out.println(csIssues);
    }

    /**
     * 下载文件流
     * 
     * @param response 文件流
     */
    @RequestMapping(value = "/export", method = RequestMethod.POST)
    public void export(HttpServletResponse response) {
        List<CsIssue> csIssueList = getExcelDownLoadData(); // 在实体类中 用 @ExcelIgnore 注解控制字段不展示
        String fileName = ExcelUtils.getFileName("issue");
        ExcelUtils.downLoad(response, csIssueList, CsIssue.class, fileName);
    }

    /**
     * 下载文件地址
     *
     * @param response 文件下载地址
     */
    @RequestMapping(value = "/exportStr", method = RequestMethod.POST)
    public void exportStr(HttpServletResponse response) {
        List<CsIssue> csIssueList = getExcelDownLoadData(); // 在实体类中 用 @ExcelIgnore 注解控制字段不展示
        String fileName = ExcelUtils.getFileName("issue");
        InputStream in = ExcelUtils.downLoadStr(response, csIssueList, CsIssue.class, fileName);
        String path = iOssService.upload(in, fileName, "excel");
        System.out.println("###########  PATH : " + path);
    }

    @NotNull
    private List<CsIssue> getExcelDownLoadData() {
        List<CsIssue> csIssueList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            CsIssue cs = new CsIssue();
            cs.setId(i);
            cs.setIssueNo("ino_" + i * i);
            cs.setAccountNo("ano_" + i * i);
            cs.setTransactionId("tid_" + i * i);
            cs.setTransactionDate(DateTimeUtils.getCurrentDate());
            cs.setInstituteId(i);
            cs.setOperationType(1);
            cs.setAmount(i * 100);
            cs.setPhoneNo("123456");
            cs.setIssue("这是一个测试案例");
            cs.setConclusion("这是一个结论案例");
            cs.setStatus(1);
            cs.setPicPath("http://xxxx.oss-cn-shanghai.aliyuncs.com/img/106c578df60347e5a78c48f3c1acaeb197.jpg?Expires=1641807443&OSSAccessKeyId=xxxx&Signature=xxxx%3D");
            cs.setCsInterventionTime(new Date());
            cs.setCsCompletionTime(new Date());
            csIssueList.add(cs);
        }
        return csIssueList;
    }
}

导入导出均基于EasyExcel（推荐第一、三种）

依赖如下：

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.1</version>
        </dependency>

导出（四种方式） 

方式一   直接返回流（即输出后直接下载）

步骤一：创建工具类ExcelUtils

package com.jxz.owner.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.jxz.owner.config.ExcelWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * @Description: Excel 相关的工具方法
 *
 * @Date: 2022/1/12
 * @Author: jiangXueZhi
 */
@Component
@Slf4j
public class ExcelUtils {
    /**
     * 每个sheet的容量，即超过时就会把数据分sheet
     */
    private static final int PAGE_SIZE = 10000;

    /**
     * 接收一个excel文件，并且进行解析
     * 注意一旦传入自定义监听器，则返回的list为空，数据需要在自定义监听器里面获取
     *
     * @param multipartFile excel文件
     * @param clazz         数据类型的class对象
     * @param readListener  监听器
     * @param <T> 泛型
     * @return 解析的Excel文件数据集合
     */
    public static <T> List<T> resolveExcel(MultipartFile multipartFile, Class<T> clazz, ReadListener<T> readListener) {
        try (InputStream inputStream = multipartFile.getInputStream()) {
            return read(inputStream, clazz, readListener);
        } catch (IOException e) {
            log.error("解析文件失败..");
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读文件
     *
     * @param in 输入流
     * @param clazz 反射类型
     * @param readListener 监听器
     * @param <T> 泛型
     * @return 解析的Excel文件数据集合
     */
    private static <T> List<T> read(InputStream in, Class<T> clazz, ReadListener<T> readListener) {
        List<T> list = new ArrayList<>();
        Optional<ReadListener<T>> optional = Optional.ofNullable(readListener);
        EasyExcel.read(in, clazz, optional.orElse(new AnalysisEventListener<T>() {
            @Override
            public void invoke(T data, AnalysisContext context) {
                list.add(data);
            }
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.warn("Parsing is complete");
            }
        }))
                .sheet().doRead();
        return list;
    }

    /**
     * 下载Excel  流 (推荐使用)
     *
     * @param response 响应体
     * @param list 数据
     * @param clazz JavaBean反射对象
     * @param fileName 文件名 fileName = fileName + "_" + DateTimeUtils.getCurrentDateTime() + ".xlsx";
     * @param <T> 泛型
     */
    @SuppressWarnings("rawtypes")
    public static <T> void downLoad(HttpServletResponse response, List<T> list, Class<T> clazz, String fileName) {
        try {
            //设置响应的类型
            setResponse(response, java.net.URLEncoder.encode(fileName, "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
//        setResponse(response, fileName);
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为灰
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 默认表头
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        // 自适应列宽表头
        ExcelWidthStyleStrategy excelWidthStyleStrategy = new ExcelWidthStyleStrategy();
        try (OutputStream outputStream = response.getOutputStream()) {
//            EasyExcelFactory.write(outputStream, clazz).registerWriteHandler(horizontalCellStyleStrategy).sheet("sheet1").doWrite(list);
            EasyExcelFactory.write(outputStream, clazz).registerWriteHandler(excelWidthStyleStrategy).sheet("sheet1").doWrite(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 下载Excel  地址 (推荐使用)
     *
     * @param response 响应体
     * @param list 数据
     * @param clazz JavaBean反射对象
     * @param fileName 文件名 fileName = fileName + "_" + DateTimeUtils.getCurrentDateTime() + ".xlsx";
     * @param <T> 泛型
     */
    @SuppressWarnings("rawtypes")
    public static <T> InputStream downLoadStr(HttpServletResponse response, List<T> list, Class<T> clazz, String fileName) {
        // 单个sheet的容量
        List<? extends List<?>> lists = splitList(list, PAGE_SIZE);
        ByteArrayOutputStream os = new ByteArrayOutputStream();

        try {
            //设置响应的类型
            setResponse(response, URLEncoder.encode(fileName, "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
//        setResponse(response, fileName);
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为灰
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 默认表头
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        // 自适应列宽表头
        ExcelWidthStyleStrategy excelWidthStyleStrategy = new ExcelWidthStyleStrategy();
        try (OutputStream outputStream = response.getOutputStream()) {
//            EasyExcelFactory.write(outputStream, clazz).registerWriteHandler(horizontalCellStyleStrategy).sheet("sheet1").doWrite(list);
            EasyExcelFactory.write(outputStream, clazz).registerWriteHandler(excelWidthStyleStrategy).sheet("sheet1").doWrite(list);
//            ExcelWriter excelWriter = EasyExcel.write(os, clazz).registerWriteHandler(excelWidthStyleStrategy).build();
//            浏览器访问url直接下载文件的方式
//            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(com.example.utils.easyexcel.EasyExcel.formatExcel()).registerWriteHandler(new com.example.utils.easyexcel.EasyExcel.ExcelWidthStyleStrategy()).build();
            ExcelWriter excelWriter = EasyExcelFactory.write(os, clazz).registerWriteHandler(excelWidthStyleStrategy).build();
            ExcelWriterSheetBuilder excelWriterSheetBuilder;
            WriteSheet writeSheet;
            for (int i = 1; i <= lists.size(); ++i) {
                excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
                excelWriterSheetBuilder.sheetNo(i).sheetName("sheet" + i);
                writeSheet = excelWriterSheetBuilder.build();
                excelWriter.write(lists.get(i - 1), writeSheet);
            }
            // 必须要finish才会写入，不finish只会创建empty的文件
            excelWriter.finish();

            byte[] content = os.toByteArray();
            // 返回流文件
            return new ByteArrayInputStream(content);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 切割查询的数据
     * @param list 需要切割的数据
     * @param len 按照什么长度切割
     * @param <T> 泛型
     * @return 数据
     */
    public static <T> List<List<T>> splitList(List<T> list, int len) {
        if (list == null || list.size() == 0 || len < 1) {
            return null;
        }
        List<List<T>> result = new ArrayList<List<T>>();
        int size = list.size();
        int count = (size + len - 1) / len;
        for (int i = 0; i < count; i++) {
            List<T> subList = list.subList(i * len, (Math.min((i + 1) * len, size)));
            result.add(subList);
        }
        return result;
    }

    /**
     * 获取最终的文件名
     *
     * @param fileName 文件初始名
     * @return 最终的文件名
     */
    public static String getFileName(String fileName) {
        return fileName + "_" + DateTimeUtils.getCurrentDateTime() + ".xlsx";
    }

    /**
     * 设置响应体参数
     *
     * @param response 响应对象
     * @param fileName 文件名
     */
    public static void setResponse(HttpServletResponse response, String fileName) {
        response.reset();
        response.addHeader("content-type", "application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.addHeader("Cache-Control", "no-cache");
    }
}

 ExcelWidthStyleStrategy      列宽自适应

package com.jxz.owner.config;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: 列宽自适应
 *
 * @Date: 2022/1/12
 * @Author: jiangXueZhi
 */
public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    /** 最大列宽 */
    private static final int MAX_COLUMN_WIDTH = 255;
    /** 最小列宽 */
    private static final int MIN_COLUMN_WIDTH = 25;

    private  final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>(8);

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head,
                                  Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtil.isEmpty(cellDataList);
        if (!needSetWidth) {
            return;
        }
        Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>(16));
        Integer columnWidth = dataLength(cellDataList, cell, isHead);
        if (columnWidth < 0) {
            return;
        }
        if (columnWidth > MAX_COLUMN_WIDTH) {
            columnWidth = MAX_COLUMN_WIDTH;
        }
        if (columnWidth < MIN_COLUMN_WIDTH) {
            columnWidth = MIN_COLUMN_WIDTH;
        }
        Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
        if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
            maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }

    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        }
        WriteCellData<?> cellData = cellDataList.get(0);
        CellDataTypeEnum type = cellData.getType();
        if (type == null) {
            return -1;
        }
        switch (type) {
            case STRING:
                return cellData.getStringValue().getBytes().length;
            case BOOLEAN:
                return cellData.getBooleanValue().toString().getBytes().length;
            case NUMBER:
                return cellData.getNumberValue().toString().getBytes().length;
            default:
                return -1;
        }
    }
}

步骤二：PostMan测试

不要直接点击 "Send",否则会出现乱码

先点击”Send“右侧的⬆，再选择”Snd and Download“,之后选择下载位置即可

 步骤三：Excel展示（单sheet）

显示效果一切正常，图中显示整体缩小是因为为了方便大家看到全局而手动缩小

 方式二   返回下载地址（上传至"七牛"文件服务器）

步骤一：导入依赖

<!--七牛文件上传sdk-->
        <dependency>
            <groupId>com.qiniu</groupId>
            <artifactId>qiniu-java-sdk</artifactId>
            <version>7.4.0</version>
        </dependency>

步骤二：编写工具类

// 以下拿到下载地址filePath 

/**
     * 处理异步导出消息（生成Excel文件，上传至文件服务器，并返回文件下载地址）

     * @param fileName 文件名
     * @param params 数据查询参数
     * @return filePath
     */
    public static String dealWithAsyncExportMessage(String fileName, Object params) {
        List<UbiApiQueryLog> list = iAsyncExportService.selectExportData(params);
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            webWriteExcel(out, list, UbiApiQueryLog.class, fileName, fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }
        String filePath = QiNiuUtil.uploadFileByInputStream(fileName, new ByteArrayInputStream(out.toByteArray()));
        log.info("##############################################  filePath：  " + filePath);
        return filePath;
    }

// 以下为生成Excel

/**
     * 不可合并单元格
     *
     * @param outputStream 输出流
     * @param list 数据
     * @param clazz JavaBean反射对象
     * @param fileName 文件名
     * @param sheetName sheetName
     * @throws IOException 异常
     */
    @SuppressWarnings("rawtypes")
    public static void webWriteExcel(ByteArrayOutputStream outputStream, List list, Class clazz, String fileName, String sheetName) throws IOException {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为灰
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcelFactory.write(outputStream, clazz).registerWriteHandler(horizontalCellStyleStrategy).sheet(sheetName).doWrite(list);
    }

// 以下为上传至QiNiu

/**
     * 七牛密钥
     */
    public static final String ACCESS_KEY = "#####";
    public static final String SECRET_KEY = "#####";

    /**
     * 要上传的空间名称,生成token时需要
     */
    public static final String QI_NIU_SPACE = "#####";

    /**
     * 七牛上面文件存储域名，下载时的域名
     */
    public static final String DOMAIN = "https://#####.com/";


    private static String getUpToken() {
        Auth auth = Auth.create(ACCESS_KEY, SECRET_KEY);
        String upToken = auth.uploadToken(QI_NIU_SPACE);
        return upToken;
    }

/**
     * 通过输入流上传文件
     * 
     * @param fileName 文件名
     * @param inputStream 输入流
     * @return 文件下载地址
     */
    public static String uploadFileByInputStream(String fileName, InputStream inputStream) {
        //构造一个带指定 Region 对象的配置类
        Configuration cfg = new Configuration(Region.region0());
        //...其他参数参考类注释
        UploadManager uploadManager = new UploadManager(cfg);

        //默认不指定key的情况下，以文件内容的hash值作为文件名
        String key = fileName;

        String upToken = getUpToken();
        try {
            Response response = uploadManager.put(inputStream, key, upToken, null, null);
            if(response.isOK()){
                return QiNiuUtil.DOMAIN + fileName; // 必须拼接文件存储域名
            }
            //解析上传成功的结果
            //DefaultPutRet putRet = new Gson().fromJson(response.bodyString(), DefaultPutRet.class);
            System.out.println("上传失败！");
           return null;
        } catch (QiniuException ex) {
            return null;
        }
    }

步骤三：调用步骤二的工具方法dealWithAsyncExportMessage拿到最终上传至服务器后的下载地址

方式三   返回下载地址（上传至OSS文件服务器） 

步骤一：导入依赖

<!-- https://mvnrepository.com/artifact/com.aliyun.oss/aliyun-sdk-oss -->
<dependency>
    <groupId>com.aliyun.oss</groupId>
    <artifactId>aliyun-sdk-oss</artifactId>
    <version>3.13.0</version>
</dependency>

步骤二：编写OSS相关的服务类

package com.jxz.owner.sdk.oss.service;

import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;

/**
 * Created by IDEA.
 * User: jiangXueZhi
 * Date: 2021/9/27
 * Time: 2:56 下午
 */
public interface IOssService {
    String uploadAndSaveName(String bucket, MultipartFile multipartFile, String saveDirAndFileName);

    String uploadAndSaveName(MultipartFile multipartFile, String saveDirAndFileName);

    String upload(String bucket, MultipartFile multipartFile);

    String upload(MultipartFile multipartFile);

    String upload(MultipartFile multipartFile, String dir);

    String upload(MultipartFile multipartFile, String dir, String fileName);

    String getDownLoadUrl(String object);

    String upload(InputStream in, String fileName, String dir);
}

package com.jxz.owner.sdk.oss.service.impl;

import com.aliyun.oss.OSS;
import com.aliyun.oss.OSSClientBuilder;
import com.jxz.owner.sdk.oss.config.AliyunOssConfig;
import com.jxz.owner.sdk.oss.service.IOssService;
import com.jxz.owner.utils.StrUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.net.URL;
import java.util.Arrays;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;


/**
 * Created by IDEA.
 * User: xiongyoulong
 * Date: 2021/9/27
 * Time: 2:58 下午
 */
@Slf4j
@Service
@Component
public class OssServiceImpl implements IOssService {

    private OSS client = null;

    /**
     * 图片类型后缀格式
     **/
    public static final Set<String> IMG_SUFFIX = new HashSet<>(Arrays.asList("jpg", "png", "jpeg", "gif"));

    /**
     * Excel类型后缀格式
     **/
    public static final Set<String> EXCEL_SUFFIX = new HashSet<>(Arrays.asList("xls", "xlsx"));

    @Resource
    private AliyunOssConfig aliyunOssConfig;

    /**
     * 初始化oss的配置信息
     */
    @PostConstruct
    public void init() {
        client = new OSSClientBuilder().build(aliyunOssConfig.getEndpoint(), aliyunOssConfig.getAccessKeyId(), aliyunOssConfig.getAccessKeySecret());
    }

    /**
     * 指定 bucket 上传
     *
     * @param bucket bucket
     * @param multipartFile 文件
     * @param saveDirAndFileName  文件名
     * @return OSS 中的相对路径
     */
    @Override
    public String uploadAndSaveName(String bucket, MultipartFile multipartFile, String saveDirAndFileName) {
        if (bucket.trim().isEmpty()) {
            bucket = aliyunOssConfig.getPublicBucket();
        }

        try {
            // 依次填写Bucket名称（例如examplebucket）和Object完整路径（例如exampledir/exampleobject.txt）。Object完整路径中不能包含Bucket名称。
            this.client.putObject(bucket, saveDirAndFileName, multipartFile.getInputStream());
        } catch (Exception exception) {
            log.error("upload aliyun oss file exception:{}", exception);
            return null;
        }
        return saveDirAndFileName;
    }

    /**
     * 使用默认的 Bucket 上传
     *
     * @param multipartFile 文件
     * @param saveDirAndFileName 文件名
     * @return OSS 中的相对路径
     */
    @Override
    public String uploadAndSaveName(MultipartFile multipartFile, String saveDirAndFileName) {
        String bucket = aliyunOssConfig.getPublicBucket();
        try {
            // 依次填写Bucket名称（例如examplebucket）和Object完整路径（例如exampledir/exampleobject.txt）。Object完整路径中不能包含Bucket名称。
            this.client.putObject(bucket, saveDirAndFileName, multipartFile.getInputStream());
        } catch (Exception exception) {
            log.error("upload aliyun oss file exception:{}", exception);
            return null;
        }
        return saveDirAndFileName;
    }

    /**
     * 指定 bucket 上传（文件名随机uuid）
     *
     * @param bucket 默认 bucket
     * @param multipartFile 文件
     * @return OSS 中的相对路径
     */
    @Override
    public String upload(String bucket, MultipartFile multipartFile) {
        if (bucket.trim().isEmpty()) {
            bucket = aliyunOssConfig.getPublicBucket();
        }

        //获取文件后缀
        String fileSuffix = getFileSuffix(multipartFile.getOriginalFilename(), false);
        String saveDirAndFileName = null;
        try {
            saveDirAndFileName = "images/" + StrUtils.getUUID() + "." + fileSuffix;
            // 依次填写Bucket名称（例如examplebucket）和Object完整路径（例如exampledir/exampleobject.txt）。Object完整路径中不能包含Bucket名称。
            this.client.putObject(bucket, saveDirAndFileName, multipartFile.getInputStream());
        } catch (Exception exception) {
            log.error("upload aliyun oss file exception:{}", exception);
            return null;
        }
        return saveDirAndFileName;
    }

    /**
     * 默认 bucket 上传（文件名随机uuid）
     *
     * @param multipartFile 文件
     * @return OSS 中的相对路径
     */
    @Override
    public String upload(MultipartFile multipartFile) {
        //获取文件后缀
        String fileSuffix = getFileSuffix(multipartFile.getOriginalFilename(), false);
        String saveDirAndFileName = "";
        try {
            saveDirAndFileName = "images/" + StrUtils.getUUID() + "." + fileSuffix;
            // 依次填写Bucket名称（例如examplebucket）和Object完整路径（例如exampledir/exampleobject.txt）。Object完整路径中不能包含Bucket名称。
            log.info("upload file info {}", multipartFile.getInputStream());
            this.client.putObject(aliyunOssConfig.getPublicBucket(), saveDirAndFileName, new ByteArrayInputStream(multipartFile.getBytes()));
        } catch (Exception exception) {
            log.error("upload aliyun oss file endpoint:{} filepath:{} exception:{}", aliyunOssConfig.getEndpoint(), saveDirAndFileName, exception);
            return null;
        }
        return saveDirAndFileName;
    }

    /**
     * 指定上传目录上传（默认 bucket 随机文件名）
     *
     * @param multipartFile 文件
     * @param dir 目录（如：img、excel、txt等）
     * @return OSS 中的相对路径
     */
    @Override
    public String upload(MultipartFile multipartFile, String dir) {
        //获取文件后缀
        String fileSuffix = getFileSuffix(multipartFile.getOriginalFilename(), false);
        String saveDirAndFileName = "";
        try {
            saveDirAndFileName = dir + "/" + StrUtils.getUUID() + "." + fileSuffix;
            // 依次填写Bucket名称（例如examplebucket）和Object完整路径（例如exampledir/exampleobject.txt）。Object完整路径中不能包含Bucket名称。
            log.info("upload file info {}", multipartFile.getInputStream());
            this.client.putObject(aliyunOssConfig.getPublicBucket(), saveDirAndFileName, new ByteArrayInputStream(multipartFile.getBytes()));
        } catch (Exception exception) {
            log.error("upload aliyun oss file endpoint:{} filepath:{} exception:{}", aliyunOssConfig.getEndpoint(), saveDirAndFileName, exception);
            return null;
        }
        return saveDirAndFileName;
    }

    /**
     * 指定上传目录、指定文件名
     *
     * @param multipartFile 文件
     * @param dir 目录
     * @param fileName 文件名
     * @return OSS  中的相对路径
     */
    @Override
    public String upload(MultipartFile multipartFile, String dir, String fileName) {
        //获取文件后缀
        try {
            // 依次填写Bucket名称（例如examplebucket）和Object完整路径（例如exampledir/exampleobject.txt）。Object完整路径中不能包含Bucket名称。
            log.info("upload file info {}", multipartFile.getInputStream());
            this.client.putObject(aliyunOssConfig.getPublicBucket(), fileName, new ByteArrayInputStream(multipartFile.getBytes()));
        } catch (Exception exception) {
            log.error("upload aliyun oss file endpoint:{} filepath:{} exception:{}", aliyunOssConfig.getEndpoint(), fileName, exception);
            return null;
        }
        return fileName;
    }

    /**
     * 上传流文件到OSS服务器
     *
     * @param in 流
     * @param fileName 文件名
     * @param dir 目录
     * @return OSS 中的相对路径
     */
    @Override
    public String upload(InputStream in, String fileName, String dir) {
        //获取文件后缀
        String saveDirAndFileName = "";
        try {
            saveDirAndFileName = dir + "/" + fileName;
            // 依次填写Bucket名称（例如examplebucket）和Object完整路径（例如exampledir/exampleobject.txt）。Object完整路径中不能包含Bucket名称。
            this.client.putObject(aliyunOssConfig.getPublicBucket(), saveDirAndFileName, in);
        } catch (Exception exception) {
            log.error("upload aliyun oss file endpoint:{} filepath:{} exception:{}", aliyunOssConfig.getEndpoint(), saveDirAndFileName, exception);
            return null;
        }
        return getDownLoadUrl(saveDirAndFileName);
    }

    /**
     * 获取完整的下载地址
     *
     * @param dirFilePath OSS 中的相对路径
     * @return 完整的下载地址
     */
    @Override
    public String getDownLoadUrl(String dirFilePath) {
        OSS client1 = new OSSClientBuilder().build(aliyunOssConfig.getPublicEndpoint(), aliyunOssConfig.getAccessKeyId(), aliyunOssConfig.getAccessKeySecret());
        Date expiration = new Date(new Date().getTime() + 3600 * 1000);
        URL url = client1.generatePresignedUrl(aliyunOssConfig.getPublicBucket(), dirFilePath, expiration);
        return url.toString();
    }

    // 判断上传照片文件后缀是否合法
    public static boolean isAllowFileSuffix(String fixSuffix) {
        return IMG_SUFFIX.contains(fixSuffix.toLowerCase());
    }

    // 判断上传Excel是否合法
    public static boolean isAllowExcelFileSuffix(String fixSuffix) {
        return EXCEL_SUFFIX.contains(fixSuffix.toLowerCase());
    }

    /**
     * 获取文件的后缀名
     *
     * @param appendDot 是否拼接.
     * @return 文件的后缀名
     */
    public static String getFileSuffix(String fullFileName, boolean appendDot){
        if(fullFileName == null || !fullFileName.contains(".") || fullFileName.length() <= 1) {
            return "";
        }

        return (appendDot? "." : "") + fullFileName.substring(fullFileName.lastIndexOf(".") + 1);
    }
}

步骤三：编写OSS相关的配置类（获取 OSS 的基础配置信息）

Java 配置类：

package com.jxz.owner.sdk.oss.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * @Description: Oss 相关配置信息
 *
 * @Date: 2022/1/6
 * @Author: jiangXueZhi
 */
@Data
@Component
@ConfigurationProperties(prefix = "system.oss.aliyun-oss")
public class AliyunOssConfig {

    private String endpoint;

    private String accessKeyId;

    private String accessKeySecret;

    private String publicBucket;

    private String privateBucket;

    private String publicEndpoint;
}

yml 或者 properties 配置文件（根据自己的oss文件服务器进行配置）：

#系统业务参数
system:
  oss:
    # 阿里云OSS服务配置信息
    aliyun-oss:
      endpoint: oss-cn-shanghai.aliyuncs.com  #endpoint  如： oss-cn-beijing.aliyuncs.com
      public-bucket: public #公共读 桶名称
      private-bucket: private #私有读 桶名称
      access-key-id: key #AccessKeyId
      access-key-secret: secret  #AccessKeySecret
      public-endpoint: oss-cn-shanghai.aliyuncs.com  #endpoint  如： oss-cn-beijing.aliyuncs.com

步骤四：Excel展示（多sheet）

显示效果一切正常，图中显示整体缩小是因为为了方便大家看到全局而手动缩小

方式四   生成Excel文件到本地 

自适应表头格式类(ExcelWidthStyleStrategy) 见上述方式一的步骤一

调用方式 

String localFilePath = "logs/Disburse_Timeout_Pending_" + threeHoursBeforeCurrentTimestamp + ".xlsx"; // 文件生成地址
            ExcelUtils.writeExcel(localFilePath, "Disburse", pendingLongList, AllBankDisbursePendingHandleEmailPojo.class); // 生成本地文件

/**
     * 写出一个 excel 文件到本地
     * <br />
     * 将类型所有加了 @ExcelProperty 注解的属性全部写出
     *
     * @param fileName  文件路径(相对路径及绝对路径都支持)
     * @param sheetName sheet名
     * @param data      写出的数据
     * @param clazz     要写出数据类的Class类型对象
     * @param <T>       写出的数据类型
     */
    public static <T> void writeExcel(String fileName, String sheetName, List<T> data, Class<T> clazz) {
        writeExcel(null, fileName, sheetName, data, clazz);
    }


/**
     * 按照指定的属性名进行写出 一个 excel
     *
     * @param attrName  指定的属性名 必须与数据类型的属性名一致
     * @param fileName  文件名 不要后缀
     * @param sheetName sheet名
     * @param data      要写出的数据
     * @param clazz     要写出数据类的Class类型对象
     * @param <T>       要写出的数据类型
     */
    public static <T> void writeExcel(Set<String> attrName, String fileName, String sheetName, List<T> data, Class<T> clazz) {
        fileName = StringUtils.isBlank(fileName) ? "file1" : fileName;
        sheetName = StringUtils.isBlank(sheetName) ? "sheet0" : sheetName;

        try (FileOutputStream fos = new FileOutputStream(fileName)) {
            write(fos, attrName, sheetName, data, clazz);
            LOGGER.info("Excel file generated successfully, the file location is  {}", fileName);
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }




private static <T> void write(OutputStream os, Set<String> attrName, String sheetName, List<T> data, Class<T> clazz) {
        ExcelWriterBuilder write = EasyExcel.write(os, clazz);
        // 如果没有指定要写出那些属性数据，则写出全部
        if (!CollectionUtils.isEmpty(attrName)) {
            write.includeColumnFiledNames(attrName);
        }
//        write.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName).doWrite(data); // 默认表头
        write.registerWriteHandler(new ExcelWidthStyleStrategy()).sheet(sheetName).doWrite(data); // 自适应表头
    }

上传  

    @RequestMapping(value = "/import", method = RequestMethod.POST)
    public void upload(MultipartFile file) {
        String cloudFile = iOssService.upload(file, "excel", file.getOriginalFilename()); // 相对路径
        String path = iOssService.getDownLoadUrl(cloudFile); // 绝对路径
        System.out.println("###########  PATH : " + path);

        /* 解析文件 */
        List<CsIssueExcel> csIssues = ExcelUtils.resolveExcel(file, CsIssueExcel.class, null);
        if (csIssues != null) {
            System.out.println(csIssues.size());
        }
        System.out.println(csIssues);
    }

上传基于上述  IOssService 中的接口：

String upload(MultipartFile multipartFile, String dir, String fileName);

解析基于上述 ExcelUtils 中的方法：

public static <T> List<T> resolveExcel(MultipartFile multipartFile, Class<T> clazz, ReadListener<T> readListener)

解析的注意要点

        在调用解析工具方法时，接收数据的实体类一般需要另外建立，并且字段顺序与Excel表头顺序一致。

        在接收数据时，接收数据的实体类中的字段写入只与Excel表头的顺序相关！！！ 

 上传的文件：

 接收解析数据的实体类

package com.jxz.owner.entity.excel;

import lombok.Data;

/**
 * @Description: CsIssue 类的上传解析接收者
 *
 * @Date: 2022/1/12
 * @Author: jiangXueZhi
 */
@Data
public class CsIssueExcel {
    private String issueNo;

    private String accountNo;

    private String transactionId;

    private String transactionDate;

    private Integer instituteId;

    private Integer operationType;

    private String conclusion;

    private String details; // 相比Excel的表头，多出的字段在解析时为null
}

 解析的数据

2022-01-12 18:52:58.417  INFO 18292 --- [nio-7777-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2022-01-12 18:52:58.418  INFO 18292 --- [nio-7777-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2022-01-12 18:52:58.418  INFO 18292 --- [nio-7777-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 0 ms
2022-01-12 18:52:58.460  INFO 18292 --- [nio-7777-exec-1] c.j.o.s.oss.service.impl.OssServiceImpl  : upload file info java.io.FileInputStream@762b8c58
###########  PATH : http://calf.oss-cn-shanghai.aliyuncs.com/issue_upload.xlsx?Expires=1641988378&OSSAccessKeyId=Rk2SDBXcOOpKzXHx&Signature=QToLbiKy7Xyhj3DlayDoswTkWFc%3D
2022-01-12 18:52:59.178  WARN 18292 --- [nio-7777-exec-1] com.jxz.owner.utils.ExcelUtils           : Parsing is complete
10
[CsIssueExcel(issueNo=ino_0, accountNo=ano_0, transactionId=tid_0, transactionDate=2022-01-12, instituteId=0, operationType=1, conclusion=这是一个测试案例, details=null), CsIssueExcel(issueNo=ino_1, accountNo=ano_1, transactionId=tid_1, transactionDate=2022-01-12, instituteId=1, operationType=1, conclusion=这是一个测试案例, details=null), CsIssueExcel(issueNo=ino_4, accountNo=ano_4, transactionId=tid_4, transactionDate=2022-01-12, instituteId=2, operationType=1, conclusion=这是一个测试案例, details=null), CsIssueExcel(issueNo=ino_9, accountNo=ano_9, transactionId=tid_9, transactionDate=2022-01-12, instituteId=3, operationType=1, conclusion=这是一个测试案例, details=null), CsIssueExcel(issueNo=ino_16, accountNo=ano_16, transactionId=tid_16, transactionDate=2022-01-12, instituteId=4, operationType=1, conclusion=这是一个测试案例, details=null), CsIssueExcel(issueNo=ino_25, accountNo=ano_25, transactionId=tid_25, transactionDate=2022-01-12, instituteId=5, operationType=1, conclusion=这是一个测试案例, details=null), CsIssueExcel(issueNo=ino_36, accountNo=ano_36, transactionId=tid_36, transactionDate=2022-01-12, instituteId=6, operationType=1, conclusion=这是一个测试案例, details=null), CsIssueExcel(issueNo=ino_49, accountNo=ano_49, transactionId=tid_49, transactionDate=2022-01-12, instituteId=7, operationType=1, conclusion=这是一个测试案例, details=null), CsIssueExcel(issueNo=ino_64, accountNo=ano_64, transactionId=tid_64, transactionDate=2022-01-12, instituteId=8, operationType=1, conclusion=这是一个测试案例, details=null), CsIssueExcel(issueNo=ino_81, accountNo=ano_81, transactionId=tid_81, transactionDate=2022-01-12, instituteId=9, operationType=1, conclusion=这是一个测试案例, details=null)]

最后

以上就是烂漫水杯为你收集整理的Excel导入导出（导出有两种方式：直接返回流、返回下载地址） 程序入口导出（四种方式） 上传  的全部内容，希望文章能够帮你解决Excel导入导出（导出有两种方式：直接返回流、返回下载地址） 程序入口导出（四种方式） 上传  所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。