后端
一般在项目中使用需要拦截配置器放行该导出方法,然后在控制层调用【@CrossOrigin】注解
拦截配置器
package com.bw.note.config.interceptor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
/**
* 拦截配置器
* <功能详细描述>
*/
@Configuration
public class WebConfigurer implements WebMvcConfigurer {
@Autowired
private LoginInterceptor loginInterceptor;
// 这个方法是用来配置静态资源的,比如html,js,css,等等
/*@Override
public void addResourceHandlers(ResourceHandlerRegistry registry) {
}*/
// 这个方法用来注册拦截器,我们自己写好的拦截器需要通过这里添加注册才能生效
@Override
public void addInterceptors(InterceptorRegistry registry) {
registry.addInterceptor(loginInterceptor).excludePathPatterns(
"/api/v1/note/merchant/exportExcel").addPathPatterns("/**");
}
}
控制层
@CrossOrigin
@RestController
@RequestMapping("/api/v1/note/merchant")
public class MerchantController {
@GetMapping("/exportExcel")
public RestResult exportExcel(@RequestParam(value = "classifyName", required = false) String classifyName,
HttpServletResponse response) {
return classifyService.exportExcel( classifyName, response );
}
}
实现层
private static ThreadLocal<DateFormat> threadLocal = new ThreadLocal<DateFormat>(){
@Override
protected DateFormat initialValue(){
return new SimpleDateFormat("yyyy-MM-dd");
}
};
@Override
public RestResult exportExcel(String classifyName, HttpServletResponse response) {
//从数据库获取数据
List<SpClassify> spClassifyList = classifyMapper.listSpClassify( classifyName );
List<List<List<String>>> excelDatas = new ArrayList<>();
List<String> sheetNames = new ArrayList<String>() {{
add("分类信息");
}};
List<List<String>> excelData1 = new ArrayList<>();
List<String> head1 = new ArrayList<>();
head1.add("分类编码");
head1.add("分类名称");
head1.add("分类备注");
head1.add("创建时间");
excelData1.add(head1);
spClassifyList.forEach( spClassify -> {
List<String> strings = new ArrayList<>();
strings.add(spClassify.getCode());
strings.add(spClassify.getName());
strings.add(spClassify.getNote());
strings.add(threadLocal.get().format(spClassify.getCreateTime()));
excelData1.add(strings);
});
excelDatas.add(excelData1);
String date = threadLocal.get().format(new Date());
try {
ExcelUtilN.exportExcel(response, excelDatas, sheetNames, "info" + date + ".xlsx", 15);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
工具类
/**
* Excel表格导出
*
* @param response HttpServletResponse对象
* @param excelDatas Excel表格的数据,封装为List<List<String>>
* @param sheetNames sheet的名字
* @param fileName 导出Excel的文件名
* @param columnWidth Excel表格的宽度,建议为15
* @throws IOException 抛IO异常
*/
public static void exportExcel(HttpServletResponse response,
List<List<List<String>>> excelDatas,
List<String> sheetNames,
String fileName,
int columnWidth) throws IOException {
//声明一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFCellStyle cellStyle = workbook.createCellStyle();
//生成一个表格,设置表格名称
for (int i = 0; i < sheetNames.size(); i++) {
String sheetName = sheetNames.get(i);
XSSFSheet sheet = workbook.createSheet(sheetName);
//设置表格列宽度
sheet.setDefaultColumnWidth(columnWidth);
//写入List<List<String>>中的数据
int rowIndex = 0;
List<List<String>> excelData = excelDatas.get(i);
for (List<String> data : excelData) {
//创建一个row行,然后自增1
XSSFRow row = sheet.createRow(rowIndex++);
//遍历添加本行数据
for (int j = 0; j < data.size(); j++) {
//创建一个单元格
XSSFCell cell = row.createCell(j);
try {
Double value = Double.valueOf(data.get(j));
cellStyle.setDataFormat(workbook.createDataFormat().getFormat("0"));
cell.setCellValue(value);
} catch (Exception e) {
//创建一个内容对象
XSSFRichTextString text = new XSSFRichTextString(data.get(j));
//将内容对象的文字内容写入到单元格中
cell.setCellValue(text);
}
}
}
}
//准备将Excel的输出流通过response输出到页面下载
//八进制输出流
response.setContentType("application/octet-stream");
//设置导出Excel的名称
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//刷新缓冲
response.flushBuffer();
//workbook将Excel写入到response的输出流中,供页面下载该Excel文件
workbook.write(response.getOutputStream());
//关闭workbook
workbook.close();
}
前端
// 导出
exportBtn () {
axios({
method: 'GET',
url: '/api/v1/note/merchant/exportExcel',
params: {
classifyName: this.search.name.trim(),
pageSize: this.pageSize,
pageNo: this.pageNo
},
responseType: 'blob'
}).then(res => {
let obj = res.headers
let fileName
for (const key in obj) {
if (Object.hasOwnProperty.call(obj, key)) {
if (key == 'content-disposition') {
// fileName = obj[key].split("=")
fileName = obj[key].slice(20)
}
}
}
let date = new Date().getTime()
let url = window.URL.createObjectURL(new Blob([res.data]))
let link = document.createElement('a')
link.style.display = 'none'
link.href = url
link.setAttribute('download', fileName)
document.body.appendChild(link)
link.click()
document.body.removeChild(link)
this.$message({
message: '导出提现记录成功',
type: 'success'
})
}).catch(err => {
console.log(err)
})
}