EasyPOI是针对POI进一步的封装
pom.xml添加依赖:
<!--easypoi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.1</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.1</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.1</version>
</dependency>
EasyPoiUtils:
package com.shentb.hmb.utils;
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 com.shentb.hmb.bean.ExportLogVO;
import com.shentb.hmb.service.IExportLogService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
@Component
public class EasyPoiUtils {
@Autowired
IExportLogService exportLogService;
private static EasyPoiUtils EasyPoiUtils;
@PostConstruct
public void init() {
EasyPoiUtils = this;
}
public static void sellerExportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
/**
* 功能描述:复杂导出Excel,包括文件名以及表名。创建表头
*
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param isCreateHeader 是否创建表头
* @param fileName
* @param response
* @return
*/
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);
}
/**
* 功能描述:复杂导出Excel,包括文件名以及表名,不创建表头
*
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param fileName
* @param response
* @return
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response,Integer userMasterId) {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
//导出日志
ExportLogVO exportLogVO = new ExportLogVO();
exportLogVO.setUserMasterId(userMasterId);
exportLogVO.setCreateTime(new Date());
exportLogVO.setExportTypeName(title);
exportLogVO.setFileName(fileName);
EasyPoiUtils.exportLogService.insert(exportLogVO);
}
/**
* 功能描述:Map 集合导出
*
* @param list 实体集合
* @param fileName 导出的文件名称
* @param response
* @return
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
defaultExport(list, fileName, response);
}
/**
* 功能描述:默认导出方法
*
* @param list 导出的实体集合
* @param fileName 导出的文件名
* @param pojoClass pojo实体
* @param exportParams ExportParams封装实体
* @param response
* @return
*/
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);
}
}
/**
* 功能描述:Excel导出
*
* @param fileName 文件名称
* @param response
* @param workbook Excel对象
* @return
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" +URLEncoder.encode(fileName, "UTF-8") );
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* 功能描述:默认导出方法
*
* @param list 导出的实体集合
* @param fileName 导出的文件名
* @param response
* @return
*/
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);
}
/**
* 功能描述:根据文件路径来导入Excel
*
* @param filePath 文件路径
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel实体类
* @return
*/
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();
}
return list;
}
/**
* 功能描述:根据接收的Excel文件来导入Excel,并封装成实体类
*
* @param file 上传的文件
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel实体类
* @return
*/
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;
}
}
直接调用
try {
List<SellerVO> sellerVOList = sellerService.easyPoiExport(map);
String typeName = "全部商家";
String fileName = typeName + "-" + new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + ".xls";
EasyPoiUtils.exportExcel(sellerVOList, "全部商家", typeName, SellerVO.class, fileName, response, userMasterVO.getId());
} catch (Exception e) {
e.printStackTrace();
return StringUtil.getReturnMsg(false, "导出失败");
}
return StringUtil.getReturnMsg(true, "导出成功");
实体类需要标注导出的字段:
package com.shentb.hmb.bean;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import com.alibaba.fastjson.annotation.JSONField;
import java.sql.Timestamp;
import java.util.List;
public class SellerVO {
/**
* 商户ID
*/
// @ExcelIgnore
@Excel(name = "ID",height = 11, width = 15)
private int id;
/**
* 商户电话
*/
@Excel(name = "账号",height = 11, width = 15)
private String phoneNo;
/**
* 商户名称
*/
@Excel(name = "商户名称",height = 11, width = 15)
private String name;
/**
* 商户编码
*/
private String sellerCode;
/**
* 微信号
*/
private String weixin;
/**
* 商户头像
*/
private String headPic;
/**
* 商户头像新的存储路径(fastdfs)
*/
private String newHeadPic;
/**
* 商户token
*/
private String token;
/**
* 商户创建时间
*/
@Excel(name = "开户时间",height = 11, width = 15)
private Timestamp createTime;
/**
* token时间
*/
private Timestamp tokenTime;
/**
* 记录更新时间
*/
private Timestamp updateTime;
/**
* 外部系统类型
*/
private String source;
/**
* 商户密码
*/
private String password;
/**
* 父账号ID
*/
private int parentID;
/**
* 头像rul
*/
private String headPicUrl;
/**
* 商户状态 0 :冻结 1 :可用
*/
@Excel(name = "状态",replace = {"冻结_0", "已激活_1"},height = 11, width = 15)
private String active;
/**
* 创建者ID
*/
private int creatorId;
/**
* 创建者code
*/
private String userCode;
/**
* 创建者昵称
*/
@Excel(name = "业务员",height = 11, width = 15)
private String userName;
/**
* 当前余额
*/
@Excel(name = "当前余额",height = 11, width = 15)
private int balance;
/**
* 累积充值金额
*/
private int recharge;
/**
* 商户权限
*/
private List<SellerRoleVO> sellerRoleVOS;
/**
* 商家昵称
*/
private String nickName;
/**
* 商家渠道类别
* A:自己开发的商家
* B:第三方商家介绍查询
* C:
*/
@Excel(name = "类型",height = 11, width = 15)
private String channelType;
/**
* 商户QQ号
*/
private String qq;
/**
* 财务身份证
*/
private String idCard;
/**
* 财务姓名
*/
private String realName;
/**
* 财务实名认证时的银行卡预留手机号
*/
private String bankPhone;
/**
* 财务银行卡号
*/
private String bankCard;
/**
* 邮箱
*/
private String mail;
/**
* 签约时财务需要提供的手写签名
*/
private String manualSign;
/**
* 手签新的存储地址
*/
private String newManualSign;
/**
* 消费总金额
*/
private int consumption;
/**
* 累计赠送金额
*/
private int donation;
/**
* 财务是否CFCA认证
*/
private String isCFCA;
/**
* 冻结时间
*/
private Timestamp frozenTime;
/**
* 是否有系统费 是否有系统费 1有 2没有 ==>SysCost.java
*/
private Integer sysCost;
/**
* 系统有效期 当sysCost为1时 存在系统有效期;
*/
private Timestamp sysCostTime;
/**
* 剩余本金金额
*/
private long surplusRecharge;
/**
* 剩余赠送金额
*/
private long surplusDonation;
public String getNewHeadPic() {
return newHeadPic;
}
public void setNewHeadPic(String newHeadPic) {
this.newHeadPic = newHeadPic;
}
public String getNewManualSign() {
return newManualSign;
}
public void setNewManualSign(String newManualSign) {
this.newManualSign = newManualSign;
}
public String getIsCFCA() {
return isCFCA;
}
public void setIsCFCA(String isCFCA) {
this.isCFCA = isCFCA;
}
public String getQq() {
return qq;
}
public void setQq(String qq) {
this.qq = qq;
}
public String getIdCard() {
return idCard;
}
public void setIdCard(String idCard) {
this.idCard = idCard;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public String getBankPhone() {
return bankPhone;
}
public void setBankPhone(String bankPhone) {
this.bankPhone = bankPhone;
}
public String getBankCard() {
return bankCard;
}
public void setBankCard(String bankCard) {
this.bankCard = bankCard;
}
public String getMail() {
return mail;
}
public void setMail(String mail) {
this.mail = mail;
}
public String getManualSign() {
return manualSign;
}
public void setManualSign(String manualSign) {
this.manualSign = manualSign;
}
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
public String getChannelType() {
return channelType;
}
public void setChannelType(String channelType) {
this.channelType = channelType;
}
@JSONField(name = "roles")
public List<SellerRoleVO> getSellerRoleVOS() {
return sellerRoleVOS;
}
public void setSellerRoleVOS(List<SellerRoleVO> sellerRoleVOS) {
this.sellerRoleVOS = sellerRoleVOS;
}
public String getSellerCode() {
return sellerCode;
}
public void setSellerCode(String sellerCode) {
this.sellerCode = sellerCode;
}
public String getWeixin() {
return weixin;
}
public void setWeixin(String weixin) {
this.weixin = weixin;
}
public String getActive() {
return active;
}
public void setActive(String active) {
this.active = active;
}
public int getCreatorId() {
return creatorId;
}
public void setCreatorId(int creatorId) {
this.creatorId = creatorId;
}
public String getSource() {
return source;
}
public void setSource(String source) {
this.source = source;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getPhoneNo() {
return phoneNo;
}
public void setPhoneNo(String phoneNo) {
this.phoneNo = phoneNo;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getHeadPic() {
return headPic;
}
public void setHeadPic(String headPic) {
this.headPic = headPic;
}
public String getToken() {
return token;
}
public void setToken(String token) {
this.token = token;
}
public Timestamp getCreateTime() {
return createTime;
}
public void setCreateTime(Timestamp createTime) {
this.createTime = createTime;
}
public Timestamp getTokenTime() {
return tokenTime;
}
public void setTokenTime(Timestamp tokenTime) {
this.tokenTime = tokenTime;
}
public Timestamp getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Timestamp updateTime) {
this.updateTime = updateTime;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getParentID() {
return parentID;
}
public void setParentID(int parentID) {
this.parentID = parentID;
}
public String getHeadPicUrl() {
return headPicUrl;
}
public void setHeadPicUrl(String headPicUrl) {
this.headPicUrl = headPicUrl;
}
public String getUserCode() {
return userCode;
}
public void setUserCode(String userCode) {
this.userCode = userCode;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public int getBalance() {
return balance;
}
public void setBalance(int balance) {
this.balance = balance;
}
public int getRecharge() {
return recharge;
}
public void setRecharge(int recharge) {
this.recharge = recharge;
}
public int getConsumption() {
return consumption;
}
public void setConsumption(int consumption) {
this.consumption = consumption;
}
public int getDonation() {
return donation;
}
public void setDonation(int donation) {
this.donation = donation;
}
public Timestamp getFrozenTime() {
return frozenTime;
}
public void setFrozenTime(Timestamp frozenTime) {
this.frozenTime = frozenTime;
}
public Integer getSysCost() {
return sysCost;
}
public void setSysCost(Integer sysCost) {
this.sysCost = sysCost;
}
public Timestamp getSysCostTime() {
return sysCostTime;
}
public void setSysCostTime(Timestamp sysCostTime) {
this.sysCostTime = sysCostTime;
}
public long getSurplusRecharge() {
return surplusRecharge;
}
public void setSurplusRecharge(long surplusRecharge) {
this.surplusRecharge = surplusRecharge;
}
public long getSurplusDonation() {
return surplusDonation;
}
public void setSurplusDonation(long surplusDonation) {
this.surplusDonation = surplusDonation;
}
@Excel(name = "权限",height = 11, width = 15)
private String permission="主商户";
public String getPermission() {
return permission;
}
public void setPermission(String permission) {
this.permission = permission;
}
}
结果: