easyexcel+springboot导入导出

其他链接：
easyExcel导入导出、样式填充
https://blog.csdn.net/qq_33745371/article/details/110790310

创建项目

技术栈：easyexcel+springboot+mybatis+mysql

代码下载

百度网盘

百度网盘下载 提取码：687f

天翼云盘

天翼云盘下载

准备工作

1.导入依赖 easyexcel,springbootWeb,lombok,mysql

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.6</version>
        </dependency>

2.创建数据库

3.application.yml

server:
  port: 8080
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://localhost:3306/easyexcel?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
    username: root
    password: 123456

# 配置日志
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: mapper/*.xml

4.创建实体类

Student

@Data
public class Student {

    /**
     * id
     */
    @ExcelProperty(value = "ID")
    @ExcelIgnore
    private Long id;
    /**
     * 姓名
     */
    @ExcelProperty("姓名")
    private String name;
    /**
     * 性别
     */
    @ExcelProperty("性别")
    private String gender;
    /**
     *年龄
     */
    @ExcelProperty("年龄")
    private Integer age;
    /**
     *特点
     */
    @ExcelProperty("特点")
    @ColumnWidth(40)//列宽
    private String specialty;

    /**
     * 生日
     */
    @ExcelProperty("生日")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")//日期格式
    private Date birthday;

}

导入功能

dao层以及xml

StudentDAO

@Mapper
public interface StudentDAO {

    int saveStudent(List<Student> list);
}

sqlmap_student.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qwl.easyexcel.dao.StudentDAO">


    <insert id="saveStudent">
        insert
        into
        student
        (
        id,
        `name`,
        age,
        specialty,
        gender,
        birthday
        )
        values
        <foreach collection="list" item="item" separator=",">
        (#{item.id},#{item.name},#{item.age},#{item.specialty},#{item.gender},#{item.birthday})
        </foreach>

    </insert>
</mapper>

service以及serviceImpl

先创建Result

public class Result<T> {
    /**
     * 状态
     */
    private boolean status;

    /**
     * 错误消息
     */
    private String message;

    /**
     * 返回值
     */
    private int code;

    /**
     * 结果对象
     */
    private T entry;

    /**
     * 记录数
     */
    private int totalCount;

    public static Result success() {
        Result Result = new Result();
        Result.setStatus(true);

        return Result;
    }

    public static Result success(Object entry) {
        Result Result = new Result();
        Result.setStatus(true);
        Result.setEntry(entry);
        Result.setMessage(entry + "");
        return Result;
    }
    public static Result fail(String message) {
        Result Result = new Result();
        Result.setStatus(false);
        Result.setMessage(message);
        Result.setEntry(message);

        return Result;
    }
    public static Result fail(Object message) {
        Result Result = new Result();
        Result.setStatus(false);
        Result.setMessage(message+"");
        Result.setEntry(message);

        return Result;
    }
    public static Result fail(int code, String msg) {
        Result Result = new Result();
        Result.setStatus(false);
        Result.setMessage(msg);
        Result.setCode(code);

        return Result;
    }

    public Result() {
        super();
    }

    public boolean isStatus() {
        return status;
    }

    public void setStatus(boolean status) {
        this.status = status;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public T getEntry() {
        return entry;
    }

    public void setEntry(T entry) {
        this.entry = entry;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }
}

StudentService

public interface StudentService {

    Result saveStudent(List<Student> list);
}

StudentServiceImpl

@Service
public class StudentServiceImpl implements StudentService {
    private static final Logger logger = LoggerFactory.getLogger(StudentServiceImpl.class);

    @Resource
    private StudentDAO studentDAO;

    @Override
    public Result saveStudent(List<Student> list) {
        logger.info("saveStudent List<Student>{}",list);
        try {
            studentDAO.saveStudent(list);
            return Result.success("新增成功");
        }catch (Throwable throwable){
            logger.error("saveStudent error="+throwable);
            return Result.fail("系统错误");
        }

    }
}

controller

StudentController

@RestController
public class StudentController {
    private static final Logger logger = LoggerFactory.getLogger(StudentController.class);

    @Autowired
    private StudentListener studentListener;

    @PostMapping("/student/readExcel")
    @ResponseBody
    public String readExcel(MultipartFile uploadExcel){
        logger.info("readExcel start");
        try {
            //工作簿
            ExcelReaderBuilder read = EasyExcel.read(uploadExcel.getInputStream(), Student.class, studentListener);
            //工作表
            ExcelReaderSheetBuilder sheet = read.sheet();
            //读
            sheet.doRead();
            return "success";
        } catch (IOException e) {
            e.printStackTrace();
            return "fail";
        }
    }
}

监听类Listener

StudentListener

@Component
@Scope("prototype")
public class StudentListener extends AnalysisEventListener<Student> {
    private static final Logger logger = LoggerFactory.getLogger(StudentListener.class);

    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;//我比较中意500条数据插入一次，这里只是为了测试

    List<Student> list = new ArrayList<Student>();

    @Autowired
    private StudentService studentService;
    /**
     * 每读一行内容，都会调用一次该对象的invoke,在invoke可以操作使用读取到的数据
     * @param student
     * @param analysisContext
     */
    @Override
    public void invoke(Student student, AnalysisContext analysisContext) {
        logger.info("invoke student{}",student);
        list.add(student);
        if(list.size()==BATCH_COUNT){
            studentService.saveStudent(list);
            list.clear();
        }
    }

    /**
     * 读取完整个文档之后，调用的方法
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        logger.info("doAfterAllAnalysed list="+list);
        if (list.size()!=0){
            studentService.saveStudent(list);
            list.clear();
        }
    }

}

使用postman访问

导出功能

dao层及xml

dao

List<Student> queryStudents();

xml

    <sql id="baseColumn">
        id,
        `name`,
        age,
        specialty,
        gender,
        birthday
    </sql>
    <select id="queryStudents" resultType="com.qwl.easyexcel.domain.Student">
        select
        <include refid="baseColumn"></include>
        from
        student
    </select>

service及serviceImpl

service

Result queryStudents();

serviceImpl

    @Override
    public Result queryStudents() {
        logger.info("saveStudent {}");
        try {
            List<Student> students = studentDAO.queryStudents();
            return Result.success(students);
        }catch (Throwable throwable){
            logger.error("saveStudent error="+throwable);
            return Result.fail("系统错误");
        }
    }

controller

controller

    @GetMapping("/student/writeExcel")
    @ResponseBody
    public void writeExcel(HttpServletResponse response){
        logger.info("writeExcel start");
        try {
            //固定的头信息
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //防止中文乱码
            String fileName = URLEncoder.encode("测试","UTF-8");
            response.setHeader("Content-Disposition","attachment;filename*=UTF-8''"+fileName+".xlsx");

            ServletOutputStream outputStream = response.getOutputStream();
            //工作簿
            ExcelWriterBuilder write = EasyExcel.write(outputStream, Student.class);
            //工作表
            ExcelWriterSheetBuilder sheet = write.sheet();
            //查询数据
            Result result = studentService.queryStudents();
            List<Student> list = (List)result.getEntry();
            //写
            sheet.doWrite(list);
        }catch (Throwable throwable){
            logger.error("writeExcel error="+throwable);
        }
    }

使用网页进行访问

easyExcel拓展

1.easyExcel导入导出、样式填充

https://blog.csdn.net/qq_33745371/article/details/110790310