之前工作需要前端写了一堆导出,感觉导出结果样式受限。换个思路,实现了后端导出
后端代码
maven插件引入
<!-- jxls poi -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.2.13.Final</version>
</dependency>
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.15</version>
</dependency>
工具类函数TemplateExcelUtils.java
package com.example.util;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Map;
public class TemplateExcelUtils {
/**
* 根据模板导出数据
* @param fileName
* @param sourcePath resource/template文件夹下路径
* @param beanParams
* @param response
* @throws Exception
*/
public static void downLoadExcel(String fileName,String sourcePath, Map<String, Object> beanParams, HttpServletResponse response)
throws Exception {
try{
OutputStream os = getOutputStream(fileName,response);
//读取模板
InputStream is = TemplateExcelUtils.class.getClassLoader().getResourceAsStream("template/"+sourcePath);
XLSTransformer transformer = new XLSTransformer();
//向模板中写入内容
Workbook workbook = transformer.transformXLS(is, beanParams);
//写入成功后转化为输出流
workbook.write(os);
}catch (Exception e){
e.printStackTrace();
throw e;
}
}
/**
* 导出文件时为Writer生成OutputStream.
* @param fileName 文件名
* @param response response
* @return ""
*/
private static OutputStream getOutputStream(String fileName,HttpServletResponse response) throws Exception {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
throw new Exception("导出excel表格失败!", e);
}
}
}
controller层 EnvProtectFormExportExcelController.java
package com.example.controller;
import com.example.util.ResultMoudel;
import com.example.util.TemplateExcelUtils;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
import java.util.stream.IntStream;
/**
* <p>
* 前端控制器
* </p>
*
* @author wangmin
* @since 2024-01-19
*/
@RestController
@RequestMapping("envProtect/envProtectForm")
public class EnvProtectFormExportExcelController extends BaseController{
/*导出excel*/
@PostMapping("exportEnvProtectForm")
public void exportEnvProtectForm(@RequestBody Map map, HttpServletResponse response){
List<Map<String,String>> list = (List<Map<String,String>>) map.get("tableData");
// list.stream().forEach(map1 ->{
//
// });
IntStream.range(0, list.size()).forEach(i -> {
list.get(i).put("colA",Integer.toString(i+1));
});
// for (int i = 0;i< list.size();i++){
// list.get(i).put("colA",Integer.toString(i+1));
// }
try {
System.out.println("===========tableData:" + map.get("tableData"));
Map<String,Object> param = new HashMap<>();
param.put("tbrq", map.get("tbrq"));
param.put("shr", map.get("reviewer"));
param.put("list",list);
TemplateExcelUtils.downLoadExcel("环保督查问责清单月报表","reportEP9.xlsx",param,response);
ResultMoudel res=new ResultMoudel().success("导出成功!");
} catch (Exception e) {
ResultMoudel res=new ResultMoudel().error("导出数据异常!");
}
}
}
前端调用
handleExport() {
let url = "/envProtect/envProtectForm/exportEnvProtectForm";
let body = {
tbrq: this.curMoth,
reviewer: this.formData.reviewer,
tableData: this.tableData,
};
this.$http
.post(url, body, {
responseType: "blob",
})
.then((res) => {
if (res.data.resultFlag == "error") {
this.$notify({
title: "错误",
message: res.data.body,
duration: "3000",
type: "error",
});
} else {
console.log("==============response", res);
String.fromCodePoint;
const fileName = (res.headers["content-disposition"] || "").replace(
"attachment;filename=",""
);
const export_blob = new Blob([res.data]);
let elink = document.createElement("a");
elink.download = decodeURI(fileName);
elink.href = URL.createObjectURL(export_blob);
document.body.appendChild(elink);
elink.click();
URL.revokeObjectURL(elink.href);
document.body.removeChild(elink);
}
});
}