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

爱被打了一巴掌 2023-10-13 19:00 16阅读 0赞

# 前言 #

上周学习群里有人问到，多个sheet的导出导入，我第一反应就是easypoi不是自己就有方法了么？

后面一想，可能有些看客还处于是 找工具类，然后调试 的写代码 的 阶段，可能还不会去看jar包的一些函数。

既然如此，那就写一写Excel 多sheet表 导入导出。

# 正文 #

之前的一些excel的相关的文章，感兴趣的看客，也可以看下。

最简单的入门篇：  
[Springboot 最简单的结合MYSQL数据实现EXCEL表格导出及数据导入\_小目标青年的博客-CSDN博客][Springboot _MYSQL_EXCEL_-CSDN]

还写过一篇单个，多个 excel文件导出，转成ZIP包的：

[SpringBoot 导出多个Excel文件，压缩成.zip格式下载\_小目标青年的博客-CSDN博客][SpringBoot _Excel_.zip_-CSDN]

还有指定模板导出的：  
[Springboot 指定自定义模板导出Excel文件\_小目标青年的博客-CSDN博客\_自定义导出excel][Springboot _Excel_-CSDN_excel]

还有这种复合表格的：  
[Springboot 导入导出Excel ，一对多关系，复合表格、合并单元格数据\_springboot导出excel合并单元格\_小目标青年的博客-CSDN博客][Springboot _Excel _springboot_excel_-CSDN]

还有这种动态导出的：

[Springboot 我随手封装了一个万能的导出excel工具，传什么都能导出\_小目标青年的博客-CSDN博客][Springboot _excel_-CSDN]

好了，回归本篇，开搞。

惯例，先看下我们今天要实现 excel多sheet表的导入导出，整个实战下来，需要做多少东西：

基本等于0 ，是的，没啥好写的。

![af43779d2f4d442a8e73ee46b0d77a4b.png][]

1.pom.xml 引入依赖 ：

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

2.yml 配置：

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

3.工具类 ExcelUtil.java (只保留了该篇用到的函数，精简)

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;
        }
    
    
    
    
    }

4.导出导入用到的 数据类 User.java

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;
        }
    }

5.写个测试的controller 试一下 ：

就2个接口，1个导入，1个导出， 都是 多sheet的（其实看懂代码就知道，多sheet是包含单sheet的）

![bc6c059aeadf4dd09609f808ea5061de.png][]

先是导出接口 ：

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

代码简析：  
  
![e00d6d77b55c41da8a6792e020701f19.png][]

调用一下接口看看效果：  
  
![e90f9d278cf64f05b5f945633302d2f8.png][]

打开下载的excel文件，可以看到多个sheet表数据如愿出现：

![736ed6bccc0745f793dbb1a9b01dcd99.png][]

![906d423eba704938b7acba3103937cc6.png][]

再看下导入接口 ：

@PostMapping("importExcel")
        public void importExcel(@RequestParam("file") MultipartFile multipartFile) {
            try {
                //标题占几行
                Integer titleRows = 0;
                //表头占几行
                Integer headerRows = 1;
                Workbook workBook = ExcelUtil.getWorkbook(multipartFile);
    
                //获取sheet数量
                int sheetNum = workBook.getNumberOfSheets();
                ImportParams params = new ImportParams();
                //表头在第几行
                params.setTitleRows(titleRows);
                params.setHeadRows(headerRows);
                for (int numSheet = 0; numSheet < sheetNum; numSheet++) {
                    String sheetName = workBook.getSheetAt(numSheet).getSheetName();
                    //第几个sheet页
                    params.setStartSheetIndex(numSheet);
                    List<User> result = ExcelUtil.importExcelMore(multipartFile, User.class, params);
                    System.out.println("sheetNum=" + numSheet + "   sheetName=" + sheetName);
                    System.out.println("导入的数据=" + result.toString());
    
                }
            } catch (Exception e) {
                e.printStackTrace();
    
            }
        }

代码简析：  
![d84397a8349a460e9422e943e773da17.png][]

导入的数据样例：  
![3b53c4def5b7417680b52168ebc062a1.png][]

调用下接口玩下：  
  
![634036027d694eb4881c03fb860b98c5.png][]

可以看到数据都解析拿到了：  
![68703ecd4390488abaaf5f6fab85af6d.png][]

好了，就到这吧。复制粘贴就能用。

[Springboot _MYSQL_EXCEL_-CSDN]: https://blog.csdn.net/qq_35387940/article/details/88967572
[SpringBoot _Excel_.zip_-CSDN]: https://blog.csdn.net/qq_35387940/article/details/125790362
[Springboot _Excel_-CSDN_excel]: https://blog.csdn.net/qq_35387940/article/details/125804467
[Springboot _Excel _springboot_excel_-CSDN]: https://blog.csdn.net/qq_35387940/article/details/125953667
[Springboot _excel_-CSDN]: https://blog.csdn.net/qq_35387940/article/details/129062470
[af43779d2f4d442a8e73ee46b0d77a4b.png]: https://img-blog.csdnimg.cn/af43779d2f4d442a8e73ee46b0d77a4b.png
[bc6c059aeadf4dd09609f808ea5061de.png]: https://img-blog.csdnimg.cn/bc6c059aeadf4dd09609f808ea5061de.png
[e00d6d77b55c41da8a6792e020701f19.png]: https://img-blog.csdnimg.cn/e00d6d77b55c41da8a6792e020701f19.png
[e90f9d278cf64f05b5f945633302d2f8.png]: https://img-blog.csdnimg.cn/e90f9d278cf64f05b5f945633302d2f8.png
[736ed6bccc0745f793dbb1a9b01dcd99.png]: https://img-blog.csdnimg.cn/736ed6bccc0745f793dbb1a9b01dcd99.png
[906d423eba704938b7acba3103937cc6.png]: https://img-blog.csdnimg.cn/906d423eba704938b7acba3103937cc6.png
[d84397a8349a460e9422e943e773da17.png]: https://img-blog.csdnimg.cn/d84397a8349a460e9422e943e773da17.png
[3b53c4def5b7417680b52168ebc062a1.png]: https://img-blog.csdnimg.cn/3b53c4def5b7417680b52168ebc062a1.png
[634036027d694eb4881c03fb860b98c5.png]: https://img-blog.csdnimg.cn/634036027d694eb4881c03fb860b98c5.png
[68703ecd4390488abaaf5f6fab85af6d.png]: https://img-blog.csdnimg.cn/68703ecd4390488abaaf5f6fab85af6d.png