controller
@ApiOperation(value = "excel", httpMethod = "GET", notes = "excel", produces="application/octet-stream")
@GetMapping(value = "/excel/{reportYear}")
public void exportExcel(@PathVariable Integer reportYear, HttpServletResponse response) {
service.exportExcel(reportYear, response);
}
service
@Override
public void exportExcel(Integer reportYear, HttpServletResponse response) {
//查询需要导出到excel的数据用ResponseDTO接收
ArrayList<ResponseExcelExportDTO> dataList = baseService.selectReportRecordPageByReportYear(reportYear);
try {
//文件名
String fileName = reportYear+".xlsx";
//读取已经存在的excel模板并获取输入流
ClassPathResource classPathResource = new ClassPathResource("templates/xxxx.xlsx");
InputStream inputStream = classPathResource.getInputStream();
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
// 获取DTO所有属性
Class<ResponseExcelExportDTO> responseExcelDTOClass = ResponseExcelExportDTO.class;
Field[] field = responseExcelDTOClass.getDeclaredFields();
// 写入数据
for (int i = 0; i < dataList.size(); i++) {
//从excel模板表中第七行开始写
Row row1 = sheet.createRow(i + 6);
if (row1 != null) {
for (int j = 0; j < field.length; j++) {
Cell cell1 = row1.createCell(j);
if (cell1 != null) {
Field f = field[j];
f.setAccessible(true); // 设置些属性是可以访问的
Object value = null;// 得到此属性的值
value = f.get(dataList.get(i));
if (value != null) {
if (f.getName().endsWith("Name")) {
cell1.setCellValue((String.valueOf(value)));
} else if (Integer.parseInt(String.valueOf(value)) == 0) {
cell1.setCellValue("否");
} else if (Integer.parseInt(String.valueOf(value)) == 1) {
cell1.setCellValue("是");
} else {
break;
}
}
}
}
}
}
ServletOutputStream outputStream = response.getOutputStream();
response.setCharacterEncoding("utf-8");
// 设置强制下载不打开
response.setContentType("application/force-download");
// 设置文件名
String fileNameCode = URLEncoder.encode(fileName, "UTF8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
response.setHeader("filename", fileNameCode);
response.setHeader("Access-Control-Expose-Headers", "filename");
workbook.write(outputStream);
outputStream.close();
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}