Blog Archive

Generate EXCEL in java using Apache POI XSSF implementation

XSSF (org.apache.poi.xssf.usermodel) is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format. XSSF provides ways to read spreadsheets create, modify, read and write XLS spreadsheets. They provide:
  • low level structures for those with special needs
  • an eventmodel api for efficient read-only access
  • a full usermodel api for creating, reading and modifying XLS files

Please be aware that as the new XSSF supported Excel 2007 OOXML (.xlsx) files are XML based, the memory footprint for processing them is higher than for the older HSSF supported (.xls) binary files.

Tip: Exception in thread "main" java.lang.OutOfMemoryError: Java heap space then either you have to have to increase Java VM heap size or use the low-memory footprint SXSSF API implementation.

The Java Virtual Machine takes two command line arguments which set the initial and maximum heap sizes -Xms and -Xmx. For example if you want to give your Java program needs a 64Mb initial and 256Mb maximum heap size you could launch it as follows:
java -Xms64m -Xmx256m your_java_program

Sample Java code implementing Apache POI to Generate 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


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.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class DatabaseToExcel2 {

    static Properties props;

    public static void main(String[] args)
    {

        String excelFilename = null;
        String sqlString = null;

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

        excelFilename = args[0].trim();
        sqlString = args[1].trim();
        databaseToExcel2.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 XSSFWorkbook();

            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
            Sheet sheet1 = null;
            sheet1 = 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