Attempting to write a row[10] in the range [0,21] that is already written to disk

93 Views Asked by At

I get data from DB and insert it into Excel File. I have to methods of inserting this data: with using var sxssfWorkbook = new SXSSFWorkbook(workbook); and without. First method looks like this:

@SneakyThrows
public String insertDataToExcelSheet(File fileCopy, Map<Reports, List<List<Object>>> reportsTablesMap) {
    try (var fis = new FileInputStream(fileCopy);
        var workbook = new XSSFWorkbook(fis);
        var sxssfWorkbook = new SXSSFWorkbook(workbook);
        var fos = new FileOutputStream(fileCopy)) {

            try {
                reportsTablesMap.forEach(
                        (sheet, data) -> processBatch(sxssfWorkbook, sheet, data)
                );
                sxssfWorkbook.write(fos);
            } finally {
                // paranoid dispose.
                // If we run into error while process batch, then we
                // always dispose temporary files from streaming sxssf workbook
                sxssfWorkbook.dispose();
                currentRowCount = 0;
            }
        }
        return fileCopy.getName();
    }
}

Second one is similar, but without var sxssfWorkbook = new SXSSFWorkbook(workbook); When I use the first method I catch outOfMemory exception due to 2+ millions objects and with the second one I catch Attempting to write a row[10] in the range [0,21] that is already written to disk.

Here is the processBatch:

private void processBatch(Workbook workbook, Reports reportSheet, List<List<Object>> reportData) {
    Row row = null;
    Cell cell = null;
    CellStyle style = null;
    int startRow = reportSheet.getStartRow();
    int startColumn = reportSheet.getStartColumn();
    int maxRowsPerSheet = 1000000;
    String sheetName = reportSheet.getSheetName();
    Sheet sheet = workbook.getSheet(sheetName);
    style = workbook.createCellStyle();
    style.setBorderTop(BorderStyle.THIN);
    style.setBorderRight(BorderStyle.THIN);
    style.setBorderLeft(BorderStyle.THIN);
    style.setBorderBottom(BorderStyle.THIN);
    log.info("Post to new paper '{}' started", sheetName);
    for (List<Object> report : reportData) {
        if (currentRowCount >= maxRowsPerSheet) {
            newSheetName = sheetName + "^" + (countActiveSheet + 1);
            log.info("Post to new paper '{}'", newSheetName);
            countActiveSheet++;
            sheet = workbook.createSheet(newSheetName);
            currentRowCount = 0;
            startColumn = 0;
            startRow = 0;
        }
        currentRowCount++;
        row = sheet.getRow(startRow);
        int columnCount = startColumn;
        if (row == null) {
            row = sheet.createRow(startRow);
        }
        for (Object field : report) {
            cell = row.getCell(columnCount);
            if (cell == null) {
                cell = row.createCell(columnCount);
                cell.setCellStyle(style);
            }
            switch (cell.getCellType()) {
                case STRING: {
                    if (field == null) {
                        break;
                    }
                    if (!((String) field).equalsIgnoreCase("formula"))
                        cell.setCellValue((String) field);
                    else
                        cell.setCellValue(cell.getCellFormula());
                }
                    break;
                case NUMERIC: {
                    if (field == null) {
                        break;
                    }
                    if (field instanceof Integer)
                        cell.setCellValue((Integer) field);
                    else if (field instanceof Long)
                        cell.setCellValue((Long) field);
                    else if (field instanceof Date)
                        cell.setCellValue((Date) field);
                    else
                        cell.setCellValue((Double) field);
                }
                    break;
                case BOOLEAN:
                    cell.setCellValue((Boolean) field);
                    break;
                case FORMULA: {
                }
                    break;
                case BLANK: {
                    if (field == null) {
                        break;
                    }
                    else if (field instanceof Double) {
                        cell.setCellValue((double) field);
                    }
                    else if (field instanceof Integer) {
                        cell.setCellValue((Integer) field);
                    }
                    else if (field instanceof Long) {
                        cell.setCellValue((Long) field);
                    }
                    else if (field instanceof Boolean) {
                        cell.setCellValue((Boolean) field);
                    }
                    else if (field instanceof Date) {
                        cell.setCellValue((Date) field);
                    }
                    else if (field instanceof String
                            && !((String) field).equalsIgnoreCase("formula")) {
                        cell.setCellValue((String) field);
                    }
                }
                    break;
            }
            columnCount++;
        }
        startRow++;
    }
    log.info("Post to page '{}' finished", sheetName);
    reportData.clear();
}

I've tried to use methods with ignoring inserted blocks and also tried methods with checking excel file, but suddenly it didn't help me. Problem is that I can't clear the Excel file because it's protected, but in the blocks there are nothing.

0

There are 0 best solutions below