如何使用SpringBoot实现excel文件的导入导出

如何使用SpringBoot实现excel文件的导入导出呢？

一. 导入和导出

导入：将文档中数据导入到内存中，后续可以添加到数据库

导出：将内存中的数据或数据库中查询的数据导出到文档中

注意：这里指的文档通常指的是基本的办公软件：word，excel，ppt。

二. EasyPOI导出数据

1. easypoi导入/导出excel其实就是domain对象属性和excel列的映射，而easypoi是通过注解的方式来做映射的，学习easypoi其实就是学会使用工具类和掌握它的常用注解.

2. 常见注解

   @Excel 作用到filed上面,是对Excel一列的一个描述
   @ExcelCollection 表示一个集合,主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示
   @ExcelEntity 表示一个继续深入导出的实体,但他没有太多的实际意义,只是告诉系统这个对象里面同样有导出的字段
   @ExcelIgnore 和名字一样表示这个字段被忽略跳过这个导导出
   @ExcelTarget 这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理
   

3. 导包

   <dependency>
       <groupId>cn.afterturn</groupId>
       <artifactId>easypoi-base</artifactId>
       <version>4.2.0</version>
   </dependency>
   <dependency>
       <groupId>cn.afterturn</groupId>
       <artifactId>easypoi-web</artifactId>
       <version>4.2.0</version>
   </dependency>
   <dependency>
       <groupId>cn.afterturn</groupId>
       <artifactId>easypoi-annotation</artifactId>
       <version>4.2.0</version>
   </dependency>
   

4. 添加工具类

   package io.coderyeah.basic.util;
   
   import cn.afterturn.easypoi.excel.ExcelExportUtil;
   import cn.afterturn.easypoi.excel.ExcelImportUtil;
   import cn.afterturn.easypoi.excel.entity.ExportParams;
   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.ss.usermodel.Workbook;
   import org.springframework.web.multipart.MultipartFile;
   
   import javax.servlet.http.HttpServletResponse;
   import java.io.File;
   import java.io.IOException;
   import java.net.URLEncoder;
   import java.util.List;
   import java.util.Map;
   import java.util.NoSuchElementException;
   
   /**
    * Excel导入导出工具类
    *
    * @author hm
    */
   public class ExcelUtils {
   
       /**
        * 导出工具类
        *
        * @param list
        * @param title
        * @param sheetName
        * @param pojoClass
        * @param fileName
        * @param isCreateHeader
        * @param response
        */
       public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
           ExportParams exportParams = new ExportParams(title, sheetName);
           exportParams.setCreateHeadRows(isCreateHeader);
           defaultExport(list, pojoClass, fileName, response, exportParams);
       }
   
       /**
        * 导出工具类
        *
        * @param list
        * @param title
        * @param sheetName
        * @param pojoClass
        * @param fileName
        * @param response
        */
       public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
           defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
       }
   
       public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
           defaultExport(list, fileName, response);
       }
   
       private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
                                         HttpServletResponse response, ExportParams exportParams) {
           Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
           if (workbook != null) ;
           downLoadExcel(fileName, response, workbook);
       }
   
       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 NormalException(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> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
           if (StringUtils.isBlank(filePath)) {
               return null;
           }
           ImportParams params = new ImportParams();
           params.setTitleRows(titleRows);
           params.setHeadRows(headerRows);
           List<T> list = null;
           try {
               list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
           } catch (NoSuchElementException e) {
               //throw new NormalException("模板不能为空");
           } catch (Exception e) {
               e.printStackTrace();
               //throw new NormalException(e.getMessage());
           }
           return list;
       }
   
       public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
           if (file == null) {
               return null;
           }
           ImportParams params = new ImportParams();
           params.setTitleRows(titleRows);
           params.setHeadRows(headerRows);
           List<T> list = null;
           try {
               list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
           } catch (NoSuchElementException e) {
               // throw new NormalException("excel文件不能为空");
           } catch (Exception e) {
               //throw new NormalException(e.getMessage());
               System.out.println(e.getMessage());
           }
           return list;
       }
   }
   

5. 文件导出

   • 前端请求方法

      // 导出excel文件
         exportExcel() {
           location.href = 'http://localhost:8080/shop/exportExcel';
         },
     

   • 后端接口

      @ApiOperation("导出excel文件")
         @GetMapping("/exportExcel")
         public void export(HttpServletResponse response) {
             try {
                 // 查询所有店铺信息
                 final List<Shop> list = shopService.list();
                 // List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response
                 ExcelUtils.exportExcel(list, "店铺列表", "店铺数据", Shop.class, "shop.xlsx", response);
             } catch (Exception e) {
                 e.printStackTrace();
             }
         }
     

6. 文件导入

   • 前端请求方法（文件表单项）

      <el-form-item>
               <!-- 默认name="file" -->
               <el-upload class="upload-demo"
                          action="http://localhost:8080/shop/importExcel"
                          list-type="text">
                 <el-button type="success">点击导入</el-button>
               </el-upload>
       </el-form-item>
     

   • 后端接口

      @ApiOperation("导入excel文件")
         @PostMapping("/importExcel")
         public void importExcel(@RequestPart("file") MultipartFile file) {
             final List<Shop> shops = ExcelUtils.importExcel(file, 1, 1, Shop.class);
             System.out.println("导入数据共" + shops.size() + "行");
             shops.forEach(System.out::println);
             // 通过批量添加数据到数据库
     //        shopService.saveBatch(shops, shops.size());
         }
     

7. 实体类加注解

   package io.coderyeah.org.domain;
   
   import cn.afterturn.easypoi.excel.annotation.Excel;
   import com.baomidou.mybatisplus.annotation.TableField;
   import com.fasterxml.jackson.annotation.JsonFormat;
   import lombok.Data;
   import lombok.EqualsAndHashCode;
   
   import java.io.Serializable;
   import java.util.Date;
   
   
   @EqualsAndHashCode(callSuper = true)
   @Data
   public class Shop extends BaseDomain implements Serializable {
       //店铺名称
       @Excel(name = "店铺名称", orderNum = "1", width = 30)
       private String name;
       //电话座机
       @Excel(name = "电话座机", orderNum = "2", width = 30)
       private String tel;
       //入驻时间
       @Excel(name = "入驻时间", orderNum = "3", width = 30, exportFormat = "yyyy-MM-dd")
       @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
       @TableField("registerTime")
       private Date registerTime = new Date();
       //店铺状态：待审核【1】 ，审核通过，待激活【2】，激活成功【3】，审核失败->驳回【4】
       @Excel(name = "店铺状态", orderNum = "4", width = 30)
       private Integer state = 1;
       //店铺地址
       @Excel(name = "店铺地址", orderNum = "5", width = 30)
       private String address;
       //店铺logo
       @Excel(name = "店铺logo", orderNum = "6", width = 30)
       private String logo;
       //店铺管理员ID
       private Long adminId;
       @TableField(exist = false)
       //关联对象 - 店铺管理员对象
       private Employee admin;
   }
   

三. 了解点

1. 办公软件2003与2007区别
word有：.doc[word2003及之前的版本]和.docx[word2007及以后的版本]
	docx版本更新
	docx相比doc更节省空间
	docx访问速度和兼容性更好
excel有：xls[excel2003及以前的版本]和xlsx[excel2007及以后的版本]
	xlsx格式是向下兼容的,可兼容xls格式
	xlsx格式存储内容更多
		xls最大只有65536行、256列
		xlsx可以有1048576行、16384列

2. domain/entity/pojo与dto/vo
	domain/entity/pojo[简单的Java对象]：一般用做数据库操作的对象
	dto[数据传输对象-data transfer object]/vo[值对象-value objet]：一般用来接收参数
		对象中的数据不一定和数据库字段进行一一对象
		其实query也算是一种特殊的dto对象