1.前端Vue
(注意:调用后端接口时需指定{responseType:'blob'},否则导出的excel表格打不开)
//Vue导出方法
exportExcel(){
//searchParam:为前端需传到后端的所需参数
var searchParam = {
test:this.data.test,
};
//调用后端接口,传入参数searchParam
//注意:调用后端接口时需指定{responseType:'blob'},否则导出的excel表格打不开
request.post("/exceltest/exportExcel",searchParam,{responseType:'blob'}).then((res) => {
const aLink = document.createElement('a')
//为Blob指定导出的文件类型,此处为xlsx[{type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}]
var blob = new Blob([res.data], {type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"})
// //从response的headers中获取filename, 后端response.setHeader("Content-disposition", "attachment; filename=xxxx.docx") 设置的文件名;
var patt = new RegExp('filename=([^;]+\\.[^\\.;]+);*')
var contentDisposition = decodeURI(res.headers['content-disposition'])
var result = patt.exec(contentDisposition)
var fileName = result[1]
fileName=fileName = fileName.replace(/\"/g, '')
aLink.href = URL.createObjectURL(blob)
aLink.setAttribute('download', fileName) // 设置下载文件名称
document.body.appendChild(aLink)
aLink.click()
document.body.appendChild(aLink)
})
},
2.后端Spring boot
2.1Rest层
@RestController
@RequestMapping("/exceltest")
public class ExcelExportRest {
@Autowired
private ExcelExportDao excelExportDao;
@PostMapping("/exportExcel")
public void exportExcel(HttpServletResponse response,@RequestBody HashMap queryParam) {
try {
//定义文件名字
String filename = URLEncoder.encode(System.currentTimeMillis() + ".xlsx", "UTF-8");
response.setCharacterEncoding("UTF-8");
//设定输出文件头
response.setHeader("Content-disposition", "attachment; filename=" + filename);
// 定义输出类型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//从数据库查询需写入excel的结果集[queryParam前端所传参数]
//注意返回类型需指定为定义的实体:SupportingDetailsDto
List<SupportingDetailsDto> hashMaps = excelExportDao.querySendMaterial(queryParam);
//将实体以流的方式写入
EasyExcel.write(response.getOutputStream(), SupportingDetailsDto.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//指定sheet名字
.autoCloseStream(Boolean.FALSE).sheet("配套明细")
//写入数据库返回的参数到excel
.doWrite(hashMaps);
} catch (Exception e) {
e.printStackTrace();
System.out.println("失败!");
}
}
}
2.2Dto层
@Data
@EqualsAndHashCode
public class SupportingDetailsDto {
//数据类型需和数据库返回类型一致
@ExcelProperty("test字段名称")
private String test;
3.pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/poi/poi -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>