注：文章皆为个人纪录，可用性请以最终结果为准，若有错还请大佬们指出，谢谢！
程序入口
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）
显示效果一切正常，图中显示整体缩小是因为为了方便大家看到全局而手动缩小
直接下载流的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）
显示效果一切正常，图中显示整体缩小是因为为了方便大家看到全局而手动缩小
多sheet页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); // 自适应表头
}方式五 多sheet页（带表头基础设置）
核心下载方法
/**
* 下载数据
*
* @param requestParamJson 查询JSON参数
* @param fileName 下载文件名称 一般格式为 时间戳.xlsx
*/
private String getTransactionCollectionList(String requestParamJson, String fileName) {
try {
TransactionPaymentQueryReq req = JSONObject.parseObject(requestParamJson, TransactionPaymentQueryReq.class); // 系统自定义查询参数
Integer page = 1; // 本系统中用分页查询的逻辑来做的，也可以自定义一个变量再用limit关键字来查询数据，思路相同
Integer sheetNum = 1; // 默认sheet名称后缀
File file = File.createTempFile("file", ".xlxs");
String tempFilePath = file.getPath(); // 本地文件
ExcelWriter excelWriter = EasyExcel.write(tempFilePath, PaymentVO.class).build(); // 创建流, PaymentVO 为表头信息
while (true) {
req.setPage(page);
CommonAsyncDownloadResp<PaymentResp> resp = fileDownloadSpi.paymentList(req); // 系统自定义查询数据的方法, 内部修改了req.getSize()的值为 5000, 即单个sheet的最大数据条数为5000条
List<PaymentResp> list = resp.getList();
if (list.isEmpty()) {
break;
}
List<PaymentVO> collect = list.stream().map(PaymentVO::new).collect(Collectors.toList()); // PaymentVO::new 重写了构造器, 进行数据的封装
// WriteSheet writeSheet = EasyExcel.writerSheet("sheet" + sheetNum).registerWriteHandler(new ExcelWidthStyleStrategy()).build(); // new ExcelWidthStyleStrategy() 自定义表头
WriteSheet writeSheet = EasyExcel.writerSheet("sheet" + sheetNum).registerWriteHandler(headWriteStyle()).build(); // 另一种自定义表头
excelWriter.write(collect, writeSheet); // 写入数据
// 最后一页
if (collect.size() < req.getSize()) { // req.getSize() --> 每一页sheet展示的最大数据条数, 本系统中用分页查询的逻辑来做的，也可以自定义一个变量再用limit关键字来查询数据，思路相同
break;
}
page++;
sheetNum++;
}
excelWriter.finish(); // 写入数据完成
String filePath = ExcelWriterUtils.generateLocalTempFileOssUpload(tempFilePath, fileName); // TODO 调用系统自定义工具方法，上传到OSS等服务器后的路径
ExcelWriterUtils.deleteLocalTempFile(tempFilePath); // TODO 调用系统自定义工具方法，删除本地文件
return filePath;
}catch (Exception e) {
log.error("Async download transaction collection list error, Exception: {} ### ", e.getMessage());
return "";
}
}
/**
* 表头设计
*/
public HorizontalCellStyleStrategy headWriteStyle() {
//头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置头的背景绿色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//设置头不自动换行
headWriteCellStyle.setWrapped(false);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy();
horizontalCellStyleStrategy.setHeadWriteCellStyle(headWriteCellStyle);
return horizontalCellStyleStrategy;
}表头
package xxx;
import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.xxx.CountryEnum;
import com.xxx.NumberUtils;
import com.xxxp.PaymentResp;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.math.BigDecimal;
import java.time.format.DateTimeFormatter;
@Data
@NoArgsConstructor
public class PaymentVO {
@ExcelProperty(value = "Merchant Name")
private String merchantName;
@ExcelProperty(value = "Merchant's Counterparty")
private String mchCounterparty;
@ExcelProperty(value = "Merchant No.")
private String merchantNo;
@ExcelProperty(value = "Merchant Order ID")
private String merchantOrderId;
@ExcelProperty(value = "Transaction ID")
private String transactionId;
@ExcelProperty(value = "Transaction Country")
private String transactionCountry;
@ExcelProperty(value = "Transaction Currency")
private String transactionCurrency;
@ExcelProperty(value = "Payment Institute")
private String instituteName;
@ExcelProperty(value = "Payment Channel")
private String channelCode;
@ExcelProperty(value = "Amount")
private BigDecimal transactionAmount;
@ExcelProperty(value = "Create Time")
private String transactionCreateTime;
@ExcelProperty(value = "Complete Time")
private String transactionReturnTime;
@ExcelProperty(value = "Remark")
private String remark;
@ExcelProperty(value = "Payin Status")
private String transactionStatus;
@ExcelProperty(value = "Settlement Status")
private String settlementStatus;
@ExcelProperty(value = "Estimated Settlement Time")
private String estimatedSettlementTime;
@ExcelProperty(value = "Actual Settlement Time")
private String actualSettlementTime;
public PaymentVO(PaymentResp resp) {
this.merchantName = resp.getMerchantName();
this.mchCounterparty = getMchCounterpartyText(resp.getMchCounterparty());
this.merchantNo = resp.getMerchantNo();
this.merchantOrderId = resp.getMerchantOrderId();
this.transactionId = resp.getTransactionId();
this.transactionCountry = CountryEnum.getNameByCountryCode(resp.getTransactionCountry());
this.transactionCurrency = resp.getTransactionCurrency();
this.instituteName = resp.getInstituteName();
this.channelCode = resp.getChannelCode();
this.transactionAmount = NumberUtils.convertToDecimal(resp.getTransactionAmount());
this.transactionCreateTime = resp.getTransactionCreateTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
this.transactionReturnTime = BeanUtil.isNotEmpty(resp.getTransactionReturnTime()) ? resp.getTransactionReturnTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) : "";
this.remark = resp.getRemark();
this.transactionStatus = getTransactionStatusText(resp.getTransactionStatus());
this.settlementStatus = getSettlementStatusString(resp.getSettlementStatus());
this.estimatedSettlementTime = resp.getEstimatedSettlementTime();
this.actualSettlementTime = resp.getSettlementTime();
}
private String getMchCounterpartyText(Integer type) {
if (type == 1){
return "Unlock";
} else if (type == 2){
return "PayCools International";
} else {
return "";
}
}
private String getTransactionStatusText(Integer status) {
String text;
switch (status) {
case 1:
text = "Paying in";
break;
case 2:
text = "Pay For Success";
break;
case 3:
text = "Pay For Failure";
break;
default:
text = "";
break;
}
return text;
}
public static String getSettlementStatusString(Integer settlementStatus){
switch (settlementStatus){
case 1: return "Settled";
case 2: return "Pending";
case 3: return "";
}
return null ;
}
}
下载文件
上传
@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)]
1699
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
