前言
excel 文件导入及导出,是日常开发中常常遇到的需要。本次笔者以 EasyExcel 为例,针对在我的项目中遇到的 动静表头解析及导出 的场景,具体介绍具体的代码实现过程。
参考地址
https://github.com/alibaba/ea…
前端下载
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 动静表头解析
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());
// 能够对表头进行入库保留,不便后续导出
综上,动静表头即可实现解析。
EasyExcel 动静表头导出
导出示例
获取动静头
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);
总结
以上便是 EasyExcel 解析动静表头及导出的整个过程。
在应用过程中,笔者的感触是,上手难度很低,很适宜老手去做简略的表格解析,当然,如果你的需要有简单的格局,EasyExcel 也提供了 api,可能很好的满足需要。