本文主要是介绍超实用easypoi简单模板导出Excel,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
概述
本文用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;//序号//商品所属类别展现的文字@Transientprivate String typeName;//格式化的日期@Transientprivate 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;}@Overridepublic 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中的所有 sheetTemplateExportParams 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(超级核心),错误经常都是在这儿抛出的
单个对象
list集合
小tpis:在实际开发中,我们一般不会去动实体类(该类与数据库表字段一一映射),而是建一个VO或DTO去继承该类,然后在里面进行类的扩展
激动人心的时刻
在浏览器中输入请求接口url:http://localhost:8080/export
一个对象效果
list效果
总结:
1.String、Integer、Byte类型的可以直接获取,Date类型的必须格式化
2.字典数据的必须代码处理转成相应的文字
3.简单的0 1 的可以用三目运算直接在表格中去转换成相应的文字
再次提醒!!!!!
1.千万别去合并单元格,除非是已知的内容(自己写的)
2.设置样式后,所设置的样式行数一定要大于集合的长度
99.99%出错的人都在这是去合并单元格了的
作者:程er狗
链接:https://www.jianshu.com/p/b9ec05479de3
来源:简书
简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。
这篇关于超实用easypoi简单模板导出Excel的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!