springboot实现批量导入excel数据进mysql（单一实现与基于注解模板导入）

配置

在Web项目开发过程中我们有批量导入数据的需求，使用Apache的poi是一种不错的选择。
导入架包

 <!-- 导入数据 -->
  <dependencies>
        <!--spring data jpa 依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        
        <!--场景启动器-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--lombok插件依赖、简化代码-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--mysql数据库-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        
       <!--poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>
</dependencies>

数据库表创建脚本

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '账号（邮箱）',
  `username` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码（MD5加密）',
  `role` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户角色',
  `avatar` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '头像',
  `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '1为男，2为女',
  `sign` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '签名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 25 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

实体类User

@Setter
@Getter
@NoArgsConstructor
@Entity
@Table(name="t_user")
@DynamicInsert(true)
@DynamicUpdate(true)
public class User  implements Serializable {
    
    @Id
   private int id;
   private String account;
   private String username;
   private String password;
   private String role;
   private String avatar;
   private String sex;
   private String sign; //签名

   public User(Object [] values) {
       //账号、用户名、密码、角色、头像、性别、签名
       this.account = values[1].toString();
       this.username = values[2].toString();
       this.password = values[3].toString();
       this.role =values[4].toString();
       this.avatar = values[5].toString();
       this.sex=values[6].toString();
       this.sign=values[7].toString();
   }

}

dao 与server层

#dao
public interface UserDao extends JpaRepository<User,Integer>, JpaSpecificationExecutor<User> {
}

#service
public interface UserService {
   void save(List<User> users);
}

#serviceImpl

@Service
public class UserServiceImpl implements UserService {

  @Autowired
  UserDao userDao;
    
  public void save(List<User> users){
  userDao.saveAll(users);
  }
}

指定实体类导入

使excel表中每一列与实体类的属性相对应，确保数据无误

 @RequestMapping(value="/user/import",method = RequestMethod.POST)
    public Result importUser(@RequestParam(name="file") MultipartFile file) throws Exception {
        //1.解析Excel
        //1.1.根据Excel文件创建工作簿
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        //1.2.获取Sheet
        Sheet sheet = wb.getSheetAt(0);//参数：索引
        //1.3.获取Sheet中的每一行，和每一个单元格
        //2.获取用户数据列表
        List<User> list = new ArrayList<>();
        System.out.println(sheet.getLastRowNum());
        for (int rowNum = 1; rowNum<= sheet.getLastRowNum() ;rowNum ++) {
            Row row = sheet.getRow(rowNum);//根据索引获取每一个行
            Object [] values = new Object[row.getLastCellNum()];
            for(int cellNum=1;cellNum< row.getLastCellNum(); cellNum ++) {
                Cell cell = row.getCell(cellNum);
                Object value = getCellValue(cell);
                values[cellNum] = value;
            }
            User user = new User(values);
            list.add(user);
        }
        //3.批量保存用户
        userService.saveAll(list);

        return new Result(ResultCode.SUCCESS);
    }

    //格式装换
    public static Object getCellValue(Cell cell) {
        //1.获取到单元格的属性类型
        CellType cellType = cell.getCellType();
        //2.根据单元格数据类型获取数据
        Object value = null;
        switch (cellType) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)) {
                    //日期格式
                    value = cell.getDateCellValue();
                }else{
                    //数字
                    value = cell.getNumericCellValue();
                }
                break;
            case FORMULA: //公式
                value = cell.getCellFormula();
                break;
            default:
                break;
        }
        return value;
    }

基于注解实现

上面的实现方式对单一的数据导入简单快捷，如果有多种数据导入，无语会有许多重复冗余的代码，我们可以自定义注解，通过反射把创建实体类
自定义注解

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAttribute {

    /** 对应的列名称 */
    String name() default "";

    /** excel列的索引 */
    int sort();

    /** 字段类型对应的格式 */
    String format() default "";
}

为User添加注解

public class User  implements Serializable {
   
    @Id
   private int id;
   
   @ExcelAttribute(sort = 1)
   private String account;

   @ExcelAttribute(sort = 2)
   private String username;

   @ExcelAttribute(sort = 3)
   private String password;

   @ExcelAttribute(sort = 4)
   private String role;

   @ExcelAttribute(sort = 5)
   private String avatar;

   @ExcelAttribute(sort = 6)
   private String sex;

   @ExcelAttribute(sort = 7)
   private String sign; //签名

  }

导入工具类

public class ExcelImportUtil<T> {

    private Class clazz;
    private  Field fields[];  //反射字段

    //参数：
    public ExcelImportUtil(Class clazz) {
        this.clazz = clazz;
        fields = clazz.getDeclaredFields();
    }

    /**
     * 基于注解读取excel
     *      is     : 文件上传的流信息
     *      rowIndex:   读取数据的起始行
     *      cellIndex:  读取数据的其实单元格位置
     *
     */
    public List<T> readExcel(InputStream is, int rowIndex, int cellIndex) {
        List<T> list = new ArrayList<T>();
        T entity = null;
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            Sheet sheet = workbook.getSheetAt(0);
            for (int rowNum = rowIndex; rowNum <sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);

                //反射创建实例化对象
                entity = (T) clazz.getDeclaredConstructor().newInstance();
                for (int j = cellIndex; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    for (Field field : fields) {
                        if(field.isAnnotationPresent(ExcelAttribute.class)){
                            field.setAccessible(true);
                            ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                            if(j == ea.sort()) {
                                field.set(entity, covertAttrType(field, cell));  //把字段数据添加到对应entity属性中
                            }
                        }
                    }
                }
                list.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 类型转换 将cell 单元格格式转为 字段类型
     */
    private Object covertAttrType(Field field, Cell cell) throws Exception {
        String fieldType = field.getType().getSimpleName();
        if ("String".equals(fieldType)) {
            return getValue(cell);
        }else if ("Date".equals(fieldType)) {
            return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(getValue(cell)) ;
        }else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
            return Integer.parseInt(getValue(cell));
        }else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return Double.parseDouble(getValue(cell));
        }else {
            return null;
        }
    }


    /**
     * 格式转为String
     * @param cell
     * @return
     */
    public String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
                    return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(dt);
                } else {
                    // 防止数值变成科学计数法
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(num.toString());
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
}

api接口，多种数据导入使用同一种接口

 @PostMapping(value = "/admin/import")
    public Result importData(@RequestParam(name="file") MultipartFile file,@RequestParam String type) throws IOException{

        if(type.equals("1")){
            List<User> users = new ExcelImportUtil(User.class).readExcel(file.getInputStream(), 1, 1);
            //批量保存用户
            userService.saveAll(users);
            return new Result(ResultCode.IMPORT_USER_SUCESS);
        }else if(type.equals("2")){
            List<Poem> poems=new ExcelImportUtil(Poem.class).readExcel(file.getInputStream(), 1,1);       //批量保存诗词
            poemService.saveAll(poems);
            return new Result(ResultCode.IMPORT_POEM_SUCESS);
        }else if(type.equals("3")){
            List<Article> articles=new ExcelImportUtil(Article.class).readExcel(file.getInputStream(), 1,1);       //批量保存文章
            articleService.saveAll(articles);
            return new Result(ResultCode.IMPORT_ARTICLE_SUCESS);
        }else if(type.equals("4")){
            List<Author> authors=new ExcelImportUtil(Author.class).readExcel(file.getInputStream(), 1,1);       //批量保存作者
            authorService.saveAll(authors);
            return new Result(ResultCode.IMPORT_AUTHOR_SUCESS);
        }
        return new Result(ResultCode.FAIL,"");
      }

测试

excel表中数据

使用postman测试，导入用户

t_user表中原有数据

批量数据导入成功