The effect is as follows
Specific code:
Dynamic header data
/**
* Export rent property header
*
* @return
*/
public List<List<String>> buildHead() {<!-- -->
R<List<RentalProperty>> rentalPropertyR = smartPropertyClient.getRentalPropertyListByTenantId(AuthUtil.getTenantId());
List<String> feeProjectNameList = new ArrayList<>();
if (rentalPropertyR.isSuccess() & amp; & amp; rentalPropertyR.getData().size() > 0) {<!-- -->
for (RentalProperty rentalProperty : rentalPropertyR.getData()) {<!-- -->
feeProjectNameList.add(rentalProperty.getName());
}
}
List<List<String>> head = new ArrayList<>();
String headFirst = "Historical bill import template\
(statistical starting time: May 2018, ending time: the month before the month of filling in the form)";
String headSecond = "Instructions for filling in the form:\
" +
"1. Items marked with * are required, and the contracts under each company are stored in a separate file\
" +
"2. This data is an example, payment cycle: monthly\
" +
"3. Each start time and end time are the billing dates of the contract payment cycle: if quarterly, the start time is 2023/1/5 and the end time is the last month, and so on.\
" +
"4. Supports batch import, just fill in the bill content of all contracts directly in the form\
" +
"5. Please make sure that the contract number and bill content are filled in correctly";
head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*Contract Number", "*Contract Number"));
head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "belonging to the property", "belonging to the property"));
head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "House Description", "House Description"));
head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*starting time", "*starting time"));
head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*Termination time", "*Termination time"));
feeProjectNameList.stream()
.forEach(temp -> head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*Items Receivable", temp)));
head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*actual receipt items", "transfer actual receipts (yuan)"));
head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*Uncollected rent (yuan)", "*Uncollected rent (yuan)"));
return head;
}
Download method
/**
* Export execl
*
* @param response output stream
* @param fileName file name
* @param headList header
* @param data data
* @throwsIOException
*/
public static void download2(HttpServletResponse response, String fileName, List<List<String>> headList,
List data) throws IOException {<!-- -->
String excelName = StringUtil.isNotBlank(fileName) ? fileName : "" + DateUtil.format(new java.util.Date(), "yyyy-MM");
response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
excelName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\ + ", " ");
response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + excelName + ".xlsx");
//The main title and subtitle are 0-4 in excel, a total of five lines
List<Integer> columnIndexes = IntStream.range(0, 4).boxed().collect(Collectors.toList());
//Custom title and content strategy (specifically defined below)
CellStyleStrategy cellStyleStrategy = new CellStyleStrategy(columnIndexes, new WriteCellStyle(), new WriteCellStyle());
EasyExcel.write(response.getOutputStream())
.registerWriteHandler(getData2Style())
.registerWriteHandler(cellStyleStrategy)
.inMemory(true) // Rich text
.registerWriteHandler(new WriteHandlerStrategy())
.registerWriteHandler(new CellRowHeightStyleStrategy())
.head(headList).excelType(ExcelTypeEnum.XLSX)
.sheet("sheet1")
.doWrite(data);
}
public static HorizontalCellStyleStrategy getData2Style() {<!-- -->
//Create a style for setting content cells with borders
WriteCellStyle borderedCellStyle = new WriteCellStyle();
borderedCellStyle.setBorderTop(BorderStyle.THIN);
borderedCellStyle.setBorderBottom(BorderStyle.THIN);
borderedCellStyle.setBorderLeft(BorderStyle.THIN);
borderedCellStyle.setBorderRight(BorderStyle.THIN);
borderedCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return new HorizontalCellStyleStrategy(borderedCellStyle, borderedCellStyle);
}
The most important method
/**
* Description: - Set the style of the table header and fill content
*
* @author Lin Li
* @date 2023/10/13 16:17
*/
public class CellStyleStrategy extends HorizontalCellStyleStrategy {<!-- -->
private final WriteCellStyle headWriteCellStyle;
private final WriteCellStyle contentWriteCellStyle;
private final List<Integer> columnIndexes;
public CellStyleStrategy(List<Integer> columnIndexes, WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {<!-- -->
this.columnIndexes = columnIndexes;
this.headWriteCellStyle = headWriteCellStyle;
this.contentWriteCellStyle = contentWriteCellStyle;
}
@Override
protected void setHeadCellStyle(CellWriteHandlerContext context) {<!-- -->
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("宋体");
headWriteFont.setFontHeightInPoints((short) 11);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//Set header style
//Set the header font to bold
headWriteFont.setBold(false);
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//Apply different styles to headers at different levels based on row index
if (context.getRowIndex() == 1 || (context.getRowIndex() >= 3 & amp; & amp; context.getRowIndex() <= 4)) {<!-- -->
//Level 1, 3, and 4 header styles (center-aligned)
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // Center alignment
} else if (context.getRowIndex() == 2) {<!-- -->
// Secondary header style (left aligned)
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT); // Left aligned
}
if (stopProcessing(context)) {<!-- -->
return;
}
WriteCellData<?> cellData = context.getFirstCellData();
WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());
}
Set the * number to red
/**
* Description:
*
* @author Lin Li
* @date 2023/10/16 16:14
*/
public class WriteHandlerStrategy implements CellWriteHandler {<!-- -->
@Override
public void beforeCellCreate(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
final Row row, final Head head, final Integer columnIndex,
final Integer relativeRowIndex, final Boolean isHead) {<!-- -->
}
@Override
public void afterCellCreate(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
final Cell cell, final Head head, final Integer relativeRowIndex, final Boolean isHead) {<!-- -->
}
public void afterCellDispose(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
final List<WriteCellData<?>> list, final Cell cell, final Head head, final Integer integer,
final Boolean aBoolean) {<!-- -->
if (cell.getStringCellValue().contains("*")) {<!-- -->
Sheet sheet = writeSheetHolder.getSheet();
Workbook workbook = sheet.getWorkbook();
//xlsx format, if it is an old version format, use HSSFRichTextString
XSSFRichTextString richString = new XSSFRichTextString(cell.getStringCellValue());
Font font = workbook.createFont();
font.setColor(Font.COLOR_RED);
// From where to where, you can set it to whatever font you want startIndex, endIndex
richString.applyFont(0, 1, font);
// Set it back to each cell
cell.setCellValue(richString);
}
}
}
Set row height
public class CellRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {<!-- -->
@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {<!-- -->
}
@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {<!-- -->
//Set the main title row height to 17.7
if (relativeRowIndex == 2) {<!-- -->
//If excel needs to display a row height of 15, then set it here to 15*20=300
row.setHeight((short) (3200));
}
}
}
