超实用easypoi简单模板导出Excel

2018-10-24 本文已影响207人程er狗

概述

本文用esaypoi3.1.0模板导出，简单适用，能运用于大多数业务场景。陈二狗和他的小伙儿伴儿陈雪峰都惊呆了，还不赶紧瞅瞅！

环境

最简单springBoot(只包含web)+MAVEN+IDEA

步骤

1.导入esaypoi3.1.0 依赖

    <!-- easypoi简单导出所需要的jar包 start -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.1.0</version>
        </dependency>
        <!-- easypoi简单导出所需要的jar包 end-->

2.@Transient(要用到这个这个注解，所以还要把jpa的启动器引入，引入jpa，就要配置数据库，所以还要把mysql的驱动jar包导入）

<!-- 使用@Transient这个注解需要的jar 或者Hibernate 的core包也行-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

3.yml配置（我的mysql是8.0的所以url上加了useSSL=false）

4.创建测试数据类

留心数据类型
1.Integer类型
2.String类型
3.字典数据：1 食品 2 服装 3 酒水 4 花卉，展示是显示文字
4.布尔（0 假 1真）判断显示文字：如 1 显示是 0 显示否
5.Date日期类型

package com.springboot.aop.entity;

import com.fasterxml.jackson.annotation.JsonFormat;

import javax.persistence.Transient;
import java.util.Date;

/**测试类
 * @create by 程二狗 on 2018/10/21 0021
 **/
public class Goods {

    @Transient//该注解表明只是作数据存储传输，没和表对应（表中没有该字段）
    //为了生成 1 2 3 ...序列号
    private Integer order;//序号
    //商品所属类别展现的文字
    @Transient
    private String typeName;
    //格式化的日期
    @Transient
    private String dateStr;

    //商品编号，主键（Integer类型的取值）
    private Integer no;
    //商品名称（String类型的取值）
    private String name;
    //1 食品 2 服装 3 酒水 4 花卉
    //商品所属类别（Integer类型的取值，对应的数值要转成相应的文字）
    private Integer type;
    //商品保质器（测试日期值得获取）
    private Date shelfLife;
    //库存是否还有?0 无 1有(测试Integer类型的三目运算)
    private Integer isHave;

    //该商品是否经过了审核"0" 未过，"1" 通过(测试String类型的三目运算)
    private String  isAudit;

    public Integer getOrder() {
        return order;
    }

    public void setOrder(Integer order) {
        this.order = order;
    }

    public String getTypeName() {
        return typeName;
    }

    public void setTypeName(String typeName) {
        this.typeName = typeName;
    }

    public Integer getNo() {
        return no;
    }

    public void setNo(Integer no) {
        this.no = no;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }

    public Date getShelfLife() {
        return shelfLife;
    }

    public void setShelfLife(Date shelfLife) {
        this.shelfLife = shelfLife;
    }

    public Integer getIsHave() {
        return isHave;
    }

    public void setIsHave(Integer isHave) {
        this.isHave = isHave;
    }

    public String getIsAudit() {
        return isAudit;
    }

    public void setIsAudit(String isAudit) {
        this.isAudit = isAudit;
    }

    public String getDateStr() {
        return dateStr;
    }

    public void setDateStr(String dateStr) {
        this.dateStr = dateStr;
    }

    public Goods(Integer no, String name, Integer type, Date shelfLife, Integer isHave, String isAudit) {
        this.no = no;
        this.name = name;
        this.type = type;
        this.shelfLife = shelfLife;
        this.isHave = isHave;
        this.isAudit = isAudit;
    }

    @Override
    public String toString() {
        return "Goods{" +
                "order=" + order +
                ", typeName='" + typeName + '\'' +
                ", dateStr='" + dateStr + '\'' +
                ", no=" + no +
                ", name='" + name + '\'' +
                ", isHave=" + isHave +
                ", isAudit='" + isAudit + '\'' +
                '}';
    }
}

4.导出Excel代码（核心）

package com.springboot.aop.easypoi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.springboot.aop.entity.Goods;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * esayPOI 简单模板导出测试
 *
 * @create by 程二狗 on 2018/10/21 0021
 **/

@RestController
public class EasyPOIController {

    @GetMapping("/export")
    public void export(HttpServletResponse response) {
        Goods goods1 = new Goods(110, "苹果", 1, new Date(), 0, "1");
        Goods goods2 = new Goods(111, "格子衫", 2, new Date(), 0, "0");
        Goods goods3 = new Goods(112, "拉菲红酒", 3, new Date(), 1, "1");
        Goods goods4 = new Goods(113, "玫瑰", 4, new Date(), 1, "0");

        List<Goods> goodsList = new ArrayList<>();
        goodsList.add(goods1);
        goodsList.add(goods2);
        goodsList.add(goods3);
        goodsList.add(goods4);

        //可以抽取为日期工具类
        Date date1 = new Date();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        String date = df.format(date1);

        for (int i = 0; i < goodsList.size(); ++i) {
            //添加序号列
            goodsList.get(i).setOrder(i + 1);
            //Date类型日期转换
            goodsList.get(i).setDateStr(df.format(goodsList.get(i).getShelfLife()));
            //type转换成显示文字
            if (goodsList.get(i).getType() == 1) {
                goodsList.get(i).setTypeName("食品");
            } else if (goodsList.get(i).getType() == 2) {
                goodsList.get(i).setTypeName("服装");
            } else if (goodsList.get(i).getType() == 3) {
                goodsList.get(i).setTypeName("酒水");
            } else if (goodsList.get(i).getType() == 4) {
                goodsList.get(i).setTypeName("花卉");
            }
        }

        for (Goods goods : goodsList) {
            System.out.println(goods);
        }
        // 获取导出excel指定模版，第二个参数true代表显示一个Excel中的所有 sheet
        TemplateExportParams params = new TemplateExportParams("/templates/商品详情表.xls", true);
        Map<String, Object> data = new HashMap<String, Object>();
        data.put("date", date);//导出一般都要日期
        data.put("one", goods1);//导出一个对象
        data.put("list", goodsList);//导出list集合

        try {
            // 简单模板导出方法
            Workbook book = ExcelExportUtil.exportExcel(params, data);
            //下载方法
            export(response, book, "商品信息");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * export导出请求头设置
     *
     * @param response
     * @param workbook
     * @param fileName
     * @throws Exception
     */
    private static void export(HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
        response.reset();
        response.setContentType("application/x-msdownload");
        fileName = fileName + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
        ServletOutputStream outStream = null;
        try {
            outStream = response.getOutputStream();
            workbook.write(outStream);
        } finally {
            outStream.close();
        }
    }
}

5.构建模板Excel（超级核心），错误经常都是在这儿抛出的