Super easy to use [general excel import function]

I wrote a simple one before Excel Import function , You can look at this first ： https://www.bilibili.com/video/BV1kf4y1i761?p=5

There's a demand recently , Make one Excel Batch import function , The specific steps are as follows ：

One 、 demand

1-1、 Download the template (excel)

full name （2-4 Characters ）	Age （1-150）	Gender （ male / Woman ）

1-2、 Data filling and importing

full name （2-4 Characters ）	Age （1-150）	Gender （ male / Woman ）
Zhang Sanfeng	20	male
Xiaodaoxian 97	24	male
Murong Xiaoxiao	18	women

1-3、 Import results display

1-4、 Data validation rules

• The name cannot be empty and can only be 2-4 Characters
• Age cannot be empty and can only be 1-150
• Gender cannot be empty and can only be male / Woman
• The imported data cannot have two files with the same name , If it exists, the import fails
• If there is a person with the same name in the database, you can't import

Two 、 Plan ideas

Use the above Excel Import function , hold Excel The template data is parsed into the corresponding List<Map<String,Object>> data .

Use data to format the corresponding data into the corresponding entity , Then check the corresponding data according to the conditions in turn .

In fact, in addition to the import data verification rules , Everything else is universal , For example, return field 、 analysis excel And encapsulate it into bean, Deal with abnormal data, etc .

3、 ... and 、 Code using

3-1、 Create a new one VO, For example, the field you import is name、age、gender Then your entity should be as follows ：

notes ： omitted get 、set 、 And null parameter construction method

public class TestImportExcelVo {
    

    /** *  Header  */
    public final static String[] HEADER = {
    "name","age","gender"};


    /** *  full name  */
    private String name;

    /** *  Age  */
    private String age;

    /** *  Gender  */
    private String gender;
}

You can see that the only difference is that there is one more constant HEADER, Its rule is Import template field order

3-2、 Add a new one ImportExcel Implementation class of interface , Realize the check Method （ This interface is explained below ）

@Service
public class TestImportExcelImpl implements ImportExcel<TestImportExcelVo> {
    

    @Override
    public String[] check(ImportItem importItem, List<TestImportExcelVo> successResult, List<ImportItem> errorResult) {
    
        
        return new String[]{
    "1",""};
    }
}

3-3、 call

@RestController
public class ImportExcelController {
    
    @Autowired
    public TestImportExcelImpl testImportExcelImpl;

    @PostMapping("/importExcel")
    public Object importExcel(MultipartFile file) {
    

       return testImportExcelImpl.importCheck(file, TestImportExcelVo.HEADER, TestImportExcelVo.class);
    }
}

Four 、 Code ideas explain

In fact, it depends on the interface ImportExcel There are two ways .

4-1、 Get ready

Two are used here bean as follows （ Removed get/set）

/** *  Export error result set entity bean * *  Because the error result set needs one more reason field  * * @author  Xiaodaoxian  * @date 2021 year 8 month 4 Japan  */
public class ImportItem<T> {
    

    private String errorReason;

    public T t;
}

/** *  Import Excel Return result entity  * * @author  Xiaodaoxian  * @date 2021-08-04 */
public class ImportResultVo<T> {
    

    /** *  Total number of lines  */
    private Integer totalRows;

    /** *  Wrong number of lines  */
    private Integer errorRows;

    /** *  Number of successful lines  */
    private Integer successRows;

    /** *  Failed result set  */
    private List<T> errorResults;

    /** *  Successful result set  */
    private List<T> successResults;

    public ImportResultVo() {
    
    }

    public ImportResultVo(Integer totalRows,Integer errorRows,Integer successRows,List<T> errorResults,List<T> successResults) {
    
        this.totalRows = totalRows;
        this.errorRows = errorRows;
        this.successRows = successRows;
        this.errorResults =errorResults;
        this.successResults = successResults;
    }
}

4-2、importCheck Method

This is a default method to complete some general functions , Such as ：excel analysis 、 Entity bean Package, etc

default ImportResultVo importCheck(MultipartFile file, String[] header,Class clazz){
    
    //  from excel The data parsed from it 
    List<Map<String, Object>> maps;
    try {
    
        maps = ExcelUtils.importExcel(file, header);
    }catch (Exception e){
    
        logger.error(" Parse failure , The data template is incorrect ",e);
        throw new RuntimeException(" Parse failure , The data template is incorrect ");
    }
    if (CollectionUtils.isEmpty(maps)){
    
        return new ImportResultVo(0,0,0,null,null);
    }

    //  Step by step validation of the data 
    int totalRows = 0;
    int successRows = 0;
    int errorRows = 0;
    List<T> successResult = new ArrayList<>(50);
    List<ImportItem> errorResult = new ArrayList<>(50);
    for (Map map : maps) {
    
        ImportItem<T> importItem = new ImportItem();
        totalRows ++;
        try {
    
            Object tmpT = JsonUtils.objectToPojo(map, clazz);
            importItem.setT((T)tmpT);
        }catch (Exception e) {
    
            errorRows ++;
            logger.error("bean switch views ", e);
            continue;
        }
        String[] check = check(importItem, successResult, errorResult);
        if ("1".equals(check[0])) {
    
            successRows ++;
            successResult.add(importItem.getT());
        }else if ("2".equals(check[0])) {
    
            errorRows ++;
            importItem.setErrorReason(check[1]);
            errorResult.add(importItem);
        }else if ("3".equals(check[0])){
    
            successRows --;
            errorRows ++;
            importItem.setErrorReason(check[1]);
            errorResult.add(importItem);
        }
    }
    return new ImportResultVo(totalRows,errorRows,successRows,errorResult,successResult);
}

4-3、check Method

This is an interface method , You need to implement specific verification rules by yourself

/** * * @param t  The entity to be verified at present bean * @param successResult  Verified correct result set  * @param errorResult  Result set of verified errors  * @return  The first parameter is the verification result , The second is the reason for failure  * 1  success  * 2  Normal failure （ For example, the field is empty , The field format is incorrect , Conflict with database, etc ） * 3  Abnormal failure （ Conflict with previously successful data , For example, the uniqueness field already exists ） *  notes ：【 Abnormal failure 】 When , You need to check Method to delete the previously successful data , And insert the previous data into  errorResult  Inside  */
String[] check(ImportItem t, List<T> successResult, List<ImportItem> errorResult);

4-4、 Other

there excel We have already said before exporting tool classes , Here's a simple copy .

/** * Excel  Import  * @param file  file  * @param keys  Data order  */
public static  List<Map<String, Object>>  importExcel(MultipartFile file, String[] keys) throws Exception{
    
    Workbook wb = null;
    String fileName = file.getOriginalFilename();
    if (fileName.endsWith("xls")) {
    
        POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
        wb = new HSSFWorkbook(pois);
    } else if (fileName.endsWith("xlsx")) {
    
        wb = new XSSFWorkbook(file.getInputStream());
    }
    Sheet sheet = wb.getSheetAt(0);
    int rowCount = sheet.getPhysicalNumberOfRows();
    if (sheet.getRow( 1).getPhysicalNumberOfCells() != keys.length){
    
        throw new RuntimeException(" Imported Excel Does not match the column of the template ");
    }
    List<Map<String,Object>> result = new ArrayList<>();
    for (int i = 0; i < rowCount - 1; i++) {
    
        Row row = sheet.getRow(i + 1);
        Map<String,Object> tmp = new HashMap<>();
        for (int j = 0;j < keys.length; j++){
    
            Cell cell = row.getCell(j);
            if(cell == null) {
    
                tmp.put(keys[j], null);
            }else {
    
                //  Convert type to line Spring
                cell.setCellType(CellType.STRING);
                tmp.put(keys[j], cell.getStringCellValue());
            }
        }
        result.add(tmp);
    }
    return result;
}

Look at the video and explain the address ：https://www.bilibili.com/video/BV1kf4y1i761

Pay attention to WeChat official account reply ：xdxFrameSimple Access to the source code .