文件上传/easypoi简单模板导出Excel

编程入门行业动态更新时间:2024-10-09 15:16:16

文件上传/easypoi简单模板导出Excel

工具类:Upload.java

package com.example.sl.layer.util;import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import org.apachemons.lang3.StringUtils;
import org.springframework.web.multipart.MultipartFile;import java.io.File;
import java.util.List;
import java.util.UUID;//上传
public class Upload {public  String executeUpload1(String uploadDir,MultipartFile file,String fileName) throws Exception{//文件后缀名String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));//上传文件名String filename = fileName + suffix;//服务器端保存的文件对象File serverFile = new File(uploadDir + filename);//将上传的文件写入到服务器端文件内file.transferTo(serverFile);return filename;}public  <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 (Exception e) {e.printStackTrace();}return list;}public  <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 (Exception e) {e.printStackTrace();}return list;}}

环境

最简单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><!--  --><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（超级核心），错误经常都是在这儿抛出的