Writing large Excel in Java causing high CPU usage using apache-poi

Question

Creating huge data with 25 columns and half a million records.

To write data from a list to an excel file, use the apache-poi streaming workbook. When tested on a local PC, it too produces large CPU spikes. while writing workbook data to file, appears to be the cause

workbook.write(fileOutputStream) // it is causing CPU spikes debugged and confirmed.

When a cloud application (deployed in Kubernetes) reaches its resource constraints, it restarts itself due to heavy CPU utilization. We have a straightforward app with 2042Mi memory and 1024M CPU configuration.

Is there a technique to effectively write a huge excel file without affecting the CPU, memory, or Java heap?

Code using:

import java.io.File;
import java.io.FileOutputStream;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;

import com.king.medicalcollege.model.Medico;

@Service
public class ExcelWriterService {

    // file is an empty file already created
    // Large List around 500K records of medico data [Medico is POJO]

    public File writeData(File file, List<Medico> medicos) {

        SXSSFWorkbook sxssfWorkbook = null;
        try (SXSSFWorkbook workbook = sxssfWorkbook = new SXSSFWorkbook(1);
                FileOutputStream fileOutputStream = new FileOutputStream(file)) {

            Sheet sheet = workbook.createSheet();
            CellStyle cellStyle = workbook.createCellStyle();
            int rowNum = 0;
            for (Medico medico : medicos) {
                Row row = sheet.createRow(rowNum);
                //just adding POJO values (25 fields)  into ROW 
                addDataInRow(medico, row, cellStyle);
                rowNum++;
            }

            //workbook.write causing CPU spike
            workbook.write(fileOutputStream);

            workbook.dispose();

        } catch (Exception exception) {
            return null;
        } finally {
            if (sxssfWorkbook != null) {
                sxssfWorkbook.dispose();
            }
        }

        return file;
    }

    private void addDataInRow(Medico medico, Row row, CellStyle cellStyle) {
        Cell cell_0 = row.createCell(0);
        cell_0.setCellValue(medico.getFirstName());
        cell_0.setCellStyle(cellStyle);
        
        Cell cell_1 = row.createCell(1);
        cell_1.setCellValue(medico.getMiddleName());
        cell_1.setCellStyle(cellStyle);
        
        Cell cell_2 = row.createCell(2);
        cell_2.setCellValue(medico.getLastName());
        cell_2.setCellStyle(cellStyle);
        
        Cell cell_3 = row.createCell(2);
        cell_3.setCellValue(medico.getFirstName());
        cell_3.setCellStyle(cellStyle);
        
        //...... around 25 columns will be added like this
    }
}

Answer 1

Giving SXSSFWorkbook a window size appears to be the proper move (albeit 1 might be too tiny and cause issues?). When the number of rows exceeds the limit you set, the workbook ought to be flushed to disc, so consuming less RAM. Though I doubt there is a fix to lower CPU consumption.

By changing the JVM parameters so that the K8s limit is not triggered, you can attempt to restrict your memory usage. Here are several to consider. -Xmx -Xms -XX:MaxRAM -XX:+UseSerialGC

Have you considered using an alternate library for writing Excel files? For example, have a look at this SO answer: Are there any alternatives to using Apache POI Java for Microsoft Office?