POI构建一对多动态表头 , 三层动态表头导出样例
一 , 引入maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
二 , 动态表头实体类
定义动态表头数据实体类
@Data
public class CellDTO {
private String name;
private List<CellDTO> childList;
}
三 , 设置动态表头数据
dataList: 三层数据结构, 基本情况, 包含第一层表头, 第一层表头里包含第二层表头
基本情况表头是固定的, 可以手动写死, 三层动态去构建表头
/**
* 构建表头
*
* @param dataList 表头数据
* @return {@code List<CellDTO>}
*/
private List<CellDTO> buildHeaderData(List<BasicData> dataList) {
List<CellDTO> list = new ArrayList<>();
if (!CollectionUtils.isEmpty(dataList)){
CellDTO cell1 = new CellDTO();
cell1.setName("基本情况");
List<CellDTO> childList1 = new ArrayList<>();
CellDTO child1 = new CellDTO();
child1.setName("年份");
CellDTO child2 = new CellDTO();
child2.setName("所属市");
CellDTO child3 = new CellDTO();
child3.setName("所属县");
CellDTO child4 = new CellDTO();
child4.setName("所属乡镇");
CellDTO child5 = new CellDTO();
child5.setName("名称");
CellDTO child6 = new CellDTO();
child6.setName("层级");
childList1.add(child1);
childList1.add(child2);
childList1.add(child3);
childList1.add(child4);
childList1.add(child5);
childList1.add(child6);
cell1.setChildList(childList1);
list.add(cell1);
//取出第一条数据指标树集合, 组成动态表头
BasicData basicData= dataList.get(0);
//取出
List<ClassifyDTO> classifyList = basicData.getChildren();
for (ClassifyDTO classifyDTO : classifyList) {
//第一层
CellDTO cell11 = new CellDTO();
cell11.setName(classifyDTO.getName());
//第二层 总
List<CellDTO> childList22 = new ArrayList<>();
//取出
List<IndicatorDTO> indicatorList = classifyDTO.getChildren();
for (IndicatorDTO indicatorDTO : indicatorList) {
//第二层
CellDTO child22 = new CellDTO();
child22.setName(indicatorDTO.getName());
//第三层
List<CellDTO> childList33 = new ArrayList<>();
CellDTO child555 = new CellDTO();
child555.setName("计划值");
CellDTO child666 = new CellDTO();
child666.setName("完成值");
childList33.add(child555);
childList33.add(child666);
//第二层单 添加第三层总
child22.setChildList(childList33);
//第二层 指标总 添加第二层单
childList22.add(child22);
}
//第一层 添加第二层总
cell11.setChildList(childList22);
//list添加 第一层单
list.add(cell11);
}
}
return list;
}
四 , excel构建动态表头
用设置好的动态表头数据对象, 去构建excel动态表头
/**
* 构建动态表头
*
* @param list 动态表头数据
* @param sheetName sheet页名称
* @return {@code HSSFWorkbook}
*/
public static HSSFWorkbook buildHeader(List<CellDTO> list,String sheetName) {
HSSFWorkbook workbook = new HSSFWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
// 文字居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置单元格内容垂直对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
cellStyle.setWrapText(true);
HSSFSheet sheet = workbook.createSheet(sheetName);
HSSFRow row0 = sheet.createRow(0);
HSSFRow row1 = sheet.createRow(1);
HSSFRow row2 = sheet.createRow(2);
CellDTO index0Data = list.get(0);
int index0DataChildSize = index0Data.getChildList().size();
//第一层递增索引(从0开始)
int oneIncrementalIndex = 0;
//三层起始位置索引
int startPosition = index0DataChildSize - 1;
for (CellDTO one : list) {
String oneName = one.getName();
List<CellDTO> twoChildList = one.getChildList();
if (CollUtil.isNotEmpty(twoChildList)) {
//递增索引(从0开始) - 列
int incrementalIndex = 0;
for (CellDTO two : twoChildList) {
String twoName = two.getName();
List<CellDTO> threeChildList = two.getChildList();
//第三层
if (CollUtil.isNotEmpty(threeChildList)) {
for (CellDTO three : threeChildList) {
String threeName = three.getName();
HSSFCell row2Cell = row2.createCell(startPosition + incrementalIndex + 1);
row2Cell.setCellValue(threeName);
row2Cell.setCellStyle(cellStyle);
//+1
incrementalIndex = incrementalIndex + 1;
}
//重置
incrementalIndex = 0;
HSSFCell row1Cell = row1.createCell(startPosition + incrementalIndex + 1);
row1Cell.setCellValue(twoName);
row1Cell.setCellStyle(cellStyle);
//合并单元格(行数不变,列数进行合并)
CellRangeAddress region = new CellRangeAddress(1, 1, startPosition + incrementalIndex + 1, startPosition + incrementalIndex + threeChildList.size());
sheet.addMergedRegion(region);
//第三层 列 + 2
startPosition = startPosition + 2;
} else {
HSSFCell row1Cell = row1.createCell(incrementalIndex);
row1Cell.setCellValue(twoName);
row1Cell.setCellStyle(cellStyle);
//合并单元格(列数不变,行数进行合并)
CellRangeAddress region = new CellRangeAddress(1, 2, incrementalIndex, incrementalIndex);
sheet.addMergedRegion(region);
//列+1
incrementalIndex = incrementalIndex + 1;
}
}
HSSFCell row0Cell = row0.createCell(oneIncrementalIndex);
row0Cell.setCellValue(oneName);
row0Cell.setCellStyle(cellStyle);
//合并单元格(行数不变,列数进行合并)
//判断是基本情况还是指标
if ("基本情况".equals(oneName)){
int lastCol = twoChildList.size() == 1 ? oneIncrementalIndex + 1 : oneIncrementalIndex + twoChildList.size() - 1;
CellRangeAddress region = new CellRangeAddress(0, 0, oneIncrementalIndex, lastCol);
sheet.addMergedRegion(region);
//下个第一层起始索引
oneIncrementalIndex = oneIncrementalIndex + twoChildList.size();
}else {
int lastCol = twoChildList.size() == 1 ? oneIncrementalIndex + 1 : oneIncrementalIndex + twoChildList.size() * 2 - 1;
CellRangeAddress region = new CellRangeAddress(0, 0, oneIncrementalIndex, lastCol);
sheet.addMergedRegion(region);
//下个第一层起始索引
oneIncrementalIndex = oneIncrementalIndex + twoChildList.size() * 2;
}
}
}
return workbook;
}
五 , 塞入业务数据
dataList: 三层数据结构, 基本情况, 包含第一层表头, 第一层表头里包含第二层表头
workbook: 设置好的动态表头
取出设置好表头的sheet页, 然后循环去设置每一个格子的值
/**
* 塞入业务数据
*
* @param workbook excel
* @param list 数据
*/
private void insertValue(HSSFWorkbook workbook, List<BasicData> list) {
if (!CollectionUtils.isEmpty(list)){
//取出sheet页
HSSFSheet sheet = workbook.getSheetAt(0);
//行起始索引
int rowStartIndex = 3;
//指标值-列起始索引
int columnStartIndex = 6;
//设置单元格样式
CellStyle cellStyle = workbook.createCellStyle();
//遍历数据, 塞入表格
for (BasicData basicData : list) {
//创建行
HSSFRow row = sheet.createRow(rowStartIndex);
//列0
HSSFCell rowCell0 = row.createCell(0);
rowCell0.setCellValue(basicData.getYear());
rowCell0.setCellStyle(cellStyle);
//列1
HSSFCell rowCell1 = row.createCell(1);
rowCell1.setCellValue(basicData.getCity());
rowCell1.setCellStyle(cellStyle);
//列2
HSSFCell rowCell2 = row.createCell(2);
rowCell2.setCellValue(basicData.getCounty());
rowCell2.setCellStyle(cellStyle);
//列3
HSSFCell rowCell3 = row.createCell(3);
rowCell3.setCellValue(basicData.getTownship());
rowCell3.setCellStyle(cellStyle);
//列4
HSSFCell rowCell4 = row.createCell(4);
rowCell4.setCellValue(basicData.getVillage());
rowCell4.setCellStyle(cellStyle);
//列5
HSSFCell rowCell5 = row.createCell(5);
rowCell5.setCellValue(basicData.getLevel);
rowCell5.setCellStyle(cellStyle);
//第一层
List<ClassifyDTO> classifyDTOS = basicData.getChildren();
for (ClassifyDTO classifyDTO : classifyDTOS) {
//第二层
List<IndicatorDTO> indicatorDTOList = classifyDTO.getChildren();
for (IndicatorDTO indicatorDTO : indicatorDTOList) {
//第三层
//列 - 计划值
HSSFCell rowCell11 = row.createCell(columnStartIndex);
rowCell11.setCellValue(String.valueOf(indicatorDTO.getPlanValue()));
rowCell11.setCellStyle(cellStyle);
//列 - 完成值
HSSFCell rowCell22 = row.createCell(columnStartIndex + 1);
rowCell22.setCellValue(String.valueOf(indicatorDTO.getCompletionValue()));
rowCell22.setCellStyle(cellStyle);
//第三层 列索引 + 2
columnStartIndex = columnStartIndex + 2;
}
}
//行索引 + 1
rowStartIndex = rowStartIndex + 1;
//重置 第三层-列起始索引
columnStartIndex = 6;
}
}
}
六 , 导出excel
/**
* 导出
*
* @param workbook excel
* @param response 响应
*/
protected static void write(HSSFWorkbook workbook, HttpServletResponse response){
try {
String fileType = ".xlsx";
String fileName = "excel" + fileType;
String attachment = "attachment; filename=" + URLEncoder.encode(fileName,"UTF-8");
response.setHeader("Content-disposition", attachment);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("导出Excel失败!");
}
}