Java Import complex excel Table data to database

Take a look at the... To be imported into the database excel How complex the table data is ？ Directly above ！

If you want to import it into the database, you need to parse it into what I need , As shown in the figure below .

After comparison , If we don't find it, we need to make a comparison of Figure 1 excel To analyze , Go straight to the code ！

controller layer

    @PostMapping("/xfkylImport")
    @ApiOperation(value = " Import complex excel Table data ")
    public R<Boolean> xfkylImport(@ApiParam(value = " File object ") @RequestBody MultipartFile file) {
    
        Boolean aBoolean = importTableService.xfkylImport(file);
        return R.OK(aBoolean);
    }

service layer

   /** *  Import complex excel Table data  * * @param file  file  * @return  result  */
    public Boolean xfkylImport(MultipartFile file) {
    

        // Look up the code table ：excel No unit number  , So we can only check the code table first , Then insert the unit number of the code table into the corresponding table data to be imported 
        List<IcDwbmk> icDwbmks = icDwbmkService.listAll();

        Workbook workbook;
        try {
    
            // according to file establish excel
            workbook = WorkbookFactory.create(file.getInputStream());
        } catch (Exception e) {
    
            throw new CustomException(" Import failed , Please check whether the import template is correct ");
        }

        // Get the first one sheet page 
        Sheet xssSheet0 = workbook.getSheetAt(0);

        // Get the first and last lines 
        int rows1 = xssSheet0.getFirstRowNum();
        int rows2 = xssSheet0.getLastRowNum();

        // Create a temporary object to receive parameters 
        List<IcDreportXfkylVo> entities = new ArrayList<>();
        List<String> str = new ArrayList<>();

        //  Let's start ：j representative excel The line of ,k representative excel The column of 
        // Loop traversal ： Discard the first row header data 
        for (int j = rows1 + 1; j <= rows2; j++) {
    

            // Create temporary objects 
            IcDreportXfkylVo excelVo = new IcDreportXfkylVo();

            // Get current row 
            Row row3 = xssSheet0.getRow(j);
            // If there is no data in the current line, directly end the cycle 
            if (null == row3) continue;

            // Traverse all the columns in the row 
            int firstCellNum = row3.getFirstCellNum();
            int lastCellNum = row3.getLastCellNum();

            // Number of circular columns ： Discard the first column , Here, according to the business needs  lastCellNum - 2  operation 
            for (int k = firstCellNum + 1; k < lastCellNum - 2; k++) {
    
                // Get cell value , Whether the cells are merged or not , You can get 
                String value = getSheelValue(xssSheet0, j, k);

                // Column data is empty , End the cycle 
                if (StrUtil.isBlank(value)) continue;

                // analysis excel The date value in , And stored in the collection 
                if (j == 1 && k == 1) {
    
                    // Date formatting 
                    Date date = Convert.toDate(value);
                    String datee = DateUtil.format(date, "yyyy-MM-dd");
                    str.add(datee);
                }

                // Abandon these two columns 
                if (k == 10 || k > 17) continue;

                // ===========  If the conditions are met, the cycle will be ended directly  start（ According to the actual needs ）===========
                if (" Data date ".equals(value)) continue;
                if (" Test Category 1 ".equals(value)) continue;
                if (" Test Category II ".equals(value)) continue;
                if (j < 4) continue;
                if (j == 5) continue;
                if (j > 12) continue;
                // ===========  If the conditions are met, the cycle will be ended directly  end（ According to the actual needs ）===========

                // Obtain that the company name in the first column matches the name in the code table , If it is the same, take out the unit number in the coding table 
                if (k == 1) {
    

                    // There is a name that does not correspond to , Manually rename .
                    if (" So and so company 1".equals(value)) {
    
                        value = " So and so company 2";
                    }

                    // Take out the corresponding number of each unit 
                    String finalValue = value;
                    String s = icDwbmks.stream().filter(a -> finalValue.contains(a.getDwmc())).map(IcDwbmk::getDwbh).collect(Collectors.joining());
                    excelVo.setDwbh(s);
                    excelVo.setDwmc(value.replace(" So and so company 2", " So and so company 1"));
                }

                try {
    
                    if (StringUtils.isNumeric(value)) {
    
                        // Assign the value of the current column to the corresponding attribute field , The corresponding value of each column   And list Form storage of 
                        if (k == 2) excelVo.setSjlx1(Integer.valueOf(value));
                        if (k == 3) excelVo.setSjlx2(Integer.valueOf(value));
                        if (k == 4) excelVo.setSjlx3(Integer.valueOf(value));
                        if (k == 5) excelVo.setSjlx4(Integer.valueOf(value));
                        if (k == 6) excelVo.setSjlx5(Integer.valueOf(value));
                        if (k == 7) excelVo.setSjlx6(Integer.valueOf(value));
                        if (k == 8) excelVo.setSjlx7(Integer.valueOf(value));
                        if (k == 9) excelVo.setSjlx8(Integer.valueOf(value));
                        if (k == 11) excelVo.setSjlx9(Integer.valueOf(value));
                        if (k == 12) excelVo.setSjlx10(Integer.valueOf(value));
                        if (k == 13) excelVo.setSjlx11(Integer.valueOf(value));
                        if (k == 14) excelVo.setSjlx12(Integer.valueOf(value));
                        if (k == 15) excelVo.setSjlx13(Integer.valueOf(value));
                        if (k == 16) excelVo.setSjlx14(Integer.valueOf(value));
                    }
                } catch (Exception e) {
    
                    throw new CustomException(" Import failed , Please check whether the import template is correct ");
                }
            }

            //j  Each cycle will store a set of data , There will be a lot of empty objects , Empty objects will be emptied later 
            entities.add(excelVo);
        }

        if (entities.size() > 0) {
    
            // excel  Parsing complete ： Empty the empty objects in the collection 
            entities.removeAll(Collections.singleton(new IcDreportXfkylVo()));

            // Traverse ： Assign the date value to the data 
            for (IcDreportXfkylVo item :
                    entities) {
    
                item.setDatee(str.get(0));
            }

            // Manually catching exceptions 
            try {
    
                // Bulk insert data 
                Integer integer = icDreportXfkylMapper.insertBatch(entities);

                if (integer > 0) {
    
                    return true;
                }
            } catch (Exception e) {
    
                throw new CustomException(" Import failed , Please check whether the import template is correct ");
            }
        }
        throw new CustomException(" Import failed , Please check the imported excel Whether the table data is correct ");
    }



   /** *  Get cell contents , Includes the of merged cells  * * @param sheet sheet Forms  * @param row  Current line subscript  * @param col  Current column subscript  * @return  result  */
    public String getSheelValue(Sheet sheet, int row, int col) {
    
        // Get the number of merged areas 
        int mergedRegions = sheet.getNumMergedRegions();

        // Number of traversing merge regions 
        for (int i = 0; i < mergedRegions; i++) {
    
            // Specifies the merge area at the index 
            CellRangeAddress region = sheet.getMergedRegion(i);
            // Line start subscript 
            int firstRow = region.getFirstRow();
            // End of line subscript 
            int lastRow = region.getLastRow();
            // Column start subscript 
            int firstColumn = region.getFirstColumn();
            // Column end subscript 
            int lastColumn = region.getLastColumn();
            if (row >= firstRow && row <= lastRow) {
    
                if (col >= firstColumn && col <= lastColumn) {
    
                    Row fRow = sheet.getRow(firstRow);
                    Cell cell = fRow.getCell(firstColumn);
                    // All contents have been processed in string form 
                    cell.setCellType(CellType.STRING);
                    // Get the value of the cell as a string 
                    return cell.getStringCellValue();
                }
            }
        }
        Cell cell = sheet.getRow(row).getCell(col);
        // All contents have been processed in string form 
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }

after service Layer resolution , Here we have successfully completed the effect of Figure 2 , The final effect is “entities” This List It's in the assembly , Then insert the set data into the database in batches .

Friends with similar needs , Copy my code directly and change it according to my own needs , According to the debug Step by step debugging , It will succeed in the end .