easyExcel 多sheet 导入导出Demo带源码

easyExcel 多sheet 导入导出Demo带源码

一、pom文件

    4.0.0
    
        org.springframework.boot
        spring-boot-starter-parent
        2.6.1
         
    

    com.example
    easyExcel
    0.0.1-SNAPSHOT
    easyExcel

    Demo project for Spring Boot
    
        1.8
    
    

        
            org.springframework.boot
            spring-boot-starter-web
        

        
        
            cn.afterturn
            easypoi-base
            4.1.2
        
        
            cn.afterturn
            easypoi-annotation
            4.1.2
        
        
            cn.afterturn
            easypoi-web
            4.1.2
        
        
            org.projectlombok
            lombok
        
        
            com.alibaba
            easyexcel
            2.2.3
        

    

二、实体类

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;


@RestController
public class EasyController {


    
    @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 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 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();
        }
    }


    
    @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