POI导入功能代码记录

Controller层方法

 

@PostMapping("uploadIntentionList")
@ResponseBody
@ApiOperation("导入文档")
public String uploadIntentionFile(@RequestParam("file") MultipartFile file, @RequestParam String type) {
    String tableName = "intention".equals(type) ? "CRD_DATA_INTEND_CUSTOMER" : "";
    if (StringUtils.isBlank(tableName)) {
        return WebUtil.returnError("清单类型不正确");
    }
    String sql = "INSERT INTO " + tableName + "(ID,ID_CARD,NAME,CELL,CAR_LINES,STATUS,DESCRI) VALUES(?,?,?,?,?,?,?)";
    boolean result = false;
    try {
        //读取excel
        List<List<String>> dataList = WebUtil.readExcel(file.getInputStream(), FileValidateUtil.isExcel2003(file.getOriginalFilename()), 0);     //读取Excel文件，读取指定sheet
        //去除表头
        if (CollectionUtils.isNotEmpty(dataList) && dataList.get(0).get(0).contains("身份证号码")) {
            dataList.remove(0);
        }
        result = bathSaveIntentionListData(sql, dataList); //批量保存方法
    } catch (SQLException e) {
        return WebUtil.returnError("未完成导入,请检查文件");
    } catch (IOException e) {
        log.error("[清单管理]", e);
    }
    if (!result) {
        return WebUtil.returnError("未完成导入,请检查文件");
    }
    return WebUtil.returnOk("success");
}

 

WebUtil工具类主要使用方法：

/**
 * @return java.util.List
 * @throws
 * @description 读取excel文件，读取指定sheet
 * @Param [inputStream, isExcel2003, sheetIndex]
 * @create 2018/5/18 11:25
 */
public static List<List<String>> readExcel(InputStream inputStream, boolean isExcel2003, int sheetIndex) throws IOException {
    /** 根据版本选择创建Workbook的方式 */
    @Cleanup Workbook wb = null;
    if (isExcel2003) {
        wb = new HSSFWorkbook(inputStream);
    } else {
        wb = new XSSFWorkbook(inputStream);
    }
    if (sheetIndex < wb.getNumberOfSheets()) {
        return read(wb, sheetIndex);
    } else {
        return null;
    }
}

批量保存导入的文档方法：

@Transactional(rollbackFor = Exception.class)
private Boolean bathSaveIntentionListData(String sql, List<List<String>> datas) throws SQLException {
    List<Object[]> list = new ArrayList<>();
    if (CollectionUtils.isEmpty(datas)) {
        return false;
    }
    for (List<String> data : datas) {
        if (data.size() < 2) {
            continue;
        }
        //ID,CITYNAME,CITYPRICE,COUNTYPRICE,INPUT_DATE,STATUS,DESCRI
        String status = "";
        if(StringUtils.equals(data.get(4).trim().toString(),"启用")){
            status = "1";
        }else if(StringUtils.equals(data.get(4).trim().toString(),"停用")){
            status = "0";
        }
        Object[] obj = new Object[]{WebUtil.getUUID(), data.get(0).trim(), data.get(1).trim(),data.get(2).trim(),data.get(3).trim(),status,data.get(5).trim()};
        list.add(obj);
    }
    if (org.apache.commons.collections.CollectionUtils.isEmpty(list)) {
        return false;
    }
    int result = 0;
    //设置1000次一提交
    int flag = 1000;
    int size = datas.size();
    int tempSize = size / flag + 1;
    boolean special = size % flag == 0;
    List<Object[]> currentList = null;
    for (int i = 0; i < tempSize; i++) {
        if (i == tempSize - 1) {
            if (special) {
                break;
            }
            currentList = list.subList(flag * i, size);
        } else {
            currentList = list.subList(flag * i, flag * (i + 1));
        }
        result += jdbcTemplate.batchUpdate(sql, currentList).length;
    }
    return result == datas.size();
}