spring boot 整合easypoi 导入 数据数据库

一开始我使用阿里巴巴 easyexcel 进行操作，觉得太繁琐了，觉得麻烦，然后我就用easypoi-base ，感觉挺不错了，操作简单方便。话不多说直接上代码：

一、首先相关的依赖

<dependency>
         <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.2.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

二、读取excel数据工具类

import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.beanutils.converters.DateConverter;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.*;
import java.util.regex.Pattern;

/**
 * 导入excel工具类
 * @author  ZHY
 * @param <T>
 */
public class ExcelReader<T> {
    private static BeanUtilsBean beanUtilsBean = new BeanUtilsBean();

    static {
        beanUtilsBean.getConvertUtils().register(new DateConverter(null), Date.class);
    }
    /**
     * 表头名字和对应所在第几列的下标，用于根据title取到对应的值
     */
    private final Map<String,Integer> title_to_index = new HashMap<>();
    /**
     * 所有带有ExcelIn注解的字段
     */
    private final List<Field> fields = new ArrayList<>();

    /**
     * 统计表格的行和列数量用来遍历表格
     */
    private  int firstCellNum = 0;
    private  int lastCellNum = 0;
    private  int firstRowNum = 0;
    private  int lastRowNum = 0;

    private String sheetName ;

    private Sheet sheet ;

    public List read(InputStream in , Class clazz,String fileName) throws Exception {
        /**
         * 获取封装得实体得字段注解名称
         */
        gatherAnnotationFields(clazz);
        /**
         *
         */
        configSheet(in,fileName);
        /**
         * 表头配置
         */
        configHeader();
        /**
         * 读取sheet内容
         */
        List rList = readContent(clazz);
        return rList ;
    }

    /**
     * 读取内容值
     * @param clazz
     * @return
     * @throws HandException
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws InvocationTargetException
     */
    private List readContent(Class clazz) throws HandException, IllegalAccessException, InstantiationException, InvocationTargetException {
        Object o = null ;
        List<Object> rsList = new ArrayList<>();
        for(int i = (firstRowNum+1);i<=lastRowNum;i++){
            o = clazz.newInstance();
            Row row = sheet.getRow(i);
            beanUtilsBean.setProperty(o,"rowNum",i+1);
            for (Field field : fields) {
                //根据注解中的title，取到表格中该列所对应的的值
                ExcelIn annotation = field.getAnnotation(ExcelIn.class);
                Integer column=title_to_index.get(annotation.title());
                if(column==null){
                    continue;
                }
                Cell cell = row.getCell(column);
                Object value = getCellValue(cell);
                //验证必填项
                isRequired((i+1),column+1,annotation,value);
                if(null != value && StringUtils.isNotBlank(value.toString())) {
                    beanUtilsBean.setProperty(o, field.getName(), value);
                }
            }
            rsList.add(o);
        }
        return rsList ;
    }

    /**
     * 验证是否为空
     * @param rowNum
     * @param cellNum
     * @param annotation
     * @param value
     * @throws HandException
     */
    private void isRequired(int rowNum,int cellNum,ExcelIn annotation,Object value) throws HandException {
        boolean required =annotation.required();
        String msg =annotation.msg();
        String title =annotation.title();
        String regex =annotation.regex();
        if( required && (null == value || StringUtils.isBlank(value.toString()))){
            if(title.indexOf("*")!=-1){
                title=title.replace("*","");
            }
            throw new HandException("第"+rowNum+"行"+"第"+cellNum+"列"+title+"不能为空");
        }
        if(StringUtils.isNotBlank(regex)){
            if(!Pattern.matches(regex,value.toString())){
                throw new HandException("第"+rowNum+"行"+"第"+cellNum+"列"+msg);
            }
        }


    }

    /**
     * 判断是否是这两种类型文件
     * @param in
     * @param fileName
     * @throws Exception
     */
    private void configSheet(InputStream in,String fileName) throws Exception {
        //HSSFWorkbook:只能创建97-03版本的Excel，即：以xls结尾的Excel
        // 想要导入xlsx结尾的Excel，用XSSFWorkbook
        String ext = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(ext)) {
            try(Workbook wb = new HSSFWorkbook(in)){
                getSheetByName(wb);
            } catch (FileNotFoundException e) {
                throw new Exception(e);
            } catch (IOException e) {
                throw new Exception(e);
            }
        } else if (".xlsx".equals(ext)) {
            try(Workbook wb = new XSSFWorkbook(in)){
                getSheetByName(wb);
            } catch (FileNotFoundException e) {
                throw new Exception(e);
            } catch (IOException e) {
                throw new Exception(e);
            }
        }

    }


    /**
     * 根据sheet获取对应的行列值，和表头对应的列值映射
     */
    private void configHeader(){
        this.firstRowNum = sheet.getFirstRowNum() ;
        this.lastRowNum = sheet.getLastRowNum() ;
        //第一行为表头，拿到表头对应的列值
        Row row = sheet.getRow(firstRowNum);
        this.firstCellNum = row.getFirstCellNum();
        this.lastCellNum = row.getLastCellNum();
        for (int i = firstCellNum;i<lastCellNum;i++){
            title_to_index.put(row.getCell(i).getStringCellValue(),i);
        }
    }

    /**
     * 根据sheet名称获取sheet
     * @param workbook
     * @return
     * @throws Exception
     */
    private void getSheetByName(Workbook workbook) throws Exception {
        int sheetNumber = workbook.getNumberOfSheets();
        for (int i = 0; i < sheetNumber; i++) {
            String name = workbook.getSheetName(i);
            if(StringUtils.equals(this.sheetName,name)) {
                this.sheet = workbook.getSheetAt(i);
                return;
            }
        }
        throw new HandException("excel中未找到名称为"+this.sheetName+"的sheet");
    }

    /**
     * 根据自定义注解，获取所要导入表格的sheet名称和需要导入的字段名称
     * @param clazz
     * @throws Exception
     */
    private void gatherAnnotationFields(Class clazz) throws Exception {
        if(!clazz.isAnnotationPresent(ExcelIn.class)){
            throw new Exception(clazz.getName()+"类上没有ExcelIn注解");
        }
        ExcelIn excelIn = (ExcelIn)clazz.getAnnotation(ExcelIn.class) ;
        this.sheetName = excelIn.sheetName();
        // 得到所有定义字段
        Field[] allFields = FieldUtils.getAllFields(clazz) ;
        // 得到所有field并存放到一个list中
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelIn.class)) {
                fields.add(field);
            }
        }
        if( fields.isEmpty()){
            throw new Exception(clazz.getName()+"中没有ExcelIn注解字段");
        }
    }

    /**
     * 获取单元格得值
     * @param cell
     * @return
     */
    private Object getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        Object obj = null;
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case FORMULA:
                try {
                    obj = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    obj = numericToBigDecimal(cell);
                }
                break;
            case NUMERIC:
                obj = getNumericValue(cell);
                break;
            case STRING:
                String value = String.valueOf(cell.getStringCellValue());
                value = value.replace(" ", "");
                value = value.replace("\n", "");
                value = value.replace("\t", "");
                obj = value;
                break;
            default:
                break;
        }
        DataFormatter dataFormatter = new DataFormatter();
        dataFormatter.addFormat("###########", null);
        obj=dataFormatter.formatCellValue(cell);
        return obj;
    }

    /**
     *
     * @param cell
     * @return
     */
    private Object getNumericValue(Cell cell){
        // 处理日期格式、时间格式
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return  cell.getDateCellValue();
        }else if (cell.getCellStyle().getDataFormat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            double value = cell.getNumericCellValue();
            return DateUtil.getJavaDate(value);
        } else {
            return numericToBigDecimal(cell);
        }
    }

    /**
     *
     * @param cell
     * @return
     */
    private Object numericToBigDecimal(Cell cell) {
        cell.setCellType(CellType.STRING);
        return String.valueOf(cell.getRichStringCellValue().getString());
    }
}

 

三、对应实体的注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD ,ElementType.TYPE})
public @interface ExcelIn {

    /**
     * 导入sheet名称
     * @return
     */
    String sheetName() default "";

    /**
     * 字段对应的表头名称
     * @return
     */
    String title() default "";

    boolean required() default false;

    /**
     * 提示信息
     * @return
     */
    String msg() default "";

    /**
     * 正则匹配
     * @return
     */
    String regex() default "";
}

 

四、请求接收的实体

import cn.afterturn.easypoi.excel.annotation.Excel;


/**
 * @author ZHY
 * @since 2021/3/11 16:07
 */
@ExcelIn(sheetName = "员工信息导入模板")
public class StaffDTO {

    @Excel(name = "手机号码*")
    @ExcelIn(title = "手机号码*",required=true,regex = Regex.PHONE,msg = "手机号只能是11位")
    private String cellPhone;

    @Excel(name = "员工姓名*")
    @ExcelIn(title = "员工姓名*",required=true)
    private String userName;

    public String getCellPhone() {
        return cellPhone;
    }

    public void setCellPhone(String cellPhone) {
        this.cellPhone = cellPhone;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    @Override
    public String toString() {
        return "StaffDTO{" +
                "cellPhone='" + cellPhone + '\'' +
                ", userName='" + userName + '\'' +
                '}';
    }
}

 五、业务操作流程

 /**
     * @param file
     * @return
     */
    @Override
    public int importAddStaff(MultipartFile file) {
        try {
            InputStream is = file.getInputStream();
            ExcelReader<StaffDTO> reader = new ExcelReader<>();
            String fileName = file.getOriginalFilename();
            //读取excel数据
            List<StaffDTO> data = reader.read(is, StaffDTO.class, fileName);
            Integer shopId = getUser().getShopId();
            List<ShopUserInfo> infoList = new ArrayList<>();
            for (StaffDTO e : data) {
                ShopUserInfo shopUserInfo = shopUserInfoDao.selectByPhones(e.getCellPhone(), shopId);
                if (shopUserInfo != null) {
                    logger.info("Staff importAddStaff fail,The query is exist according shopId and phone,shopId:{},phone:{}", shopId,e.getCellPhone());
                    continue;
                }
                if (!RegexUtils.isChinaUnicomPhoneNum(e.getCellPhone())) {
                    logger.info("Staff importAddStaff fail, Verify the mobile phone number does not conform to Unicom mobile phoneNumber,phone:{}",e.getCellPhone());
                    continue;
                }
                ShopUserInfo info = new ShopUserInfo();
                info.setName(e.getUserName());
                info.setPhone(e.getCellPhone());
                info.setAuthStatus(2);
                info.setShopId(shopId);
                info.setMsgStatus(2);
                info.setCreateTime(LocalDateTime.now());
                infoList.add(info);
            }
            if (infoList.size() == 0) {
                logger.info("Staff importAddStaff fail, Excluding according to Excel file import does not get the data that needs to be imported,file:{}",file.getOriginalFilename());
                return 6;
            }
            return shopUserInfoDao.insertBatchUserInfo(infoList);
        } catch (Exception e) {
            logger.error("Failed to get employee import Excel file，{}", e.getMessage(), e);
        }
        return 0;
    }

六、excel模板如图：

 

如果上面没有解决你的问题你留言稍后帮你解决