1200字范文 > EasyExcel导出自定义合并单元格文件

EasyExcel导出自定义合并单元格文件

时间：2020-01-15 13:16:51

目标

需要使用阿里的EasyExcel库来导出excel，并要自定义合并单元格。

思路

这里自定义的CellWriteHandler需要将数据进行如下处理：

1.Excel每一行数据必须对应一个对象；2.每一个对象必须有ID字段。

这里使用EasyExcel的数据数组，必须达到这两个条件。合并单元格的判断逻辑如下：

判断当前行的对象ID与上一行的对象ID相等，且当前单元格对象的字段值与上一行单元格对象的字段值相等。才能够进行单元格合并。

步骤

ExportKbdAreaRuleItemVO.java

import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.write.style.ColumnWidth;import com.alibaba.excel.annotation.write.style.ContentStyle;import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;import io.swagger.annotations.ApiModelProperty;import lombok.*;import java.util.Date;/*** KBD区域规则（hq bu sd）列表回参对象**/@Builder@NoArgsConstructor@AllArgsConstructor@Data@EqualsAndHashCode@ColumnWidth(value = 28)@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)public class ExportKbdAreaRuleItemVO {@ExcelProperty(value = "id", index = 0)private String id;/** 组织架构 —— BU */@ExcelProperty(value = "BU", index = 1)private String bu;/** 组织架构 —— Region */@ApiModelProperty(value = "组织架构二级")@ExcelProperty(value = "Region", index = 2)private String region;/** 组织架构 —— Area */@ApiModelProperty(value = "组织架构三级")@ExcelProperty(value = "Area", index = 3)private String area;/** 组织架构 —— Territory */@ApiModelProperty(value = "组织架构四级")@ExcelProperty(value = "Territory", index = 4)private String territory;/** 渠道业态name*/@ApiModelProperty(value = "渠道业态name")@ExcelProperty(value = "渠道业态", index = 5)private String channelBusinessLevelName;/** 进行中的规则 */@ApiModelProperty(value = "进行中的规则")@ExcelProperty(value = "进行中规则", index = 6)private String doingName;/** 规则开始时间 */@ApiModelProperty(value = "规则开始时间")@ExcelProperty(value = "规则开始时间", index = 7)private Date ruleStartTime;/** 规则结束时间 */@ApiModelProperty(value = "规则结束时间")@ExcelProperty(value = "规则结束时间", index = 8)private Date ruleEndTime;/** 其他规则 */@ApiModelProperty(value = "其他规则")@ExcelProperty(value = "其他规则", index = 9)private String otherName;/** 失效规则 */@ApiModelProperty(value = "失效规则")@ExcelProperty(value = "上一条规则信息", index = 10)private String endChannelRuleName;/** 失效日期 */@ApiModelProperty(value = "失效日期")@ExcelProperty(value = "上一条规则失效时间", index = 11)private Date endTime;}

ExcelFillCellMergeStrategy.java

这里就是处理核心类。

import com.alibaba.excel.metadata.Head;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;public class ExcelFillCellMergeStrategy implements CellWriteHandler {// 需要从第几行开始合并，0表示第1行private final int mergeRowIndex;// 合并的哪些列，比如为4时，当前行id和上一行id相同则合并前五列private final int mergeColumnRegion;private final List<Integer> ignoreColumn;public ExcelFillCellMergeStrategy(int mergeRowIndex, int mergeColumnRegion, List<Integer> ignoreColumn) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnRegion = mergeColumnRegion;this.ignoreColumn = ignoreColumn;}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {CellWriteHandler.super.afterCellCreate(writeSheetHolder, writeTableHolder, cell, head, relativeRowIndex, isHead);// 隐藏id列writeSheetHolder.getSheet().setColumnHidden(0, true);}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {CellWriteHandler.super.afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead);//当前行int curRowIndex = cell.getRowIndex();//当前列int curColIndex = cell.getColumnIndex();if (!ignoreColumn.contains(curColIndex) && curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnRegion; i++) {if (curColIndex <= mergeColumnRegion) {mergeWithPreviousRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}/*** 当前单元格向上合并：当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列** @param cell 当前单元格* @param curRowIndex当前行* @param curColIndex当前列*/private void mergeWithPreviousRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {// 当前行的第一个CellCell curFirstCell = cell.getSheet().getRow(curRowIndex).getCell(0);Object curFirstData = curFirstCell.getCellType() == CellType.STRING ? curFirstCell.getStringCellValue() : curFirstCell.getNumericCellValue();// 上一行的第一个CellCell preFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0);Object preFirstData = preFirstCell.getCellType() == CellType.STRING ? preFirstCell.getStringCellValue() : preFirstCell.getNumericCellValue();// 当前cellObject data = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();// 上面的CellCell upCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);Object upData = upCell.getCellType() == CellType.STRING ? upCell.getStringCellValue() : upCell.getNumericCellValue();// 当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列且上一行值相同if (curFirstData.equals(preFirstData) && data.equals(upData)) {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);}}}}

使用片段

@Overridepublic void kbdAreaRuleListToExcel(HttpServletResponse response, List<KbdAreaRuleListVO> kbdAreaRuleListVOList) {try {List<ExportKbdAreaRuleItemVO> dataList = new ArrayList<>();int size = kbdAreaRuleListVOList.size();for (int i = 0; i < size; i++) {KbdAreaRuleListVO data = kbdAreaRuleListVOList.get(i);List<KbdAreaRuleListVO.BasicDTO> doingList = data.getDoingList();int doingListSize;if (CollectionUtils.isEmpty(doingList)){doingListSize = 0;} else {doingListSize = doingList.size();}List<KbdAreaRuleListVO.BasicDTO> otherList = data.getOtherList();List<KbdAreaRuleListVO.BasicTime> timeList = data.getTimeList();int timeListSize;if (CollectionUtils.isEmpty(timeList)){timeListSize = 0;} else {timeListSize = timeList.size();}int sizeMax = Math.max(doingListSize, timeListSize);if (sizeMax > 0){// 这里需要重复填充对象for (int j = 0; j < sizeMax; j++) {ExportKbdAreaRuleItemVO exportKbdAreaRuleItemVO = ExportKbdAreaRuleItemVO.builder().id(String.valueOf(i)).bu(data.getOrgLevel1Name()).region(data.getOrgLevel2Name()).area(data.getOrgLevel3Name()).territory(data.getOrgLevel4Name()).channelBusinessLevelName(data.getChannelBusinessLevelName()).endTime(data.getEndTime()).endChannelRuleName(data.getEndChannelRuleName()).otherName(CollectionUtils.isEmpty(otherList) ? "" : otherList.stream().map(KbdAreaRuleListVO.BasicDTO::getName).collect(Collectors.joining(","))).build();if (j < doingListSize){KbdAreaRuleListVO.BasicDTO doing = doingList.get(j);exportKbdAreaRuleItemVO.setDoingName(doing.getName());}if (j < timeListSize){KbdAreaRuleListVO.BasicTime time = timeList.get(j);exportKbdAreaRuleItemVO.setRuleStartTime(time.getStartTime());exportKbdAreaRuleItemVO.setRuleEndTime(time.getEnDTime());}dataList.add(exportKbdAreaRuleItemVO);}} else {ExportKbdAreaRuleItemVO exportKbdAreaRuleItemVO = ExportKbdAreaRuleItemVO.builder().id(String.valueOf(i)).bu(data.getOrgLevel1Name()).region(data.getOrgLevel2Name()).area(data.getOrgLevel3Name()).territory(data.getOrgLevel4Name()).channelBusinessLevelName(data.getChannelBusinessLevelName()).endTime(data.getEndTime()).endChannelRuleName(data.getEndChannelRuleName()).otherName(CollectionUtils.isEmpty(otherList) ? "" : otherList.stream().map(KbdAreaRuleListVO.BasicDTO::getName).collect(Collectors.joining(","))).build();dataList.add(exportKbdAreaRuleItemVO);}}// 上面就是数据的前处理response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码当然和easyexcel没有关系String fileName = URLEncoder.encode("数据.xlsx", "UTF-8");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);// 这里是设置这些列不用合并的列List<Integer> ignoreColumn = new ArrayList<>();ignoreColumn.add(6);ignoreColumn.add(7);ignoreColumn.add(8);ignoreColumn.add(10);ignoreColumn.add(11);// 这里需要设置不关闭流EasyExcelFactory.write(response.getOutputStream(), ExportKbdAreaRuleItemVO.class).autoCloseStream(Boolean.TRUE).sheet("数据").registerWriteHandler(new ExcelFillCellMergeStrategy(1, 11, ignoreColumn)).doWrite(dataList);} catch (Exception e) {// 重置responselog.error("exportList导出异常:{}", e);}}