excel导出、可自定义合并单元格策略

ExcelFillCellMergeStrategy时注册合并策略的工具类

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>2.2.6</version>
</dependency>

// 工具类
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 {

    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategy() {
    }

    public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public 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;
                }
            }
        }
    }



    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    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);
            }
        }
    }
}

// Controller
@RequestMapping(value = { "/download.do" }, method = RequestMethod.POST)
@ResponseBody
public void download(HttpServletRequest request,HttpServletResponse resp) throws IOException{
	   int[] mergeColumnIndex = {1};// 需要合并的列
       int mergeRowIndex = 2;// 开始合并的行数，一般去除表头在第二行开始
       resp.setContentType("application/vnd.ms-excel");
       resp.setCharacterEncoding("utf-8");
	   SimpleDateFormat format= new SimpleDateFormat("yyyyMMddHHmmss");
       String time =format.format(new Date());
       // 需要导出的数据
       List<ShopOrderBean2> beanList = Lists.newArrayList();
       // 去除以下null
       beanList = beanList.stream().filter(Objects::nonNull).collect(Collectors.toList());
	   String fileName = "订单export" + time;	
       resp.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
       // 如果需要合并，打开下边注释即可
       EasyExcel.write(resp.getOutputStream(), 	javaBean.class).excelType(ExcelTypeEnum.XLSX)
	  .autoCloseStream(Boolean.TRUE)
	  //.registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, 	mergeColumnIndex))
	  .sheet(fileName)
	  .doWrite(beanList);
}

// javaBean

import java.io.Serializable;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@Builder
@NoArgsConstructor
public class ShopOrderBean2 implements Serializable {

	private static final long serialVersionUID = 1L;

	/**
	 * 订单编号
	 */
	@ExcelProperty(value = "订单编号", index = 1)
	@ColumnWidth(value = 30)
	private String ORDER_ID;
	
	/**
	 * 子订单编号
	 */
	@ExcelProperty(value = "子订单编号", index = 2)
	@ColumnWidth(value = 30)
	private String OERDER_NO;

}