excel导入通用代码

导入模板的样式：

1:自定义注解

/**
 * 用于注解需要插入的对象
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Excel {
}

/**
 * 用于注解对象的属性
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Column {
    /**
     * 单元格对应的列号，必须指明，不能重复
     *
     * @return
     */
    public int column() default -1;

    /**
     * 对应的名字、描述
     *
     * @return
     */
    public String description() default "no description";

    /**
     * 单元格内容是否可为空
     * true：可以为空，false:不能为空
     * @return
     */
    public boolean nullable() default true;
    
}

2：使用注解

@Data
@Excel
public class CreateReservoirDto {
    @Column(column = 0,nullable = false,description = "测站名字")
    private String name;
    @Column(column = 1,nullable = false,description = "测站编码")
    private String number;
    @Column(column = 2,nullable = false,description = "东经")
    private String lng;
    @Column(column = 3,nullable = false,description = "北纬")
    private String lat;
    @Column(column = 4,description = "地区")
    private String address;
    @Column(column = 5,description = "坝顶高程")
    private Double damTop;
    @Column(column = 6,description = "堰顶高程")
    private Double weirTop;
    @Column(column = 7,description = "汛限水位")
    private Double floodWaterLevel;
    @Column(column = 8,description = "校核洪水位")
    private Double checkFloodWaterLevel;
    @Column(column = 9,description = "设计洪水位")
    private Double designFloodWaterLevel;
    @Column(column = 10,description = "正常蓄水位")
    private Double normalStoreWater;
    @Column(column = 11,description = "死水位")
    private Double deadWaterLevel;
    @Column(column = 12,description = "总库容")
    private Double totalStorage;
    @Column(column = 13,description = "兴利库容")
    private Double utilizableCapacity;
    @Column(column = 14,description = "死库容")
    private Double deadStorage;
    @Column(column = 15,description = "创建时间")
    private Long createdTime;
}

3：导入转转对象的工具类

import com.irrigation.icl.exception.ContextRuntimeException;
import com.irrigation.synthetic.annotation.Column;
import com.irrigation.synthetic.annotation.Excel;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;


@Slf4j
public class ExcelUtils {

    /**
     * 将excel数据转换为javaBean
     *
     * @param file     excel文件
     * @param startRow 导入内容的开始行号（排除表头）
     * @param clazz    要转换的对象
     * @param <T>
     * @return
     */
    public static <T> List<T> getDataFromExcel(MultipartFile file, int startRow, Class<T> clazz) {
        List<T> list = new ArrayList<>();
        InputStream is = null;
        try {
            //创建工作簿
            is = file.getInputStream();
            String excelFileName = file.getOriginalFilename();
            Workbook workbook = createWorkbook(is, excelFileName);
            //创建工作表sheet
            Sheet sheet = getSheet(workbook, 0);
            //获取sheet中数据的行数
            int rows = sheet.getPhysicalNumberOfRows();
            //获取表头单元格个数
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            //判断是否使用excel注解
            if (!clazz.isAnnotationPresent(Excel.class)) {
                log.error(clazz + "没有使用@Excel注解");
                return null;
            }
            //利用反射，给JavaBean的属性进行赋值
            Field[] fields = clazz.getDeclaredFields();
            Map<Integer, Field> fieldMap = new HashMap<>();
            if (null != fields && fields.length > 0) {
                for (Field field : fields) {
                    if (field.isAnnotationPresent(Column.class)) {
                        Column column = field.getAnnotation(Column.class);
                        int number = column.column();
                        if (number < 0) {
                            log.error(field.getName() + "没有使用@column注解");
                            return null;
                        } else {
                            fieldMap.put(number, field);
                        }
                    }
                }
            }
            //包装对象
            for (int i = startRow; i < rows; i++) {
                Row row = sheet.getRow(i);
                int index = 0;
                T instance = clazz.newInstance();
                while (index < cells) {
                    Field field = fieldMap.get(index);
                    if (null != field) {
                        //获得单元格
                        Cell cell = row.getCell(index);
                        Column column = field.getAnnotation(Column.class);
                        Object value = null;
                        if (Integer.class.equals(field.getType()) || Double.class.equals(field.getType())) {
                            if(cell!=null&&cell.getCellTypeEnum()!=CellType.BLANK){
                                cell.setCellType(CellType.NUMERIC);
                                value =cell.getNumericCellValue();
                            }
                        } else if (String.class.equals(field.getType())) {
                            if(cell!=null&&cell.getCellTypeEnum()!=CellType.BLANK){
                                cell.setCellType(CellType.STRING);
                                value = cell.getStringCellValue();
                            }
                        } else if (Date.class.equals(field.getType())) {
                            if(cell!=null&&cell.getCellTypeEnum()!=CellType.BLANK){
                                cell.setCellType(CellType.STRING);
                                value = cell.getDateCellValue();
                            }
                        } else if (Boolean.class.equals(field.getType())) {
                            if(cell!=null&&cell.getCellTypeEnum()!=CellType.BLANK){
                                cell.setCellType(CellType.BOOLEAN);
                                value = cell.getBooleanCellValue();
                            }
                        } else {
                            log.error("单元格类型错误");
                            return null;
                        }
                        //不能为空
                        if (!column.nullable()) {
                            if (StringUtils.isEmpty(value)) {
                                throw new ContextRuntimeException("第" + i+1 + "行第" + index+1 + "列不能为空");
                            }
                        }
                        //设置属性值
                        if(!StringUtils.isEmpty(value)){
                            String fieldName = field.getName();
                            String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                            //获得set方法的名字
                            Method setMethod = clazz.getMethod(methodName, new Class[]{field.getType()});
                            setMethod.invoke(instance, new Object[]{value});
                        }
                    }
                    index++;
                }
                list.add(instance);
            }

        } catch (ContextRuntimeException e) {
            throw e;
        } catch (Exception e) {
            log.error(e.getMessage());
        } finally {
            try {
                if (null != is) {
                    is.close();//关闭流
                }
            } catch (Exception e2) {
                log.error(e2.getMessage());
            }
        }
        return list;
    }
    
    /**
     * 创建工作簿
     *
     * @param is
     * @param excelFileName
     * @return
     * @throws IOException
     */
    private static Workbook createWorkbook(InputStream is, String excelFileName) throws IOException {
        if (null != excelFileName) {
            if (excelFileName.endsWith(".xls")) {
                return new HSSFWorkbook(is);
            } else if (excelFileName.endsWith(".xlsx")) {
                return new XSSFWorkbook(is);
            }
        }
        return null;
    }

    /**
     * 创建工作表
     *
     * @param workbook
     * @param sheetIndex
     * @return
     */
    private static Sheet getSheet(Workbook workbook, int sheetIndex) {
        return workbook.getSheetAt(0);
    }
}

4：controller层

    @ApiOperation(value = "导入excel", notes = "导入excel")
    @ApiImplicitParam(name = "irriAreaId", value = "灌区id", paramType = "query", dataType = "int", required = true)
    @PostMapping("/import")
    public RestResult<String> importReservoir(@RequestParam("file") MultipartFile file, @RequestParam("irriAreaId") Integer irriAreaId) {
        try {
            reservoirService.importReservoir(file, irriAreaId);
            return RestResultGeneratorUtil.getSuccessResult();
        } catch (ContextRuntimeException e) {
            throw e;
        } catch (Exception e) {
            throw new ContextRuntimeException(ErrorEnum.IMPORT_DATA_ERROR.getMessage());
        }
    }

5：service层

    @Override
    public void importReservoir(MultipartFile file, Integer irriAreaId) {
        //excel表中每一列对应数据字段的属性
        List<CreateReservoirDto> createReservoirDtoList = ExcelUtils.getDataFromExcel(file,1, CreateReservoirDto.class);
        if (!CollectionUtils.isEmpty(createReservoirDtoList)) {
           createReservoirDtoList =  createReservoirDtoList.parallelStream().map(r -> {
                r.setIrriAreaId(irriAreaId);
                r.setCreatedTime(System.currentTimeMillis());
                return r;
            }).collect(Collectors.toList());    
           //批量写入数据库
           reservoirMapper.batchInsert(createReservoirDtoList);
        }
    }