Java实现解析excel文件

解析excel主要是根据excel的某些文字获取特定行的数据，以此来解析整个文件，excel文件的行和列都是从0开始的，解析列时要注意单元格的问题，合并单元格时要计算该列的位置
实现代码如下：

@ApiOperation(value = "解析excel", notes = "解析excel")
    @PostMapping(value = "/readExcel", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public BaseInfoDTO readExcel(@RequestPart MultipartFile file) {
        BaseInfoDTO dto = new BaseInfoDTO();
        String originalFilename = file.getOriginalFilename();
        String fileType = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);

        //读取excel文件
        InputStream is = null;
        try {
            is = file.getInputStream();
            //获取工作薄
            Workbook wb;
            if ("xls".equals(fileType)) {
                wb = new HSSFWorkbook(is);
            } else if ("xlsx".equals(fileType)) {
                wb = new XSSFWorkbook(is);
            } else {
                return null;
            }

            //读取第一个工作页sheet
            Sheet sheet = wb.getSheetAt(0);
            int baseInfo = 0;
            int zycpfw = 0;
            int hytz = 0;
            int zyjzz = 0;
            int gqjgtzf = 0;
            int gqjgbtzf = 0;
            int zzkgqy = 0;
            int jtzyyw = 0;

            //0开始
            Row row;
            //0开始
            Cell cell;
            //日期格式
            SimpleDateFormat sdf;
            //先查询出数据位置
            for (int i = 0; i < sheet.getLastRowNum(); i++) {
                row = sheet.getRow(i);
                cell = row.getCell(0);
                cell.setCellType(1);
                String cellValue = cell.getStringCellValue();
                if ("基础信息1".equals(cellValue)) {
                    baseInfo = i + 2;
                } else if ("基础信息2".equals(cellValue)) {
                    zycpfw = i + 2;
                } else if ("基础信息3".equals(cellValue)) {
                    hytz = i + 2;
                } else if ("基础信息4".equals(cellValue)) {
                    zyjzz = i + 2;
                } else if ("基础信息5".equals(cellValue)) {
                    gqjgtzf = i + 2;
                } else if ("基础信息5".equals(cellValue)) {
                    gqjgbtzf = i + 2;
                } else if ("基础信息6".equals(cellValue)) {
                    zzkgqy = i + 2;
                } else if ("基础信息7".equals(cellValue)) {
                    jtzyyw = i + 2;
                } else {
                    continue;
                }
            }

            //这是注释
            //这是注释
            row = sheet.getRow(baseInfo);
            cell = row.getCell(3);
            cell.setCellType(1);
            String nsrmc = cell.getStringCellValue();
            dto.setNsrmc(nsrmc);
            //这是注释
            row = sheet.getRow(baseInfo + 1);
            cell = row.getCell(3);
            cell.setCellType(1);
            String nsrsbh = cell.getStringCellValue();
            dto.setNsrsbh(nsrsbh);
            //这是注释
            row = sheet.getRow(baseInfo + 2);
            cell = row.getCell(3);
            cell.setCellType(1);
            String shxydm = cell.getStringCellValue();
            dto.setShxydm(shxydm);
            //这是注释
            row = sheet.getRow(baseInfo + 3);
            cell = row.getCell(3);
            cell.setCellType(1);
            String qygn = cell.getStringCellValue();
            dto.setQygn(qygn);

            //这是注释
            List<ZycpDTO> zycpDTOS = new ArrayList<>();
            for (int i = zycpfw; i < hytz - 2; i++) {

                ZycpDTO zycpDTO = new ZycpDTO();
                row = sheet.getRow(i);
                //这是注释
                cell = row.getCell(1);
                cell.setCellType(1);
                zycpDTO.setNd(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(2);
                cell.setCellType(1);
                zycpDTO.setMc(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(3);
                cell.setCellType(1);
                zycpDTO.setCphfwms(cell.getStringCellValue());
                //这是注释 注意这儿单元格合并问题
                cell = row.getCell(5);
                cell.setCellType(1);
                zycpDTO.setGljybl(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(6);
                cell.setCellType(1);
                zycpDTO.setKjjy(cell.getStringCellValue());
                zycpDTOS.add(zycpDTO);
            }
            dto.setZycp(zycpDTOS);

            //这是注释
            List<HytzDTO> hytzDTOS = new ArrayList<>();
            for (int i = hytz; i < zyjzz - 2; i++) {
                HytzDTO hytzDTO = new HytzDTO();
                row = sheet.getRow(i);
                //这是注释 注意合并单元格问题
                cell = row.getCell(1);
                cell.setCellType(1);
                hytzDTO.setHytz(cell.getStringCellValue());
                hytzDTOS.add(hytzDTO);
            }
            dto.setHytz(hytzDTOS);

            //这是注释
            List<HyzyjzzDTO> hyzyjzzDTOS = new ArrayList<>();
            for (int i = zyjzz; i < gqjgtzf - 2; i++) {
                HyzyjzzDTO hyzyjzzDTO = new HyzyjzzDTO();
                row = sheet.getRow(i);
                //这是注释 注意合并单元格问题
                cell = row.getCell(1);
                cell.setCellType(1);
                hyzyjzzDTO.setQymc(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(5);
                cell.setCellType(1);
                hyzyjzzDTO.setSsgj(cell.getStringCellValue());
                hyzyjzzDTOS.add(hyzyjzzDTO);
            }
            dto.setHyzyjzz(hyzyjzzDTOS);

            //这是注释
            List<GqjgDTO> gqjgTzfDTOS = new ArrayList<>();
            for (int i = gqjgtzf; i < gqjgbtzf - 2; i++) {
                GqjgDTO tzfDTO = new GqjgDTO();
                row = sheet.getRow(i);
                //这是注释 注意合并单元格问题
                cell = row.getCell(1);
                cell.setCellType(1);
                tzfDTO.setNsrmc(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(2);
                cell.setCellType(1);
                tzfDTO.setGjdq(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(3);
                cell.setCellType(1);
                try {
                    tzfDTO.setTzbl(Float.parseFloat(cell.getStringCellValue()));
                }catch (Exception e){
                    tzfDTO.setTzbl(0f);
                }
                //这是注释
                cell = row.getCell(4);
                cell.setCellType(1);
                try {
                    tzfDTO.setTzje(new BigDecimal(cell.getStringCellValue()));
                }catch (Exception e){
                    tzfDTO.setTzje(new BigDecimal(0));
                }
                //这是注释
                cell = row.getCell(5);
                cell.setCellType(1);
                tzfDTO.setBzlx(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(5);
                cell.setCellType(1);
                tzfDTO.setBzlx(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(6);
                cell.setCellType(1);
                tzfDTO.setSjId(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(7);
                cell.setCellType(1);
                tzfDTO.setBcId(cell.getStringCellValue());
                gqjgTzfDTOS.add(tzfDTO);
            }
            dto.setGqjgTzf(gqjgTzfDTOS);

            //这是注释
            List<GqjgDTO> gqjgBtzfDTOS = new ArrayList<>();
            for (int i = gqjgbtzf; i < zzkgqy - 3; i++) {
                GqjgDTO btzfDTO = new GqjgDTO();
                row = sheet.getRow(i);

                //这是注释
                cell = row.getCell(2);
                cell.setCellType(1);
                btzfDTO.setGjdq(cell.getStringCellValue());
                //这是注释 注意合并单元格问题
                cell = row.getCell(1);
                cell.setCellType(1);
                btzfDTO.setNsrmc(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(3);
                cell.setCellType(1);
                try {
                    btzfDTO.setTzbl(Float.parseFloat(cell.getStringCellValue()));
                }catch (Exception e){
                    btzfDTO.setTzbl(0f);
                }
                //这是注释
                cell = row.getCell(5);
                cell.setCellType(1);
                btzfDTO.setBzlx(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(4);
                cell.setCellType(1);
                try {
                    btzfDTO.setTzje(new BigDecimal(cell.getStringCellValue()));
                }catch (Exception e){
                    btzfDTO.setTzje(new BigDecimal(0));
                }
                //这是注释
                cell = row.getCell(6);
                cell.setCellType(1);
                btzfDTO.setSjId(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(5);
                cell.setCellType(1);
                btzfDTO.setBzlx(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(7);
                cell.setCellType(1);
                btzfDTO.setBcId(cell.getStringCellValue());
                gqjgBtzfDTOS.add(btzfDTO);

            }
            dto.setGqjgBtzf(gqjgBtzfDTOS);

            //这是注释
            List<ZzkgqyDTO> zzkgqyDTOS = new ArrayList<>();
            for (int i = zzkgqy; i < jtzyyw - 2; i++) {
                ZzkgqyDTO zzkgqyDTO = new ZzkgqyDTO();
                row = sheet.getRow(i);
                //这是注释
                cell = row.getCell(1);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                zzkgqyDTO.setNd(cell.getStringCellValue());
                //这是注释
                cell = row.getCell(2);
                cell.setCellType(1);
                zzkgqyDTO.setQymc(cell.getStringCellValue());
                //这是注释 注意合并单元格问题
                cell = row.getCell(5);
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String format = sdf.format(cell.getDateCellValue());
                zzkgqyDTO.setCreateTime(sdf.parse(format));
                zzkgqyDTOS.add(zzkgqyDTO);
            }
            dto.setZzkgqy(zzkgqyDTOS);

            //这是注释
            List<JtzyywDTO> jtzyywDTOS = new ArrayList<>();
            for (int i = jtzyyw; i < sheet.getLastRowNum() + 1; i++) {
                JtzyywDTO jtzyywDTO = new JtzyywDTO();
                row = sheet.getRow(i);
                //这是注释
                cell = row.getCell(1);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                jtzyywDTO.setYwly(cell.getStringCellValue());
                //这是注释 注意合并单元格问题
                cell = row.getCell(5);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                jtzyywDTO.setJzz(cell.getStringCellValue());
                //这是注释 注意合并单元格问题
                cell = row.getCell(6);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                jtzyywDTO.setDq(cell.getStringCellValue());
                jtzyywDTOS.add(jtzyywDTO);
            }
            dto.setJtzyyw(jtzyywDTOS);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (ParseException p) {
            p.printStackTrace();
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return dto;
    }