Springboot Excel 最简单的 多sheet表 导入导出_springboot表格导入导出

        <!--easypoi-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.0.0</version>
        </dependency>

server:
  port: 8697
 
spring:
  main:
    allow-bean-definition-overriding: true

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
 
import java.util.Map;
import java.util.NoSuchElementException;
 
public class ExcelUtil {
 
    protected static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    private static final String SPLIT = ".";
 
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }
 
 
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }
 
 
 
    public static <T> List<T> importExcelMore(MultipartFile file, Class<T> pojoClass,ImportParams params){
        if (file == null){
            return null;
        }
 
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }
 
    public static Workbook getWorkbook(MultipartFile file) {
        Workbook workbook=null;
        try {
            // 获取Excel后缀名
            String fileName = file.getOriginalFilename();
            if (StringUtils.isEmpty(fileName) || fileName.lastIndexOf(SPLIT) < 0) {
                logger.warn("解析Excel失败，因为获取到的Excel文件名非法！");
                return null;
            }
            String fileType = fileName.substring(fileName.lastIndexOf(SPLIT) + 1, fileName.length());
            // 获取Excel工作簿
            if (fileType.equalsIgnoreCase(XLS)) {
                workbook = new HSSFWorkbook(file.getInputStream());
            } else if (fileType.equalsIgnoreCase(XLSX)) {
                workbook = new XSSFWorkbook(file.getInputStream());
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }
 
 
 
 
}

import cn.afterturn.easypoi.excel.annotation.Excel;
 
public class User {
    @Excel(name = "学号", orderNum = "0")
    private Integer id;
    @Excel(name = "姓名", orderNum = "1")
    private String  userName;
    @Excel(name = "年龄", orderNum = "2")
    private String  userAge;
 
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", userAge='" + userAge + '\'' +
                '}';
    }
 
    public User() {
    }
 
    public User(Integer id, String userName, String userAge) {
        this.id = id;
        this.userName = userName;
        this.userAge = userAge;
    }
 
    public Integer getId() {
        return id;
    }
 
    public void setId(Integer id) {
        this.id = id;
    }
 
    public String getUserName() {
        return userName;
    }
 
    public void setUserName(String userName) {
        this.userName = userName;
    }
 
    public String getUserAge() {
        return userAge;
    }
 
    public void setUserAge(String userAge) {
        this.userAge = userAge;
    }
}

    @GetMapping("exportExcel")
    public void exportExcel(HttpServletResponse response) {
        List<User> userListOne = new ArrayList<>();
        User user1 = new User();
        user1.setId(1001);
        user1.setUserName("JCccc");
        user1.setUserAge("18");
        userListOne.add(user1);
 
 
        List<User> userListTwo = new ArrayList<>();
        User user2 = new User();
        user2.setId(2001);
        user2.setUserName("Mike");
        user2.setUserAge("18");
        userListTwo.add(user2);
 
 
        // 多个sheet配置参数
        final List<Map<String, Object>> sheetsList = Lists.newArrayList();
 
        final String sheetNameOne = "sheet1-1班";
        Map<String, Object> exportMapOne = Maps.newHashMap();
        final ExportParams exportParamsOne = new ExportParams(null, sheetNameOne, ExcelType.HSSF);
        // 以下3个参数为API中写死的参数名 分别是sheet配置/导出类（注解定义）/数据集
        exportMapOne.put("title", exportParamsOne);
        exportMapOne.put("entity", User.class);
        exportMapOne.put("data", userListOne);
 
        final String sheetNameTwo = "sheet2-2班";
        Map<String, Object> exportMapTwo = Maps.newHashMap();
        final ExportParams exportParamsTwo = new ExportParams(null, sheetNameTwo, ExcelType.HSSF);
        // 以下3个参数为API中写死的参数名 分别是sheet配置/导出类（注解定义）/数据集
        exportMapTwo.put("title", exportParamsTwo);
        exportMapTwo.put("entity", User.class);
        exportMapTwo.put("data", userListTwo);
 
        // 加入多sheet配置列表
        sheetsList.add(exportMapOne);
        sheetsList.add(exportMapTwo);
 
        //导出操作
        ExcelUtil.exportExcel(sheetsList, "userList.xls", response);
    }