Java导入与导出Excel的组件设计

背景

最近项目快上线遇到数据迁移的问题,需要将excel中的数据导入到DB中,并支持部分重要数据导出备份。

问题

  • 导入与导出的excel种类多
  • 基于poi实现的重复代码多

思路

  • 将excel的导入与导出逻辑基于poi进一步封装。封装点如下:
    1. 导出的数据直接来源SQL查询结果(无需单独填充单元格)
    2. 通过对象属性与列的绑定实现单元格数据的自动填充与反向解析
    3. 导入数据直接被解析为DB的实体Bean对象组集合(无需单独填充Bean属性)

设计

  • 难点
    1. excel一行的数据可来源多张表,且存在A表一条数据对应B表多条数据
    2. 对象属性与列的双向绑定,怎么获取值填充对应列
    3. excel展示值与DB存储值的双向转换
  • 方案
    • 难点一:在处理完A类表数据后,通过回调函数获取对应的B表多条记录。A类表数据合并单元格,B表每条记录占一行
    • 难点二:每个excel建立一个简单模型,使用ognl表达式标识对象属性,使用名称标识列。通过ognl表达式获取属性值
    • 难点三:在模型中建立转换函数

Java代码实现

  • 导入与导出控制层处理(ShopSellRentInfo为DB表对象组、Shop为DB表对象)

    @PostMapping("/export/shop")
     public void exportShop(HttpServletResponse response) {
         // 查询需要导出的数据
         Page<ShopComplex.ShopSellRentInfo> page = shopService.searchPage();
         List<ShopComplex.ShopSellRentInfo> infoList = page.getResults();
    
         // 渲染导出数据并导出
         ExcelUtil.export(TemplateEnum.SHOP, infoList, "档位信息", response);
     }
    
     @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = RuntimeException.class)
     @PostMapping("/import/shop")
     public void importShop(MultipartFile file) {
         List<ImportRow> rowList = ExcelUtil.importExcel(TemplateEnum.SHOP, file, excelDao);
    
         // 将Bean数据同步到DB中
         for (ImportRow importRow : rowList) {
             Shop shop = importRow.getBean(Shop.class);
             shopService.addOrUpdateExcel(shop);
         }
     }
    
  • 通过枚举定义简单模型

    1. 利用嵌套模型实现A类表对应多条B表的情况
    2. 利用模型属性实现通用逻辑的处理,如隐藏列、非空
    3. 利用BiMap实现简单的excel展示值与DB存储值的双向转换
    4. 利用ExcelDao实现复杂的excel展示值与DB存储值的单向转换(通过某个字段值获取ID)
     /**
     * 导入与导出的Excel模板枚举
     *
     * @author XiaoJia
     * @since 2020/3/11 21:49
     */
    public enum TemplateEnum {
    
      /**
       * 档位
       */
      SHOP(ShopEnum.class, Shop.class),
    
      /**
       * 档位租赁 的 子模板
       */
      SHOP_LEASE_CHARGE(ShopLeaseChargeEnum.class, ShopLeaseCharge.class),
      /**
       * 档位租赁
       */
      SHOP_LEASE_ORDER(TemplateEnum.SHOP_LEASE_CHARGE, ShopLeaseOrderEnum.class, ShopLeaseOrder.class);
    
    
      interface Detail {
          /**
           * 将数据库与excel展示的字符建立一一映射
           *
           * @return 选项值
           */
          default BiMap<Object, String> getConvertMap() {
              return null;
          }
    
          /**
           * 将OGNL解析的值进行再一步转换
           *
           * @param export   导出,true-导出、false-导入
           * @param origin   原始的值
           * @param excelDao 数据访问层对象
           * @return 转换后的值
           */
          default Object convert(boolean export, Object origin, ExcelDao excelDao) {
              if (getConvertMap() == null) {
                  return origin;
              }
    
              return (export ? getConvertMap() : getConvertMap().inverse()).get(origin);
          }
    
          /**
           * 获取sheet中的列名
           *
           * @return sheet中的列名
           */
          String getName();
    
          /**
           * 获取列值的OGNL表达式
           *
           * @return 列值的OGNL表达式
           */
          String getOgnl();
    
          /**
           * 获取是否隐藏列
           *
           * @return 是否隐藏列
           */
          boolean isHidden();
    
          /**
           * 获取是否导入列
           *
           * @return 是否导入列
           */
          boolean isImportable();
    
          /**
           * 获取是否可为空
           *
           * @return 是否可为空
           */
          boolean isNullable();
      }
    
      /**
       * 子模板
       */
      private TemplateEnum subTemplate;
      /**
       * 属于该模板的所有列信息
       */
      private Detail[] columns;
      /**
       * 保持模板导入时新增或更新的实体类
       */
      private Map<String, Class<? extends BaseModel>> modelMap = new HashMap<>();
    
      @SafeVarargs
      TemplateEnum(Class<? extends Detail> columnClass,
                   Class<? extends BaseModel>... modelClass) {
          this(null, columnClass, modelClass);
      }
    
      @SafeVarargs
      TemplateEnum(TemplateEnum subTemplate, Class<? extends Detail> columnClass,
                   Class<? extends BaseModel>... modelClass) {
          this.subTemplate = subTemplate;
          this.columns = columnClass.getEnumConstants();
    
          for (Class<? extends BaseModel> aClass : modelClass) {
              String key = StringUtil.firstCharToLowerCase(aClass.getSimpleName());
              modelMap.put(key, aClass);
          }
      }
    
      public TemplateEnum getSubTemplate() {
          return subTemplate;
      }
    
      public Detail[] getColumns() {
          return columns;
      }
    
      public Map<String, Class<? extends BaseModel>> getModelMap() {
          return modelMap;
      }
    }
    

    非A类表对应B表的案例

    /**
     * @author XiaoJia
     * @since 2020/3/11 23:34
     */
    public enum ShopEnum implements TemplateEnum.Detail {
    
      /**
       *
       */
      AREA_NAME("区域", "area.name", false, false, true),
      HOUSE_NAME("栋号", "house.name", false, true, false),
      ID("档位ID", "shop.id", true, true, true),
      USE_STATUS("使用情况", "shop.useStatus", false, true, false) {
          @Override
          public BiMap<Object, String> getConvertMap() {
              BiMap<Object, String> biMap = HashBiMap.create();
              biMap.put(0, "空闲");
              biMap.put(1, "正常经营");
              return biMap;
          }
      },
    
      ;
    
      private String name;
      private String ognl;
      private boolean hidden;
      private boolean importable;
      private boolean nullable;
    
      ShopEnum(String name, String ognl, boolean hidden, boolean importable, boolean nullable) {
          this.name = name;
          this.ognl = ognl;
          this.hidden = hidden;
          this.importable = importable;
          this.nullable = nullable;
      }
    
      @Override
      public String getName() {
          return name;
      }
    
      @Override
      public String getOgnl() {
          return ognl;
      }
    
      @Override
      public boolean isHidden() {
          return hidden;
      }
    
      @Override
      public boolean isImportable() {
          return importable;
      }
    
      @Override
      public boolean isNullable() {
          return nullable;
      }
    }
    

    A类表对应B表的案例

    public enum ShopLeaseChargeEnum implements TemplateEnum.Detail {
      /**
       *
       */
      ID("档位租赁费用ID", "shopLeaseCharge.id", true, true, true),
      PRICE("价格", "shopLeaseCharge.price", false, true, false),
      ;
    
      private String name;
      private String ognl;
      private boolean hidden;
      private boolean importable;
      private boolean nullable;
    
      ShopLeaseChargeEnum(String name, String ognl, boolean hidden, boolean importable, boolean nullable) {
          this.name = name;
          this.ognl = ognl;
          this.hidden = hidden;
          this.importable = importable;
          this.nullable = nullable;
      }
    
      @Override
      public String getName() {
          return name;
      }
    
      @Override
      public String getOgnl() {
          return ognl;
      }
    
      @Override
      public boolean isHidden() {
          return hidden;
      }
    
      @Override
      public boolean isImportable() {
          return importable;
      }
    
      @Override
      public boolean isNullable() {
          return nullable;
      }
    
    }
    
    public enum ShopLeaseOrderEnum implements TemplateEnum.Detail {
    
      /**
       *
       */
      ID("档位租赁ID", "shopLeaseOrder.id", true, true, true),
      CUSTOMER_NAME("商户", "customer.name", false, true, false),
      AREA_NAME("区域", "area.name", false, false, true),
      HOUSE_NAME("栋号", "house.name", false, false, true),
      SHOP_NUM("档位号", "shop.number", false, true, false),
      GOOD_TYPE_NAME("主营品种", "goodType.name", false, true, false),
      WATER_BEGIN("水表基数", "shopLeaseOrder.waterBegin", false, true, false),
      WATER_RELATED_SHOP_ID("关联水表档位", "shopLeaseOrder.waterRelatedShopId", false, true, true) {
          @Override
          public Object convert(boolean export, Object origin, ExcelDao excelDao) {
              return convertShop(export, origin, excelDao);
          }
      },
      WATER_RELATED_BEGIN("关联水表度数", "shopLeaseOrder.waterRelatedBegin", false, true, true),
      ;
    
      private String name;
      private String ognl;
      private boolean hidden;
      private boolean importable;
      private boolean nullable;
    
      ShopLeaseOrderEnum(String name, String ognl, boolean hidden, boolean importable, boolean nullable) {
          this.name = name;
          this.ognl = ognl;
          this.hidden = hidden;
          this.importable = importable;
          this.nullable = nullable;
      }
    
      @Override
      public String getName() {
          return name;
      }
    
      @Override
      public String getOgnl() {
          return ognl;
      }
    
      @Override
      public boolean isHidden() {
          return hidden;
      }
    
      @Override
      public boolean isImportable() {
          return importable;
      }
    
      @Override
      public boolean isNullable() {
          return nullable;
      }
    
      private static Object convertShop(boolean export, Object origin, ExcelDao excelDao) {
          if (ObjectUtil.isEmpty(origin)) {
              return origin;
          }
    
          if (export) {
              return excelDao.searchById(Shop.class, (String) origin).getNumber();
          } else {
              List<String> idList = excelDao.searchId("shop", "number", (String) origin);
              if (ObjectUtil.isEmpty(idList) || idList.size() >= 2) {
                  throw new BusinessException("%s 不唯一或不存在!", origin);
              }
              return idList.get(0);
          }
      }
    
    }
    
  • 由于具体实现与现有公司平台有一定耦合,所以不贴具体实现代码。以下是直接访问DB的案例

    /**
    * @author XiaoJia
    * @since 2020/3/12 21:12
    */
    @Component
    public class ExcelDao {
    
     @Autowired
     private SqlSessionFactory sqlSessionFactory;
    
     @Autowired
     @Qualifier("sqlSessionTemplate")
     private SqlSession sqlSession;
    
     @PostConstruct
     public void init() {
         sqlSessionFactory.getConfiguration().addMapper(ExcelMapper.class);
     }
    
     /**
      * 通过某个字段值查询记录ID
      * <p>
      * 注意:此方法有SQL注入的可能,传入的参数不能直接来源用户请求
      *
      * @param table 查询的表
      * @param field 查询的字段名称
      * @param value 查询的条件值
      * @return 记录ID
      */
     public List<String> searchId(String table, String field, String value) {
         ExcelMapper mapper = sqlSession.getMapper(ExcelMapper.class);
         return mapper.searchId(table, field, value);
     }
    
     public interface ExcelMapper {
         /**
          * 通过某个字段值查询记录ID
          *
          * @param table 查询的表
          * @param field 查询的字段名称
          * @param value 查询的条件值
          * @return 记录ID集合
          */
         @SelectProvider(type = ExcelProvider.class, method = "searchId")
         List<String> searchId(@Param("table") String table, @Param("field") String field, @Param("value") String value);
    
     }
    
     public static class ExcelProvider {
         /**
          * 通过某个字段值查询记录ID
          *
          * @param table 查询的表
          * @param field 查询的字段名称
          * @param value 查询的条件值
          * @return 查询SQL
          */
         public String searchId(@Param("table") String table, @Param("field") String field,
                                @Param("value") String value) {
             return new SQL() {
                 {
                     SELECT("id");
                     FROM(table);
                     WHERE(field + " = '" + value + "'");
                 }
             }.toString();
         }
    
     }
    
    }
    
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,511评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,495评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,595评论 0 225
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,558评论 0 190
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,715评论 3 270
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,672评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,112评论 2 291
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,837评论 0 181
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,417评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,928评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,316评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,773评论 2 234
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,253评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,827评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,440评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,523评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,583评论 2 249