最近有一个业务需要,导出业务数据,多表头,如下图
是多表头的。
使用easypoi可以根据注解导出单一表头的数据,也可以自定义模板导出复杂表头的数据。自定义模板将每一个单元表格数据都是一个map,根据key找到对应的行,如果对应的表头是单一表头,value就是数据,如果是复杂表头,则value可以是List
[
{
"简单表头1": "数据1"
},
{
"简单表头2": "数据2"
},
{
"二级复杂表头1": [
{
"二级表子标题1": "二级数据1"
},
{
"二级表子标题2": "二级数据2"
}
]
},
{
"三级复杂表头1": [
{
"二级表子标题1": [
{
"二级表子标题1": "二级数据1"
},
{
"二级表子标题2": "二级数据2"
}
]
},
{
"二级表子标题2": "二级数据2"
}
]
}
]
了解了easypoi创建复杂表头的数据结构,接下来看一下easypoi的方法:
/**
* 根据Map创建对应的Excel
* @param entity
* 表格标题属性
* @param entityList
* Map对象列表
* @param dataSet
* Excel对象数据List
*/
public static Workbook exportExcel(ExportParams entity, List<ExcelExportEntity> entityList,
Collection<?> dataSet) {
Workbook workbook = getWorkbook(entity.getType(),dataSet.size());;
new ExcelExportService().createSheetForMap(workbook, entity, entityList, dataSet);
return workbook;
}
使用上面的方法,自定义列表对象。简单介绍一下上面的方法,ExportParams entity 是创建导出的excel的基本属性的,比如文件名称,文件的sheetName;
List entityList 方法注释上解释的是 Map对象列表,在创建复杂表头的时候,我们需要创建多个ExcelExportEntity
通过看源码,可以看出来,ExcelExportEntity是根据key来映射到对应的Excel的表头的。
实现多表头的代码:
public void export(HttpServletResponse response) {
//表头设置
List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
ExcelExportEntity colEntity = new ExcelExportEntity("日期", "dt");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("产品PV", "pv");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("产品UV", "uv");
colEntity.setNeedMerge(true);
colList.add(colEntity);
ExcelExportEntity group_1 = new ExcelExportEntity("业务数据", "businessData");
List<ExcelExportEntity> exportEntities = new ArrayList<>();
for (int i = 1; i < 5; i++) {
exportEntities.add(new ExcelExportEntity("数据" + i, "data" + i));
}
group_1.setList(exportEntities);
colList.add(group_1);
//文件数据
List<Map<String, Object>> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Map<String, Object> valMap = new HashMap<String, Object>();
valMap.put("dt", "日期" + i);
valMap.put("pv", "pv" + i);
valMap.put("uv", "uv" + i);
{
List<Map<String, Object>> list_1 = new ArrayList<Map<String, Object>>();
Map<String, Object> valMap_1 = new HashMap<String, Object>();
for (int j = 1; j < 5; j++) {
valMap_1.put("data" + j, "数据" + j);
}
list_1.add(valMap_1);
valMap.put("businessData", list_1);
}
}
//导出
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("数据表", "数据"), colList, list);
setResponseHeader(response, LocalDateTime.now().toString());
write(workbook, response);
}
/**
* 设置导出文件头
*
* @param response 相应servlet
* @param fileName 文件名称--不能是中文
*/
private static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
fileName = new String(fileName.getBytes(), StandardCharsets.UTF_8);
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
log.error("", ex);
}
}
/**
* 将book 导出excel
*
* @param book
* @param response
*/
private static void write(Workbook book, HttpServletResponse response) {
try {
OutputStream os = response.getOutputStream();
book.write(os);
os.flush();
os.close();
} catch (IOException e) {
log.error("export excel exception", e);
}
}