Easy Excel小试牛刀 读文件

官网：EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel 官网 (alibaba.com)（目前停更维护中）

1、下载依赖

<!--https://gitee.com/easyexcel/easyexcel#https://gitee.com/link?target=https%3A%2F%2Feasyexcel.opensource.alibaba.com%2F-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>4.0.3</version>
        </dependency>

2、编写Excel映射类

package com.shier.model.domain;


import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;


/**
* 
* @TableName schools
*/
@Data
public class Schools implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
    * 学校id
    */
    @TableId(value = "id", type = IdType.AUTO) // 指定主键自增
    @ApiModelProperty("学校id")
    private Long id;
    /**
    * 学校名称
    */
    @ApiModelProperty("学校名称")
    private String schoolName;
    /**
    * 学校编码
    */
    @ApiModelProperty("学校编码")
    private Long schoolCode;
    /**
    * 主管部门
    */
    @ApiModelProperty("主管部门")
    private String schoolDepartment;
    /**
    * 学校地址
    */
    @ApiModelProperty("学校地址")
    private String schoolAddress;
    /**
    * 学校办学层次
    */
    @ApiModelProperty("学校办学层次")
    private String schoolLevel;
    /**
    * 备注
    */
    @ApiModelProperty("备注")
    private String remark;
    /**
     * 逻辑删除
     */
    @ApiModelProperty("逻辑删除")
    private int isDelete;

    /**
     * 城市id
     */
    @ApiModelProperty("城市id")
    private Long cityId;
    /**
     * 创建时间
     */
    @ApiModelProperty("创建时间")
    private Date createTime;
    /**
     * 更新时间
     */
    @ApiModelProperty("更新时间")
    private Date updateTime;

    @Override
    public String toString() {
        return "Schools{" +
                "id=" + id +
                ", schoolName='" + schoolName + '\'' +
                ", schoolCode=" + schoolCode +
                ", schoolDepartment='" + schoolDepartment + '\'' +
                ", schoolAddress='" + schoolAddress + '\'' +
                ", schoolLevel='" + schoolLevel + '\'' +
                ", remark='" + remark + '\'' +
                ", isDelete=" + isDelete +
                ", cityId=" + cityId +
                ", createTime=" + createTime +
                ", updateTime=" + updateTime +
                '}';
    }
}

3、准备Excel文件

这里用的是全国高校名单

4、编写测试类

package com.shier.service.impl;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.google.gson.Gson;
import com.shier.mapper.SchoolsMapper;
import com.shier.model.domain.Schools;
import com.shier.model.excel.ReadDemoData;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.File;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.Date;
import java.util.logging.Logger;

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class EasyexcelTest {

    private static final Logger log = Logger.getLogger(EasyexcelTest.class.getName());


    @Autowired
    private SchoolsMapper schoolsMapper;



    @Test
    public void simpleRead() {
        try {
            log.info("开始读取 Excel 文件");

            // 构建文件路径
            String fileName = getFilePath("school.xls");

            // 检查文件是否存在
            if (!Files.exists(Paths.get(fileName))) {
                log.severe("文件不存在: " + fileName);
                return;
            }

            // 读取 Excel 文件
            readExcelFile(fileName);
            log.info("读取 Excel 文件完成");
        } catch (Exception e) {
            log.severe("读取 Excel 文件时发生异常: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private String getFilePath(String fileName) {
        // 使用类加载器获取资源文件路径
        System.out.println("当前路径：" + this.getClass().getClassLoader().getResource(""));
        File file = new File(this.getClass().getClassLoader().getResource(fileName).getFile());
        return file.getAbsolutePath();
        //return this.getClass().getClassLoader().getResource("").getPath()  + fileName;
    }

    private void readExcelFile(String fileName) {
        EasyExcel.read(fileName, ReadDemoData.class, new PageReadListener<ReadDemoData>(dataList -> {
            for (ReadDemoData demoData : dataList) {
                //插入数据库
                Schools schools = new Schools();
                schools.setSchoolAddress(demoData.getSchoolAddress());
                schools.setSchoolCode(demoData.getSchoolCode());
                schools.setSchoolDepartment(demoData.getSchoolDepartment());
                schools.setRemark(demoData.getRemark());
                schools.setSchoolLevel(demoData.getSchoolLevel());
                schools.setIsDelete(0);
                schools.setSchoolName(demoData.getSchoolName());
                schools.setCreateTime(new Date());
                System.out.println(schools.toString());
                if (schools != null && schools.getSchoolCode() != null) {
                    try {
                        schoolsMapper.insert(schools);
                    } catch (Exception e) {
                        System.out.println("保存数据时发生错误: " + e.getMessage());
                        e.printStackTrace();
                    }
                }
                String json = new Gson().toJson(demoData);
                System.out.println("读取到一条数据: " + json);
            }
        })).sheet().doRead();
    }
}

5、读入并写入数据库完毕

6、总结

关于读入方式有很多种，这里用的是最简单的方式，不用写监听器，注意测试类要导入正确的测试方法注解。读入文件共有四种方式，你还知道怎么写吗（读Excel | Easy Excel 官网 (alibaba.com)），抄这种事，怎么能算偷呢。