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

0 votes

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
    }
}
Jan 29, 2023 in Others by Kithuzzz
• 38,020 points
1,603 views

1 answer to this question.

0 votes

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?

answered Jan 29, 2023 by narikkadan
• 63,680 points

Related Questions In Others

0 votes
1 answer

How to create page borders using Apache POI in excel files with Java?

Microsoft Excel cannot do this. Libreoffice Calc ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 63,680 points
994 views
0 votes
1 answer

Apache POI - watermark in Excel - different appearance in Excel and LibreOffice

There is nothing that apache poi could ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,680 points
2,127 views
0 votes
1 answer

Is there a way to hide excel formula using Apache POI

In Excel, hiding formulae is a feature ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,680 points
1,167 views
0 votes
1 answer

Apache POI. Setup data filters in Excel

It's already enabled in Apache POI 3.7. ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 63,680 points
1,999 views
0 votes
1 answer
0 votes
1 answer

What is the better API to Reading Excel sheets in java - JXL or Apache POI

Here are the things where both APIs ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,680 points
3,356 views
0 votes
1 answer

How to get columns from Excel files using Apache POI?

The only way to see all the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,680 points
5,183 views
0 votes
1 answer

Formatting Excel Spreadsheet using Apache POI in JAVA

Try this: CellRangeAddress range= new CellRangeAddress(firstrow, lastrow, firstcol, ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,680 points
2,188 views
0 votes
1 answer

Excel formula gives error when write using Apache-poi library in Java

I tested some sample code. The cell ...READ MORE

answered Nov 5, 2022 in Others by narikkadan
• 63,680 points
1,915 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP