在业务开发中，有大量的excel下载导出的场景，有时候需要excel的列可以支持动态的扩展，没有固定的表头。下面Util可实现excel表头的动态扩展并支持多sheet页下载
/**
* 动态导出工具
*/
@Slf4j
public class DynamicEasyExcelExportUtils {
private static final String DEFAULT_SHEET_NAME = "自定义sheet1";
/**
* 动态导出文件。具体使用查看本类的单元测试方法
* @param headColumnMap 有序列头部
* 按顺序，key=列的code标识，value=第一行,第二行,……
* 如果下面的列不写，自动以上面列填充，并合并单元格。
* 如果不同的列的同一行的名字相同，自动合并单元格
* @param dataList 数据体
* @return
*/
public static byte[] exportExcelFile(LinkedHashMap<String, String> headColumnMap, List<Map<String, Object>> dataList){
//获取列名称
List<List<String>> excelHead = new ArrayList<>();
if(MapUtils.isNotEmpty(headColumnMap)){
//key为匹配符，value为列名，如果多级列名用逗号隔开
headColumnMap.entrySet().forEach(entry -> {
excelHead.add(Lists.newArrayList(entry.getValue().split(",")));
});
}
List<List<Object>> excelRows = new ArrayList<>();
if(MapUtils.isNotEmpty(headColumnMap) && CollectionUtils.isNotEmpty(dataList)){
for (Map<String, Object> dataMap : dataList) {
List<Object> rows = new ArrayList<>();
headColumnMap.entrySet().forEach(headColumnEntry -> {
if(dataMap.containsKey(headColumnEntry.getKey())){
Object data = dataMap.get(headColumnEntry.getKey());
rows.add(data);
} else {
rows.add(null);
}
});
excelRows.add(rows);
}
}
byte[] stream = createExcelFile(excelHead, excelRows);
return stream;
}
/**
* 生成文件
* @param excelHead
* @param excelRows
* @return
*/
private static byte[] createExcelFile(List<List<String>> excelHead, List<List<Object>> excelRows){
try {
if(CollectionUtils.isNotEmpty(excelHead)){
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
EasyExcel.write(outputStream).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.head(excelHead)
.sheet(DEFAULT_SHEET_NAME)
.doWrite(excelRows);
return outputStream.toByteArray();
}
} catch (Exception e) {
log.error("动态生成excel文件失败，headColumns：" + JSON.toJSONString(excelHead) + "，excelRows：" + JSON.toJSONString(excelRows), e);
}
return null;
}
/**
* 动态导出文件
* @param headColumnMap 有序列头部
* @param dataList 数据体
* @return
*/
public static byte[] exportExcelFileSheets(List<SheetObject> sheetObjects){
Assert.isTrue(!CollectionUtils.isEmpty(sheetObjects),"sheetObjects不可为空");
List<SheetObjectDetail> sheetObjectDetails = new ArrayList<>();
for(SheetObject sheetObject : sheetObjects){
LinkedHashMap<String, String> headColumnMap = sheetObject.getHeadColumnMap();
List<Map<String, Object>> dataList = sheetObject.getDataList();
//获取列名称
List<List<String>> excelHead = new ArrayList<>();
if(MapUtils.isNotEmpty(headColumnMap)){
//key为匹配符，value为列名，如果多级列名用逗号隔开
headColumnMap.entrySet().forEach(entry -> {
excelHead.add(Lists.newArrayList(entry.getValue().split(",")));
});
}
List<List<Object>> excelRows = new ArrayList<>();
if(MapUtils.isNotEmpty(headColumnMap) && CollectionUtils.isNotEmpty(dataList)){
for(Map<String, Object> dataMap : dataList){
List<Object> rows = new ArrayList<>();
headColumnMap.entrySet().forEach(headColumnEntry -> {
if(dataMap.containsKey(headColumnEntry.getKey())){
Object data = dataMap.get(headColumnEntry.getKey());
rows.add(data);
}else{
rows.add("/");
}
});
excelRows.add(rows);
}
}
SheetObjectDetail sheetObjectDetail = new SheetObjectDetail(sheetObject.getSheetName(),excelHead,excelRows);
sheetObjectDetails.add(sheetObjectDetail);
}
byte[] stream = createExcelFileSheets(sheetObjectDetails);
return stream;
}
/**
* 生成文件
* @param excelHead
* @param excelRows
* @return
*/
private static byte[] createExcelFileSheets(List<SheetObjectDetail> sheetObjectDetails){
Assert.isTrue(!CollectionUtils.isEmpty(sheetObjectDetails),"sheetObjectDetails 不可为空");
log.info("createExcelFileSheets sheetObjectDetails " + JSON.toJSONString(sheetObjectDetails));
try {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel.write(outputStream).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
for(SheetObjectDetail sheetObjectDetail : sheetObjectDetails){
WriteSheet sheet = EasyExcel.writerSheet(sheetObjectDetail.getSheetName()).head(sheetObjectDetail.getExcelHead()).build();
log.info("createExcelFileSheets sheet" + JSON.toJSONString(sheet));
excelWriter.write(sheetObjectDetail.getExcelRows(),sheet);
}
excelWriter.finish();
return outputStream.toByteArray();
} catch (Exception e) {
log.error("动态生成excel文件失败，sheetObjects：" + JSON.toJSONString(sheetObjectDetails), e);
throw new RuntimeException("动态生成excel文件失败",e);
}
}
}
一、动态excel表头
private static void testCase1() throws IOException{
//导出包含数据内容的文件
LinkedHashMap<String, String> headColumnMap = Maps.newLinkedHashMap();
headColumnMap.put("className","班级");
headColumnMap.put("name","学生信息,名称");
headColumnMap.put("sex","学生信息,性别");
headColumnMap.put("age","学生信息,年龄");
List<Map<String, Object>> dataList = new ArrayList<>();
for (int i = 0; i < 5; i++) {
Map<String, Object> dataMap = Maps.newHashMap();
dataMap.put("className", "一年级");
dataMap.put("name", "张三" + i);
dataMap.put("sex", "男");
dataMap.put("age", i+20);
dataList.add(dataMap);
}
byte[] stream = exportExcelFile(headColumnMap, dataList);
List<byte[]> list=new ArrayList();
InputStream input=new ByteArrayInputStream(exportExcelFile(headColumnMap, dataList));
list.add(exportExcelFile(headColumnMap, dataList));
list.add(exportExcelFile(headColumnMap, dataList));
FileOutputStream outputStream = new FileOutputStream(new File("/Users/eric/Documents/easyexcel-export-user5.xlsx"));
outputStream.write(stream);
outputStream.close();
}
二、多sheet页
private static void testCase2() throws IOException{
List<SheetObject> sheetObjects = new ArrayList<>();
//导出包含数据内容的文件
LinkedHashMap<String, String> headColumnMap1 = Maps.newLinkedHashMap();
headColumnMap1.put("className","班级");
headColumnMap1.put("name","学生信息");
headColumnMap1.put("sex","学生信息,性别");
List<Map<String, Object>> dataList1 = new ArrayList<>();
for (int i = 0; i < 5; i++) {
Map<String, Object> dataMap = Maps.newHashMap();
dataMap.put("className", "一年级");
dataMap.put("name", "张三" + i);
dataMap.put("sex", "男");
dataList1.add(dataMap);
}
sheetObjects.add(new SheetObject("sheet1",headColumnMap1,dataList1));
LinkedHashMap<String, String> headColumnMap2 = Maps.newLinkedHashMap();
headColumnMap2.put("className","班级");
headColumnMap2.put("name","学生信息");
headColumnMap2.put("sex","学生信息,性别");
headColumnMap2.put("age","学生信息,年龄");
List<Map<String, Object>> dataList2 = new ArrayList<>();
for (int i = 0; i < 5; i++) {
Map<String, Object> dataMap = Maps.newHashMap();
dataMap.put("className", "一年级");
dataMap.put("name", "张三" + i);
dataMap.put("sex", "男");
dataMap.put("age", i+20);
dataList2.add(dataMap);
}
sheetObjects.add(new SheetObject("sheet2",headColumnMap2,dataList2));
byte[] stream = exportExcelFileSheets(sheetObjects);
FileOutputStream outputStream = new FileOutputStream(new File("/Users/eric/Documents/easyexcel-export-user6.xlsx"));
outputStream.write(stream);
outputStream.close();
}
