EasyExcel导出自定义合并单元格文件
easyexcel
快速、简洁、解决大文件内存溢出的java处理Excel工具
项目地址：https://gitcode.com/gh_mirrors/ea/easyexcel
免费下载资源
·
目标
需要使用阿里的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;
}
@Override
public 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);
}
@Override
public 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) {
// 当前行的第一个Cell
Cell curFirstCell = cell.getSheet().getRow(curRowIndex).getCell(0);
Object curFirstData = curFirstCell.getCellType() == CellType.STRING ? curFirstCell.getStringCellValue() : curFirstCell.getNumericCellValue();
// 上一行的第一个Cell
Cell preFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0);
Object preFirstData = preFirstCell.getCellType() == CellType.STRING ? preFirstCell.getStringCellValue() : preFirstCell.getNumericCellValue();
// 当前cell
Object data = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
// 上面的Cell
Cell 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);
}
}
}
}
使用片段
@Override
public 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) {
// 重置response
log.error("exportList导出异常:{}", e);
}
}
总结
这里EasyExcel，使用在java代码里进行Excel样式设定，虽然也有模板方式，但这里就不介绍了。
参考：
GitHub 加速计划 / ea / easyexcel
31.64 K
7.47 K
下载
快速、简洁、解决大文件内存溢出的java处理Excel工具
最近提交(Master分支：3 个月前 )
c42183df
Bugfix 3 个月前
efa7dff6 * 重新加回 `commons-io`
3 个月前
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
4
0- 0
已为社区贡献6条内容
所有评论(0)