一. 导入和导出

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

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

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

二. EasyPOI导出数据

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

  2. 常见注解

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

  3. 导包

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    <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. 添加工具类

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    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. 文件导出

    • 前端请求方法

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

    • 后端接口

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      @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. 文件导入

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

      1
      2
      3
      4
      5
      6
      7
      8
      <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>

    • 后端接口

      1
      2
      3
      4
      5
      6
      7
      8
      9
       @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. 实体类加注解

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    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
2
3
4
5
6
7
8
9
10
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列
1
2
3
4
5
2. domain/entity/pojo与dto/vo
	domain/entity/pojo[简单的Java对象]：一般用做数据库操作的对象
	dto[数据传输对象-data transfer object]/vo[值对象-value objet]：一般用来接收参数
		对象中的数据不一定和数据库字段进行一一对象
		其实query也算是一种特殊的dto对象