1.引入依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.1.0</version>
</dependency>
<!-- Excel导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2.导出功能实现
先参考Hutool参考文档
@RequestMapping(value = "excle")
@ResponseBody
public void excle(HttpServletRequest request, LawInspectRecord lawInspectRecord, RedirectAttributes redirectAttributes, HttpServletResponse response) {
Date date = new Date();
lawInspectRecord.setInspectType("1");
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmm");
try {
Page<LawInspectRecord> page = lawInspectRecordService.findPages(new Page<LawInspectRecord>(request, response), lawInspectRecord);
//查出所有数据
List<LawInspectRecord> Sale = page.getList();
ExcelWriter writer = ExcelUtil.getWriter(true);
writer.setColumnWidth(-1, 30);
writer.setRowHeight(-1, 20);
// String fileName = "订单信息数据"+format.format(date);
// FileOutputStream output = new FileOutputStream("D:\\data"+fileName+".xls");
//存储循环后的数据
List<LawInspectRecord> lis = new ArrayList<>();
Sale.forEach(s -> {
/*进行字典值转换*/
String res = lawInspectRecordService.findByresult(s.getResult());
String color = lawInspectRecordService.findByColor(s.getHpys());
String carType = lawInspectRecordService.findByCartype(s.getVehicleType());
String fuelType = lawInspectRecordService.findByFueltype(s.getRlzl());
s.setVehicleType(carType);
s.setRlzl(fuelType);
s.setResult(res);
s.setHpys(color);
lis.add(s);
});
//指定对应元素对应列
writer.addHeaderAlias("motorOwner", "机动车所有人");
//未指定的元素是否展示
writer.setOnlyAlias(true);
writer.write(lis);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=road_check" + System.currentTimeMillis() + ".xlsx");
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
// 一次性写出内容
// writer.flush(out);
// 关闭writer,释放内存
writer.close();
IoUtil.close(out);
logger.info("__________数据导出成功!");
/* return
} catch (Exception e) {
e.printStackTrace();
logger.info("数据导出失败!");
}
addMessage(redirectAttributes, "数据导出成功!");
}
3.导入功能
@RequestMapping("importXls")
@ResponseBody
public ApiResponse importXls(@RequestParam(value = "impfile", required = false) MultipartFile file, HttpServletRequest request, OverVehicle overVehicler, RedirectAttributes redirectAttributes) {
try {
InputStream bookStream = file.getInputStream();
// String sbcsId = ObdUtils.getSbcsId(UserUtils.getUser().getId());
ExcelReader xls = ExcelUtil.getReader(bookStream);
if(xls!=null&&xls.getRowCount()>0) {
//指定导入列对应元素,用map存储
HashMap<String, String> headerAlias = new HashMap<>();
headerAlias.put("车牌号", "vehicleNumber");
headerAlias.put("车主电话", "vehicleHosterPhone");
// String update_flag=UserUtils.getUser().getLoginName()+"-"+ DateUtil.now();
xls.setHeaderAlias(headerAlias);
//拿到元素后用list存储
List<OverVehicle> list = xls.read(0, 1, OverVehicle.class);
list.forEach(s->{
//字典值转换
s.setDelFlag("0");
if(StringUtils.isNotBlank(s.getFuelType())){
String fuelType= s.getFuelType();
String fueltt= overVehicleService.findBytype(fuelType);
s.setFuelType(fueltt);
}
if(StringUtils.isNotBlank(s.getVehicleType())){
String hicle= s.getVehicleType();
String vehicleT= overVehicleService.findByvehicleType(hicle);
s.setVehicleType(vehicleT);
}
if(StringUtils.isNotBlank(s.getEmissionStandard())){
String hicl= s.getEmissionStandard();
String mission= overVehicleService.findByStandard(hicl);
s.setEmissionStandard(mission);
}
//存入数据库
logger.info("2222222----"+s);
overVehicleService.save(s);
});
return ApiResponse.ok("ok", list.size());
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ApiResponse.error("error");
}