Vishnu Viswambharan Vishnu Viswambharan - 1 month ago 16
Java Question

Excel generation issue in java

I am trying to use Apache POI for generating Excel file. My need is to generate 120k rows and 7 columns of data. I can easily generate excel file with 25 000 rows but, the problem is I can't generate excel file with 100k of rows. On run time execution of the code is becoming slow.

For generating the excel file, system has to iterate 900k times through a

for
loop (100k rows and 9 columns).

Initially it takes around 1 second to add 24 rows in the excel object, then it gradually decreasing the speed of execution.

After adding 70k rows, it take 24..30 seconds to add a row. Why this is happening?

List<String[]> keyList = keyService.findAllKeyByBatchCode(batchCode);

int x = 1;
if(keyList != null && keyList.size() != 0) {
Date activated_Date = null;

for (int i = 0; i<keyList.size(); i++) {
Object[] keyUser = keyList.get(i);

data.put(x, new Object[] {
String.valueOf(x),
keyForDetails.getLicenceType().getName(),
batchCode,
keyForDetails.getKeyType(),
keyUser[0],
"",
DateUtility.convertUtilDatetoString(keyForDetails.getDate()),
String.valueOf(keyForDetails.getPrice()),
keyUser[1]
});

x++;
}
}

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Key sheet");
Set<Integer> keyset = data.keySet();
int rownum = 0;
System.out.println("-------------printin------");

try {
for (Integer key : keyset) {
Row row = sheet.createRow(rownum++);
Object [] objArr = data.get(key);
int cellnum = 0;

for (Object obj : objArr) {
System.out.println("-------------print cell------");
Cell cell = row.createCell(cellnum++);

if(obj instanceof Date)
cell.setCellValue((Date)obj);
else if(obj instanceof Boolean)
cell.setCellValue((Boolean)obj);
else if(obj instanceof String)
cell.setCellValue((String)obj);
else if(obj instanceof Double)
cell.setCellValue((Double)obj);
}
}

if(rownum != 1) {
System.out.println("-------------file writein------");
FileOutputStream out = new FileOutputStream(new File("C:/Users/Public/Documents/"+batchCode+" page"+n+".xlsx"));
workbook.write(out);
out.close();
System.out.println("Excel written successfully..");
}
} catch (Exception e) {
e.printStackTrace();
}

Answer

Apache-poi has a streaming api and can work with large data. From the documentation from apache POI site it is stated that since version 3.8.beta3 there is a new api available called SXSSF. It has a much smaller footprint and should be used to produce very large excel files.

You can check this link

You can also refer following basic code:

public class ReadWriteExcelFile {  

    public static void main(String[] args) throws IOException {  
        String excelFileName = "C:\\temp\\Test.xlsx";//name of excel file  

        String sheetName = "Sheet1";//name of sheet  

        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sheet = wb.createSheet(sheetName);  

//iterating r number of rows  
        for (int r = 0; r < 55555; r++) {  
            Row row = sheet.createRow(r);  

//iterating c number of columns  
            for (int c = 0; c < 5; c++) {  
                Cell cell = row.createCell(c);  
                cell.setCellValue("Cell " + r + " " + c);  
            }  
            if ( r % 1000 == 0) {
                System.out.println(r);
            }
        }  

        FileOutputStream out = new FileOutputStream(excelFileName);
        wb.write(out);
        out.close();
    }  
}