使用easyExcel实现excel数据的导入和导出

最新推荐文章于 2024-09-14 20:30:17 发布

学会用脚编程

最新推荐文章于 2024-09-14 20:30:17 发布

阅读量2.1k

点赞数 19

文章标签： excel spring boot 数据库

本文链接：https://blog.csdn.net/weixin_50737119/article/details/134871572

版权

前言：

公司准备开展新项目了，但是数据库中还没有数据，数据都在客户提供的excel中，现在领导让我把这些数据快速导入到库中，方便后期开发。一个一个导入效率太慢了，作为一名合格的程序员，应该学会合理摸鱼。那么我是如何实现的呢？答案就是阿里巴巴提供的EasyExcel。现在教大家我是如何使用的。

什么是EasyExcel

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

官方文档: EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel (alibaba.com)

Excel解析流程图:

EasyExcel读取Excel的解析原理:

SpringBoot集成EasyExcel

第一步：引入依赖：

     <!--easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.3</version>
        </dependency>

第二步：创建实体类

@Data

public class Stu implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("班级")
    private String calss;

    @ExcelProperty("学校")
    private String school;

    @ExcelProperty(value = "性别", converter = GenderConverter.class)
    private Integer sex;
}

常用注解有：
@ExcelProperty 指定当前字段对应excel中的哪一列。可以根据名字或者Index去匹配。当然也可以不写，默认第一个字段就是index=0，以此类推。千万注意，要么全部不写，要么全部用index，要么全部用名字去匹配。千万别三个混着用，除非你非常了解源代码中三个混着用怎么去排序的。
@ExcelIgnore EasyExcel默认所有字段都会和excel去匹配，加了这个注解会忽略该字段
@DateTimeFormat 日期转换，用String去接收excel日期格式的数据会调用这个注解。里面的value参照java.text.SimpleDateFormat
@NumberFormat 数字转换，用String去接收excel数字格式的数据会调用这个注解。里面的value参照java.text.DecimalFormat

温馨提示：如果你第一次使用，博主建议直接在每个字段上面写上

@ExcelProperty("姓名")

第三步：性别枚举类

注：这个根据你的表字段形式变化

@Getter
@AllArgsConstructor
public enum GenderEnum {
    /**
     * 未知
     */
    UNKNOWN(0, "未知"),

    /**
     * 男性
     */
    MALE(1, "男"),

    /**
     * 女性
     */
    FEMALE(2, "女");

    private final Integer value;

    @JsonFormat
    private final String description;

    public static GenderEnum convert(Integer value) {
//        用于为给定元素创建顺序流
//        values:获取枚举类型的对象数组
        return Stream.of(values())
                .filter(bean -> bean.value.equals(value))
                .findAny()
                .orElse(UNKNOWN);
    }

    public static GenderEnum convert(String description) {
        return Stream.of(values())
                .filter(bean -> bean.description.equals(description))
                .findAny()
                .orElse(UNKNOWN);
    }

}

第四步：创建性别转换器

如果你导入的数据中没有类似于性别这种汉字对应数据库表中数字的字段，那么亦可以不用这个类，小编还是建议大家都创建以备不时之需。

/**
 * 性别转换器
 * */

public class GenderConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        // 实体类中对象属性类型
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        // Excel中对应的CellData(单元格数据)属性类型
        return CellDataTypeEnum.STRING;
    }



    /**
     * 将单元格里的数据转为java对象，也就是女转成2，男转成1，用于导入excel时对性别字段进行转换
     * */
    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
        // 从CellData中读取数据，判断Excel中的值，将其转换为预期的数值
        return GenderEnum.convert(context.getReadCellData().getStringValue()).getValue();
    }
    /**
     * 将java对象转为单元格数据，也就是2转成女，1转成男，用于导出excel时对性别字段进行转换
     * */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        // 判断实体类中获取的值，转换为Excel预期的值，并封装为CellData对象
        return new WriteCellData<>(GenderEnum.convert(context.getValue()).getDescription());
    }
}

第五步：编写自定义监听器

注：自定义监听器的作用就是对下载的excel中的数据进行数据校验

/**
 * 自定义监听器，对下载的excel中的数据进行校验
 * */
public class StuListener extends AnalysisEventListener {

    List<String> names = new ArrayList<>();

    /**
     * 每解析一行，回调该方法
     *
     * @param data
     * @param context
     */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        //校验名称
        String name = ((Stu) data).getName();
        if (StrUtil.isBlank(name)) {
            throw new RuntimeException(String.format("第%s行名称为空，请核实", context.readRowHolder().getRowIndex() + 1));
        }
        if (names.contains(name)) {
            throw new RuntimeException(String.format("第%s行名称已重复，请核实", context.readRowHolder().getRowIndex() + 1));
        } else {
            names.add(name);
        }
    }

    /**
     * 出现异常回调
     *
     * @param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        if (exception instanceof ExcelDataConvertException) {
            /**从0开始计算*/
            Integer columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1;
            Integer rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1;
            String message = "第" + rowIndex + "行，第" + columnIndex + "列" + "数据格式有误，请核实";
            throw new RuntimeException(message);
        } else if (exception instanceof RuntimeException) {
            throw exception;
        } else {
            super.onException(exception, context);
        }
    }

    /**
     * 解析完,全部回调
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //解析完,全部回调逻辑实现
        names.clear();
    }
}

Excel导入

前序工作准备好了，现在进行excel数据导入

controller层代码：

@RestController
@RequestMapping("/stu")
public class StuController {
    @Autowired
    private StuMapper stuMapper;

    @PostMapping(value = "/importData")
    public void importData(MultipartFile file){
        try {
            //获取文件的输入流
            InputStream inputStream = file.getInputStream();
            List<Stu> lst = EasyExcel.read(inputStream) //调用read方法
                    //注册自定义监听器，字段校验可以在监听器内实现
                    .registerReadListener(new StuListener())
                    .head(Stu.class) //对应导入的实体类
                    .sheet(0) //导入数据的sheet页编号，0代表第一个sheet页，如果不填，则会导入所有sheet页的数据
                    .headRowNumber(1) //列表头行数，1代表列表头有1行，第二行开始为数据行
                    .doReadSync(); //开始读Excel，返回一个List<T>集合，继续后续入库操作

            //导入数据库操作
            stuMapper.insertBatch(lst);
            //打印读取到的数据
            //for (Stu userDO:lst){
            // System.out.println(userDO.toString());
            //}

        }catch (IOException exception){
            throw new  RuntimeException(exception);
        }
    }

mapper.xml层

   <insert id="insertBatch" parameterType="java.util.List">
        INSERT INTO stu (name, age,calss,school,sex)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.name}, #{item.age},  #{item.calss}, #{item.school}, #{item.sex})
        </foreach>
    </insert>

我通过foreach标签进行了批量插入，一条sql就插入所有数据，减少与数据库的交互。提升效率

效果：

Excel导出

如果小伙伴需要将数据库中的数据导出到excel中，哟是可以的

controller层代码


        /**
         * 设置响应结果
         *
         * @param response    响应结果对象
         * @param rawFileName 文件名
         * @throws UnsupportedEncodingException 不支持编码异常
         */
        private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {

            //设置内容类型
            response.setContentType("application/vnd.vnd.ms-excel");
            //设置编码格式
            response.setCharacterEncoding("utf-8");
            //设置导出文件名称（避免乱码）
            String fileName = URLEncoder.encode(rawFileName.concat(".xlsx"), "UTF-8");
            // 设置响应头
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
        }

        private Date getBirthday(int year, int month, int day){
            Calendar calendar = Calendar.getInstance();
            calendar.set(year, month, day);
            return calendar.getTime();
        }

/**
 * 导出数据
 * */
        @GetMapping("/export/user")
        public void exportUserExcel(HttpServletResponse response) throws IOException {
            OutputStream outputStream=response.getOutputStream();
            try {
                String filePath = "userlist.xlsx"; // 保存到当前项目下的文件名
                File file = new File(filePath);
                this.setExcelResponseProp(response, "用户列表");
                // 模拟根据条件在数据库查询数据
                List<Stu> stus = stuMapper.selectList(null);

                // 创建本地文件输出流
                 outputStream = new FileOutputStream(file);
                //这个实现方式非常简单直接，使用EasyExcel的write方法将查询到的数据进行处理，以流的形式写出即可
                EasyExcel.write(outputStream,Stu.class)//对应的导出实体类
                        .excelType(ExcelTypeEnum.XLSX)//excel文件类型，包括CSV、XLS、XLSX
                        .sheet("Stu列表")//导出sheet页名称
                        .doWrite(stus); //查询获取的数据集合List<T>，转成excel
            } catch (IOException e) {
                throw new RuntimeException(e);
            }finally {
                outputStream.flush();
                outputStream.close();
            }
        }

导出看效果;