动态生成excel，内容跟随表头填充
组装调用工具方法：
List<FileTitleEntity> titleEntityList = new ArrayList<>();
titleEntityList.add(new FileTitleEntity("name", "姓名"));
titleEntityList.add(new FileTitleEntity("idNumber", "证件号码"));
titleEntityList.add(new FileTitleEntity("projectDistrict", "参与项目行政区划"));
titleEntityList.add(new FileTitleEntity("nationality", "民族"));
titleEntityList.add(new FileTitleEntity("address", "住址"));
titleEntityList.add(new FileTitleEntity("phoneNumber", "联系电话"));
titleEntityList.add(new FileTitleEntity("subsidyAmount", "补贴金额(元)"));
titleEntityList.add(new FileTitleEntity("bankCategory", "银行类别"));
titleEntityList.add(new FileTitleEntity("accountName", "开户姓名"));
titleEntityList.add(new FileTitleEntity("bankAccount", "银行账号"));
titleEntityList.add(new FileTitleEntity("remarks", "备注"));
List<Map<String, Object>> sheetMapList = new ArrayList<>();
Map<String, Object> sheetOne = new HashMap<>();
sheetOne.put("sheetName", "Sheet1");
sheetOne.put("sourceList", "组装的List<String,String>数组");
sheetOne.put("titleEntityList", titleEntityList);
sheetMapList.add(sheetOne);
try {
FileUtils.exportDynamicExcel(response, "测试模板.xlsx", sheetMapList);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("系统导出异常，请联系管理员！");
}
组装的List<String,String>数组，如下：
List<Map<String, String>> sourceThreeList = regionExcels.stream().map(template->{
Map<String, String> map = new HashMap<>();
map.put("姓名", template.getRegionId());
map.put("证件号码", template.getRegionName());
return map;
}).collect(Collectors.toList());
自定义列宽：
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.List;
public class ExcelColumnWidthHandler extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (Boolean.TRUE.equals(isHead)) {
int columnWidth = cell.getStringCellValue().length();
columnWidth = Math.max(columnWidth * 5, 20);
if (columnWidth > 255) {
columnWidth = 255;
}
if(cell.getStringCellValue().equals("模版使用说明")){
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(),150 * 255);
}else{
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
优化后的工具类：
/**
* 动态Excel导出，根据headMap表头顺序组装sourceList对应内容，再导出
*
* @param response response
* @param fileName 文件名
* @param sheetMapList sheet页相关内容:
* sheetName名称
* titleEntityList 动态表头对象(titleEntity),例如：{"title":"name","titleName":"姓名"}
* sourceList 原数据List，例如：{"姓名":"张三"}
*/
public static void exportDynamicExcel(HttpServletResponse response, String fileName, List<Map<String, Object>> sheetMapList) throws Exception {
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Transfer-Encoding", "binary");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
fileName = new String(fileName.getBytes(), "ISO-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
//初始化输出流
ExcelWriter writer;
try {
// 创建并配置写入样式
WriteCellStyle contentStyle = new WriteCellStyle();
// 启用自动换行
contentStyle.setWrapped(true);
// 应用样式到特定列或所有列
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(contentStyle, contentStyle);
writer = EasyExcel.write(response.getOutputStream()).build();
sheetMapList.forEach(sheetMap -> {
String sheetName = sheetMap.get("sheetName").toString();
List<Map<String, String>> sourceList = (List<Map<String, String>>) sheetMap.get("sourceList");
List<FileTitleEntity> titleEntityList = (List<FileTitleEntity>) sheetMap.get("titleEntityList");
// 设置动态表头对象
List<List<String>> head = new ArrayList<>();
// 设置对应表头转换属性名列表
List<String> headRow = new ArrayList<>();
titleEntityList.forEach(titleEntity -> {
headRow.add(titleEntity.getTitleName());
});
// 列表设置列头
headRow.forEach(item -> {
List<String> list = new ArrayList<>();
list.add(item);
head.add(list);
});
// 构造数据列表
List<List<Object>> excelData = new ArrayList<>();
if (CollectionUtils.isNotEmpty(sourceList)) {
for (Map<String, String> map : sourceList) {
List<Object> row = new ArrayList<>();
titleEntityList.forEach(titleEntity -> {
String value = map.get(titleEntity.getTitleName());
// 转换为字符串，如果为null则填充空字符串
row.add(StringUtils.isNotEmpty(value) ? value : "");
});
excelData.add(row);
}
}
// writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
// EasyExcel.write(response.getOutputStream()).head(head).registerConverter(new LongStringConverter()).sheet(sheetName).doWrite(excelData);
writer.write(excelData, EasyExcel.writerSheet(sheetName).head(head).registerWriteHandler(new ExcelColumnWidthHandler()).registerWriteHandler(horizontalCellStyleStrategy).registerConverter(new LongStringConverter()).build());
});
writer.finish();
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
try {
response.flushBuffer();
} catch (IOException ignored) {
}
}
}
工具方法：
/**
* 动态Excel导出，先sourceList转换成List<targetClass>，再导出
*
* @param response response
* @param fileName 文件名
* @param sheetName sheetName
* @param headMap 动态表头对象(Map<String,String>),例如：{"id":"ID","name":"姓名"}
* @param sourceList 原数据List，例如：{"ID":"1","姓名":"张三"}
*/
public static void exportDynamicExcel(HttpServletResponse response, String fileName, String sheetName, List<Map<String, String>> sourceList, Map<String,String> headMap) throws Exception {
// response.setContentType("application/vnd.ms-excel");
// response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
// response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1) + ".xlsx");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Transfer-Encoding", "binary");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
fileName = new String(fileName.getBytes(), "ISO-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
// 设置动态表头对象
List<List<String>> head = new ArrayList<>();
// 设置对应表头转换属性名列表
// List<String> headPropertyList = new ArrayList<>();
List<String> headRow = new ArrayList<>();
headMap.forEach((k, v) -> {
headRow.add(v);
});
// 列表设置列头
headRow.forEach(item -> {
List<String> list = new ArrayList<>();
list.add(item);
head.add(list);
});
// 构造数据列表
List<List<Object>> excelData = new ArrayList<>();
for (Map<String, String> map : sourceList) {
List<Object> row = new ArrayList<>();
headMap.forEach((k, v) -> {
String value = map.get(k);
// 转换为字符串，如果为null则填充空字符串
row.add(StringUtils.isNotEmpty(value) ? value : "");
});
excelData.add(row);
}
EasyExcel.write(response.getOutputStream()).head(head).registerConverter(new LongStringConverter()).sheet(sheetName).doWrite(excelData);
}
标签：titleEntityList,easyExcel,List,表头,add,ArrayList,new,动态,response From： https://www.cnblogs.com/mangwusuozhi/p/18299134