poi生成excel大数据量的合并单元格操作优化

之前根据官网给的api和example，对excel单元格的合并操作使用下方的代码（poi版本 3.12）

public class MergedCells {
   public static void main(String[] args) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("new sheet");

        HSSFRow row = sheet.createRow(1);
        HSSFCell cell = row.createCell(1);
        cell.setCellValue("This is a test of merging");

        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
        wb.close();
    }
}

当使用支持07以上的XSSFWorkbook和SXSSFWorkbook的时候同样可以这样，代码如下：

private static void mergeWithXSSF() throws  IOException{
       XSSFWorkbook wb = new XSSFWorkbook();
       XSSFSheet sheet = wb.createSheet("new sheet");

       XSSFRow row = sheet.createRow(1);
       XSSFCell cell = row.createCell(1);
       cell.setCellValue("This is a test of merging");
       sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));
        
       // Write the output to a file
       FileOutputStream fileOut = new FileOutputStream("d:/temp/workbook1.xlsx");
       wb.write(fileOut);
       fileOut.close();
       wb.close();
 }

但是当mergeCell的次数非常大（数万~数十万）时候，对cpu和内存消耗不仅大大增加，而且耗时也非常大，本地测试的时候，循环次数6W次，大约需要20-30分钟；耗时核心代码如下：

for(int i=0;i<100000;i++) {
      XSSFRow row = sheet.createRow(i);
      XSSFCell cell = row.createCell(1);
      cell.setCellValue("This is a test of merging");
      sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
  }

官网版本是3.16以后，我更新后发现 sheet 多了一个合并单元格的方法 addMergedRegionUnsafe,比addMergedRegion少了一些检测异常的过程

源码如下：

public int addMergedRegion(CellRangeAddress region) {
        return this.addMergedRegion(region, true);
    }

    public int addMergedRegionUnsafe(CellRangeAddress region) {
        return this.addMergedRegion(region, false);
    }

    private int addMergedRegion(CellRangeAddress region, boolean validate) {
        if(region.getNumberOfCells() < 2) {
            throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells");
        } else {
            region.validate(SpreadsheetVersion.EXCEL2007);
            if(validate) {
                this.validateArrayFormulas(region);
                this.validateMergedRegions(region);
            }

            CTMergeCells ctMergeCells = this.worksheet.isSetMergeCells()?this.worksheet.getMergeCells():this.worksheet.addNewMergeCells();
            CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
            ctMergeCell.setRef(region.formatAsString());
            return ctMergeCells.sizeOfMergeCellArray();
        }
    }

使用这个方法之后，对于十万条数据合并单元格的本地测试就降低到了30多秒，感觉真的是质的飞跃，非常高兴，但是这只是开始，我想到既然通过减少了一些异常检测就有如此神威，是否合并单元格的方法还可以继续缩减呢？

合并单元格的核心代码在这：

//判断sheet是否已经有过合并单元格的经历，如果有就getMergeCells得到ctMergeCells 对象，否则就从addNewMergeCells获取对象（因为isSetMergeCells中使用了锁，还有一些复杂的操作，感觉会比较耗时）
CTMergeCells ctMergeCells = this.worksheet.isSetMergeCells()?this.worksheet.getMergeCells():this.worksheet.addNewMergeCells();
CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
ctMergeCell.setRef(region.formatAsString());
return ctMergeCells.sizeOfMergeCellArray();

自己控制是否合并了单元格。设置一个本地变量 int mergeCellsCount = 0；如果合并了单元格 mergeCellsCount ++; 刚才核心的代码可以改成

CTMergeCells ctMergeCells = mergeCellsCount >0 ?this.worksheet.getMergeCells():this.worksheet.addNewMergeCells();
CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
ctMergeCell.setRef(region.formatAsString());
mergeCellsCount  ++
return ctMergeCells.sizeOfMergeCellArray();

return每次合并单元格的时候还要返回 已经 合并的单元格 的数目（我们上方定义的mergeCellsCount ）

我们只要合并的过程，这个计数对我们没用，所以，自己重写了以下addMergedRegion方法

private void addMergeRegion(CellRangeAddress cra) {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.getSheetAt(0);
		CTWorksheet ctWorksheet = sheet.getCTWorksheet();

		CTMergeCells ctMergeCells = mergeCellsCount > 0 ?ctWorksheet.getMergeCells():ctWorksheet.addNewMergeCells();
		CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();

		ctMergeCell.setRef(cra.formatAsString());
		mergeCellsCount ++;
	}

总结一下：主要是CellRangeAddress 是自己定义的，自己会控制合并区域的单元格的合法性，所以去掉验证合法性的代码； 去掉返回count的代码

以上转载自：https://my.oschina.net/u/1417838/blog/edit

--------------------------------------------------------------------------------分割线--------------------------------------------------------------------------------------------

然后我自己的业务场景是有大量的合并操作，我把要合并的参数保存到map中，然后再开线程跑（我是用SXSSFWorkbook 操作的，该方法操作大数据可观）

//maps用来存要合并的参数
System.out.println("总数"+maps.size());
        if (maps.size() > 0){
			ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
			// 设置核心线程数
			executor.setCorePoolSize(8);
			// 设置最大线程数
			executor.setMaxPoolSize(8);
			// 设置队列容量
			executor.setQueueCapacity(350);
			// 设置线程活跃时间（秒）
			executor.setKeepAliveSeconds(60);
			// 等待所有任务结束后再关闭线程池
			executor.setWaitForTasksToCompleteOnShutdown(true);
			// rejection-policy：当pool已经达到max size的时候，如何处理新任务
			// CALLER_RUNS：不在新线程中执行任务，而是有调用者所在的线程来执行
			executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
			executor.initialize();
			int t = Runtime.getRuntime().availableProcessors();
			System.out.println("当前可用的线程数："+t);

			BlockingQueue<List<Map<Integer, Integer>>> queue = new ArrayBlockingQueue(maps.size());
			//以先入先出的顺序排序的阻塞队列
			for (Map.Entry<Integer, Integer> entry : maps.entrySet()) {
				List<Map<Integer, Integer>> listMap = new ArrayList<>();
				Map<Integer, Integer> mapList = new HashMap<>();
				mapList.put(entry.getKey(), entry.getValue());
				listMap.add(mapList);
				queue.add(listMap);
			}
			CountDownLatch latch = new CountDownLatch(queue.size());
			int rowNum = 0;
			while (queue.size()>0){
				try {
					System.out.println(rowNum);
					rowNum++;
					executor.execute(new MergedRegionForThread(latch,queue.take(),sheet));
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			}
			try {
				latch.await();
			} catch (InterruptedException e) {
				e.printStackTrace();
			}
		}

/**
 * 多线程合并表格
 */
public class MergedRegionForThread implements Runnable{

    private CountDownLatch latch;
    private List<Map<Integer, Integer>> dataList;
    private int mergeCellsCount = 0;
    private SXSSFSheet sheet;

    public MergedRegionForThread(CountDownLatch latch, List<Map<Integer, Integer>> dataList, SXSSFSheet sheet) {

        this.latch = latch;
       this.dataList = dataList;
        this.sheet = sheet;
    }

    @Override
    public void run() {
        try {
            Map<Integer, Integer> map = (Map<Integer, Integer>) dataList.get(0);
            for (Map.Entry<Integer, Integer> entry : map.entrySet()) {
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 0, 0));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 1, 1));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 2, 2));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 3, 3));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 4, 4));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 5, 5));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 6, 6));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 7, 7));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 8, 8));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 9, 9));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 10, 10));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 11, 11));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 12, 12));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 13, 13));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 14, 14));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 15, 15));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 16, 16));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 17, 17));
                addMergeRegion(sheet,new CellRangeAddress(entry.getKey(), entry.getValue(), 39, 39));
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != latch) {
                latch.countDown();
            }
        }
    }

    /**
     * 根据源码自定义表格合并方法(去掉验证和判断)，只提取合并方法
     * @param sheet
     * @param cra
     */
    private void addMergeRegion(SXSSFSheet sheet,CellRangeAddress cra) {
        SXSSFWorkbook workbook = sheet.getWorkbook();
        XSSFWorkbook xssfWorkbook = workbook.getXSSFWorkbook();
        //获取第一个sheet
        XSSFSheet sheet1 = xssfWorkbook.getSheetAt(0);
        CTWorksheet ctWorksheet = sheet1.getCTWorksheet();

        CTMergeCells ctMergeCells = mergeCellsCount > 0 ?ctWorksheet.getMergeCells():ctWorksheet.addNewMergeCells();
        CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();

        ctMergeCell.setRef(cra.formatAsString());
        //用来判断sheet是否已经有合并表格
        mergeCellsCount ++;
    }
}

亲测，本来合并要2多个小时以上，使用后20s,30s即刻完成。有错欢迎大家指出