↵首次接触导入导出使用的的Apache下的POI技术,需要自己手动去创建excel,然后创建sheel,再去创建每行,为每行添加数据(Cell),另外手动去调整它的行高 宽度.....,一个方法下来,相当的冗余。如果有改动相当痛苦。后来在项目中 看到有更加简便的方法,特此记录下。
1 文件导出
@AutoLog(value = "测试--导出excel")
@PostMapping(value = "/test")
public ModelAndView exportXls(HttpServletRequest request, HttpServletResponse response) {
ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
try{
//获取当前登录信息-username
SysUser sysUser=(SysUser)SecurityUtils.getSubject().getPrincipal();
String username = sysUser.getUsername();
//查询当前登录用户下的数据
List<GDSEntity> list = gDSService.getExcelGDSList(username);
//excel文件名
mv.addObject(NormalExcelConstants.FILE_NAME, "测试明细");
//映射的实体
mv.addObject(NormalExcelConstants.CLASS, GDSEntity.class);
//导出的字段属性
mv.addObject(NormalExcelConstants.PARAMS, new ExportParams("测试明细", "导出人:"+username, "导出信息"));
//填充要导出的集合数据
mv.addObject(NormalExcelConstants.DATA_LIST, list);
}catch(Exception e){
e.printStackTrace();
log.error(e.getMessage());
}
return mv;
}
2 映射实体对应的属性,添加@Excel注解,设置对应的长度,以及名称
如:
public class GDSEntity {
/**
* id
*/
@TableId(type=IdType.UUID)
private String id;
/**
* 测试1
*/
@Excel(name = "测试", width = 15)
private String test1;
/**
* 测试2
*/
@Excel(name = "测试2", width = 15)
private String test2;
@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
@Excel(name = "日期", width = 25,format="yyyy-MM-dd HH:mm:ss")
private Date date;
1 模板下载
@AutoLog(value = "库存积压资源预警-下载模板")
@RequestMapping(value = "/exportXls_model", method = RequestMethod.GET)
public ModelAndView exportXls_model( HttpServletRequest request, HttpServletResponse response) {
ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
String username = JwtUtil.getUserNameByToken(request);
String[] fieldArray = null;
String export = "物料编码#zmatnr,物料描述#zemaktx,计量单位#zemsehl,技术规范ID#zjsgfsid,数量#zeknsl,金额#zemoney,仓库编号#zlgort,仓库名称#zlgobe,工厂编码#zwerks,单位编码#zbukrs,单位名称#zbutxt,入库批次#zcharg,入库日期#zersda,库龄(天)#stockAge,预警标识#warning";
String[] excelHeader = export.split(",");
//字段名数组 获取拼装导出fields
fieldArray = new String[excelHeader.length];
for (int i = 0; i < excelHeader.length; i++) {
String[] tempArray = excelHeader[i].split("#");// 临时数组 分割#
fieldArray[i] = tempArray[1];
}
//添加自定义到处fields
mv.addObject(NormalExcelConstants.EXPORT_FIELDS, org.apache.commons.lang.StringUtils.join(fieldArray, ","));
List<KCZYOverStockVO> list = new ArrayList<KCZYOverStockVO>();
try {
// 文件名
mv.addObject(NormalExcelConstants.FILE_NAME, "库存积压资源预警模板");
//映射的实体
mv.addObject(NormalExcelConstants.CLASS, KCZYOverStockVO.class);
//模板参数
mv.addObject(NormalExcelConstants.PARAMS, new ExportParams("库存积压资源预警模板", "导出人:"+username, "导出信息"));
//填充集合
mv.addObject(NormalExcelConstants.DATA_LIST, list);
} catch (Exception e) {
e.printStackTrace();
log.error(e.getMessage());
sysBaseAPI.addLog("库存积压资源预警-下载模板" + e.getMessage(), CommonConstant.LOG_TYPE_3,
CommonConstant.LOG_OPERATE_TYPE_7);
}
return mv;
}
2
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class KCZYOverSettingVO implements Serializable{
private static final long serialVersionUID = 1L;
@Excel(name = "物料编码", width = 15)
private String zmatnr;
@Excel(name = "物料描述", width = 15)
private String zemaktx;
@Excel(name = "计量单位", width = 15)
private String zemsehl;
@Excel(name = "技术规范ID", width = 15)
private String zjsgfsid;
@Excel(name = "数量", width = 15)
private Double zeknsl;
@Excel(name = "金额", width = 15)
private String zemoney;
@Excel(name = "预估金额", width = 15)
private String zckjg;
@Excel(name = "预估总金额", width = 15)
private String zckjgtotal;
3 导入
/** * 通过excel导入数据 */ @AutoLog(value = "运输信息查询-通过excel导入数据", logType = CommonConstant.LOG_TYPE_0, logOperateType = CommonConstant.LOG_OPERATE_TYPE_6) @RequestMapping(value = "/importExcel", method = RequestMethod.POST) public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; Map<String, MultipartFile> fileMap = multipartRequest.getFileMap(); for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) { // 获取上传文件对象 ImportParams params = new ImportParams(); params.setTitleRows(1); // FIXME 线上环境可能会找不到路径 // params.setNeedSave(true); try (InputStream file = entity.getValue().getInputStream() ) { ExcelImportResult<TransportInformation> result = ExcelImportUtil.importExcelVerify(file, TransportInformation.class, params); // TODO 并没有校验导入模板是否正确 if (result.isVerfiyFail()) { throw new JeecgBootException(result.getErrorInfo()); } List<TransportInformation> list = result.getList(); //intransitService.saveCustom(list); //transportInforService.check(ebeln, ebelp) transportInforService.saveData(list); return Result.ok("导入" + list.size() + "条数据成功"); }catch (JeecgBootException e) { log.error(e.getMessage(), e); sysBaseAPI.addLog("运输信息查询-通过excel导入数据" + e.getMessage(), CommonConstant.LOG_TYPE_3, CommonConstant.LOG_OPERATE_TYPE_6); return Result.error(e.getMessage()); } catch (Exception e) { log.error(e.getMessage(), e); sysBaseAPI.addLog("运输信息查询-通过excel导入数据" + e.getMessage(), CommonConstant.LOG_TYPE_3, CommonConstant.LOG_OPERATE_TYPE_6); return Result.error("操作失败,请联系管理员"); } } return Result.error("文件导入失败!"); }