关于导入导出

  ↵首次接触导入导出使用的的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("文件导入失败！");
    }