前言
excel文件导入及导出，是日常开发中经常遇到的需求。本次笔者以EasyExcel为例，针对在项目中遇到的动态表头解析及导出的场景，详细介绍具体的代码实现过程。
参考地址
https://github.com/alibaba/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包的形式运行的，所以有很多小伙伴常常
遇到在本地开发中能够实现下载功能，但部署到服务器的时候，找不到模板文件的问题。
@OverridepublicvoidtemplateDownload(HttpServletResponse response,HttpServletRequest request){//获取要下载的模板名称String fileName ="批量导入模板.xlsx";//获取文件下载路径String filePath ="/template/template.xlsx";TemplateDownloadUtil.download(response, request, fileName, filePath);}
importlombok.extern.slf4j.Slf4j;importorg.springframework.core.io.ClassPathResource;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.InputStream;importjava.io.OutputStream;importjava.net.URLEncoder;/**
* 模板文件下载工具类
* @author
* @date 2021/05/20 9:20
*/@Slf4jpublicclassTemplateDownloadUtil{publicstaticvoiddownload(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 =newClassPathResource(filePath);InputStream input = resource.getInputStream();OutputStream out = response.getOutputStream();byte[] b =newbyte[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动态表头解析
EasyExcel简单的读文件，官网中已经有详细的说明，本文不再赘述，详细操作参见
。
本文主要针对笔者遇到的复杂表头及动态表头进行讲解。
模板示例
解析
importcom.alibaba.excel.context.AnalysisContext;importcom.alibaba.excel.event.AnalysisEventListener;importcom.alibaba.fastjson.JSON;importcom.alibaba.fastjson.JSONObject;importlombok.Data;importlombok.extern.slf4j.Slf4j;importjava.time.LocalDateTime;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importjava.util.stream.Collectors;/**
* 发薪单上传excel读取类
*
* @author yupf
* @description Listener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
*/@Slf4j@DatapublicclassBatchReadListenerextendsAnalysisEventListener<Map<Integer,String>>{/**
* 每隔500条存储数据库，然后清理list ，方便内存回收
*/privatestaticfinalint BATCH_COUNT =500;//Excel数据缓存结构privateList<Map<Integer,Map<Integer,String>>> list =newArrayList<>();//Excel表头（列名）数据缓存结构privateMap<Integer,String> headTitleMap =newHashMap<>();/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/privateDbFileBatchService dbFileBatchService;privateDbFileContentService dbFileContentService;privateFileBatch fileBatch;privateint total =0;/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*/publicBatchReadListener(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
*/@Overridepublicvoidinvoke(Map<Integer,String> data,AnalysisContext context){
log.info("解析到一条数据:{}", JSON.toJSONString(data));
total++;Map<Integer,Map<Integer,String>> map =newHashMap<>();
map.put(context.readRowHolder().getRowIndex(), data);
list.add(map);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif(list.size()>= BATCH_COUNT){saveData();// 存储完成清理 list
list.clear();}}/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/@OverridepublicvoiddoAfterAllAnalysed(AnalysisContext context){// 这里也要保存数据，确保最后遗留的数据也存储到数据库saveData();
log.info("所有数据解析完成！");}/**
* 解析表头数据
**/@OverridepublicvoidinvokeHeadMap(Map<Integer,String> headMap,AnalysisContext context){
log.info("表头数据：{}",JSONObject.toJSONString(headMap));
headTitleMap = headMap;}/**
* 加上存储数据库
*/privatevoidsaveData(){
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 =newFileContent();
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 =newBatchReadListener(dbFileBatchService, dbFileContentService, fileBatch);try{//注：headRowNumber默认为1，现赋值为2，即从第三行开始读取内容EasyExcel.read(fileInputStream, listener).headRowNumber(2).sheet().doRead();}catch(Exception e){
log.info("EasyExcel解析文件失败，{}", e);thrownewCustomException("文件解析失败，请重新上传");}//获取表头信息进行处理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());//可以对表头进行入库保存，方便后续导出
综上，动态表头即可完成解析。
EasyExcel动态表头导出
导出示例
获取动态头
privateList<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
*/privateList<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 =newArrayList<>();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());
单元格格式设置
importcom.alibaba.excel.metadata.data.DataFormatData;importcom.alibaba.excel.metadata.data.WriteCellData;importcom.alibaba.excel.write.handler.context.CellWriteHandlerContext;importcom.alibaba.excel.write.metadata.style.WriteCellStyle;importcom.alibaba.excel.write.metadata.style.WriteFont;importcom.alibaba.excel.write.style.HorizontalCellStyleStrategy;importorg.apache.poi.ss.usermodel.BorderStyle;importorg.apache.poi.ss.usermodel.HorizontalAlignment;importorg.apache.poi.ss.usermodel.IndexedColors;importjava.util.List;/**
* 设置表头和填充内容的样式
*/publicclassCellStyleStrategyextendsHorizontalCellStyleStrategy{privatefinalWriteCellStyle headWriteCellStyle;privatefinalWriteCellStyle contentWriteCellStyle;/**
* 操作列
*/privatefinalList<Integer> columnIndexes;publicCellStyleStrategy(List<Integer> columnIndexes,WriteCellStyle headWriteCellStyle,WriteCellStyle contentWriteCellStyle){this.columnIndexes = columnIndexes;this.headWriteCellStyle = headWriteCellStyle;this.contentWriteCellStyle = contentWriteCellStyle;}//设置头样式@OverrideprotectedvoidsetHeadCellStyle(CellWriteHandlerContext context){// 获取字体实例WriteFont headWriteFont =newWriteFont();
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 =newDataFormatData();
dataFormatData.setIndex((short)49);
headWriteCellStyle.setDataFormatData(dataFormatData);if(stopProcessing(context)){return;}WriteCellData<?> cellData = context.getFirstCellData();WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());}//设置填充数据样式@OverrideprotectedvoidsetContentCellStyle(CellWriteHandlerContext context){WriteFont contentWriteFont =newWriteFont();
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 =newDataFormatData();
dataFormatData.setIndex((short)49);
contentWriteCellStyle.setDataFormatData(dataFormatData);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);WriteCellData<?> cellData = context.getFirstCellData();WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());}}
行高设置
importcom.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;importorg.apache.poi.ss.usermodel.Row;/**
* 设置表头的自动调整行高策略
*/publicclassCellRowHeightStyleStrategyextendsAbstractRowHeightStyleStrategy{@OverrideprotectedvoidsetHeadColumnHeight(Row row,int relativeRowIndex){//设置主标题行高为17.7if(relativeRowIndex ==0){//如果excel需要显示行高为15，那这里就要设置为15*20=300
row.setHeight((short)3240);}}@OverrideprotectedvoidsetContentColumnHeight(Row row,int relativeRowIndex){}}
列宽度自适应
如果是简单表头，可以使用EasyExcel中的LongestMatchColumnWidthStyleStrategy()来实现。
EasyExcel.write(fileName,LongestMatchColumnWidthData.class).registerWriteHandler(newLongestMatchColumnWidthStyleStrategy()).sheet("模板").doWrite(dataLong());
如果是复杂表头，就需要自己来实现，代码如下：
importcom.alibaba.excel.enums.CellDataTypeEnum;importcom.alibaba.excel.metadata.Head;importcom.alibaba.excel.metadata.data.CellData;importcom.alibaba.excel.metadata.data.WriteCellData;importcom.alibaba.excel.write.metadata.holder.WriteSheetHolder;importcom.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;importlombok.extern.slf4j.Slf4j;importorg.apache.commons.collections.CollectionUtils;importorg.apache.poi.ss.usermodel.Cell;importjava.util.HashMap;importjava.util.List;importjava.util.Map;/**
* @author yupf
* @description
* @date 2022/9/7 18:48
*/@Slf4jpublicclassCellWidthStyleStrategyextendsAbstractColumnWidthStyleStrategy{privateMap<Integer,Map<Integer,Integer>> CACHE =newHashMap<>();@OverrideprotectedvoidsetColumnWidth(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 =newHashMap<>();
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);}}}}privateIntegerdataLength(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(newCellRowHeightStyleStrategy())//设置行高的策略.registerWriteHandler(newCellStyleStrategy(Arrays.asList(0,1),newWriteCellStyle(),newWriteCellStyle())).registerWriteHandler(newCellWidthStyleStrategy()).sheet(sheetName).doWrite(list);
总结
以上便是EasyExcel解析动态表头及导出的整个过程。
在使用过程中，笔者的感受是，上手难度很低，很适合新手去做简单的表格解析，当然，如果你的需求有复杂的格式，EasyExcel也提供了api，能够很好的满足需要。
本文转载自: https://blog.csdn.net/yupengfei112233/article/details/126837768
版权归原作者 技术深耕 所有， 如有侵权，请联系我们删除。
版权归原作者 技术深耕 所有， 如有侵权，请联系我们删除。