easy poi实现不定列导出excel -map方式- 以及指定某些列是数字格式
为什么使用不定列转出
由于业务需求,有时会需要进行行转列之后导出,而因为大多数的行转列后的数据列数是不固定的,所以此时传统的使用实体类加注解的方式就无法解决该问题了。
使用map方式导出
首先引入:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>3.3.0</version>
</dependency>
导出方法:
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
public void downloadTest(HttpServletResponse response) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Random random = new Random();
//主表数据
List<Map<String,Object>> datas = new LinkedList<>();
for (int i = 0;i<10;i++){
Map<String,Object> mainMap = new HashMap<>();
mainMap.put("plan_no",i);
mainMap.put("name","chanpin"+i);
mainMap.put("erp_code","chanpinbianma"+i);
for (int t = 0;t<5;t++) {
Date date = new Date();
Calendar calendar = new GregorianCalendar();
calendar.setTime(date);
calendar.add(Calendar.DATE, t);
date = calendar.getTime();
mainMap.put(format.format(date), random.nextInt());
}
datas.add(mainMap);
}
Map<String,Object> data = datas.get(0);
List<String> dateList = new LinkedList<>();
for (Object key : data.keySet()) {
if (key.toString().contains("-") && key.toString().length() == 10) {
dateList.add(key.toString());
}
}
//导出文件名称
String fileName = "测试导出.xls";
//要导出的动态列
List<ExcelExportEntity> beanList = new ArrayList<ExcelExportEntity>();
//key 为标题 (列头) ,value为导出列的key
beanList.add(new ExcelExportEntity("计划编号", "plan_no"));
beanList.add(new ExcelExportEntity("产品编码", "erp_code"));
beanList.add(new ExcelExportEntity("产品名称", "name"));
for (String date : dateList) {
ExcelExportEntity entity = new ExcelExportEntity(date, date);
//设置列为数字格式
entity.setType(10);
beanList.add(entity);
}
//文件名
//要导出的列
//数据源
//response
try {
// easyPoi操作excel
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), beanList, datas);
// 设置响应头
response.setHeader("content-Type", "application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
// 写入到流中
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
workbook.close();
outputStream.close();
} catch (Exception e) {
// log.error(e.getMessage());
try {
response.setHeader("Content-type", "text/html;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
PrintWriter pw = response.getWriter();
pw.write("导出文件异常!");
} catch (Exception e1) {
// log.error(e1.getMessage());
}
}
}
代码解析【ExcelExportEntity,ExcelBaseEntity】
代码中使用的:ExcelExportEntity 是easyPoi提供的一个类
这个类可以提供对列的各种操作,一些基础的属性需要去看:ExcelBaseEntity这个基类
ExcelExportEntity是继承自一个基类:ExcelBaseEntity
在这个类中有type属性,这个属性就是设置列的类型的。这个属性由一个枚举类提供:BaseEntityTypeConstants
ExcelExportEntity,ExcelBaseEntity,BaseEntityTypeConstants的源代码:
ExcelExportEntity:
package cn.afterturn.easypoi.excel.entity.params;
import java.util.List;
/**
* excel 导出工具类,对cell类型做映射
*
* @author JueYue
* @version 1.0 2013年8月24日
*/
public class ExcelExportEntity extends ExcelBaseEntity implements Comparable<ExcelExportEntity> {
/**
* 如果是MAP导出,这个是map的key
*/
private Object key;
private double width = 10;
private double height = 10;
/**
* 图片的类型,1是文件,2是数据库
*/
private int exportImageType = 0;
/**
* 排序顺序
*/
private int orderNum = 0;
/**
* 是否支持换行
*/
private boolean isWrap;
/**
* 是否需要合并
*/
private boolean needMerge;
/**
* 单元格纵向合并
*/
private boolean mergeVertical;
/**
* 合并依赖`
*/
private int[] mergeRely;
/**
* 后缀
*/
private String suffix;
/**
* 统计
*/
private boolean isStatistics;
/**
* 数字格式化属性numFormat,使用了DecimalFormat对象
* numFormat传值参考DecimalFormat使用
*/
private String numFormat;
/**
* 是否隐藏列
*/
private boolean isColumnHidden;
/**
* 枚举导出属性字段
*/
private String enumExportField;
public boolean isColumnHidden() {
return isColumnHidden;
}
public void setColumnHidden(boolean columnHidden) {
isColumnHidden = columnHidden;
}
private List<ExcelExportEntity> list;
public ExcelExportEntity() {
}
public ExcelExportEntity(String name) {
super.name = name;
}
public ExcelExportEntity(String name, Object key) {
super.name = name;
this.key = key;
}
public ExcelExportEntity(String name, Object key, int width) {
super.name = name;
this.width = width;
this.key = key;
}
@Override
public int compareTo(ExcelExportEntity prev) {
return this.getOrderNum() - prev.getOrderNum();
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((key == null) ? 0 : key.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj) {
return true;
}
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
ExcelExportEntity other = (ExcelExportEntity) obj;
if (key == null) {
if (other.key != null) {
return false;
}
} else if (!key.equals(other.key)) {
return false;
}
return true;
}
}
ExcelBaseEntity:
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//
package cn.afterturn.easypoi.excel.entity.params;
import cn.afterturn.easypoi.excel.entity.vo.BaseEntityTypeConstants;
import java.lang.reflect.Method;
import java.util.List;
public class ExcelBaseEntity {
/**
* 对应name
*/
protected String name;
/**
* 对应groupName
*/
protected String groupName;
/**
* 对应type
*/
private int type = BaseEntityTypeConstants.STRING_TYPE;
/**
* 数据库格式
*/
private String databaseFormat;
/**
* 导出日期格式
*/
private String format;
/**
* 导出日期格式
*/
private String[] replace;
/**
* 字典名称
*/
private String dict;
/**
* set/get方法
*/
private Method method;
/**
* 这个是不是超链接,如果是需要实现接口返回对象
*/
private boolean hyperlink;
/**
* 固定的列
*/
private Integer fixedIndex;
private List<Method> methods;
public ExcelBaseEntity() {
this.type = BaseEntityTypeConstants.STRING_TYPE;
}
public String getDatabaseFormat() {
return this.databaseFormat;
}
public String getFormat() {
return this.format;
}
public Method getMethod() {
return this.method;
}
public List<Method> getMethods() {
return this.methods;
}
public String getName() {
return this.name;
}
public String[] getReplace() {
return this.replace;
}
public int getType() {
return this.type;
}
public void setDatabaseFormat(String databaseFormat) {
this.databaseFormat = databaseFormat;
}
public void setFormat(String format) {
this.format = format;
}
public void setMethod(Method method) {
this.method = method;
}
public void setMethods(List<Method> methods) {
this.methods = methods;
}
public void setName(String name) {
this.name = name;
}
public void setReplace(String[] replace) {
this.replace = replace;
}
public void setType(int type) {
this.type = type;
}
public boolean isHyperlink() {
return this.hyperlink;
}
public String getGroupName() {
return this.groupName;
}
public void setGroupName(String groupName) {
this.groupName = groupName;
}
public void setHyperlink(boolean hyperlink) {
this.hyperlink = hyperlink;
}
public int getFixedIndex() {
return this.fixedIndex;
}
public void setFixedIndex(int fixedIndex) {
this.fixedIndex = fixedIndex;
}
public String getDict() {
return this.dict;
}
public void setDict(String dict) {
this.dict = dict;
}
public void setFixedIndex(Integer fixedIndex) {
this.fixedIndex = fixedIndex;
}
}
BaseEntityTypeConstants:
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//
package cn.afterturn.easypoi.excel.entity.vo;
public interface BaseEntityTypeConstants {
Integer STRING_TYPE = 1;
Integer DOUBLE_TYPE = 10;
Integer IMAGE_TYPE = 3;
}