pom依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
替换核心代码:
public static InputStream onlineWriter(String fileType, InputStream inputStream, JSONObject model) throws Exception {
ByteArrayOutputStream out = new ByteArrayOutputStream();
try (ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(inputStream)
.registerWriteHandler(new CustomCellWeightWeightConfig())
.registerWriteHandler(new CustomMergeStrategy())
.build()) {
// 设置自动换行,前提内容中需要加「\n」才有效
WriteCellStyle writeCellStyle = new WriteCellStyle();
writeCellStyle.setWrapped(true);
HorizontalCellStyleStrategy cellStyleStrategy = new HorizontalCellStyleStrategy(null, writeCellStyle);
WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(cellStyleStrategy).build();
excelWriter.fill(model, writeSheet);
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
for (Map.Entry<String, Object> entry : model.entrySet()) {
if (entry.getValue() instanceof ArrayList) {
JSONArray jsonArray = JSON.parseArray(JSONObject.toJSONString(entry.getValue()));
FillWrapper fillWrapper = new FillWrapper(entry.getKey(), jsonArray);
excelWriter.fill(fillWrapper, fillConfig, writeSheet);
}
}
excelWriter.finish();
}
return new ByteArrayInputStream(out.toByteArray());
}
自定义调整行高策略:
/**
* 填充内容包含\n换行符,重新计算行高
*/
public class CustomCellWeightWeightConfig extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || StringUtils.isNotEmpty(cellDataList);
if (needSetWidth) {
//包含\n的内容修改行高
String cellValue = getCellValue(cell);
boolean contains = StringUtils.contains(cellValue, ExcelTemplateUtil.WRAPPED);
if (contains) {
int rows = cellValue.split(ExcelTemplateUtil.WRAPPED).length;
float height = cell.getRow().getHeightInPoints();
cell.getRow().setHeightInPoints(rows * height);
}
}
}
public static String getCellValue(Cell cell) {
String cellValue;
switch (cell.getCellType()) {
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
default:
cellValue = "";
break;
}
return cellValue;
}
}
自定义合并表格策略:
/**
* 表格填充 每一列都赋值上一列的格式,确保列格式一致
*/
public class CustomMergeStrategy extends AbstractMergeStrategy {
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if(relativeRowIndex==null ||relativeRowIndex==0){
return;
}
int rowIndex = cell.getRowIndex();
int colIndex = cell.getColumnIndex();
sheet=cell.getSheet();
Row preRow = sheet.getRow(rowIndex - 1);
Cell preCell = preRow.getCell(colIndex);//获取上一行的该格
List<CellRangeAddress> list = sheet.getMergedRegions();
for (int i = 0; i < list.size(); i++) {
CellRangeAddress cellRangeAddress = list.get(i);
if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) {
int lastColIndex = cellRangeAddress.getLastColumn();
int firstColIndex = cellRangeAddress.getFirstColumn();
if (lastColIndex > firstColIndex) {
int lastRowIndex = cellRangeAddress.getLastRow();
CellRangeAddress cra = new CellRangeAddress(rowIndex, rowIndex, firstColIndex, lastColIndex);
sheet.addMergedRegion(cra);
//设置合并区域cra的边框
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
return;
}
}
}
}
}