@RestController
public class ExcelDownLoadController {
@Resource
private AddCustomerService addCustomerService;
@ApiOperation(value = "新增客户数据", notes = "导出时间范围内新增客户数据")
@ApiImplicitParams({
@ApiImplicitParam(name = "startTime",paramType = "query", value = "开始时间", required = true, dataType = "Date"),
@ApiImplicitParam(name = "endTime",paramType = "query", value = "结束时间", required = true, dataType = "Date")
})
@RequestMapping(value = "/api/fm/addcustomer", method = RequestMethod.GET)
public void addCustomer(HttpServletResponse response,@RequestParam @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") Date startTime,@RequestParam @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") Date endTime) throws IOException {
List<HashMap<String, Object>> userinfos = addCustomerService.getNewUserInfo(startTime, endTime);
List<HashMap<String, Object>> merchantinfos = addCustomerService.getNewMerchantInfo(startTime, endTime);
Integer index = 1;
InputStream is = XlsDownLoadController.class.getClassLoader().getResourceAsStream("export/新增客户数据.xls");
Workbook workbook = new HSSFWorkbook(is);
Sheet sheetOne = workbook.getSheet("往来单位");
Sheet sheetTwo = workbook.getSheet("联系方式");
for (HashMap<String, Object> userinfo : userinfos) {
Row row = sheetOne.createRow(index);
row.createCell(0).setCellValue(getCellValue(userinfo,"user_id"));
row.createCell(1).setCellValue(getCellValue(userinfo,"real_name"));
row.createCell(3).setCellValue("客户");
row.createCell(4).setCellValue("1");
row = sheetTwo.createRow(index);
row.createCell(0).setCellValue(getCellValue(userinfo,"user_id"));
row.createCell(1).setCellValue(getCellValue(userinfo,"real_name"));
row.createCell(8).setCellValue(getCellValue(userinfo,"mobile"));
index++;
}
for (HashMap<String, Object> merchantinfo : merchantinfos) {
Row row = sheetOne.createRow(index);
row.createCell(0).setCellValue(getCellValue(merchantinfo,"merchant_no"));
row.createCell(1).setCellValue(getCellValue(merchantinfo,"merchant_name"));
row.createCell(3).setCellValue("客户");
row.createCell(4).setCellValue("1");
row = sheetTwo.createRow(index);
row.createCell(0).setCellValue(getCellValue(merchantinfo,"merchant_no"));
row.createCell(1).setCellValue(getCellValue(merchantinfo,"merchant_name"));
row.createCell(8).setCellValue(getCellValue(merchantinfo,"mobile"));
index++;
}
String fileName = "("+sdf.format(startTime)+"~"+sdf.format(endTime)+").xls";
//解决火狐浏览器乱码的问题:Header中填写这个Content-Disposition很关键
response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode("新增客户数据", "UTF-8")+fileName);
workbook.write(response.getOutputStream());
workbook.close();
response.getOutputStream().close();
}
private String getCellValue(HashMap<String, Object> map,String key) {
if(null==map.get(key)) {
return "";
}
if(map.get(key).getClass().equals(String.class)) {
return map.get(key).toString();
}
if(map.get(key).getClass().equals(Integer.class)) {
Integer value = (Integer)map.get(key);
return value.toString();
}
if(map.get(key).getClass().equals(Long.class)) {
Long value = (Long)map.get(key);
return value.toString();
}
if(map.get(key).getClass().equals(Integer.class)) {
Double value = (Double)map.get(key);
DecimalFormat df= new DecimalFormat("######0.00");
return df.format(value);
}
if(map.get(key).getClass().equals(Date.class)) {
Date value = (Date)map.get(key);
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
return df.format(value);
}
return map.get(key).toString();
}
}
java后端实现Excel数据的导出、解决导出数据会在火狐浏览器乱码的问题
最新推荐文章于 2022-11-21 13:43:14 发布