springboot导入excel（POI）

POI官方文档

引入依赖

        <!--POI-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

编写导入工具类

        结合springboot导出（POI）用到的自定义注解，让导入使用起来更方便简洁且更容易扩展。

public class ExcelImportUtil {
 
    /*
     * @description: 读取excel数据 
     * @create: 2023/9/20 16:12 
     * @param file
     * @param tClass
     * @return java.util.List
     */
    public static List readExcel(MultipartFile file, Class tClass){
        if(file.isEmpty()){
            return new ArrayList<>();
        }
        //获取列名与属性名的映射
        Map feildMap = getFeildMap(tClass);
        if(ObjectUtils.isEmpty(feildMap)){
            return new ArrayList<>();
        }
        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            //解析文件
            inputStream = file.getInputStream();
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            int lastCellNum = sheet.getRow(0).getLastCellNum();
            //解析表头
            Row headerRow = sheet.getRow(0);
            List headerNameList = new ArrayList<>();
            for (int i = 0; i < lastCellNum; i++) {
                headerNameList.add(headerRow.getCell(i).getStringCellValue());
            }
            //读取数据
            List> dataList = new ArrayList<>();
            for (int i = 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                Map rowValueMap = new HashMap<>();
                for (int j = 0; j < lastCellNum; j++) {
                    String key = feildMap.get(headerNameList.get(j));
                    Object value = getCellValue(row.getCell(j));
                    rowValueMap.put(key, value);
                }
                dataList.add(rowValueMap);
            }
            workbook.close();
            inputStream.close();
            List list = convertToList(dataList);
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                if(workbook != null){
                    workbook.close();
                }
                if(inputStream !=null){
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return new ArrayList<>();
    }
 
    /*
     * @description: 解析导入的实体，获取列名与属性名的映射 
     * @create: 2023/9/20 16:27 
     * @param tClass
     * @return java.util.Map
     */
    private static Map getFeildMap(Class tClass){
        Field[] fields = tClass.getDeclaredFields();
        if(ObjectUtils.isEmpty(fields)){
            return null;
        }
        Map keyMap = new HashMap<>();
        for(Field field : fields){
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if(ObjectUtils.isNotEmpty(excelField)){
                keyMap.put(excelField.name(), field.getName());
            }
        }
        return keyMap;
    }
 
    /*
     * @description: 获取单元格的值 
     * @create: 2023/9/20 16:47 
     * @param cell
     * @return java.lang.Object
     */
    private static Object getCellValue(Cell cell){
        if(cell == null){
            return null;
        }
        CellType cellType = cell.getCellType();
        if(cellType.equals(CellType.BLANK)){
            return null;
        }
        //字符串
        if (cellType.equals(CellType.STRING)){
            return cell.getStringCellValue();
        }
        if(cellType.equals(CellType.NUMERIC)){
            //日期
            if(HSSFDateUtil.isCellDateFormatted(cell)){
                return cell.getDateCellValue();
            }else{
                //数值
                return cell.getNumericCellValue();
            }
        }
        //布尔
        if(cellType.equals(CellType.BOOLEAN)){
            return cell.getBooleanCellValue();
        }
        return null;
    }
 
    /*
     * @description: 转list 
     * @create: 2023/9/20 17:54 
     * @param obj
     * @return java.util.List
     */
    private static List convertToList(Object obj){
        ObjectMapper objectMapper = new ObjectMapper();
        //日期格式
        objectMapper.setDateFormat(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));
        //设置时区
        objectMapper.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
        //序列化-忽略null值的属性
        objectMapper.setSerializationInclusion(Include.NON_NULL);
        //序列化-允许序列化空对象
        objectMapper.disable(SerializationFeature.FAIL_ON_EMPTY_BEANS);
        //反序列化-在遇到未知属性的时候不抛出异常
        objectMapper.disable(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES);
        if(null == obj){
            return null;
        }
        return objectMapper.convertValue(obj, new TypeReference>() {});
    }
}

测试

导入模板

导入的泛型（对象）

@Data
public class ImportTest {
 
    @ExcelField(name = "字符串")
    private String feild1;
 
    @ExcelField(name = "数值")
    private BigDecimal feild2;
 
    @ExcelField(name = "布尔")
    private Boolean feild3;
 
    @ExcelField(name = "日期")
    private Date feild4;
}

controller

@Api(tags = "测试")
@RestController
@RequestMapping("/test")
public class TestController {
 
    @ApiOperation("导入")
    @PostMapping("/importExcel")
    public ResponseEntity> importExcel(@ApiParam(name = "file") @RequestPart("file") MultipartFile file){
        List list = ExcelImportUtil.readExcel(file, ImportTest.class);
        System.out.print(list);
        return ResponseEntity.ok(list);
    }
}

response