最近写了一个excel导出 谷歌导出都是可以的 火狐导出会报错 并且是个空白文件 后来查了原因是因为到处文件名称中有空格
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH-mm");
String name = "逾期数据" + format.format(new Date()) + ".xlsx";
后改称下面这种的就可以了
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmm");
String name = "逾期数据" + format.format(new Date()) + ".xlsx";
@RequestMapping("/exportPage")
public void exportPage(Long partnerId, Integer bankId, Integer overdueStatus, Integer compensatoryStatus,
Integer processStatus, String defaultSymbol, Integer consecutiveOverdueTimes, String cumulSymbol,
Integer cumulativeOverdueTimes, String search, HttpServletResponse response, HttpServletRequest request) {
// 1. 查询本机构及下属机构id集合
List<Long> partnerIds = null;
if (partnerId != null) {
partnerIds = new ArrayList<>();
partnerIds.add(partnerId);
} else {
UserDetail userDetail = (UserDetail)ShiroUtils.getSessionAttribute("userDetail");
List<PmsDepartment> list = pmsDepartmentService.getCompanyAndChildCompanyById(userDetail.getPartnerId());
if (CollectionUtils.isNotEmpty(list)) {
partnerIds = new ArrayList<>();
for (PmsDepartment item : list) {
partnerIds.add(item.getId());
}
}
}
List<OverdueOrderInfoVo> overdueOrderInfoVo =
overdueOrderInfoService.exportOverdueInfo(partnerIds, bankId, overdueStatus, compensatoryStatus,
processStatus, defaultSymbol, consecutiveOverdueTimes, cumulSymbol, cumulativeOverdueTimes, search);
String[] headerText = {"订单号", "借款人/身份证号", "资金方", "逾期还款日期", "当前逾期金额", "分期金额", "连续违约(资方)", "累计违约次数", "代偿总次数",
"代偿状态", "逾期状态", "处理状态"};
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmm");
String name = "逾期数据" + format.format(new Date()) + ".xlsx";
try {
downExcel(response, request, overdueOrderInfoVo, headerText, name);
} catch (IOException e) {
e.printStackTrace();
}
}
public void downExcel(HttpServletResponse response, HttpServletRequest request, List<OverdueOrderInfoVo> list,
String[] headerText, String name) throws IOException {
String[] compenStatus = {"未代偿", "部分代偿", "全额代偿", "已还清"};
String[] overStatus = {"已逾期", "逾期已还"};
String[] proStatus = {"未处理", "已处理"};
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
// 新增数据行,并且设置单元格数据
int rowNum = 1;
String[] headers = headerText;
// headers表示excel表中第一行的表头
XSSFRow row = sheet.createRow(0);
// 在excel表中添加表头
for (int i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
if (CollectionUtils.isNotEmpty(list)) {
// 在表中存放查询到的数据放入对应的列
for (OverdueOrderInfoVo overdueInfoVo : list) {
XSSFRow row1 = sheet.createRow(rowNum);
row1.createCell(0).setCellValue(overdueInfoVo.getOrderId() + "");
if (StringUtils.isNotEmpty(overdueInfoVo.getMmbName())
&& StringUtils.isNotEmpty(overdueInfoVo.getCertNumber())) {
row1.createCell(1).setCellValue(overdueInfoVo.getMmbName() + "/" + overdueInfoVo.getCertNumber());
} else if (StringUtils.isEmpty(overdueInfoVo.getMmbName())
&& StringUtils.isNotEmpty(overdueInfoVo.getCertNumber())) {
row1.createCell(1).setCellValue(overdueInfoVo.getCertNumber());
} else if (StringUtils.isNotEmpty(overdueInfoVo.getMmbName())
&& StringUtils.isEmpty(overdueInfoVo.getCertNumber())) {
row1.createCell(1).setCellValue(overdueInfoVo.getMmbName());
}
row1.createCell(2).setCellValue(overdueInfoVo.getBankName());
if (overdueInfoVo.getLoanDate() != null) {
row1.createCell(3).setCellValue(format.format(overdueInfoVo.getLoanDate()));
} else {
row1.createCell(3).setCellValue("");
}
row1.createCell(4).setCellValue(overdueInfoVo.getOverdueAmount());
row1.createCell(5).setCellValue(overdueInfoVo.getLoanAmount());
row1.createCell(6).setCellValue(overdueInfoVo.getConsecutiveOverdueTimes());
row1.createCell(7).setCellValue(overdueInfoVo.getCumulativeOverdueTimes());
row1.createCell(8).setCellValue(overdueInfoVo.getCompensatoryTimes());
String compenSts = overdueInfoVo.getCompensatoryStatus() + "";
compenSts = "0".equals(overdueInfoVo.getCompensatoryStatus() + "") ? compenStatus[0] : compenSts;
compenSts = "1".equals(overdueInfoVo.getCompensatoryStatus() + "") ? compenStatus[1] : compenSts;
compenSts = "2".equals(overdueInfoVo.getCompensatoryStatus() + "") ? compenStatus[2] : compenSts;
compenSts = "3".equals(overdueInfoVo.getCompensatoryStatus() + "") ? compenStatus[3] : compenSts;
row1.createCell(9).setCellValue(compenSts);
String oveStatus = overdueInfoVo.getOverdueStatus() + "";
oveStatus = "0".equals(overdueInfoVo.getOverdueStatus() + "") ? overStatus[0] : oveStatus;
oveStatus = "1".equals(overdueInfoVo.getOverdueStatus() + "") ? overStatus[1] : oveStatus;
row1.createCell(10).setCellValue(oveStatus);
String proStus = overdueInfoVo.getProcessStatus() + "";
proStus = "0".equals(overdueInfoVo.getProcessStatus() + "") ? proStatus[0] : proStus;
proStus = "1".equals(overdueInfoVo.getProcessStatus() + "") ? proStatus[1] : proStus;
row1.createCell(11).setCellValue(proStus);
rowNum++;
}
}
String fileName;
String userAgent = request.getHeader("user-agent").toLowerCase();
if (userAgent.contains("msie") || userAgent.contains("trident")) {
// win10 ie edge 浏览器 和其他系统的ie
fileName = URLEncoder.encode(name, "UTF-8");
} else {
// 其他的浏览器
fileName = new String(name.getBytes("UTF-8"), "ISO-8859-1");
}
// 设置要导出的文件的名字
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());
}