poi3.8用((SXSSFSheet)sheet).flushRows()方法解决了大数据量的excel导出,但是我在使用过程中,由于每个单元格都要设置不同的颜色和数据格式,发现导出第一张报表时速度还勉强可以接受,但是继续导出另一张报表,速度就慢的跟蜗牛似的,要10分钟甚至3个小时,请问有什么方法可以解决?
代码如下:
private static void fillExcel(KDTable kdtReport, SXSSFWorkbook book, Sheet sheet) {
int headRowCount = kdtReport.getHeadRowCount();
headRowCount=headRowCount+startRowCount;
Font font=book.createFont();
font.setFontHeightInPoints((short)10);
//改为保留整数
Short numFormat = book.createDataFormat().getFormat("0"); //numFormat==>0.00=0
//单元格样式或行样式
CellStyle cellStyle = book.createCellStyle(); // 建立新的cell样式
cellStyle.setBorderBottom(CellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(CellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(CellStyle.BORDER_THIN);//右边框
Set mergeBlockSet = new HashSet();
Cell cell = null;
Object value = null;
KDTMergeBlock mergeBlock = null;
IRow row2 = null;
Color color = null;
Row row = null;
int treeLevel = 0;
short colorIndex = IndexedColors.WHITE.getIndex();
for (int i = 0; i < kdtReport.getRowCount(); i++) {
row2 = kdtReport.getRow(i);
treeLevel = row2.getTreeLevel();
color = row2.getStyleAttributes().getBackground();
row = sheet.getRow(i + headRowCount);
if(row==null){
row = sheet.createRow(i+headRowCount);
}
cellStyle = book.createCellStyle();
cellStyle.setBorderBottom(CellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(CellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(CellStyle.BORDER_THIN);//右边框
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
if(Color.white.getRed()==color.getRed()&&Color.white.getGreen()==color.getGreen()&&Color.white.getBlue()==color.getBlue()){
colorIndex = IndexedColors.WHITE.getIndex();
}else{
colorIndex = spaceColorIndex[treeLevel];
}
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(colorIndex);
for (int j = 0; j < kdtReport.getColumnCount(); j++) {
value = row2.getCell(j).getValue();
mergeBlock = row2.getCell(j).getMergeBlock();
if(mergeBlock!=null){
mergeBlockSet.add(mergeBlock);
}
if (value != null) {
if(KDTableUtil.TREE_LEVEL_COLKEY.equals(kdtReport.getColumn(j).getKey()) || "curProject".equals(kdtReport.getColumn(j).getKey())){
if(!RptHelper.IndexHistoryChangeUIName.equals(tableName)){//指标历史变化表(这个好特殊啊)
value = value.toString().trim();
value = space1[treeLevel]+value;
}
}
cell = row.createCell(j);
if(value instanceof BigDecimal || value instanceof Integer || value instanceof Long || value instanceof Double){
BigDecimal bValue = FDCHelper.toBigDecimal(value);
if(bValue.compareTo(BigDecimal.ZERO)!=0){
cell.setCellValue(bValue.doubleValue());
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
}
cellStyle.setDataFormat(numFormat);
cell.setCellStyle(cellStyle);
}else{
cell.setCellValue(value.toString());
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
if(cellStyle!=null){
cell.setCellStyle(cellStyle);
}else{
cellStyle = book.createCellStyle();
cell.setCellStyle(cellStyle);
}
}
}else{
cell = row.createCell(j);
cellStyle.setFillForegroundColor(colorIndex);
cell.setCellStyle(cellStyle);
}
}
//每100行,就设置分组和合并信息,最后把这100行内容从内存写入本地硬盘
if(i>1&&i%100==0){
System.out.println("i:"+i);
try {
// 设置分组和合并
setRowGroup(kdtReport, sheet);
// 设置分组数据显示
sheet.setRowSumsBelow(false);
sheet.setRowSumsRight(false);
//这100行内容从内存写入本地硬盘
((SXSSFSheet)sheet).flushRows();
cellStyle = book.createCellStyle();
} catch (IOException e) {
e.printStackTrace();
}
}
}
//把剩余行内容从内存写入本地硬盘
try {
// 设置分组和合并
setRowGroup(kdtReport, sheet);
// 设置分组数据显示
sheet.setRowSumsBelow(false);
sheet.setRowSumsRight(false);
((SXSSFSheet)sheet).flushRows();
cellStyle = null;
} catch (IOException e) {
e.printStackTrace();
}
Iterator iterator = mergeBlockSet.iterator();
CellRangeAddress cellRangeAddress =null;
while (iterator.hasNext()) {
mergeBlock = (KDTMergeBlock) iterator.next();
int left = mergeBlock.getLeft();
int right = mergeBlock.getRight();
int top=mergeBlock.getTop()+headRowCount;
int bottom=mergeBlock.getBottom()+headRowCount;
cellRangeAddress = new CellRangeAddress(top, bottom, left, right);
sheet.addMergedRegion(cellRangeAddress);
}
}