Excel利用poi导入导出(上)

一，pom.xml文件

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
          <groupId>net.sf.jxls</groupId>
          <artifactId>jxls-core</artifactId>
          <version>1.0.3</version>
      </dependency>
      <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
        <version>3.1.0</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-web</artifactId>
        <version>4.1.6.RELEASE</version>
    </dependency>

二、Controller 层调用解析

    @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    public Result importExcel(@RequestParam(value = "testFile") MultipartFile testFile,
                              @RequestParam(value = "fileType") Integer fileType, @RequestParam(value = "tableName") String tableName) throws Exception {
        //表内外台账导入
        LOGGER.info("=========进入导入方法表标识==========" + fileType);
        if (testFile == null) {
            return Result.error("请传输文件");
        }
        
        String fileName = dpOriTableInfo.getFileName();
        String tName = tableName.substring(0, tableName.indexOf("."));if (!tName.equals(fileName) && !tName.equals(fileName)) {
            return new Result(-1, "导入文件失败，请选择对应的模板类型或上传正确文件", null);
        }
        InputStream inputStream = null;
        try {
            inputStream = testFile.getInputStream();
            //读取excel
            dataProcessService.uploadExcel(fileType, inputStream);
            return new Result(0, "上传成功", null);
        } catch (Exception e) {
            LOGGER.error("上传出现异常", e);
        }finally {
            if(inputStream != null){
                inputStream.close();
            }
        }
        return new Result(-1, "上传失败", null);
    }

三、service

void uploadExcel(Integer fileType, InputStream inputStream) throws Exception;

四、业务实现serviceImpl

  @Override
    public void uploadExcel(Integer flieType, InputStream inputStream) throws Exception {
        List<Map<String, Object>> dataList = readExcel(flieType, inputStream);
        if ((TableFalgEnum.TabFlag.ori_bad.getIndex().equals(flieType)) && dataList.size() > 0) {
           
            List<BadLoan> dpLoanList = new ArrayList<>();
            for (int j = 3; j < dataList.size(); j++) {
                int g = j + 2;
                Map<Object, Object> map = new HashMap();
                map = (HashMap) dataList.get(j);
                try {
                   BadLoan record = new BadLoan();
                    if (map.get("B" + g) != null && map.get("B" + g) != "") {
                        logger.info("=====解析机构代码：{},第几行：{} ", JSONObject.toJSONString(map.get("B" + g)), "B" + g);
                        record.setOrgCode(map.get("B" + g).toString());
                    } else {
                        logger.info("=====解析机构代码：{} ,第几行：{} ", JSONObject.toJSONString(map.get("B" + g)), "B" + g);
                        record.setOrgCode(null);
                    }
                       dpLoanList.add(record);
                    if (dpLoanList.size() >= appConfig.getDpBatchSize()) {
                        //当集合中满500个元素的时候，执行批量新增
                        dpLoanMapper.batchInsertBad(dpLoanList);
                        //新增完成将集合清空，用于下一次批量新增
                        dpLoanList.clear();
                    }
                } catch (Exception e) {
                    logger.error("解析出现异常", e);
                    updateStatus(flieType, TableFalgEnum.Status.FAIL_STATUS.getIndex());
                }
            }
            dpLoanMapper.batchInsertBad(dpLoanList);
            //更新原始表是状态与上传时间
            updateStatus(flieType, TableFalgEnum.Status.SUCCESSS_STATUS.getIndex());
}

/**
     * 读取excel文件
     *
     * @param type        文件类型
     * @param inputStream 字节liu
     */
    private List<Map<String, Object>> readExcel(Integer type, InputStream inputStream) throws Exception {
        List<Map<String, Object>> dataList = null;
        AssetUploadCheekVo vo = null;
        dataList = AssetExcelReadUtils.parseExcel(inputStream, false, type);
        return dataList;
    }

 public static List<Map<String, Object>> parseExcel(InputStream inputStream, boolean readHeadFlag,int type) throws IOException, InvalidFormatException {

        Workbook workbook = WorkbookFactory.create(inputStream);
        return readExcel(workbook,readHeadFlag,type);
    }

/**
     * 解析Excel数据
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private static  List<Map<String,Object>> readExcel(Workbook workbook, boolean readHeadFlag,int type) {
        List<Map<String,Object>> resultDataList = new ArrayList<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }

            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();

            Row firstRow = sheet.getRow(firstRowNum);
//            if (null == firstRow) {
//                throw new BusinessException(ResponseCode.PARAMS_ERROR.getCode(),"解析Excel失败，在第一行没有读取到任何数据！");
//            }

            // 解析每一行的数据，构造数据对象
            int rowStart = readHeadFlag ? firstRowNum : firstRowNum + 1 ;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            logger.info("第一行："+rowStart);
            logger.info("末行："+rowEnd);
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row) {
                    continue;
                }
                Map<String, Object> map = convertRowToData(row,rowNum,type);
                resultDataList.add(map);
            }
        }
        return resultDataList;
    }

private static Map<String,Object> convertRowToData(Row row,int rowNum,int type) {
        Map<String,Object> map=new HashMap<>();
        Iterator<Cell> iterator = row.iterator();
        Cell cell = null;
        while (iterator.hasNext()) {
            cell = iterator.next();
            String cellValue = getCellValue(cell).trim();
            cell.getSheet();
           String name=cell.getAddress().toString();
           // logger.info("excel读取列名：{}", name);
            //此处不会读取空值，故不在此进行数据类型校验
           /* switch (name) {
                case "B":
                    logger.info("excel读取B行：{}", JSONObject.toJSONString(cellValue));
                    if (null==cellValue || cellValue.equals("")){
                        throw new BusinessException(ResponseCode.PARAMS_ERROR.getCode(),""+rowNum+"行"+"B列，"+"姓名不能为空");
                    }
                    map.put(name,cellValue);
                    break;
                  **/
                map.put(name,cellValue);
            }
        }
        return map;
    }