POI导出百万数据 EXCEL addMergedReigon 合并单元格慢的问题解决

本文最重要的就是以下重写的方法，如果你仅仅是感觉导出慢，原因又是因为合并单元格，那么你直接用一下方法合并就好。

private static void addMergedReigon(CTWorksheet sheetX, CellRangeAddress cellRangeAddress) {
        CTMergeCells ctMergeCells;
        if (sheetX.isSetMergeCells()) {
            ctMergeCells = sheetX.getMergeCells();
        } else {
            ctMergeCells = sheetX.addNewMergeCells();
        }

        CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
        ctMergeCell.setRef(cellRangeAddress.formatAsString());
    }

上面方法的参数，如果你不知道怎么获取就用我下面写的，可能有更好的方法，请留言告诉我，我找了好久才弄明白如何将CTWorksheet 转成SXSSFSheet，强制转换会抛出异常，不可以的，只能创建完SXSSFSheet之后再去根据sheetIndex获取。

SXSSFSheet sheet = sxssfWorkbook.createSheet();
sxssfWorkbook.setSheetName(sheetIndex, "表1");
CTWorksheet sheetX = sheet.getWorkbook().getXSSFWorkbook().getSheetAt(sheetIndex).getCTWorksheet();

如果你对除了合并单元格之外的也感兴趣，那就继续往下看。（PS：以下代码没有测试，因为开始说的那个方法才是最重要的），可以导出十几万的数据，但是WORKBOOK很消耗内存，合并单元格很消耗CPU，建议根据业务来拆分EXCEL

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCells;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;

import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        int sheetIndex = 0;
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000);
        SXSSFSheet sheet = sxssfWorkbook.createSheet();
        sxssfWorkbook.setSheetName(sheetIndex, "表1");
        CTWorksheet sheetX = sheet.getWorkbook().getXSSFWorkbook().getSheetAt(sheetIndex).getCTWorksheet();

        CellStyle cellStyle = sxssfWorkbook.createCellStyle();
        // 样式自己定义 ......

        // 宿便输出一些内容
        for (int i = 0; i < 10; i++) {
            List list = new ArrayList<>();
            list.add(new ColspanAndText("姓名001", 2, cellStyle));
            list.add(new ColspanAndText("男", 1, cellStyle));
            list.add(new ColspanAndText("地址", 10, cellStyle));
            // 写入一行数据
            setCellTextForCTWorksheet(sheet, sheetX, 0, i, list, cellStyle);
        }

        // 随便输出到文件或者返回给用户......
    }

    private static void setCellTextForCTWorksheet(SXSSFSheet sheet, CTWorksheet sheetX, int rowIndex, int firstCol, List<ColspanAndText> list, CellStyle style) {
        /**
         *  合并单元格
         */
        int startCol = firstCol;
        CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex, startCol, startCol);
        for (ColspanAndText text : list) {
            int cols = text.getColspan();
            // cols > 1 合并单元格，业务控制，cols==1回报错误
            if (cols > 1) {
                cellRangeAddress.setFirstRow(rowIndex);
                cellRangeAddress.setLastRow(rowIndex);
                cellRangeAddress.setFirstColumn(startCol);
                cellRangeAddress.setLastColumn(startCol + cols - 1);
                // 重写合并的方法
                addMergedReigon(sheetX, cellRangeAddress);
            }
            startCol += cols;
        }
    }

    private static void addMergedReigon(CTWorksheet sheetX, CellRangeAddress cellRangeAddress) {
        CTMergeCells ctMergeCells;
        if (sheetX.isSetMergeCells()) {
            ctMergeCells = sheetX.getMergeCells();
        } else {
            ctMergeCells = sheetX.addNewMergeCells();
        }

        CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
        ctMergeCell.setRef(cellRangeAddress.formatAsString());
    }

    private static class ColspanAndText {
        private int colspan;
        private String text;
        private CellStyle cellStyle;

        public ColspanAndText(String text, int colspan, CellStyle cellStyle) {
            this.colspan = colspan;
            this.text = text;
            this.cellStyle = cellStyle;
        }

        public int getColspan() {
            return colspan;
        }

        public void setColspan(int colspan) {
            this.colspan = colspan;
        }

        public String getText() {
            return text;
        }

        public void setText(String text) {
            this.text = text;
        }

        public void setCellStyle(HSSFCellStyle cellStyle) {
            this.cellStyle = cellStyle;
        }
    }
}