Generate large EXCEL in java using Apache POI SXSSF implementation and avoid java.lang.OutOfMemoryError

Note: SXSSF is a brand new contribution, features are still being added after it was first introduced in POI 3.8-beta3. Users can to try the latest build from trunk or take the latest beta version. 

Since 3.8-beta3, POI provides a low-memory footprint SXSSF API built on top of XSSF.

SXSSF is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document. Older rows that are no longer in the window become inaccessible, as they are written to the disk.

In auto-flush mode the size of the access window can be specified, to hold a certain number of rows in memory. When that value is reached, the creation of an additional row causes the row with the lowest index to to be removed from the access window and written to disk. Or, the window size can be set to grow dynamically; it can be trimmed periodically by an explicit call to flushRows(int keepRows) as needed. Due to the streaming nature of the implementation, there are the following limitations when compared to XSSF:
  • Only a limited number of rows are accessible at a point in time.
  • Sheet.clone() is not supported.
  • Formula evaluation is not supported

Tip: If your are getting Exception in thread "main" java.lang.OutOfMemoryError: Java heap space, then you must use the low-memory footprint SXSSF API implementation.

Sample Java code implementing Apache POI to Generate very large Excel from any database

In the example below I am connecting to the Db2 database in iSeries or currently named as IBM i. You can change the database connection to SQL server, MYSQL, Oracle etc.

The following jars are needed to execute the sample code

Note: You need Java 1.6 for this implementation !


package com.as400samplecode;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Properties;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;

public class DatabaseToExcel3 {

    static Properties props;

    public static void main(String[] args)
    {

        String excelFilename = null;
        String sqlString = null;

        DatabaseToExcel3 databaseToExcel3 = new DatabaseToExcel3();
        if (args.length < 2)
        {
            System.err.println("Usage: java "+databaseToExcel3.getClass().getName()+
            " Excel_Filename Sql_Select_String");
            System.exit(1);
        }

        excelFilename = args[0].trim();
        sqlString = args[1].trim();
        databaseToExcel3.generateExcel(sqlString, excelFilename);

    }

    public void  generateExcel(String sqlString, String excelFilename){

        try {

            PrintStream p = new PrintStream(new FileOutputStream( "data/error.txt" ) );   
            System.setOut(p);

            props = new Properties();
            props.load(new FileInputStream("properties/dataconnection.properties"));

            String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";        
            String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";
            Connection conn = null; 
            String sql = null;
            PreparedStatement stmt = null;

            //Connect to iSeries                                         
            Class.forName(DRIVER);                                       
            conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());   

            //New Workbook
            Workbook wb = new SXSSFWorkbook();

            Cell c = null;

            //Cell style for header row
            CellStyle cs = wb.createCellStyle();
            cs.setFillForegroundColor(IndexedColors.LIME.getIndex());
            cs.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            Font f = wb.createFont();
            f.setBoldweight(Font.BOLDWEIGHT_BOLD);
            f.setFontHeightInPoints((short) 12);
            cs.setFont(f);

            //New Sheet
            SXSSFSheet sheet1 = null;
            sheet1 = (SXSSFSheet) wb.createSheet("myData");


            sql = sqlString;
            stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();
            int colCount = metaData.getColumnCount();

            //Create Hash Map of Field Definitions
            LinkedHashMap<Integer, MyTableInfo> hashMap = new LinkedHashMap<Integer, MyTableInfo>(colCount);

            for (int i = 0; i < colCount; i++) {
                MyTableInfo db2TableInfo = new MyTableInfo(); 
                db2TableInfo.setFieldName(metaData.getColumnName(i + 1).trim());
                db2TableInfo.setFieldText(metaData.getColumnLabel(i + 1));
                db2TableInfo.setFieldSize(metaData.getPrecision(i + 1));
                db2TableInfo.setFieldDecimal(metaData.getScale(i + 1));
                db2TableInfo.setFieldType(metaData.getColumnType(i + 1));
                db2TableInfo.setCellStyle(getCellAttributes(wb, c, db2TableInfo));
                hashMap.put(i, db2TableInfo);
            }

            // Row and column indexes
            int idx = 0;
            int idy = 0;

            // Generate column headings
            Row row = sheet1.createRow(idx);
            MyTableInfo db2TableInfo = new MyTableInfo();

            Iterator<Integer> iterator = hashMap.keySet().iterator();
            while (iterator.hasNext()) {
                Integer key = (Integer) iterator.next();
                db2TableInfo = hashMap.get(key); 
                c = row.createCell(idy);
                c.setCellValue(db2TableInfo.getFieldText());
                c.setCellStyle(cs);
                if(db2TableInfo.getFieldSize() > db2TableInfo.getFieldText().trim().length()){
                    sheet1.setColumnWidth(idy, (db2TableInfo.getFieldSize()* 500));
                }
                else {
                    sheet1.setColumnWidth(idy, (db2TableInfo.getFieldText().trim().length() * 500));
                }
                idy++;
            }

            while (rs.next()) {

                idx++;
                row = sheet1.createRow(idx);
                System.out.println(idx);
                for (int i = 0; i < colCount; i++) {

                    c = row.createCell(i);
                    db2TableInfo = hashMap.get(i);

                    switch (db2TableInfo.getFieldType()) {
                    case 1:
                        c.setCellValue(rs.getString(i+1));
                        break;
                    case 2:
                        c.setCellValue(rs.getDouble(i+1));
                        break;
                    case 3:
                        c.setCellValue(rs.getDouble(i+1));
                        break;
                    default:
                        c.setCellValue(rs.getString(i+1));
                        break;
                    }
                    c.setCellStyle(db2TableInfo.getCellStyle());
                }

            }

            rs.close();
            stmt.close();
            conn.close();

            FileOutputStream fileOut = new FileOutputStream(props.getProperty("excel_path").trim() + excelFilename.trim() );

            wb.write(fileOut);
            fileOut.close();

        }
        catch (Exception e) {
            System.out.println(e);
        }

    }


    private static CellStyle getCellAttributes (Workbook wb, Cell c, MyTableInfo db2TableInfo){

        CellStyle cs= wb.createCellStyle();
        DataFormat df = wb.createDataFormat();
        Font f = wb.createFont();

        switch (db2TableInfo.getFieldDecimal()) {
        case 1:
            cs.setDataFormat(df.getFormat("#,##0.0"));
            break;
        case 2:
            cs.setDataFormat(df.getFormat("#,##0.00"));
            break;
        case 3:
            cs.setDataFormat(df.getFormat("#,##0.000"));
            break;
        case 4:
            cs.setDataFormat(df.getFormat("#,##0.0000"));
            break;
        case 5:
            cs.setDataFormat(df.getFormat("#,##0.00000"));
            break;
        default:
            break;
        }

        cs.setFont(f);

        return cs;

    }


}

Source code for MyTableInfo.java

package com.as400samplecode;

import org.apache.poi.ss.usermodel.CellStyle;

public class MyTableInfo {

    String fieldName = null;
    String fieldText = null;
    int fieldType = 0;
    int fieldSize = 0;
    int fieldDecimal = 0;
    CellStyle cellStyle = null;
   
    public String getFieldName() {
        return fieldName;
    }
    public void setFieldName(String fieldName) {
        this.fieldName = fieldName;
    }
    public String getFieldText() {
        return fieldText;
    }
    public void setFieldText(String fieldText) {
        this.fieldText = fieldText;
    }
    public int getFieldType() {
        return fieldType;
    }
    public void setFieldType(int fieldType) {
        this.fieldType = fieldType;
    }
    public int getFieldSize() {
        return fieldSize;
    }
    public void setFieldSize(int fieldSize) {
        this.fieldSize = fieldSize;
    }
    public int getFieldDecimal() {
        return fieldDecimal;
    }
    public void setFieldDecimal(int fieldDecimal) {
        this.fieldDecimal = fieldDecimal;
    }
    public CellStyle getCellStyle() {
        return cellStyle;
    }
    public void setCellStyle(CellStyle cellStyle) {
        this.cellStyle = cellStyle;
    }
   
   
}

Sample for dataconnection.properties file

#I-series ip or host name
local_system=192.168.100.101

#I-series UserId, used for login and library list
userId={your_user_id}

#I-series Password
password={your_password}

#Path for EXCEL data
excel_path=data/

Recommended Reading


8 comments :

  1. Hi

    we are haing problem in generating huge excel documents using POI. It looks like we can use this to overcome our problem.Can i have source code download along with jars used in the sample

    thanks
    Siva K

    ReplyDelete
  2. No, I don't have the zip file for this project. The complete source and the required jars are mentioned above. All you need to do is google the jars for download. For some of them I have provided the links. You can click on the jar names.

    The jar for the apache poi is beta so it might have changed from time this blog was written. It will let you create large excel files without any issues such as java memory or old excel limitation of number of rows.

    ReplyDelete
  3. Thanks so much. It works perfect!

    ReplyDelete
  4. improve your coding skills. atleast dont use hashmap for storing objects where arraylist can be used.

    ReplyDelete
  5. Consider adding

    wb.setCompressTempFiles(true);
    In my case, your solution doesn't work without this rule!

    ReplyDelete
  6. It work's perfectly, this is the solution for my heap size error, thank you very much!

    ReplyDelete
  7. # My requirement is to generate an excel workbook with multiple sheets in it. with this condition that execute a query with specific date range and get the output and pass those values in to excel workbook with different sheets based on date eg: sheet 1 should contain only Date 1 values and sheet 2 should contain only date 2 values and so on till the given date range . #

    ## In the below code i have achieved only getting out of query for a selected date range and passed to excel workbook with only one sheet .Please help me out in how to go forward from here and achieve my requirement. ##

    import java.sql.*;
    import java.io.File;
    import java.io.FileOutputStream;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    public class CreateExcelFile{
    public static void main(String[]args){
    try{
    XSSFWorkbook wb=new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet("new sheet");

    XSSFRow rowhead= sheet.createRow((short)0);
    rowhead.createCell((short) 0).setCellValue("EMPNO");
    rowhead.createCell((short) 1).setCellValue("ENAME");
    rowhead.createCell((short) 2).setCellValue("JOB");
    rowhead.createCell((short) 3).setCellValue("MGR");
    rowhead.createCell((short) 4).setCellValue("HIREDATE");

    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","pass");
    Statement st=con.createStatement();
    ResultSet rs=st.executeQuery("SELECT * FROM emp WHERE HIREDATE BETWEEN TO_DATE ('1980/12/17', 'yyyy/mm/dd')AND TO_DATE ('1981/02/20', 'yyyy/mm/dd')");
    int i=1;
    while(rs.next()){
    XSSFRow row= sheet.createRow((short)i);
    row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt("empno")));
    row.createCell((short) 1).setCellValue(rs.getString("ename"));
    row.createCell((short) 2).setCellValue(rs.getString("job"));
    row.createCell((short) 3).setCellValue(rs.getString("mgr"));
    row.createCell((short) 4).setCellValue(rs.getString("hiredate"));
    i++;
    }

    FileOutputStream fileOut = new FileOutputStream(new File("data.xlsx"));
    wb.write(fileOut);
    fileOut.close();
    System.out.println("Your excel file has been generated!");


    } catch ( Exception ex ) {
    System.out.println(ex);

    }
    }
    }

    ReplyDelete
  8. Your article is incredibly advantageous! Looking at your work has educated me. Learned a lot from it. I will save your website and will consistently go through your next blogposts. Awesome! Thank you! royal-essay.com

    ReplyDelete