本文最重要的就是以下重写的方法，如果你仅仅是感觉导出慢，原因又是因为合并单元格，那么你直接用一下方法合并就好。
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.x.main.CTMergeCell;import org.openxmlformats.schemas.spreadsheetml.x.main.CTMergeCells;import org.openxmlformats.schemas.spreadsheetml.x.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;}}}
如果觉得《POI导出百万数据 EXCEL addMergedReigon 合并单元格慢的问题解决》对你有帮助，请点赞、收藏，并留下你的观点哦！
