大数据量POI导出Excel出错及getRow(行号)返回空行的问题咨询

Question

public class ExportCheckStyleService extends AbstractExportService {
@Override
protected String getSheetName() {
return "checkStyle";
}
@Override
protected String getTableName() {
return "T_CHECKSTYLE";
}
@Override
protected String getHeadMsg() {
return "checkstyle检查结果";
}
@Override
protected String getCurTotalCondition() {
return " BIF='" + this.arrBuildInfoDTO.get(0).getStrBIF() + "'";
}
private int tmpRowNum = 0;
@Override
protected void writeBody(Sheet sheet) throws Exception {
// 设置列宽度
toSetBodyColumnWidht(sheet);
// 比较块
Row row = sheet.createRow(++rowPositin);
Cell cell = row.createCell(0);
cell.setCellValue("与上次集成结果比较:");
cell.setCellStyle(CommonPOIUtil.getTextRowStyle(wb));
CellRangeAddress region = CommonPOIUtil.getCellRangeAddr(rowPositin,
rowPositin, 0, 4);
CommonPOIUtil.setCellRangeBorder(region, sheet, wb);
sheet.addMergedRegion(region);
toFillDiffInfoBlock(sheet);// 填充与上次的比较块 // 错误详细信息
row = sheet.createRow(++rowPositin);//
cell = row.createCell(0);
cell.setCellValue("错误详细信息:");
cell.setCellStyle(CommonPOIUtil.getDetailTextRowStyle(wb));
region = CommonPOIUtil.getCellRangeAddr(rowPositin, ++rowPositin, 0, 4);
CommonPOIUtil.setCellRangeBorder(region, sheet, wb);
sheet.addMergedRegion(region);
toFillErrInfoBlock(sheet);// 填充错误信息块
}
/**
 * 设置列的宽
 * 
 * @param sheet
 */
private void toSetBodyColumnWidht(Sheet sheet) {
sheet.setColumnWidth(0, 30 * 256);
sheet.setColumnWidth(1, 18 * 256);
sheet.setColumnWidth(2, 18 * 256);
sheet.setColumnWidth(3, 18 * 256);
sheet.setColumnWidth(4, 18 * 256);
}
/**
 * 填充构建比较块
 * 
 * @param row
 */
private void toFillDiffInfoBlock(Sheet sheet) {
Row row = sheet.createRow(++rowPositin);
Cell cell = row.createCell(0);
cell.setCellValue("集成时间");
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
cell = row.createCell(1);
cell.setCellValue("总文件");
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
cell = row.createCell(2);
cell.setCellValue("错误的文件");
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
cell = row.createCell(3);
cell.setCellValue("错误数");
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
cell = row.createCell(4);
cell.setCellValue("文件平均错误数");
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
writeSumDiffInfo(sheet);// 填差异汇总信息
}
@Override
protected CellRangeAddress getHeadCellRangeAddr() {
rowPositin += 2;// 将合并的列+2
return CommonPOIUtil.getCellRangeAddr(0, rowPositin, 0, 4);
}
/**
 * 差异汇总信息
 * 
 * @param sheet
 */
private void writeSumDiffInfo(Sheet sheet) {
Row row = sheet.createRow(++rowPositin);
toSetBuildInfoVal(arrBuildInfoDTO.get(0), row);
if (arrBuildInfoDTO.size() > 1) {// 当存在上次构建成功信息。则列出上次构建信息
row = sheet.createRow(++rowPositin);
toSetBuildInfoVal(arrBuildInfoDTO.get(1), row);
}
}
/**
 * 设置构建信息
 * 
 * @param buildInfoDTO
 * @param row
 */
private void toSetBuildInfoVal(BuildInfoDTO buildInfoDTO, Row row) {
Cell cell = row.createCell(0);
cell.setCellValue(buildInfoDTO.getStrStartTime());
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
int iFileNum = 0;
int iErrorNum = 0;
int iErrorFileNum = 0; String strBuildInfo = buildInfoDTO.getStrBuildSumInfo();// 汇总信息
String chekstyleInfo = CommonUtil.getSplitStrBySepAndPreWithOutPrefix(
strBuildInfo, "&", "checkstyle:");
cell = row.createCell(1);
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
iFileNum = Integer.valueOf(CommonUtil
.getSplitStrBySepAndPreWithOutPrefix(chekstyleInfo, "@",
"fileNum-"));
cell.setCellValue(iFileNum);
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
cell = row.createCell(2);
iErrorFileNum = Integer.valueOf(CommonUtil
.getSplitStrBySepAndPreWithOutPrefix(chekstyleInfo, "@",
"errfileNum-"));
cell.setCellValue(iErrorFileNum);
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
cell = row.createCell(3);
iErrorNum = Integer.valueOf(CommonUtil
.getSplitStrBySepAndPreWithOutPrefix(chekstyleInfo, "@",
"errorNum-"));
cell.setCellValue(iErrorNum);
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
cell = row.createCell(4);
cell.setCellValue(iErrorNum / iFileNum);
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
}
/**
 * 填充错误信息块
 * 
 * @param sheet
 * @throws IOException
 */
private void toFillErrInfoBlock(Sheet sheet) throws Exception {
// String TeamPackageParse.getTeamValueByKey("");
Map<String, String> teamMap = TeamPackageParse.getTeamMap();// 得到各个组的信息
if (teamMap == null) {
return;
}
// 各个块的数据填充
for (String key : teamMap.keySet()) {
toSetEverTeamHeadInfo(sheet);// 设置各组的汇总头部信息
toSetEverTeamSumInfo(sheet, key);// 设置各组的汇总信息
try {
((SXSSFSheet) sheet).flushRows();
} catch (IOException e) {
throw new Exception(e.toString());
}// 写入磁盘 toFillErrorRecord(sheet, teamMap.get(key));// 填充详情
}
}
/**
 * 设置各个组的汇总信息
 * 
 * @param sheet
 */
private void toSetEverTeamHeadInfo(Sheet sheet) {
Row row = sheet.createRow(++rowPositin);
Cell cell = row.createCell(0);
cell.setCellValue("組名");
cell.setCellStyle(CommonPOIUtil.setCommonHeadWithBG(wb));
cell = row.createCell(1);
cell.setCellStyle(CommonPOIUtil.setCommonHeadWithBG(wb));
CellRangeAddress region = CommonPOIUtil.getCellRangeAddr(rowPositin,
rowPositin, 0, 1);
sheet.addMergedRegion(region);
cell = row.createCell(2);
cell.setCellValue("错误的文件");
cell.setCellStyle(CommonPOIUtil.setCommonHeadWithBG(wb));
cell = row.createCell(3);
cell.setCellValue("错误数");
cell.setCellStyle(CommonPOIUtil.setCommonHeadWithBG(wb));
cell = row.createCell(4);
cell.setCellValue("文件平均错误数");
cell.setCellStyle(CommonPOIUtil.setCommonHeadWithBG(wb));
}
/**
 * 设置汇总信息
 * 
 * @param sheet
 */
private void toSetEverTeamSumInfo(Sheet sheet, String key) {
Row row  = sheet.createRow(++rowPositin);
tmpRowNum = rowPositin;//记录行号以便回填
Cell cell = row.createCell(0);
cell.setCellValue(key);// 设置组名
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
cell = row.createCell(1);
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
CellRangeAddress region = CommonPOIUtil.getCellRangeAddr(rowPositin,
rowPositin, 0, 1);
sheet.addMergedRegion(region);
cell = row.createCell(2);
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
cell = row.createCell(3);
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
cell = row.createCell(4);
cell.setCellStyle(CommonPOIUtil.setCommonCellTextAlignCenter(wb));
}
/**
 * 出错详细信息行头
 * 
 * @param sheet
 */
private void toSetDetailHead(Sheet sheet) {
Row row = sheet.createRow(++rowPositin);
Cell cell = row.createCell(0);
cell.setCellValue("出错行");
cell.setCellStyle(CommonPOIUtil.setCommonHeadWithBG(wb));
cell = row.createCell(1);
cell.setCellValue("错误描述");
cell.setCellStyle(CommonPOIUtil.setCommonHeadWithBGLeft(wb));
cell = row.createCell(2);
cell.setCellStyle(CommonPOIUtil.setCommonHeadWithBGLeft(wb));
cell = row.createCell(3);
cell.setCellStyle(CommonPOIUtil.setCommonHeadWithBGLeft(wb));
cell = row.createCell(4);
cell.setCellStyle(CommonPOIUtil.setCommonHeadWithBGLeft(wb));
CellRangeAddress region = CommonPOIUtil.getCellRangeAddr(rowPositin,
rowPositin, 1, 4);
sheet.addMergedRegion(region);
}

Answer 1

/**
 * 填充数据
 * 
 * @param sheet
 * @param value
 */
private void toFillErrorRecord(Sheet sheet, String value) throws Exception {
int countSize = 0;// 该组的总共记录数
int querySize = 0;// 当次查询记录数
int fileNum = 0;// 文件数 String tmpCondition = "";
DBConnectionUtil dbUtil = null;
Object[] objs = null;
Row row = null;
Cell cell = null;
CellRangeAddress region = null;
Map map = null;
String oldClassName = "";//主要用于分页取出数据的时候，判断是不是此类出现过。如果出现过则只是在接下行创建错误信息否则列类名，然后显示出错信息
String curClassName = "";//取每条记录时对应的类
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER); List<Object> lstObj = null;
if (!"".equals(value)) {
String[] strs = value.split("@"); for (String str : strs) {
tmpCondition = " INSTR(CLASSNAME,'" + str + "') > 0";
dbUtil = new DBConnectionUtil();
try {
querySize = (((BigDecimal) dbUtil
.executeQuerySingle("SELECT COUNT(*) FROM "
+ this.getTableName() + " WHERE "
+ this.getCurTotalCondition() + " AND "
+ tmpCondition)).intValue()); countSize += querySize;
// 循环查询记录
for (int i = 1; i <= querySize;) {
loger.debug("begin query fill date:"+CommonUtil.formatDateByPattern());
objs = new Object[2];
objs[0] = i + 5000;
objs[1] = i;
i = i + 5000;
lstObj = dbUtil
.excuteQuery(
"SELECT * FROM (SELECT * FROM (SELECT ROWNUM AS ROWNO, CLASSNAME,LINENO,MSG FROM "
+ this.getTableName()
+ " WHERE "
+ this.getCurTotalCondition()
+ " AND "
+ tmpCondition
+ " ORDER BY CLASSNAME)  WHERE ROWNO <= ? ) WHERE ROWNO >= ?",
objs);
loger.debug("end query fill date:"+CommonUtil.formatDateByPattern()); if (lstObj != null && lstObj.size() > 0) {
loger.debug("begin fill date:"+CommonUtil.formatDateByPattern());
for (Object obj : lstObj) {
map = (Map) obj;
curClassName = map.get("CLASSNAME").toString(); if (!curClassName.equals(oldClassName)) {// 当下一条记录和上一条记录的className一样时。归为上一class的结果列显示。否则创建一列类名
row = sheet.createRow(++rowPositin);
cell = row.createCell(0);
cell.setCellValue(curClassName);
region = CommonPOIUtil.getCellRangeAddr(
rowPositin, rowPositin, 0, 4);
sheet.addMergedRegion(region); toSetDetailHead(sheet);
fileNum++;
}
row = sheet.createRow(++rowPositin);
cell = row.createCell(0);
cell.setCellValue(map.get("LINENO").toString());
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue(map.get("MSG").toString());
region = CommonPOIUtil.getCellRangeAddr(
rowPositin, rowPositin, 1, 4);
sheet.addMergedRegion(region); oldClassName = curClassName;
curClassName = null;
try {
((SXSSFSheet) sheet).flushRows();
} catch (IOException e) {
throw new Exception(e.toString());
}
}
loger.debug("end fill date:"+CommonUtil.formatDateByPattern());
}
} } catch (SQLException e) {
e.printStackTrace();
}
}
// 回调设置出错汇总信息
callbakSetTeamSumInfo(fileNum, countSize, sheet);
}
}
// 回调设置各组错误汇总消息
private void callbakSetTeamSumInfo(int fileNum, int countSize, Sheet sheet)
throws IOException {
loger.debug("=====countSize:"+countSize);
/**此处getRow报空指针**/
// Row row = sheet.getRow(tmpRowNum);
// row.getCell(2).setCellValue(fileNum);
// row.getCell(3).setCellValue(countSize);
// row.getCell(4).setCellValue(countSize / fileNum);
tmpRowNum = 0;
}
}

Answer 2

Heap溢出了，数据量太大了，分批次处理看看。

Answer 3

你写出方法在哪呢 wb.write(output);SXSSFWorkbook wb = new SXSSFWorkbook(-1)相当于所有记录都放在内存上，很容易溢出SXSSFWorkbook wb = new SXSSFWorkbook(10000)这样你试试，把10000条放在内存，其他都在硬盘，这样写流的时候你再看看