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.