首先在导出excel 是通过poi 实现的,但是这里我采用的是阿里巴巴提供的 easyExcel 实现的多表头导出,当然还需要数据列对应类的属性,用属性获取数据并实现数据导出。
第一步:首先需要导入maven 坐标:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
导入这个就可以了,但是为了防止有poi 版本问题,这里需要引入poi的实现版本,因为在对excel 的时候总是有类找不到,这里我使用poi 版本为3.17,3.17对之前的3.15是有版本升级的,所以有些类会出现找不到:
<!-- 2003 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- 2007 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
这里做好兼容。
第二步,代码实现:
我这里进行了封装,直接上代码
package gcloud.collect.util.excel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import gcloud.collect.entity.*;
import gcloud.collect.protobuf.*;
import gcloud.collect.util.excel.header.*;
import gcloud.core.util.FilePathUtil;
import org.apache.commons.logging.LogFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class ExportExcel {
private static final Logger log = LoggerFactory.getLogger(ExportExcel.class);
private ExportExcel() {
}
// 文件地址
private static String outFileSrc(String fileName) {
try {
String fileSrc = FilePathUtil.createNewTempFile(fileName+".xls","excel");
return FilePathUtil.realPath(fileSrc);
} catch (Exception e) {
LogFactory.getLog(ExportExcel.class).error("",e);
}
return null;
}
// 对象映射
private static List<List<Object>> getDataList(List<?> list, List<String> fieldList) {
List<String> headList = fieldList.stream().filter(string -> !string.trim().isEmpty()).collect(Collectors.toList());
return list.stream().map(e -> {
Class<?> cls = e.getClass().getSuperclass();
List<Object> valueList = new ArrayList<>();
for (String name : headList) {
try {
Field field = cls.getDeclaredField(name);
field.setAccessible(true);
String text = null;
if(field.get(e)!=null) {
text = field.get(e).toString();
}
valueList.add(text);
} catch (NoSuchFieldException | IllegalAccessException noSuchFieldException) {
noSuchFieldException.printStackTrace();
}
}
return valueList;
}).collect(Collectors.toList());
}
/**
* 导出数据实现
* @param excelHeader 导出数据接口
* @return
*/
public static String exportExcel(ExportExcelHeader excelHeader) {
// 文件名字
String fileName = excelHeader.getFileName();
// 指定文件地址
String filePath = outFileSrc(fileName);
// 导出数据对应类中的字段名
List<String> fieldList = excelHeader.getFieldList();
// 头数据
List<List<String>> head = new ArrayList<List<String>>();
for (int i = 0; i < fieldList.size(); i++) {
List<String> headText = excelHeader.getHeadText(fieldList.get(i));
if(head!=null){
head.add(headText);
}
}
List<?> list = excelHeader.getData();
// 内容数据
List<List<Object>> data = getDataList(list, fieldList);
EasyExcel.write(filePath).head(head)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet(fileName).doWrite(data);
return filePath;
}
}
既然在这里要传入导出数据接口就进行写一个接口:
package gcloud.collect.util.excel.header;
import java.util.List;
public interface ExportExcelHeader {
/**
* code 对应枚举类中的code,返回枚举类中的name(头名称)
* @param code
* @return
*/
public List<String> getHeadText(String code);
/**
* 文件名字
* @return
*/
public String getFileName();
/**
* 导出数据对应类中的字段名
* @return
*/
public List<String> getFieldList();
/**
* 导出的数据
* @return
*/
public List<?> getData();
}
第三步进行实现接口执行导出:
List<FrTreasuryfSituationEntity> finalList = list;
ExportExcelHeader exportExcelHeader = new ExportExcelHeader() {
@Override
public List<String> getHeadText(String code) {
FrTreasuryfSituationHeaderEnum[] values = FrTreasuryfSituationHeaderEnum.values();
for (FrTreasuryfSituationHeaderEnum value:values) {
if(code.equals(value.getCode())){
return value.getText();
}
}
return null;
}
@Override
public String getFileName() {
return request.getFileName();
}
@Override
public List<String> getFieldList() {
return request.getFieldList();
}
@Override
public List<?> getData() {
return finalList;
}
};
String distFile = ExportExcel.exportExcel(exportExcelHeader);
GcloudFileUtil.download(responseObserver,distFile);
前提需要准备实体类和枚举类:
实体类:
@Entity
@Table(name = "fr_treasuryf_situation")
public class FrTreasuryfSituationEntity extends FrTreasuryfSituationModel {
/**
* 序列化Id
*/
private static final long serialVersionUID = 1L;
/**
* 默认构造函数
*/
public FrTreasuryfSituationEntity()
{
super();
}
// TODO 可以生成其他构造函数,包括非自动生成的其他项
}
@MappedSuperclass
public abstract class FrTreasuryfSituationModel implements Serializable {
/**
* 版本号
*/
private static final long serialVersionUID = 1L;
/**ID*/
private UUID treasuryId= UUID.randomUUID() ;
/**财政年度*/
private String year ;
/**月份*/
private String month ;
/**财政区划代码*/
private String mofDivCode ;
/**财政区划名称*/
private String mofDivName ;
/**时间*/
private String debtTime ;
/**月初库款余额*/
private BigDecimal begMonthBal ;
/**当月支出*/
private BigDecimal monthPay ;
/**当月收入*/
private BigDecimal monthIncome ;
/**月末库款余额*/
private BigDecimal endMonthBalance ;
/**本月库款保障水平*/
private BigDecimal securityLevel ;
/**创建时间*/
private Date createTime ;
/**修改时间*/
private Date updateTime ;
/**创建人*/
private String createUser ;
/**创建人ID*/
private UUID createUserId ;
/**修改人*/
private String updateUser ;
/**修改人ID*/
private UUID updateUserId ;
/**
* ID(TreasuryId)
* @return treasuryId
*/
@Id
@Column(name="treasury_id",nullable = false,columnDefinition = "BINARY(16)")
public UUID getTreasuryId() {
return treasuryId;
}
/**
* ID(TreasuryId)
* @param treasuryId
*/
public void setTreasuryId(UUID treasuryId) {
this.treasuryId = treasuryId;
}
/**
* 财政年度(Year)
* @return year
*/
@Column(name="year_")
public String getYear() {
return year;
}
/**
* 财政年度(Year)
* @param year
*/
public void setYear(String year) {
this.year = year;
}
/**
* 月份(Month)
* @return month
*/
@Column(name="month_")
public String getMonth() {
return month;
}
/**
* 月份(Month)
* @param month
*/
public void setMonth(String month) {
this.month = month;
}
/**
* 财政区划代码(MofDivCode)
* @return mofDivCode
*/
@Column(name="mof_div_code")
public String getMofDivCode() {
return mofDivCode;
}
/**
* 财政区划代码(MofDivCode)
* @param mofDivCode
*/
public void setMofDivCode(String mofDivCode) {
this.mofDivCode = mofDivCode;
}
/**
* 财政区划名称(MofDivName)
* @return mofDivName
*/
@Column(name="mof_div_name")
public String getMofDivName() {
return mofDivName;
}
/**
* 财政区划名称(MofDivName)
* @param mofDivName
*/
public void setMofDivName(String mofDivName) {
this.mofDivName = mofDivName;
}
/**
* 时间(DebtTime)
* @return debtTime
*/
@Column(name="debt_time")
public String getDebtTime() {
return debtTime;
}
/**
* 时间(DebtTime)
* @param debtTime
*/
public void setDebtTime(String debtTime) {
this.debtTime = debtTime;
}
/**
* 月初库款余额(BegMonthBal)
* @return begMonthBal
*/
@Column(name="beg_month_bal")
public BigDecimal getBegMonthBal() {
return begMonthBal;
}
/**
* 月初库款余额(BegMonthBal)
* @param begMonthBal
*/
public void setBegMonthBal(BigDecimal begMonthBal) {
this.begMonthBal = begMonthBal;
}
/**
* 当月支出(MonthPay)
* @return monthPay
*/
@Column(name="month_pay")
public BigDecimal getMonthPay() {
return monthPay;
}
/**
* 当月支出(MonthPay)
* @param monthPay
*/
public void setMonthPay(BigDecimal monthPay) {
this.monthPay = monthPay;
}
/**
* 当月收入(MonthIncome)
* @return monthIncome
*/
@Column(name="month_income")
public BigDecimal getMonthIncome() {
return monthIncome;
}
/**
* 当月收入(MonthIncome)
* @param monthIncome
*/
public void setMonthIncome(BigDecimal monthIncome) {
this.monthIncome = monthIncome;
}
/**
* 月末库款余额(EndMonthBalance)
* @return endMonthBalance
*/
@Column(name="end_month_balance")
public BigDecimal getEndMonthBalance() {
return endMonthBalance;
}
/**
* 月末库款余额(EndMonthBalance)
* @param endMonthBalance
*/
public void setEndMonthBalance(BigDecimal endMonthBalance) {
this.endMonthBalance = endMonthBalance;
}
/**
* 本月库款保障水平(SecurityLevel)
* @return securityLevel
*/
@Column(name="security_level")
public BigDecimal getSecurityLevel() {
return securityLevel;
}
/**
* 本月库款保障水平(SecurityLevel)
* @param securityLevel
*/
public void setSecurityLevel(BigDecimal securityLevel) {
this.securityLevel = securityLevel;
}
/**
* 创建时间(CreateTime)
* @return createTime
*/
@Column(name="create_time")
@Temporal(TemporalType.TIMESTAMP)
public Date getCreateTime() {
return createTime==null?null:(Date)createTime.clone();
}
/**
* 创建时间(CreateTime)
* @param createTime
*/
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
/**
* 修改时间(UpdateTime)
* @return updateTime
*/
@Column(name="update_time")
@Temporal(TemporalType.TIMESTAMP)
public Date getUpdateTime() {
return updateTime==null?null:(Date)updateTime.clone();
}
/**
* 修改时间(UpdateTime)
* @param updateTime
*/
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
/**
* 创建人(CreateUser)
* @return createUser
*/
@Column(name="create_user")
public String getCreateUser() {
return createUser;
}
/**
* 创建人(CreateUser)
* @param createUser
*/
public void setCreateUser(String createUser) {
this.createUser = createUser;
}
/**
* 创建人ID(CreateUserId)
* @return createUserId
*/
@Column(name="create_user_id",columnDefinition = "BINARY(16)")
public UUID getCreateUserId() {
return createUserId;
}
/**
* 创建人ID(CreateUserId)
* @param createUserId
*/
public void setCreateUserId(UUID createUserId) {
this.createUserId = createUserId;
}
/**
* 修改人(UpdateUser)
* @return updateUser
*/
@Column(name="update_user")
public String getUpdateUser() {
return updateUser;
}
/**
* 修改人(UpdateUser)
* @param updateUser
*/
public void setUpdateUser(String updateUser) {
this.updateUser = updateUser;
}
/**
* 修改人ID(UpdateUserId)
* @return updateUserId
*/
@Column(name="update_user_id",columnDefinition = "BINARY(16)")
public UUID getUpdateUserId() {
return updateUserId;
}
/**
* 修改人ID(UpdateUserId)
* @param updateUserId
*/
public void setUpdateUserId(UUID updateUserId) {
this.updateUserId = updateUserId;
}
public FrTreasuryfSituationModel() {
super();
}
@Transient
@XmlTransient
public UUID getFrTreasuryfSituationPK()
{
return this.treasuryId;
}
public void setFrTreasuryfSituationPK(UUID pk)
{
this.treasuryId=pk;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((treasuryId == null) ? 0 : treasuryId.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;
}
final FrTreasuryfSituationModel other = (FrTreasuryfSituationModel) obj;
if (treasuryId == null) {
if (other.treasuryId != null){
return false;
}
} else if (!treasuryId.equals(other.treasuryId)){
return false;
}
return true;
}
}
枚举类需要对应实体类的属性:
public enum FrTreasuryfSituationHeaderEnum {
DEBT_TIME("debtTime","库款情况数据表","时间"),
MOF_DIV_CODE("mofDivCode","库款情况数据表","财政区划"),
BEG_MONTH_BAL("begMonthBal","库款情况数据表","月初库款余额"),
MONTH_PAY("monthPay","库款情况数据表","支出执行数"),
MONTH_INCOME("monthIncome","库款情况数据表","收入执行数"),
END_MONTH_BALANCE("endMonthBalance","库款情况数据表","月末库款余额"),
SECURITY_LEVEL("securityLevel","库款情况数据表","本月库款保障水平"),
UPDATE_TIME("updateTime","库款情况数据表","修改时间");
FrTreasuryfSituationHeaderEnum(String code, String... text) {
this.code = code;
this.text = new ArrayList<>();
for (String s:text) {
this.text.add(s);
}
}
private String code;
private List<String> text;
public String getCode() {
return code;
}
public List<String> getText() {
return text;
}
}
这样就实现了多层表格导出实现。