easyExec 合并单元格
添加依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
自定义注解,用于判断是否需要合并以及合并的主键
import java.lang.annotation.*;
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {
boolean needMerge() default false;
boolean isPk() default false;
}
注解实现
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.mstdt.admin.b2c.service.CustomMerge;
import org.apache.commons.lang3.StringUtils;
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.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
public class CustomMergeStrategy implements RowWriteHandler {
private List<Integer> pkColumnIndex = new ArrayList<>();
private List<Integer> needMergeColumnIndex = new ArrayList<>();
private Set<String> orderNoSet = new HashSet<>();
private Class<?> elementType;
public CustomMergeStrategy(Class<?> elementType) {
this.elementType = elementType;
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
return;
}
Sheet sheet = writeSheetHolder.getSheet();
Row titleRow = sheet.getRow(0);
if (pkColumnIndex.isEmpty()) {
this.lazyInit(writeSheetHolder);
}
if (row.getRowNum() <= 1) {
return;
}
Row lastRow = sheet.getRow(row.getRowNum() - 1);
boolean margeBol = true;
for (Integer pkIndex : pkColumnIndex) {
String lastKey = lastRow.getCell(pkIndex).getCellType() == CellType.STRING ? lastRow.getCell(pkIndex).getStringCellValue() : String.valueOf(lastRow.getCell(pkIndex).getNumericCellValue());
String currentKey = row.getCell(pkIndex).getCellType() == CellType.STRING ? row.getCell(pkIndex).getStringCellValue() : String.valueOf(row.getCell(pkIndex).getNumericCellValue());
if (!StringUtils.equalsIgnoreCase(lastKey, currentKey)) {
if (!orderNoSet.contains(currentKey)) {
margeBol = false;
break;
}
} else {
orderNoSet.add(currentKey);
}
}
if (margeBol) {
for (Integer needMerIndex : needMergeColumnIndex) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
needMerIndex, needMerIndex);
sheet.addMergedRegionUnsafe(cellRangeAddress);
Cell cell = row.getCell(needMerIndex);
cell.setCellValue("");
}
}
}
private void lazyInit(WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
Row titleRow = sheet.getRow(0);
Class<?> eleType = this.elementType;
Field[] fields = eleType.getDeclaredFields();
for (Field theField : fields) {
ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
if (null == easyExcelAnno) {
continue;
}
CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);
if (null == customMerge) {
continue;
}
int index = easyExcelAnno.index();
if (customMerge.isPk()) {
pkColumnIndex.add(index);
}
if (customMerge.needMerge()) {
needMergeColumnIndex.add(index);
}
}
if (pkColumnIndex.isEmpty()) {
throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
}
}
}
controller
public void exportMiddleOrderRefundWaterExcel(B2cOrderListSearchRequest params, HttpServletResponse response)
throws Exception {
List<Demo> list = new ArrayList<>();
setExcelRespProp(response, "demo");
EasyExcel.write(response.getOutputStream())
.head(Demo.class)
.registerWriteHandler(new CustomMergeStrategy(Demo.class))
.excelType(ExcelTypeEnum.XLSX)
.sheet("demo")
.doWrite(list);
}
private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(rawFileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
}
实体
@Data
public class Demo{
@ApiModelProperty(value = "订单类型(1:平台订单 2:中台订单),格式化前端使用")
@ExcelProperty( value = "订单类型",index = 0)
@CustomMerge(needMerge = true)
@ColumnWidth(12)
private String orderSourceStr;
@ApiModelProperty(value = "平台订单号")
@ExcelProperty( value = "订单号",index = 1)
@CustomMerge(needMerge = true,isPk = true)
@ColumnWidth(18)
private String orderNo;
@ExcelProperty( value = "规格",index = 22)
@ColumnWidth(26)
private String specification;
}