EasyExcel官网:点击查看
1、动态合并单元格
此处主要根据自定义处理类ExcelFillCellMergeStrategy进行处理,具体内容可看代码注释。
1.1 前端调用controller
@PostMapping("/download/template")public void toDoExport(HttpServletResponse response) {// 设置模拟表头(此处为多表头,也可以传入单表头)List<List<String>> headList = new ArrayList<>();List<String> heads1 = new ArrayList<>();heads1.add("总表头");heads1.add("表头1");headList.add(heads1);List<String> heads2 = new ArrayList<>();heads2.add("总表头");heads2.add("表头2");headList.add(heads2);List<String> heads3 = new ArrayList<>();heads3.add("总表头");heads3.add("表头3");headList.add(heads3);List<String> heads4 = new ArrayList<>();heads4.add("总表头");heads4.add("表头4");headList.add(heads4);// 设置模拟内容List<List<String>> data = new ArrayList<>();List<String> data1 = new ArrayList<>(); // 第1行的内容data1.add("内容1");data1.add("内容1-2");data1.add("内容3");data1.add("内容4");List<String> data2 = new ArrayList<>();// 第2行的内容data2.add("内容1");data2.add("内容2-2");data2.add("内容3");data2.add("内容4");data.add(data1);data.add(data2);try {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 防止中文乱码String fileName = URLEncoder.encode("导入模板名称", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");ServletOutputStream outputStream = response.getOutputStream();ExcelWriter writer = EasyExcel.write(outputStream).build();WriteSheet sheet = EasyExcel.writerSheet(0).needHead(Boolean.FALSE).sheetName("sheet页名称").build();WriteTable table1 = EasyExcel.writerTable(1).head(headList)// 设置只合并第1、2、3列,其余不合并.registerWriteHandler(new ExcelFillCellMergeStrategy(1,new int[]{0,1,2})).needHead(Boolean.TRUE).build();writer.write(data, sheet, table1); // datawriter.finish();} catch (IOException e) {e.printStackTrace();}}
1.2 批注渲染处理类(ExcelFillCellMergeStrategy)
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {/*** 合并字段的下标,如第一到五列new int[]{0,1,2,3,4}*/private int[] mergeColumnIndex;/*** 从第几行开始合并,如果表头占两行,这个数字就是2*/private int mergeRowIndex;public ExcelFillCellMergeStrategy() {}public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {//当前行int curRowIndex = cell.getRowIndex();//当前列int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {//获取当前行的当前列的数据和上一行的当前列列数据,通过上一行数据是否相同进行合并Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :cell.getNumericCellValue();Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :preCell.getNumericCellValue();// 比较当前行的第一列的单元格与上一行是否相同,相同合并当前单元格与上一行if (curData.equals(preData)) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {CellRangeAddress cellRangeAddr = mergeRegions.get(i);// 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddr.setLastRow(curRowIndex);sheet.addMergedRegion(cellRangeAddr);isMerged = true;}}// 若上一个单元格未被合并,则新增合并单元if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}
}
1.3 效果
通过传递参数控制了只合并前3列,第4列不合并,效果如图
2、自适应列宽、行高
2.1 自适应列宽
此处主要根据自定义处理类CustomCellWriteWeightConfig进行处理。
public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());if (maxColumnWidthMap == null) {maxColumnWidthMap = new HashMap<>();CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);}Integer columnWidth = this.dataLength(cellDataList, cell, isHead);if (columnWidth >= 0) {if (columnWidth > 254) {columnWidth = 254;}Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);Sheet sheet = writeSheetHolder.getSheet();sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);}}}}/*** 计算长度* @param cellDataList* @param cell* @param isHead* @return*/private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData cellData = cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return -1;} else {switch (type) {case STRING:// 换行符(数据需要提前解析好)int index = cellData.getStringValue().indexOf("\n");return index != -1 ?cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}
}
2.2 自适应行高
此处主要根据自定义处理类CustomCellWriteHeightConfig进行处理。
public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {/*** 默认高度*/private static final Integer DEFAULT_HEIGHT = 300;@Overrideprotected void setHeadColumnHeight(Row row, int relativeRowIndex) {}@Overrideprotected void setContentColumnHeight(Row row, int relativeRowIndex) {Iterator<Cell> cellIterator = row.cellIterator();if (!cellIterator.hasNext()) {return;}// 默认为 1行高度Integer maxHeight = 1;while (cellIterator.hasNext()) {Cell cell = cellIterator.next();switch (cell.getCellTypeEnum()) {case STRING:if (cell.getStringCellValue().indexOf("\n") != -1) {int length = cell.getStringCellValue().split("\n").length;maxHeight = Math.max(maxHeight, length);}break;default:break;}}row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));}
}
2.3 注册器处理
EasyExcel.registerWriteHandler(new CustomCellWriteWeightConfig()).registerWriteHandler(new CustomCellWriteHeightConfig());