JAVA easyExcel 多sheet 导入导出Demo带源码

一、pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.example</groupId>
    <artifactId>easyExcel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>easyExcel</name>

    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>

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

        <!-- easypoi -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>

    </dependencies>


</project>

二、实体类

package com.example.easyexcel.domain;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class Student {

    private static final long serialVersionUID = 1L;


    @ExcelProperty(value = "学生姓名")
    private String studentName;

    @ExcelProperty(value = "科室号码")
    private Integer classNum;


}

package com.example.easyexcel.domain;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class User {

    private static final long serialVersionUID = 1L;


    @ExcelProperty(value = "用户名")
    private String name;

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


}

三、控制器

package com.example.easyexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.easyexcel.domain.Student;
import com.example.easyexcel.domain.User;
import com.example.easyexcel.listen.AnalysisEventListenerImpl;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.LinkedList;
import java.util.List;

/**
 * desciption 导入导出测试控制器
 *
 * @author cjw
 * @Date 2021/12/16 21:01
 */
@RestController
public class EasyController {


    /**
     * http://localhost:1001/export
     * desciption 多sheet导出
     *
     * @param response
     * @return
     * @Date 2021/12/16 20:53
     */
    @GetMapping("/export")
    public void export(HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("test", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();


            WriteSheet userSheet = EasyExcel.writerSheet(0, "用户信息").head(User.class).build();
            List<User> userList = new LinkedList<>();

            User user1=new User();
            user1.setName("张三");
            user1.setAge(13);

            User user2=new User();
            user2.setName("李四");
            user2.setAge(14);

            userList.add(user1);
            userList.add(user2);
            excelWriter.write(userList, userSheet);

            WriteSheet studentSheet = EasyExcel.writerSheet(1, "学生信息").head(Student.class).build();
            List<Student> studentList = new LinkedList<>();

            Student student=new Student();
            student.setStudentName("王同学");
            student.setClassNum(1);

            Student student2=new Student();
            student2.setStudentName("王同学");
            student2.setClassNum(2);

            studentList.add(student);
            studentList.add(student2);
            excelWriter.write(studentList, studentSheet);

            //关闭流
            excelWriter.finish();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }


    /**
     * desciption
     *
     * @param file
     * @return
     * @Date 2021/12/16 20:56
     */
    @PostMapping("/importData")
    public String importData(MultipartFile file) throws Exception {
        InputStream inputStream = file.getInputStream();
        ExcelReader excelReader = null;
        try {
            AnalysisEventListenerImpl listener = new AnalysisEventListenerImpl();

            excelReader = EasyExcel.read(inputStream, listener).build();

            ReadSheet readSheet1 = EasyExcel.readSheet(0).head(User.class).build();
            excelReader.read(readSheet1);
            List<Object> list1 = listener.getDatas();
            List<User> userList = (List<User>) (List) list1;
            for (User user : userList) {
                System.out.println(user);
            }
            listener.getDatas().clear();

            ReadSheet readSheet2 = EasyExcel.readSheet(1).head(Student.class).build();
            excelReader.read(readSheet2);
            List<Object> list2 = listener.getDatas();
            List<Student> studentList = (List<Student>) (List) list2;
            for (Student student : studentList) {
                System.out.println(student);
            }
            listener.getDatas().clear();

            return "导入成功";
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                throw new RuntimeException();
            }
            if (excelReader != null) {
                excelReader.finish();
            }
        }
    }


}

四、导入的listen实现类

package com.example.easyexcel.listen;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;


/**
 * desciption listen实现类
 * @author cjw
 * @return
 * @Date 2021/12/16 20:58
 */
public class AnalysisEventListenerImpl extends AnalysisEventListener {

    //可以通过实例获取该值
    private List<Object> datas = new ArrayList<Object>();

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        datas.add(o);
    }

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

五、测试

导出 http://localhost:1001/export

 导入

 源码：easyExcel: easyExcel 多sheet导入导出