POI builds one-to-many dynamic header, three-layer dynamic header export example
One, introduce maven dependencies
<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>
Two, dynamic header entity class
Define dynamic header data entity class
@Data
public class CellDTO {
private String name;
private List<CellDTO> childList;
}
Three, set dynamic header data
dataList: three-layer data structure, basic situation, including the first layer header, the first layer header contains the second layer header
In the basic situation, the header is fixed and can be programmed manually. Three layers are used to dynamically build the header.
/**
* Build header
*
* @param dataList header data
* @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("Basic situation");
List<CellDTO> childList1 = new ArrayList<>();
CellDTO child1 = new CellDTO();
child1.setName("Year");
CellDTO child2 = new CellDTO();
child2.setName("City");
CellDTO child3 = new CellDTO();
child3.setName("County");
CellDTO child4 = new CellDTO();
child4.setName("township it belongs to");
CellDTO child5 = new CellDTO();
child5.setName("name");
CellDTO child6 = new CellDTO();
child6.setName("level");
childList1.add(child1);
childList1.add(child2);
childList1.add(child3);
childList1.add(child4);
childList1.add(child5);
childList1.add(child6);
cell1.setChildList(childList1);
list.add(cell1);
//Get the first data indicator tree collection to form a dynamic header
BasicData basicData= dataList.get(0);
//take out
List<ClassifyDTO> classifyList = basicData.getChildren();
for (ClassifyDTO classifyDTO : classifyList) {
//level one
CellDTO cell11 = new CellDTO();
cell11.setName(classifyDTO.getName());
//Second layer total
List<CellDTO> childList22 = new ArrayList<>();
//take out
List<IndicatorDTO> indicatorList = classifyDTO.getChildren();
for (IndicatorDTO indicatorDTO : indicatorList) {
//Second floor
CellDTO child22 = new CellDTO();
child22.setName(indicatorDTO.getName());
//the third floor
List<CellDTO> childList33 = new ArrayList<>();
CellDTO child555 = new CellDTO();
child555.setName("plan value");
CellDTO child666 = new CellDTO();
child666.setName("Complete value");
childList33.add(child555);
childList33.add(child666);
//The second layer single adds the third layer total
child22.setChildList(childList33);
//Second layer indicator total Add second layer single
childList22.add(child22);
}
//The first layer adds the second layer total
cell11.setChildList(childList22);
//Add the first layer to the list
list.add(cell11);
}
}
return list;
}
Four, Excel builds dynamic header
Use the set dynamic header data object to build an excel dynamic header.
/**
* Build dynamic header
*
* @param list dynamic header data
* @param sheetName sheet page name
* @return {@code HSSFWorkbook}
*/
public static HSSFWorkbook buildHeader(List<CellDTO> list,String sheetName) {
HSSFWorkbook workbook = new HSSFWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
// center text
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//Set vertical alignment of cell content
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//Set automatic line wrapping
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();
//Increasing index of the first level (starting from 0)
int oneIncrementalIndex = 0;
//Three-layer starting position index
int startPosition = index0DataChildSize - 1;
for (CellDTO one : list) {
String oneName = one.getName();
List<CellDTO> twoChildList = one.getChildList();
if (CollUtil.isNotEmpty(twoChildList)) {
//increasing index (starting from 0) - column
int incrementalIndex = 0;
for (CellDTO two : twoChildList) {
String twoName = two.getName();
List<CellDTO> threeChildList = two.getChildList();
//the third floor
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;
}
//Reset
incrementalIndex = 0;
HSSFCell row1Cell = row1.createCell(startPosition + incrementalIndex + 1);
row1Cell.setCellValue(twoName);
row1Cell.setCellStyle(cellStyle);
//Merge cells (the number of rows remains unchanged and the number of columns is merged)
CellRangeAddress region = new CellRangeAddress(1, 1, startPosition + incrementalIndex + 1, startPosition + incrementalIndex + threeChildList.size());
sheet.addMergedRegion(region);
//Third level column + 2
startPosition = startPosition + 2;
} else {
HSSFCell row1Cell = row1.createCell(incrementalIndex);
row1Cell.setCellValue(twoName);
row1Cell.setCellStyle(cellStyle);
//Merge cells (number of columns remains unchanged, number of rows are merged)
CellRangeAddress region = new CellRangeAddress(1, 2, incrementalIndex, incrementalIndex);
sheet.addMergedRegion(region);
//Column + 1
incrementalIndex = incrementalIndex + 1;
}
}
HSSFCell row0Cell = row0.createCell(oneIncrementalIndex);
row0Cell.setCellValue(oneName);
row0Cell.setCellStyle(cellStyle);
//Merge cells (the number of rows remains unchanged and the number of columns is merged)
//Determine whether it is the basic situation or the indicator
if ("base case".equals(oneName)){
int lastCol = twoChildList.size() == 1 ? oneIncrementalIndex + 1 : oneIncrementalIndex + twoChildList.size() - 1;
CellRangeAddress region = new CellRangeAddress(0, 0, oneIncrementalIndex, lastCol);
sheet.addMergedRegion(region);
//Next first level starting index
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);
//Next first level starting index
oneIncrementalIndex = oneIncrementalIndex + twoChildList.size() * 2;
}
}
}
return workbook;
}
Five, insert business data
dataList: three-layer data structure, basic situation, including the first layer header, the first layer header contains the second layer header
workbook: set dynamic header
Take out the sheet page with the header set, and then loop to set the value of each grid
/**
* Insert business data
*
* @param workbook excel
* @param list data
*/
private void insertValue(HSSFWorkbook workbook, List<BasicData> list) {
if (!CollectionUtils.isEmpty(list)){
//Get the sheet page
HSSFSheet sheet = workbook.getSheetAt(0);
//row starting index
int rowStartIndex = 3;
//Indicator value-column starting index
int columnStartIndex = 6;
//Set cell style
CellStyle cellStyle = workbook.createCellStyle();
//Traverse the data and insert it into the table
for (BasicData basicData : list) {
//Create row
HSSFRow row = sheet.createRow(rowStartIndex);
//Column 0
HSSFCell rowCell0 = row.createCell(0);
rowCell0.setCellValue(basicData.getYear());
rowCell0.setCellStyle(cellStyle);
//Column 1
HSSFCell rowCell1 = row.createCell(1);
rowCell1.setCellValue(basicData.getCity());
rowCell1.setCellStyle(cellStyle);
//Column 2
HSSFCell rowCell2 = row.createCell(2);
rowCell2.setCellValue(basicData.getCounty());
rowCell2.setCellStyle(cellStyle);
//Column 3
HSSFCell rowCell3 = row.createCell(3);
rowCell3.setCellValue(basicData.getTownship());
rowCell3.setCellStyle(cellStyle);
//Column 4
HSSFCell rowCell4 = row.createCell(4);
rowCell4.setCellValue(basicData.getVillage());
rowCell4.setCellStyle(cellStyle);
//Column 5
HSSFCell rowCell5 = row.createCell(5);
rowCell5.setCellValue(basicData.getLevel);
rowCell5.setCellStyle(cellStyle);
//level one
List<ClassifyDTO> classifyDTOS = basicData.getChildren();
for (ClassifyDTO classifyDTO : classifyDTOS) {
//Second floor
List<IndicatorDTO> indicatorDTOList = classifyDTO.getChildren();
for (IndicatorDTO indicatorDTO : indicatorDTOList) {
//the third floor
//column - planned values
HSSFCell rowCell11 = row.createCell(columnStartIndex);
rowCell11.setCellValue(String.valueOf(indicatorDTO.getPlanValue()));
rowCell11.setCellStyle(cellStyle);
//column - completion value
HSSFCell rowCell22 = row.createCell(columnStartIndex + 1);
rowCell22.setCellValue(String.valueOf(indicatorDTO.getCompletionValue()));
rowCell22.setCellStyle(cellStyle);
//Third level column index + 2
columnStartIndex = columnStartIndex + 2;
}
}
//row index + 1
rowStartIndex = rowStartIndex + 1;
//Reset the third level-column starting index
columnStartIndex = 6;
}
}
}
Six, export excel
/**
* Export
*
* @param workbook excel
* @param response 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("Export to Excel failed!");
}
}