sqlserver中excel 入库的思考

 

 

1.纯使用sqlserver 的存储过程

   在sqlserver中有 关于打开excel 的函数 可以写一个 存储过程搞定 例如下面:

create procedure ExcelRead
as

delete from test

insert into test
select *
 from OPENROWSET('Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0;Database=C:\Users\xin.wang3\Desktop\MyTest.xlsx;',
 'SELECT * FROM [Sheet1$]')

 exec ExcelRead

但是 这里数据库中必须含有Microsoft.ACE.OLEDB.12.0 的引擎插件

个人感觉这种方法:让数据导入下沉到 db层 不用使用 外部的代码 干扰

2.使用SSMS用可视化窗口进行导入

使用方法参考 https://www.cnblogs.com/zhangliangzlee/p/3585862.html?utm_source=tuicool&utm_medium=referral

3.使用外部的代码实现

由于种种原因 dba 不愿意加装插件 ssms 也不太自动化 所以我就使用java 代码完成

本项目为 springboot 项目 就启动 定时器 扫描共享目录中的 excel 文件

关注过 阿里的 easyexecl 所以 就 使用这个 execl 框架

@Component
@Slf4j
public class ScheduleJob {

  @Autowired
  private ExcelCacheMapper customerCacheMapper;

//  @Scheduled(cron = "0 22 14 * * ? ")
//  public void twoSecondExport(){
//    log.info("==============ssssssss================");
//    inportExcelToDatabase(ParseExcelType.E1CustomerType);
//    log.info("==============ssssssss================");
//  }
//
//  @Scheduled(cron = "0 22 14 * * ? ")
//  public void fourSecondExport(){
//    inportExcelToDatabase(ParseExcelType.ITSCARCacheType);
//  }

  public void inportExcelToDatabase(ParseExcelType excelType){
    InputStream inputStream = null;
    ExcelListener listener = null;
    try {
      inputStream = new FileInputStream(new File(excelType.getSourcePath()));
      // 解析每行结果在listener中处理
      listener = new ExcelListener(excelType.getType());
      ExcelReader excelReader = new ExcelReader(inputStream,  null, listener);

      if(excelType.getType() == SkuPrice.class){
        //use map insert
        excelReader.read(new Sheet(excelType.getSheetNo(), excelType.getHeadLine(), null));
      }else{
        //use javaBean insert
        excelReader.read(new Sheet(excelType.getSheetNo(), excelType.getHeadLine(), excelType.getType()));
      }
      List datas = listener.getDatas();
      switch (excelType){
        case E1CustomerType:
          this.insertList(datas,s->customerCacheMapper.insertEoneCustomerCacheList(s));
          //TODO refresh cache to main table

          break;
        case SaleCustomerType:
          this.insertList(datas,s->customerCacheMapper.insertSaleCustomerCacheList(s));
          //TODO refresh cache to main table

          break;
        case ITSCARCacheType:
          this.insertList(datas,s->customerCacheMapper.insertITSCARCacheList(s));
          //TODO refresh cache to main table

          break;
        case InventoryExcelType:
          this.insertList(datas,s->customerCacheMapper.insertIventoryCacheList(s));
          //TODO refresh cache to main table

          break;
        case SkuPriceType:
          this.insertList(datas,s->customerCacheMapper.insertSkuPriceCacheList(s));
          //TODO refresh cache to main table
          break;
        default:
          break;
      }
    } catch (Exception e) {
      log.error(e.toString());
    } finally {
      listener.clear();
      try {
        inputStream.close();
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
  }

  private void insertList(List datas,Consumer<List> con){
    for (int i= 0;i<=datas.size()/100;i++){
      if (i==datas.size()/100){
        con.accept(datas.subList(i*100,datas.size()));
      }else {
        con.accept(datas.subList(i*100,(i+1)*100));
      }
    }
  }

}

@Slf4j
public class ExcelListener<T> extends AnalysisEventListener {


  public ExcelListener(Class listenerClazz) {
    TITLE.put("SKU_NBR","skuNbr");
    TITLE.put("SKU Desc","skuDesc");
    TITLE.put("2019LP CNY","lpCny");
    TITLE.put("AM Disc%","amDisc");
    TITLE.put("RM Disc%","rmDisc");
    TITLE.put("SD Disc%","sdDisc");
    TITLE.put("SKU Size","skuSize");
    this.listenerClazz = listenerClazz;
  }

  private List datas = new ArrayList<>();

  private Class listenerClazz;

  public static final LinkedHashMap<String,String> TITLE_FILED = new LinkedHashMap<>();
  public static final HashMap<String,String> TITLE = new HashMap<>();

  @Override
  public void invoke(Object object, AnalysisContext context) {
    if (listenerClazz.getName().indexOf("SkuPrice") != -1){
      Integer rowNum = context.getCurrentRowNum();
      List<String> list = (List<String>)object;
      if (TITLE_FILED.isEmpty()){
        list.stream().forEach(s->{
            TITLE_FILED.put(TITLE.get(s),null);
        });
      }
      if (rowNum>0){
        LinkedHashMap<String, String> linkedHashMap = this.copyMap();
        int i = 0;
        for (String key:linkedHashMap.keySet()){
          String vaule = list.get(i);
          if (isIntTitle(key)){
            try {
              vaule = Integer.valueOf(vaule).toString();
            }catch (NumberFormatException e){
              vaule = null;
            }
          }
          linkedHashMap.put(key,vaule);
          i++;
        }
        linkedHashMap.remove(null);
        datas.add(linkedHashMap);
      }
    }else{
      datas.add(object);
      //log.info(object.toString());
    }
  }

  private boolean isIntTitle(String key){
    if ("amDisc".equals(key) || "rmDisc".equals(key)|| "sdDisc".equals(key)|| "lpCny".equals(key)){
      return true;
    }
    return false;
  }

  private LinkedHashMap<String,String> copyMap(){
    LinkedHashMap<String,String> linkedHashMap = new LinkedHashMap<>();
    TITLE_FILED.forEach((k,v)->linkedHashMap.put(k,null));
    return linkedHashMap;
  }


  @Override
  public void doAfterAllAnalysed(AnalysisContext context) {
    // datas.clear();//解析结束销毁不用的资源
  }

  private Class getThisClass(){
     return  Util.getActualArgumentType(this.getClass(), 0);
  }

  public void clear() {
    datas.clear();
  }
  public List<T> getDatas() {
    return datas;
  }
  public void setDatas(List<T> datas) {
    this.datas = datas;
  }
}

@Getter
@Setter
@Table(name = "itsc_ar_cache")
public class ITSCARCache  extends BaseRowModel {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @ExcelProperty(index = 3)
  @Column(name = "bt_no")
  private String bill2NO;

  @ExcelProperty(index = 27)
  @Column(name = "bill_date")
  private Date billDate;

  @ExcelProperty(index = 28)
  @Column(name = "expire_date")
  private Date expireDate;

  @ExcelProperty(index = 35)
  @Column(name = "indent_no")
  private String indentNO;

  @ExcelProperty(index = 33)
  @Column(name = "bill_no")
  private String billNO;

  @ExcelProperty(index = 40)
  @Column(name = "overdue_amount")
  private BigDecimal overdueAmount;

}

主要思路就是 excel 的列的index 映射到实体类中 @ExcelProperty(index = 35) 方法

在excelReader.read(new Sheet(excelType.getSheetNo(), excelType.getHeadLine(), excelType.getType()));中封装到其中

但是 续期中有一个 execl 中的index 不明确可能会改变 但是 列明不变 在easyexcel 中ExcelProperty中value 是存在bug

https://github.com/alibaba/easyexcel/issues/213

所以就手动的使用map实现 详情见代码