springboot2.x Easy poi 导入导出

链接: api文档.

pom

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

application.properties

spring.main.allow-bean-definition-overriding=true

Excel 注解导出

@Data
public class ExStudent implements Serializable {
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年龄",orderNum = "1")
    private Integer age;
    @Excel(name = "性别",replace = { "男_0", "女_1" ,"_null"},orderNum = "2")
    private Integer sex;
    @Excel(name = "地址",orderNum = "3")
    private String addr;
    @Excel(name = "高数",groupName = "学科",numFormat = "#.#",orderNum = "4")
    private Double gs ;
    @Excel(name = "数据结构",groupName = "学科",orderNum = "5")
    private Double sjjg;
    @Excel(name = "创建时间",orderNum = "6",format = "yyyy-MM-dd")
    private Date createTime;
    @Excel(name = "备注",width = 50,orderNum = "7")
    private String nt;
}

@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) {
     List<ExStudent> list = new ArrayList<>();
     list.add(new ExStudent("tom",28,1,"南山",95.6d,96.66d,new Date(),"这是一条备注"));
     Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("这是title","sheet名称"), ExStudent.class, list);
     try {
         // 导出配置
         response.reset();
         response.setHeader("content-disposition",
                 "attachment;filename=" + new String(("导出测试".getBytes()), "ISO8859-1") + ".xlsx");
         response.setContentType("application/vnd.ms-excel");
         response.setCharacterEncoding("UTF-8");
         workbook.write(response.getOutputStream());
     } catch (IOException e) {
         e.printStackTrace();
     }
 }

导出：

Excel模板导出

模板 指令介绍

空格分割
三目运算 {{test ? obj:obj2}}
n: 表示 这个cell是数值类型 {{n:}}
le: 代表长度{{le:()}} 在if/else 运用{{le:() > 8 ? obj1 : obj2}}
fd: 格式化时间 {{fd:(obj;yyyy-MM-dd)}}
fn: 格式化数字 {{fn:(obj;###.00)}}
fe: 遍历数据,创建row （下方没有数据）
!fe: 遍历数据不创建row
$fe: 下移插入,把当前行,下面的行全部下移.size()行,然后插入 （下方有数据）
#fe: 横向遍历
v_fe: 横向遍历值
!if: 删除当前列 {{!if:(test)}}
单引号表示常量值 ‘’ 比如’1’ 那么输出的就是 1
&NULL& 空格
&INDEX& 表示循环中的序号,自动添加
]] 换行符 多行遍历导出
sum： 统计数据
cal: 基础的±X% 计算
dict: 字典
i18n: 国际化

@RequestMapping("/exportExcel")
public Map exportExcel(HttpServletResponse response) {
      Map<String, Object> map = new HashMap<>();
      //1、获取excel模板 TemplateExportParams("url",true)多sheet页导出
      TemplateExportParams exportParams = new TemplateExportParams("excel/Easypoi导出模板.xlsx");
      //2、获取数据 excelList mapList
      map.put("dataList", excelList);
      //3、执行Excel 导出
      Workbook wb = ExcelExportUtil.exportExcel(exportParams, map);
      //4、写入输出流
      try {
          // 导出配置
          response.reset();
          response.setHeader("content-disposition",
                  "attachment;filename=" + new String(("导出测试".getBytes()), "ISO8859-1") + ".xlsx");
          response.setContentType("application/vnd.ms-excel");
          response.setCharacterEncoding("UTF-8");
          wb.write(response.getOutputStream());
      } catch (Exception e) {
          e.printStackTrace();
      } finally {

      }
      System.out.println("导出完成......");
      return null;
  }