Read Excel and Write DB2 data using Apache POI HSSF implementation

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. HSSF 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

The following jars are needed to execute the sample code


The following sample code reads any excel file and will convert it into a Db2 database table with the help of a properties file. You can modify the program to convert excel data into Mysql, Oracle, SQL Server or any other relational database using the proper database JDBC drivers.

Sample Db2 table named MYTABLE

                R TABLER                            
                  EMPLOYEE       5S 1               
                  NAME          30A                 
                  SALARY        11P 2               
                  ZSTAMP          Z                 
                K EMPLOYEE                          

Sample EXCEL file


Read Excel and Write DB2 data using Apache POI

Sample Properties file

#Properties File for EXCEL to Db2 generation

localSystem=localhost
userId={user_id}    
password={password}
excelPath=data/
fileName=MYTABLE
fields=EMPLOYEE,NAME,SALARY 

Source code for ReadExcelWriteDb2.java

package com.as400samplecode;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ReadExcelWriteDb2 {

    static Properties props;

    public static void main(String[] args) {

        String excelFilename = null;
        String propsFilename = null;

        ReadExcelWriteDb2 readExcelWriteDb2 = new ReadExcelWriteDb2();
        if (args.length < 1)
        {
            System.err.println("Usage: java "+readExcelWriteDb2.getClass().getName()+
            " Excel_Filename  Properties_Filename");
            System.exit(1);
        }

        excelFilename = args[0].trim();
        propsFilename = args[1].trim();
        readExcelWriteDb2.generateDb2(excelFilename, propsFilename);

    }

    public void  generateDb2(String excelFilename, String propsFilename){

        try {

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

            String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";        
            String URL = "jdbc:as400://" + props.getProperty("localSystem").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());   

            sql = "SELECT " + props.getProperty("fields").trim() + " from " + props.getProperty("fileName").trim();
            stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();

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

            String q = "";
            for (int c = 0; c < colCount; c++) {
                if(q.equalsIgnoreCase("")){
                    q = "?";
                }
                else{
                    q = q + ",?";
                }
            }
            sql = "INSERT into " +
            props.getProperty("fileName").trim() + 
            " (" + props.getProperty("fields").trim() + ") VALUES(" + q + ")";
            stmt = conn.prepareStatement(sql);
            boolean insertData = true;
           
            FileInputStream fileInputStream = new FileInputStream(props.getProperty("excelPath").trim() + excelFilename.trim());

            HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
            HSSFSheet sheet = workbook.getSheetAt(0);

            int rows = sheet.getPhysicalNumberOfRows() + 5;
            System.out.println(workbook.getSheetName(0) + "\" has " + rows + " row(s).");
            for (int r = 0; r < rows; r++) {

                HSSFRow row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                // Skip Header Row
                if (r == 0) {
                    continue;
                }


                int cells = row.getPhysicalNumberOfCells();
                System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s).");
                for (int c = 0; c < cells; c++) {
                    HSSFCell cell = row.getCell(c);
                    String value = null,printValue = null;

                    System.out.println("Column=" + cell.getColumnIndex() + " CELL Type=" + cell.getCellType());
                   
                    switch (cell.getCellType()) {

                    case HSSFCell.CELL_TYPE_FORMULA:
                        printValue = "FORMULA value=" + cell.getCellFormula();
                        value = cell.getCellFormula();
                        break;

                    case HSSFCell.CELL_TYPE_NUMERIC:
                        printValue = "NUMERIC value=" + cell.getNumericCellValue();
                        value = String.valueOf(cell.getNumericCellValue());
                        break;

                    case HSSFCell.CELL_TYPE_STRING:
                        printValue = "STRING value=" + cell.getStringCellValue();
                        value = cell.getStringCellValue();
                        break;
                       
                    case HSSFCell.CELL_TYPE_BLANK:
                        printValue = "STRING value=" + cell.getStringCellValue();
                        value = cell.getStringCellValue();
                        break;   

                    default:
                    }
                   
                    System.out.println("Column=" + cell.getColumnIndex() + " VALUE=" + printValue);
                   
                    // If no data was provided, do not insert
                    if(value.equalsIgnoreCase("") || value == null){
                        insertData = false;
                        break;
                    }
                   
                    switch (metaData.getColumnType(cell.getColumnIndex()+1)) {

                    case java.sql.Types.CHAR:
                        stmt.setString(cell.getColumnIndex()+1,value);
                        break;

                    case java.sql.Types.NUMERIC:
                        if(metaData.getScale(cell.getColumnIndex()+1) == 0){
                            stmt.setInt(cell.getColumnIndex()+1,Integer.parseInt(value));
                        }
                        else {
                            stmt.setDouble(cell.getColumnIndex()+1,Double.parseDouble(value));
                        }
                        break;

                    case java.sql.Types.DECIMAL:
                        if(metaData.getScale(cell.getColumnIndex()+1) == 0){
                            stmt.setInt(cell.getColumnIndex()+1,Integer.parseInt(value));
                        }
                        else {
                            stmt.setDouble(cell.getColumnIndex()+1,Double.parseDouble(value));
                        }
                        break;

                    default:
                    }
                }

                if(insertData){
                stmt.executeUpdate();
                }
            }

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


        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}