前言:
开发中经常会遇到excel的处理,导入导出解析等等,java中比较流行的用poi,但是每次都要写大段工具类来搞定这事儿,此处推荐一个别人造好的轮子【easypoi】,下面介绍下“轮子”的使用(以导入、导出Excel到实体类为例)。
详细用法,可参考EasyPoi参考文档:点击查看
首先是maven依赖:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
编写实体类
此处注意必须要有空构造函数,否则会报错“对象创建错误”
package com.wcc.easypoi.domain;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
/**
* @author wangcc
* @create 2020-03-05 19:06
**/
@Data
@NoArgsConstructor //此处注意必须要有空构造函数,否则会报错“对象创建错误”
@AllArgsConstructor
public class Goods {
//未添加@Excel注解,不解析
private String id;
//商品编号,主键(Integer类型的取值)
@Excel(name = "NO",width = 20)
private Integer no;
//商品名称(String类型的取值)
@Excel(name = "商品名称",width = 20)
private String name;
//1 食品 2 服装 3 酒水 4 花卉
//商品所属类别(Integer类型的取值,对应的数值要转成相应的文字)
@Excel(name = "商品类别",width = 20)
private Integer type;
//商品保质器(测试日期值得获取)
@Excel(name = "保质期",width = 20,exportFormat = "yyyy-MM-dd")
private Date shelfLife;
//库存是否还有?0 无 1有(测试Integer类型的三目运算)
@Excel(name = "库存是否还有",width = 20)
private Integer isHave;
//该商品是否经过了审核"0" 未过,"1" 通过(测试String类型的三目运算)
//未添加@Excel注解,不解析
private String isAudit;
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;
}
}
Controller层代码
package com.wcc.easypoi.controller;
import com.wcc.easypoi.domain.Goods;
import com.wcc.easypoi.util.FileUtil;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author wangcc
* @create 2020-03-05 19:07
**/
@RestController
public class EasyPOIController {
@RequestMapping("/exportExcel")
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<Goods>();
goodsList.add(goods1);
goodsList.add(goods2);
goodsList.add(goods3);
goodsList.add(goods4);
for (Goods goods : goodsList) {
System.out.println(goods);
}
//导出
FileUtil.exportExcel(goodsList,Goods.class,"商品.xls",response);
}
@RequestMapping("/importExcel")
public void importExcel(){
//本地文件 模拟文件上传解析
String filePath = "C:\\Users\\86178\\Desktop\\商品.xls";
//解析excel
List<Goods> goodsList = FileUtil.importExcel(filePath,0,1,Goods.class);
//也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
System.out.println("导入数据一共【"+goodsList.size()+"】行");
//TODO 保存数据库
for (Goods goods:goodsList) {
System.out.println(goods);
}
}
}
导入导出工具类
package com.wcc.easypoi.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.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* @author wangcc
* @create 2020-03-05 19:12
**/
public class FileUtil {
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);
}
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response) {
ExportParams exportParams = new ExportParams();
defaultExport(list, pojoClass, fileName, response, exportParams);
}
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) {
OutputStream outputStream=null;
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
outputStream = response.getOutputStream();
workbook.write(outputStream);
} catch (IOException e) {
throw new RuntimeException(e.getMessage());
}finally {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
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 RuntimeException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(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 RuntimeException("excel文件不能为空");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
return list;
}
}
启动类
package com.wcc.easypoi;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class EasypoiApplication {
public static void main(String[] args) {
SpringApplication.run(EasypoiApplication.class, args);
}
}