您好，登录后才能下订单哦！
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
本文小编为大家详细介绍“Java怎么用EasyExcel解析动态表头并导出”，内容详细，步骤清晰，细节处理妥当，希望这篇“Java怎么用EasyExcel解析动态表头并导出”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。
const download = () => {
axios({
method: 'GET',
url: config.http.baseUrl + '/templateDownload',
responseType: 'blob',
})
.then(function (res) {
const content = res.data
const blob = new Blob([content], { type: "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" })
const downloadElement = document.createElement("a");
const href = window.URL.createObjectURL(blob);
downloadElement.href = href;
downloadElement.download = decodeURI(res.headers['filename']);
document.body.appendChild(downloadElement);
downloadElement.click();
document.body.removeChild(downloadElement); // 下载完成移除元素
window.URL.revokeObjectURL(href); // 释放掉blob对象
})
}excel文件导入功能，常常需要进行模板下载，在springboot项目中，程序是以jar包的形式运行的，所以有很多小伙伴常常
遇到在本地开发中能够实现下载功能，但部署到服务器的时候，找不到模板文件的问题。
@Override
public void templateDownload(HttpServletResponse response, HttpServletRequest request) {
//获取要下载的模板名称
String fileName = "批量导入模板.xlsx";
//获取文件下载路径
String filePath = "/template/template.xlsx";
TemplateDownloadUtil.download(response, request, fileName, filePath);
}import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.ClassPathResource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
/**
* 模板文件下载工具类
* @author
* @date 2021/05/20 9:20
*/
@Slf4j
public class TemplateDownloadUtil {
public static void download(HttpServletResponse response, HttpServletRequest request,String fileName,String filePath){
try {
response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setHeader("filename", URLEncoder.encode(fileName, "UTF-8"));
response.setHeader("Access-Control-Expose-Headers", "filename,Content-Disposition");
//获取文件的路径，此方式本地开发可以运行，服务器无法获取文件
// String filePath = getClass().getResource("/template/template.xlsx").getPath();
// FileInputStream input = new FileInputStream(filePath);
//在服务器中能够读取到模板文件
ClassPathResource resource = new ClassPathResource(filePath);
InputStream input = resource.getInputStream();
OutputStream out = response.getOutputStream();
byte[] b = new byte[2048];
int len;
while ((len = input.read(b)) != -1) {
out.write(b, 0, len);
}
//修正 Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容？如果信任此工作簿的来源，请点击"是"
// response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
input.close();
} catch (Exception e) {
log.error("下载模板失败 :", e);
}
}
}EasyExcel简单的读文件，官网中已经有详细的说明，本文不再赘述。
本文主要针对笔者遇到的复杂表头及动态表头进行讲解。
模板示例
解析
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* 发薪单上传excel读取类
*
* @author yupf
* @description Listener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
*/
@Slf4j
@Data
public class BatchReadListener extends AnalysisEventListener<Map<Integer, String>> {
/**
* 每隔500条存储数据库，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 500;
//Excel数据缓存结构
private List<Map<Integer, Map<Integer, String>>> list = new ArrayList<>();
//Excel表头（列名）数据缓存结构
private Map<Integer, String> headTitleMap = new HashMap<>();
/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private DbFileBatchService dbFileBatchService;
private DbFileContentService dbFileContentService;
private FileBatch fileBatch;
private int total = 0;
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*/
public BatchReadListener(DbFileBatchService dbFileBatchService, DbFileContentService dbFileContentService, FileBatch fileBatch) {
this.dbFileBatchService = dbFileBatchService;
this.dbFileContentService = dbFileContentService;
this.fileBatch = fileBatch;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
total++;
Map<Integer, Map<Integer, String>> map = new HashMap<>();
map.put(context.readRowHolder().getRowIndex(), data);
list.add(map);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成！");
}
/**
* 解析表头数据
**/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
log.info("表头数据：{}", JSONObject.toJSONString(headMap));
headTitleMap = headMap;
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据，开始存储数据库！", list.size());
FileContent fileContent = null;
List<FileContent> fileContentList = list.stream().flatMap(
integerMap -> integerMap.entrySet().stream().map(entrySet -> {
//entrySet.getKey()获取的是内容的RowIndex,实际的行数需要根据表头数进行处理
Integer rowIndex = entrySet.getKey();
Map<Integer, String> value = entrySet.getValue();
log.info(JSONObject.toJSONString(value));
fileContent = new FileContent();
fileContent.setBatchId(fileBatch.getId());
fileContent.setBatchNo(fileBatch.getBatchNo());
//固定字段入库
fileContent.setName(value.get(0) != null ? value.get(0).trim() : "");
fileContent.setCertNo(value.get(1) != null ? value.get(1).trim() : "");
fileContent.setRealAmount(value.get(2) != null ? value.get(2).trim() : "");
//所有动态表头数据转为JSON串入库
fileContent.setFieldsValue(JSONObject.toJSONString(value));
//取实际的内容rowIndex
fileContent.setRowNum(rowIndex + 1);
fileContent.setCreateTime(LocalDateTime.now());
return xcSalaryFileContent;
}
)).collect(Collectors.toList());
log.info(JSONObject.toJSONString(fileContentList));
dbFileContentService.saveBatch(fileContentList);
log.info("存储数据库成功！");
}
} BatchReadListener listener = new BatchReadListener(dbFileBatchService, dbFileContentService, fileBatch);
try {
//注：headRowNumber默认为1，现赋值为2，即从第三行开始读取内容
EasyExcel.read(fileInputStream, listener).headRowNumber(2).sheet().doRead();
} catch (Exception e) {
log.info("EasyExcel解析文件失败，{}", e);
throw new CustomException("文件解析失败，请重新上传");
}
//获取表头信息进行处理
Map<Integer, String> headTitleMap = listener.getHeadTitleMap();
//获取动态表头信息
List<String> headList = headTitleMap.keySet().stream().map(key -> {
String head = headTitleMap.get(key);
log.info(head);
return head == null ? "" : head.replace("*", "");
}).collect(Collectors.toList());
//可以对表头进行入库保存，方便后续导出综上，动态表头即可完成解析。
导出示例
获取动态头
private List<List<String>> getFileHeadList( FileBatch fileBatch) {
String head = fileBatch.getFileHead();
List<String> headList = Arrays.asList(head.split(","));
List<List<String>> fileHead = headList.stream().map(item -> concatHead(Lists.newArrayList(item))).collect(Collectors.toList());
fileHead.add(concatHead(Lists.newArrayList("备注")));
return fileHead;
} /**
* 填写须知
* @param headContent
* @return
*/
private List<String> concatHead(List<String> headContent) {
String remake = "填写须知： \n" +
"1.系统自动识别Excel表格，表头必须含有“企业账户号”、“企业账户名”、“实发金额”；\n" +
"2.带 “*” 为必填字段，填写后才能上传成功；\n" +
"3.若需上传其他表头，可自行在“实发金额”后添加表头，表头最多可添加20个，表头名称请控制在8个字以内；\n" +
"4.填写的表头内容不可超过30个字；\n" +
"5.实发金额支持填写到2位小数；\n" +
"6.每次导入数据不超过5000条。\n" +
"\n" +
"注：请勿删除填写须知，删除后将导致文件上传失败\n" +
"\n" +
"表头示例：";
headContent.add(0, remake);
return headContent;
}获取数据
List<FileContent> fileContentList = dbFileContentService.list(
Wrappers.<FileContent>lambdaQuery()
.eq(FileContent::getBatchId, fileBatch.getId())
.orderByAsc(FileContent::getRowNum)
);
List<List<Object>> contentList = fileContentList.stream().map(fileContent -> {
List<Object> rowList = new ArrayList<>();
String fieldsValue = fileContent.getFieldsValue();
JSONObject contentObj = JSONObject.parseObject(fieldsValue);
for (int columnIndex = 0 , length = headList.size(); columnIndex < length; columnIndex++) {
Object content = contentObj.get(columnIndex);
rowList.add(content == null ? "" : content);
}
rowList.add(fileContent.getCheckMessage());
return rowList;
}).collect(Collectors.toList());单元格格式设置
import com.alibaba.excel.metadata.data.DataFormatData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.util.List;
/**
* 设置表头和填充内容的样式
*/
public class CellStyleStrategy extends HorizontalCellStyleStrategy {
private final WriteCellStyle headWriteCellStyle;
private final WriteCellStyle contentWriteCellStyle;
/**
* 操作列
*/
private final List<Integer> columnIndexes;
public CellStyleStrategy(List<Integer> columnIndexes,WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
this.columnIndexes = columnIndexes;
this.headWriteCellStyle = headWriteCellStyle;
this.contentWriteCellStyle = contentWriteCellStyle;
}
//设置头样式
@Override
protected void setHeadCellStyle( CellWriteHandlerContext context) {
// 获取字体实例
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("宋体");
//表头不同处理
if (columnIndexes.get(0).equals(context.getRowIndex())) {
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
headWriteFont.setFontHeightInPoints((short) 12);
headWriteFont.setBold(false);
headWriteFont.setFontName("宋体");
}else{
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteFont.setFontHeightInPoints((short) 11);
headWriteFont.setBold(false);
headWriteFont.setFontName("微软雅黑");
}
headWriteCellStyle.setWriteFont(headWriteFont);
DataFormatData dataFormatData = new DataFormatData();
dataFormatData.setIndex((short)49);
headWriteCellStyle.setDataFormatData(dataFormatData);
if (stopProcessing(context)) {
return;
}
WriteCellData<?> cellData = context.getFirstCellData();
WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());
}
//设置填充数据样式
@Override
protected void setContentCellStyle(CellWriteHandlerContext context) {
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontName("宋体");
contentWriteFont.setFontHeightInPoints((short) 11);
//设置数据填充后的实线边框
contentWriteCellStyle.setWriteFont(contentWriteFont);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
DataFormatData dataFormatData = new DataFormatData();
dataFormatData.setIndex((short)49);
contentWriteCellStyle.setDataFormatData(dataFormatData);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteCellData<?> cellData = context.getFirstCellData();
WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());
}
}行高设置
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Row;
/**
* 设置表头的自动调整行高策略
*/
public class CellRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {
@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
//设置主标题行高为17.7
if(relativeRowIndex == 0){
//如果excel需要显示行高为15，那这里就要设置为15*20=300
row.setHeight((short) 3240);
}
}
@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {
}
}列宽度自适应
如果是简单表头，可以使用EasyExcel中的LongestMatchColumnWidthStyleStrategy()来实现。
EasyExcel.write(fileName, LongestMatchColumnWidthData.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("模板").doWrite(dataLong());如果是复杂表头，就需要自己来实现，代码如下：
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author yupf
* @description
* @date 2022/9/7 18:48
*/
@Slf4j
public class CellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>();
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
if (isHead) {
if(relativeRowIndex.intValue() == 1){
Integer length = cell.getStringCellValue().getBytes().length;
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || length > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), length);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), length * 300);
}
}
}else{
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}写入文件
EasyExcel.write(response.getOutputStream()) .head(head) .registerWriteHandler(new CellRowHeightStyleStrategy()) //设置行高的策略 .registerWriteHandler(new CellStyleStrategy(Arrays.asList(0,1),new WriteCellStyle(), new WriteCellStyle())) .registerWriteHandler(new CellWidthStyleStrategy()) .sheet(sheetName) .doWrite(list);
读到这里，这篇“Java怎么用EasyExcel解析动态表头并导出”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注亿速云行业资讯频道。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。