本文最重要的就是以下重写的方法,如果你仅仅是感觉导出慢,原因又是因为合并单元格,那么你直接用一下方法合并就好。
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;
}
}
}