在很多时候我们都会碰到关于文件上传和下载的问题,本项目中我们使用的是minIO。
在此之前我们需先导入这两个依赖,hutool是给后面做压缩文件下载准备的。
<dependency>
<groupId>com.kotei.framework</groupId>
<artifactId>minio-client</artifactId>
<version>0.0.2-SNAPSHOT</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.4</version>
</dependency>
先将数据从数据库读取出来,我们这里使用的是map根据每条线路的不同,分多个sheet去填充数据的,resolveDbDataToExcelDataUtils类中就是处理数据库数据到excel填充实体类之间的一个转换,如果你数据库里的字段和excel填充实体类中的一致,那也可以不用写,我这里是需要做一个转换的。
/** 获取每条路线对应数据 */
public Map<String, List<VerticalCurveElementExcelDataVo>> toVerticalCurveData(List<KProjectRouteEntity> routes) {
// 储存多个路线的数据
Map<String, List<VerticalCurveElementEntity>> map = new HashMap<>(10);
Map<String, List<VerticalCurveElementExcelDataVo>> excelMap = new HashMap<>(16);
for (KProjectRouteEntity route : routes) {
List<VerticalCurveElementEntity> list = verticalCurveElementService
.list(new QueryWrapper<VerticalCurveElementEntity>().eq("route_id", route.getId()).orderByAsc("bp_stake"));
map.put(route.getRouteName(), list);
}
// 处理每一个sheet的数据
for (Map.Entry<String, List<VerticalCurveElementEntity>> entry : map.entrySet()) {
List<VerticalCurveElementExcelDataVo> excelDataList = new ArrayList<>();
List<VerticalCurveElementEntity> list = entry.getValue();
int i = 1;
for (VerticalCurveElementEntity verticalCurveElementEntity : list) {
VerticalCurveElementExcelDataVo vo = new VerticalCurveElementExcelDataVo();
VerticalCurveElementExcelData data = resolveDbDataToExcelDataUtils.toVerticalCurveElementExcelData(verticalCurveElementEntity, entry.getKey());
BeanUtils.copyProperties(verticalCurveElementExcelData, vo);
vo.setId(i++);
excelDataList.add(vo);
}
excelMap.put(entry.getKey(), excelDataList);
}
return excelMap;
}
拿到数据之后填充excel表格,并上传至minIO。
@Value("${excel.template.realPath}")
private String excelTemplateRealpath;
/** 填充excel表数据并上传 */
public void verticalCurveDataUploadMinIo(List<KProjectRouteEntity> routes, String projectName, LoginUserVo user, Long projectId) throws IOException {
// 获取纵曲线表数据
Map<String, List<VerticalCurveElementExcelDataVo>> verticalCurveData = toVerticalCurveData(routes);
// 判断是否有数据
boolean flag = false;
for (String s : verticalCurveData.keySet()) {
if (verticalCurveData.get(s) != null && verticalCurveData.get(s).size() > 0) {
flag = true;
break;
}
}
if (flag) {
// 获取模板路径
String verticalCurveExcelPath = excelTemplateRealpath + "纵坡、竖曲线表.xlsx";
InputStream is = new FileInputStream(verticalCurveExcelPath);
is = copySheet(routes, is);
// 读取模板表
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel.write(outputStream).excelType(ExcelTypeEnum.XLSX).withTemplate(is).build();
for (String rout : verticalCurveData.keySet()) {
// 获取不同路线的值
List<VerticalCurveElementExcelDataVo> value = verticalCurveData.get(rout);
WriteSheet writeSheet = EasyExcel.writerSheet(rout).build();
excelWriter.fill(value, writeSheet);
}
excelWriter.finish();
byte[] bytes = outputStream.toByteArray();
ByteArrayInputStream inputStream = new ByteArrayInputStream(bytes);
MultipartFile multipartFile = new MockMultipartFile("纵坡、竖曲线表" + projectName + ".xlsx", inputStream);
// 上传文件至minIo并储存文件信息至db
// 这里使用的minIo上传方法如果想了解的话,可以移步我的另一篇文章
kProjectFileService.insertProjectFile(multipartFile, user.getSiteId(), user.getId(), projectId.intValue(), 1);
outputStream.close();
is.close();
}
}
/**
* 复制sheet,并给每个sheet赋值
*/
public InputStream copySheet(List<KProjectRouteEntity> routes, InputStream is) throws IOException {
XSSFWorkbook sheets = new XSSFWorkbook(is);
ByteArrayOutputStream bos = new ByteArrayOutputStream();
for (int i = 0; i < routes.size(); i++) {
String sheetName = routes.get(i).getRouteName();
if (i == 0) {
sheets.setSheetName(i, sheetName);
} else {
sheets.cloneSheet(0, sheetName);
}
}
sheets.write(bos);
byte[] bArray = bos.toByteArray();
// 不需要调用close()关闭流,在调用ExcelWriter.finish()会被关闭
return new ByteArrayInputStream(bArray);
}
ServiceImpl,服务层调用方法。
/**
* 将该项目下挂载的文件找出,并上传到minIo(这里是调用的方法)
*/
@Override
public List<KProjectFileEntity> getAllFilesByProjectId(Long projectId, String projectName, LoginUserVo user) throws IOException {
// 获取所有的路线,遍历路线id,储存到map中
List<KProjectRouteEntity> routes = kProjectRouteService.list(new QueryWrapper<KProjectRouteEntity>()
.eq("is_delete", 0).eq("project_id", projectId));
if (routes != null && routes.size() > 0) {
projectFileTools.verticalCurveDataUploadMinIo(routes, projectName, user, projectId);
// 查询该项目下对应的表信息
return downloadFileByProject(projectId);
}
return null;
}
Controller层,这里用到的是@Controller注解,如果使用@RestController注解的话,在下载压缩文件时会报错:Could not find acceptable representation。
@GetMapping("/download")
public ResultBody downloadByProjectId(Long projectId, String projectName, HttpServletRequest request, HttpServletResponse response) throws IOException {
List<KProjectFileEntity> entities = kProjectFileService.downloadFileByProject(projectId);
if (entities != null && entities.size() > 0) {
minIoUtils.projectFileDownload(entities, response);
return ResultBody.success(ResultStatus.SUCCESS);
} else {
LoginUserVo user = tokenService.getUser(request);
// 将数据写入excel表,并上传至minIo
List<KProjectFileEntity> allFiles = kProjectFileService.getAllFilesByProjectId(projectId, projectName, user);
if (allFiles != null) {
// 获取项目对应文件
List<KProjectFileEntity> files = kProjectFileService.downloadFileByProject(projectId);
minIoUtils.projectFileDownload(files, response);
return ResultBody.success(ResultStatus.SUCCESS);
}
return ResultBody.failed(ResultStatus.NOT_FOUND);
}
}
下载文件,压缩,并响应。
public void projectFileDownload(List<KProjectFileEntity> entities, HttpServletResponse response) throws IOException {
//被压缩文件InputStream
InputStream[] fileStreams = new InputStream[entities.size()];
//被压缩文件名称
String[] fileNames = new String[entities.size()];
for (int i = 0; i < entities.size(); i++) {
JSONObject object = download(entities.get(i));
if (object != null) {
String data = object.getString("data");
URL url = new URL(data);
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
// 设置超时间为3秒
conn.setConnectTimeout(3 * 1000);
// 防止屏蔽程序抓取而返回403错误
conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
// 得到输入流
InputStream is = conn.getInputStream();
fileStreams[i] = is;
fileNames[i] = entities.get(i).getFileName();
}
}
response.setHeader("Content-disposition", "attachment;filename =" + UUID.randomUUID().toString() + ".zip");
// 这里要注意如果你响应的不是zip包应该要改一下contenType的值
response.setContentType("application/zip");
response.setHeader("Expires", "0");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
// 设置编码格式,以免文件名中有中文会报错
response.setCharacterEncoding("utf-8");
//多个文件压缩成压缩包返回
ZipUtil.zip(response.getOutputStream(), fileNames, fileStreams);
}