优化过程分为了上下两篇,上篇是基础篇,下篇是优化篇,优化是利用java8 提供的新功能Consumer,方法可以作为参数传递!
单行数据
调用此方法的地方,此处就比较单了,实体不为空,就设置itemList值。
多行数据有两个方法,一个需要显示序号,一个不需要序号。
调用此方法的地方,此处就比较单了,把实体转为真是类型,然后设置itemList值。
抽出一个工具类
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import java.util.ArrayList;
import java.util.List;
import java.util.function.BiConsumer;
import java.util.function.Consumer;
public class ExcelMergeUtil {
/**
* 设置单个实体
* @param row
* @param currRow
* @param colIndex
* @param maxRow
* @param mergedRegionList
* @param colCount
* @param baseEntity
* @param dataConsumer
* @return
*/
public static int setEntity(ExcelRow row, int currRow, int colIndex, int maxRow, List<ExcelRow.MergedRegion> mergedRegionList, int colCount, BaseEntity baseEntity, Consumer<List<Object>> dataConsumer) {
List<Object> itemList = new ArrayList<>();
if (baseEntity != null) {
dataConsumer.accept(itemList);
}else{
ExcelMergeUtil.addBlankCell(itemList, colCount);
}
if(currRow < maxRow){
//需要合并
return ExcelMergeUtil.setObjectWithMerge(row, currRow, colIndex, maxRow, mergedRegionList, itemList);
}else {
return ExcelMergeUtil.setObjectWithoutMerge(row, currRow, colIndex, itemList);
}
}
/**
* 设置集合(多个实体)
* @param row
* @param currRow
* @param colIndex
* @param maxRow
* @param mergedRegionList
* @param colCount
* @param baseEntitieList
* @param dataConsumer
* @return
*/
public static int setEntityList(ExcelRow row, int currRow, int colIndex, int maxRow, List<ExcelRow.MergedRegion> mergedRegionList, int colCount, List<? extends BaseEntity> baseEntitieList, BiConsumer<List<Object>, BaseEntity> dataConsumer) {
List<Object> itemList = new ArrayList<>();
if (CollectionUtils.isEmpty(baseEntitieList)) {
ExcelMergeUtil.addBlankCell(itemList, colCount);
if (currRow < maxRow) {
//需要合并行
return ExcelMergeUtil.setObjectWithMerge(row, currRow, colIndex, maxRow, mergedRegionList, itemList);
} else {
return ExcelMergeUtil.setObjectWithoutMerge(row, currRow, colIndex, itemList);
}
}
int dataSize = baseEntitieList.size();
for (int index = 0; index < dataSize; index++) {
BaseEntity entity = baseEntitieList.get(index);
// 获取excel内容
itemList = new ArrayList<>();
dataConsumer.accept(itemList, entity);
if (index == dataSize - 1 && currRow < maxRow) {
//最后一行需要合并行
ExcelMergeUtil.setObjectWithMerge(row, currRow, colIndex, maxRow, mergedRegionList, itemList);
} else {
ExcelMergeUtil.setObjectWithoutMerge(row, currRow, colIndex, itemList);
}
currRow++;
}
return colIndex + colCount;
}
public interface TreeConsumer<T, U, M> {
void accept(T t, U u, M m);
}
public static int setEntityListWithNo(ExcelRow row, int currRow, int colIndex, int maxRow, List<ExcelRow.MergedRegion> mergedRegionList, int colCount, List<? extends BaseEntity> baseEntitieList, TreeConsumer<List<Object>, BaseEntity, Integer> dataConsumer) {
List<Object> itemList = new ArrayList<>();
if (CollectionUtils.isEmpty(baseEntitieList)) {
ExcelMergeUtil.addBlankCell(itemList, colCount);
if (currRow < maxRow) {
//需要合并行
return ExcelMergeUtil.setObjectWithMerge(row, currRow, colIndex, maxRow, mergedRegionList, itemList);
} else {
return ExcelMergeUtil.setObjectWithoutMerge(row, currRow, colIndex, itemList);
}
}
int dataSize = baseEntitieList.size();
for (int index = 0; index < dataSize; index++) {
BaseEntity entity = baseEntitieList.get(index);
// 获取excel内容
itemList = new ArrayList<>();
dataConsumer.accept(itemList, entity, index);
if (index == dataSize - 1 && currRow < maxRow) {
//最后一行需要合并行
ExcelMergeUtil.setObjectWithMerge(row, currRow, colIndex, maxRow, mergedRegionList, itemList);
} else {
ExcelMergeUtil.setObjectWithoutMerge(row, currRow, colIndex, itemList);
}
currRow++;
}
return colIndex + colCount;
}
public static void addBlankCell(List<Object> itemList, int count){
for(int index = 0; index < count; index++){
itemList.add("");
}
}
public static int setObjectWithMerge(ExcelRow row, int currRow, int colIndex, int maxRow, List<ExcelRow.MergedRegion> mergedRegionList, List<Object> itemList) {
for(Object item : itemList){
mergedRegionList.add(new ExcelRow.MergedRegion(currRow, maxRow, colIndex, colIndex));
if(item instanceof UrlBean){
UrlBean urlBean = (UrlBean) item;
colIndex = setUrlWithoutMerge(row, currRow, colIndex, urlBean.getLinkUrl(), urlBean.getLinkTitle());
}else{
row.setObject(currRow + "-" + (colIndex++), item);
}
}
return colIndex;
}
public static int setObjectWithoutMerge(ExcelRow row, int currRow, int colIndex, List<Object> itemList) {
for(Object item : itemList){
if(item instanceof UrlBean){
UrlBean urlBean = (UrlBean) item;
colIndex = setUrlWithoutMerge(row, currRow, colIndex, urlBean.getLinkUrl(), urlBean.getLinkTitle());
}else{
row.setObject(currRow + "-" + (colIndex++), item);
}
}
return colIndex;
}
public static int setUrlWithoutMerge(ExcelRow row, int currRow, int colIndex, String linkPath, String linkTitle) {
if (StringUtils.isNotBlank(linkPath) && StringUtils.isNotBlank(linkTitle)) {
row.setObject(currRow + "-" + (colIndex++), KrExcelWrite.WriteType.FORMULA_URL, "HYPERLINK(\"" + linkPath + "\",\"" + linkTitle + "\")");
} else {
row.setObject(currRow + "-" + (colIndex++), "");
}
return colIndex;
}
public static class UrlBean {
private String linkTitle;
private String linkUrl;
public UrlBean() {
}
public UrlBean(String linkTitle, String linkUrl) {
this.linkTitle = linkTitle;
this.linkUrl = linkUrl;
}
public String getLinkTitle() {
return linkTitle;
}
public void setLinkTitle(String linkTitle) {
this.linkTitle = linkTitle;
}
public String getLinkUrl() {
return linkUrl;
}
public void setLinkUrl(String linkUrl) {
this.linkUrl = linkUrl;
}
}
}
具体代码
private void writeToResponse(ContestEnterpriseExportReqForm reqForm, List<ContestEnterpriseListResultBean> enterpriseList, Map<Long, String> fieldMap) {
HttpServletResponse response = reqForm.getServletResponse();
int maxRowNumPerSheet = 1000;
List<ExcelHeader> headerList = initExcelHeaderList();
KrExcelWriteBuilder builder = KrExcelWriteBuilder.build()
.setSheetName("企业用户")
.setMaxRowNumPerSheet(maxRowNumPerSheet)
.setHeaderRowNum(1)
.setHeaderList(headerList)
.setDataRowNum(2);
String dateNow = DateUtil.formatByPattern(Instant.now().toEpochMilli(), DateUtil.DATE_FORMAT);
String fileName = dateNow + "_" + UUIDUtil.getUUID() + ".xlsx";
KrExcelWrite write = new DefaultExcelWriteImpl();
try {
initResponseHeader(response, fileName);
write.exportMergedRegion(response.getOutputStream(), builder, new KrExcelWriteDataSource(enterpriseList, fieldMap, maxRowNumPerSheet));
} catch (IOException e) {
logger.error("下载文件时候发生异常:{}", ExceptionUtil.outException(e));
}
}
private List<ExcelHeader> initExcelHeaderList() {
int width = 5000;
List<ExcelHeader> headerList = new ArrayList<>();
addExcelColHeader(headerList, "ID", "id", 2000);
addExcelColHeader(headerList, "公司名称", "name", width);
addExcelColHeader(headerList, "一句话介绍", "oneWordBrief", width);
addExcelColHeader(headerList, "企业简介", "brief", width);
addExcelColHeader(headerList, "成立时间", "createData", width);
addExcelColHeader(headerList, "公司官网", "website", width);
addExcelColHeader(headerList, "融资轮次", "lastFinanceRound", width);
addExcelColHeader(headerList, "公司规模", "scale", width);
addExcelColHeader(headerList, "所在地", "address", width);
addExcelColHeader(headerList, "注册地", "registAddress", width);
addExcelColHeader(headerList, "所在领域", "field", width);
addExcelColHeader(headerList, "融资-序号", "financeNo", width);
addExcelColHeader(headerList, "融资-金额", "financeMoney", width);
addExcelColHeader(headerList, "融资-轮次", "financeRound", width);
addExcelColHeader(headerList, "融资-投资方", "financeInvestor", width);
addExcelColHeader(headerList, "核心成员-序号", "coreMemberNo", width);
addExcelColHeader(headerList, "核心成员-姓名", "coreMemberName", width);
addExcelColHeader(headerList, "核心成员-职位", "coreMemberPosition", width);
addExcelColHeader(headerList, "核心成员-简介", "coreMemberBrief", width);
addExcelColHeader(headerList, "联系人-姓名", "contactName", width);
addExcelColHeader(headerList, "联系人-手机号", "contactMobile", width);
addExcelColHeader(headerList, "联系人-邮箱", "contactEmail", width);
addExcelColHeader(headerList, "联系人-职位", "contactPosition", width);
addExcelColHeader(headerList, "BP", "BP", width);
addExcelColHeader(headerList, "往期报名-报名时间", "historyApplyTime", width);
addExcelColHeader(headerList, "往期报名-招募活动名称", "historyRecruitTitle", width);
addExcelColHeader(headerList, "往期报名-方案", "historyApplyFile", width);
addExcelColHeader(headerList, "往期报名-入围情况", "historyEvaluateStatus", width);
return headerList;
}
private void addExcelColHeader(List<ExcelHeader> headerList, String columnName, String property, int width) {
ExcelHeader header = new ExcelHeader();
header.setColumnName(columnName);
header.setColumnProperty(property);
header.setWidth(width);
headerList.add(header);
}
private void initResponseHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
response.setContentType("application/octet-stream");
//设置Http响应头告诉浏览器下载这个附件
response.setHeader("Content-disposition", "attachment; filename="
+ fileName);
// response.setHeader("Content-Length", response.getOutputStream());
// response.setContentType("application/json;charset=utf-8");
}
private class KrExcelWriteDataSource implements com.kr.media.util.doc.excel.KrExcelWriteDataSource {
private List<ContestEnterpriseListResultBean> enterpriseList;
private Map<Long, String> fieldMap;
private int pageSize;
private volatile int curPage = 0;
private final int totalPage;
private final int totalCount;
private int currRow = 1;
private int total = 0;
public KrExcelWriteDataSource(List<ContestEnterpriseListResultBean> enterpriseList, Map<Long, String> fieldMap, int maxRowNumPerSheet) {
this.enterpriseList = enterpriseList;
this.fieldMap = fieldMap;
this.pageSize = maxRowNumPerSheet;
this.totalCount = enterpriseList == null ? 0 : enterpriseList.size();
this.totalPage = totalCount / pageSize + 1;
}
@Override
public boolean hasNext() {
curPage++;
if (curPage <= totalPage) {
return true;
}
return false;
}
@Override
public List<ExcelRow> next() {
List<ExcelRow> rowList = new ArrayList<>();
if (enterpriseList == null || enterpriseList.isEmpty()) {
ExcelRow excelRow = new ExcelRow();
excelRow.setObject("id", "空");
rowList.add(excelRow);
return rowList;
}
int startIndex = (curPage - 1) * pageSize;
if (startIndex > totalCount) {
return rowList;
}
int endIndex = curPage * pageSize - 1;
if (endIndex > (totalCount - 1)) {
endIndex = totalCount - 1;
}
logger.info("导出企业用户信息{}条", enterpriseList.size());
for (int i = startIndex; i <= endIndex; i++) {
ContestEnterpriseListResultBean enterprise = enterpriseList.get(i);
long enterpriseId = enterprise.getId();
int dataMaxSize = 0;
// 融资情况
List<ContestEnterpriseFinanceHistory> financeHistories = contestEnterpriseFinanceHistoryService.findByEnterpriseId(enterpriseId);
if (dataMaxSize < financeHistories.size()) {
dataMaxSize = financeHistories.size();
}
// 核心成员
List<ContestEnterpriseMember> memberList = contestEnterpriseMemberService.findByEnterpriseId(enterpriseId);
if (dataMaxSize < memberList.size()) {
dataMaxSize = memberList.size();
}
// 往期报名
List<ContestSignup> contestSignupList = contestSignupService.findByEnterpriseId(enterpriseId);
if (dataMaxSize < contestSignupList.size()) {
dataMaxSize = contestSignupList.size();
}
int maxRow = currRow;
if (dataMaxSize > 1) {
maxRow = currRow + dataMaxSize - 1;
}
if (total % pageSize == 0) {
currRow = 1;
}
ExcelRow row = new ExcelRow();
rowList.add(row);
row.setStartRow(currRow);
row.setEndRow(maxRow);
List<ExcelRow.MergedRegion> mergedRegionList = new ArrayList<>();
row.setMergedRegionList(mergedRegionList);
int colIndex = 0;
// 企业基本信息
ContestEnterpriseTianyancha enterpriseTianyancha = contestEnterpriseTianyanchaService.findByEnterpriseId(enterpriseId);
colIndex = setEnterpriseBaseInfo(row, currRow, colIndex, enterprise, enterpriseTianyancha, fieldMap, maxRow, mergedRegionList);
// 融资情况
colIndex = setFinanceMessageList(row, currRow, colIndex, financeHistories, maxRow, mergedRegionList);
// 核心成员
colIndex = setMemberList(row, currRow, colIndex, memberList, maxRow, mergedRegionList);
// 联系人
ContestEnterprise contestEnterprise = contestEnterpriseService.get(enterpriseId);
colIndex = setContactBean(row, currRow, colIndex, contestEnterprise, maxRow, mergedRegionList);
// BP
colIndex = setBP(row, currRow, colIndex, contestEnterprise, maxRow, mergedRegionList);
// 往期报名
colIndex = setHistoryContestSignupList(row, currRow, colIndex, contestSignupList, maxRow, mergedRegionList);
currRow = maxRow + 1;
total++;
}
return rowList;
}
}
/**
* 企业基本信息
*
* @param row
* @param currRow
* @param colIndex
* @param entity
* @param fieldMap
* @return
*/
private int setEnterpriseBaseInfo(ExcelRow row, int currRow, int colIndex, ContestEnterpriseListResultBean entity, ContestEnterpriseTianyancha enterpriseTianyancha, Map<Long, String> fieldMap, int maxRow, List<ExcelRow.MergedRegion> mergedRegionList) {
int colCount = 11;
return ExcelMergeUtil.setEntity(row, currRow, colIndex, maxRow, mergedRegionList, colCount, enterpriseTianyancha, (itemList)->{
if (entity != null) {
itemList.add(String.valueOf(entity.getId()));
itemList.add(String.valueOf(entity.getEnterpriseTitle()));
itemList.add(String.valueOf(entity.getBriefIntro()));
}else{
ExcelMergeUtil.addBlankCell(itemList, 3);
}
if (enterpriseTianyancha != null) {
itemList.add(enterpriseTianyancha.getIntro());
if(enterpriseTianyancha.getSetUpTime() != null){
itemList.add(DateUtil.formatByPattern(enterpriseTianyancha.getSetUpTime(), DateUtil.DATE_FORMAT));
}else{
itemList.add("");
}
itemList.add(enterpriseTianyancha.getWebsite());
itemList.add(enterpriseTianyancha.getFinanceRound());
itemList.add(enterpriseTianyancha.getCompanySize());
itemList.add(enterpriseTianyancha.getAddress());
itemList.add(enterpriseTianyancha.getRegisteredAddress());
itemList.add(getFieldStr(entity.getId(), fieldMap));
}
});
}
private int setFinanceMessageList(ExcelRow row, int currRow, int colIndex, List<ContestEnterpriseFinanceHistory> financeHistories, int maxRow, List<ExcelRow.MergedRegion> mergedRegionList) {
int colCount = 4;
return ExcelMergeUtil.setEntityListWithNo(row, currRow, colIndex, maxRow, mergedRegionList, colCount, financeHistories, (itemList, entity, index)->{
ContestEnterpriseFinanceHistory finance = (ContestEnterpriseFinanceHistory) entity;
itemList.add(String.valueOf(index + 1));
itemList.add(finance.getMoney());
itemList.add(finance.getRound());
itemList.add(finance.getInvestorName());
});
}
private int setMemberList(ExcelRow row, int currRow, int colIndex, List<ContestEnterpriseMember> memberList, int maxRow, List<ExcelRow.MergedRegion> mergedRegionList) {
int colCount = 4;
return ExcelMergeUtil.setEntityListWithNo(row, currRow, colIndex, maxRow, mergedRegionList, colCount, memberList, (itemList, entity, index)->{
ContestEnterpriseMember member = (ContestEnterpriseMember) entity;
itemList.add(String.valueOf(index + 1));
itemList.add(member.getName());
itemList.add(member.getTitle());
itemList.add(member.getDesc());
});
}
private int setContactBean(ExcelRow row, int currRow, int colIndex, ContestEnterprise contestEnterprise, int maxRow, List<ExcelRow.MergedRegion> mergedRegionList) {
int colCount = 4;
return ExcelMergeUtil.setEntity(row, currRow, colIndex, maxRow, mergedRegionList, colCount, contestEnterprise, (itemList)->{
if (contestEnterprise != null) {
itemList.add(contestEnterprise.getContactsName());
itemList.add(contestEnterprise.getContactsMobileNo() == null ? null : contestEnterprise.getContactsMobileNo().toString());
itemList.add(contestEnterprise.getContactsMobileEmail());
itemList.add(contestEnterprise.getContactsPosition());
}
});
}
private int setBP(ExcelRow row, int currRow, int colIndex, ContestEnterprise contestEnterprise, int maxRow, List<ExcelRow.MergedRegion> mergedRegionList) {
int colCount = 1;
return ExcelMergeUtil.setEntity(row, currRow, colIndex, maxRow, mergedRegionList, colCount, contestEnterprise, (itemList)->{
if (contestEnterprise != null) {
ExcelMergeUtil.UrlBean urlBean = new ExcelMergeUtil.UrlBean();
itemList.add(urlBean);
urlBean.setLinkTitle(contestEnterprise.getEnterpriseBpName());
urlBean.setLinkUrl(getUrlByPath(contestEnterprise.getEnterpriseBpUrl()));
}
});
}
private int setHistoryContestSignupList(ExcelRow row, int currRow, int colIndex, List<ContestSignup> contestSignupList, int maxRow, List<ExcelRow.MergedRegion> mergedRegionList) {
int colCount = 4;
return ExcelMergeUtil.setEntityList(row, currRow, colIndex, maxRow, mergedRegionList, colCount, contestSignupList, (itemList, entity)->{
ContestSignup signup = (ContestSignup) entity;
//报名时间
itemList.add(DateUtil.formatByPattern(signup.getCreateTime(), DateUtil.DATE_TIME_FORMAT_DEFAULT));
// 招募活动名称
ContestRecruit contestRecruit = contestRecruitService.get(signup.getRecrcuitId());
if (contestRecruit != null) {
itemList.add(contestRecruit.getRecruitTitle());
}else{
itemList.add("");
}
// 方案
if (StringUtils.isNotBlank(signup.getOpusName()) && StringUtils.isNotBlank(signup.getOpusUrl())) {
ExcelMergeUtil.UrlBean urlBean = new ExcelMergeUtil.UrlBean();
itemList.add(urlBean);
urlBean.setLinkTitle(signup.getOpusName());
urlBean.setLinkUrl(getUrlByPath(signup.getOpusUrl()));
}else{
itemList.add("");
}
// 入围情况
ContestSignupService.EvaluateStatusEnum evaluateEnum = EnumUtil.getByKey(ContestSignupService.EvaluateStatusEnum.class, ContestSignupService.EvaluateStatusEnum::getCode, signup.getEvaluateStatus());
if (evaluateEnum != null) {
itemList.add(evaluateEnum.getName());
}else{
itemList.add("");
}
});
}
private String getFieldStr(Long id, Map<Long, String> fieldMap) {
List<ContestEnterpriseField> fieldList = contestEnterpriseFieldService.findByEnterpriseId(id);
List<ContestEnterpriseSubField> subFieldList = contestEnterpriseSubFieldService.findByEnterpriseId(id);
List<Long> fieldIdList = fieldList.stream().map(ContestEnterpriseField::getSubFieldType).collect(Collectors.toList());
List<Long> subFieldIdList = subFieldList.stream().map(ContestEnterpriseSubField::getSubFieldType).collect(Collectors.toList());
fieldIdList.addAll(subFieldIdList);
List<String> list = new ArrayList<>();
fieldIdList.forEach(fieldId -> {
String field = fieldMap.get(fieldId);
list.add(field);
});
if (CollectionUtils.isNotEmpty(list)) {
return StringUtils.join(list, "、");
}
return null;
}