Iseries / AS400 creating Excel files

How can you convert Iseries data to Excel from within a program?

If you have some knowledge of Java it will be a breeze.

In this example we will take a AS400 Physical file and create an excel from it.

The first step will be to download the Apache POI api. This  API contains a number of java classes that will enable you to create and read microsoft excel files from within a Java program.  You will also need to download the IBM Java toolbox.  The toolbox contains a number of classes which allows you to access iseries data and resources.

In the IFS root folder we created a folder called java.  Inside the java folder we created three subfolders. JT400, poi and Test.  In the JT400 folder we installed the IBM Java toolbox.  In the poi folder we installed the Apache Poi API.  In our case we used version 3.6 (poi-3.6-20091214.jar).  The Test folder  contained  a folder called pf2excel and inside here we had the java class  we wrote (PF2Excel.class).

Below is the source of our java program.  The program will convert an iseries physical file in to an excel file.  The program requires three parameters:-

1. iseries file - The name of the iseries file you wish to create as an excel file
2. iseries library - The library containing the file
3. IFS path - The full path and file name of where the excel file should be created.

When the java program runs it will create an excel file containing all the iseries data from the file/library specified.  In addition row one will be a header row.  Column titles are based on the COLHDG keyword in the file DDS or if this is not specified the columns field name.

package pf2excel;

import java.io.FileOutputStream;
import java.sql.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataFormat;

public class PF2Excel {

    //iseriesFile - The name of the file to be concerted to CSV
    //iseriesLib - Library containing the file
    //ifsFile - Full Path of where the XL file should be placed
    private String iseriesFile, iseriesLib, ifsFile;

   
    private String sDriver = "com.ibm.db2.jdbc.app.DB2Driver";
    private String sURL = "jdbc:db2:DKS;translate binary=true;extended metadata=true";
   
    private Connection connection;
    private ResultSet rs;
    private PreparedStatement stmt;
   
    //Array used to store file Meta data information
    private String[] arColNames;
    private int[] arColType;
    private int[] arColScale;
    private int[] arColPrecision;
   
    //rownum - Keeps track of the current row in the woorkbook
    //cellnum - Keeps track of the Cell in the row being processed
    private int cellnum, rownum;

   
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private FileOutputStream fs;
    private HSSFRow row;
    private HSSFCell cell;
   
    //Cell Styles
    private HSSFFont font;
    private HSSFCellStyle headStyle;
       
    private HSSFFont font2;
    private HSSFCellStyle detailStyle;
    private HSSFCellStyle detailStyle1;
    private HSSFCellStyle detailStyle0;
    private HSSFCellStyle detailStyle2;
    private HSSFCellStyle detailStyle3;
    private HSSFCellStyle detailStyle4;
    private HSSFCellStyle detailStyle5;
    private HSSFCellStyle detailStyleDMY;
    private HSSFCellStyle detailStyleHMS;

    //Constructor
    PF2Excel(String iseriesFile, String iseriesLib, String ifsFile) {
        this.iseriesFile = iseriesFile;
        this.iseriesLib = iseriesLib;
        this.ifsFile = ifsFile;
    }

    private void CreateFile() {
       
        //Connect to Iseries
        establishConnection();
        //Run SQL Statement
        runSQL();
        //Create Excel workbook
        createWorkbook();
        //Create Header Row
        createHeader();
        //Create Detal Row
        createDetail();
        //Resize Columns
        autoSize();
        //Close Resources
        closeWorkbook();
        System.exit(0);
    }

    private void runSQL() {

        try {
            String s = "select * from " + iseriesLib + "." + iseriesFile;
            stmt = connection.prepareStatement(s);
            rs = stmt.executeQuery();
           
            //Get Meta Data information.
            //Need to know number of columns, Names and Data Type
            ResultSetMetaData rsMD = rs.getMetaData();
            int columnCount = rsMD.getColumnCount();
            arColNames = new String[columnCount];
            arColType = new int[columnCount];
            arColScale = new int[columnCount];
            arColPrecision = new int[columnCount];

            for (int i = 0; i < columnCount; i++) {
                arColType[i] = rsMD.getColumnType(i + 1);
                arColNames[i] = rsMD.getColumnLabel(i + 1);
                arColScale[i] = rsMD.getScale(i + 1);
                arColPrecision[i] = rsMD.getPrecision(i + 1);
            }

        } catch (Exception e) {
            System.err.println("Error Running SQL");
            e.printStackTrace();
            return;
        } // end catch

    }

    private void establishConnection() {

        try {
            Class.forName(sDriver);
            connection = DriverManager.getConnection(sURL);
        } catch (Exception e) {
            System.err.println("Unable to Connect.");
            e.printStackTrace();
            return;
        } // end catch
    }

    public void createWorkbook() {

        try {
            fs = new FileOutputStream(ifsFile);
            // Create workbook
            wb = new HSSFWorkbook();
            sheet = wb.createSheet("Data");
           
        }// end try
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void closeWorkbook() {

        try {
            wb.write(fs);
            fs.close();
            rs.close();
            stmt.close();
            connection.close();

        }// end try
        catch (Exception e) {
            e.printStackTrace();

        }

    }

    public void autoSize() {
        for (int i = 0; i < arColNames.length; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    public void createHeader() {

        try {
            // Freeze Top row
            sheet.createFreezePane(0, 1, 0, 1);
            sheet.setZoom(9, 10); // 90 percent magnification
            font = wb.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setFontName("Calibri");
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headStyle = wb.createCellStyle();
            headStyle.setFont(font);

            cellnum = 0;
            rownum = 0;

            row = sheet.createRow(rownum);
            row.setHeightInPoints((float) 12.75);

           
            for (String s : arColNames) {
                cell = row.createCell(cellnum++);
                cell.setCellStyle(headStyle);
                cell.setCellValue(s);
            }

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

        }

    }

    public void createDetail() {

        try {

            font2 = wb.createFont();
            font2.setFontHeightInPoints((short) 11);
            font2.setFontName("Calibri");

            detailStyle = wb.createCellStyle();
            detailStyle.setFont(font2);

            DataFormat format = wb.createDataFormat();
       
            detailStyle0 = wb.createCellStyle();
            detailStyle0.setFont(font2);
            detailStyle0.setDataFormat(format.getFormat("0"));
            detailStyle1 = wb.createCellStyle();
            detailStyle1.setFont(font2);
            detailStyle1.setDataFormat(format.getFormat("0.0"));
            detailStyle2 = wb.createCellStyle();
            detailStyle2.setFont(font2);
            detailStyle2.setDataFormat(format.getFormat("0.00"));
            detailStyle3 = wb.createCellStyle();
            detailStyle3.setFont(font2);
            detailStyle3.setDataFormat(format.getFormat("0.000"));
            detailStyle4 = wb.createCellStyle();
            detailStyle4.setFont(font2);
            detailStyle4.setDataFormat(format.getFormat("0.0000"));
            detailStyle5 = wb.createCellStyle();
            detailStyle5.setFont(font2);
            detailStyle5.setDataFormat(format.getFormat("0.00000"));
            detailStyleDMY = wb.createCellStyle();
            detailStyleDMY.setFont(font2);
            detailStyleDMY.setDataFormat(format.getFormat("dd/mm/yyyy"));
            detailStyleHMS = wb.createCellStyle();
            detailStyleHMS.setFont(font2);
            detailStyleHMS.setDataFormat(format.getFormat("HH:MM:SS"));

            while (rs.next()) {

                cellnum = 0;
                int colIdx = 1;
                rownum++;

                row = sheet.createRow(rownum);
                row.setHeightInPoints((float) 12.75);

                for (int i : arColType) {
                    cell = row.createCell(cellnum);

                    if (i == 1) {
                        cell.setCellStyle(detailStyle);
                        cell.setCellValue(rs.getString(colIdx));
                    }
                    if (i == 3) {

                        switch (arColScale[colIdx - 1]) {
                        case 0:

                            if (arColNames[colIdx - 1].contains("Date")
                                    && arColPrecision[colIdx - 1] == 7) {
                                cell.setCellStyle(detailStyleDMY);
                                if (rs.getInt(colIdx) > 0) {
                                    cell.setCellValue(HSSFDateUtil
                                            .parseYYYYMMDDDate(fmtDateYYYYMMDD(rs
                                                    .getInt(colIdx))));
                                }
                            } else if ((arColNames[colIdx - 1].contains("Time") && arColPrecision[colIdx - 1] == 6)) {
                                cell.setCellStyle(detailStyleHMS);
                                cell.setCellValue(rs.getInt(colIdx));
                            } else {
                                cell.setCellStyle(detailStyle0);
                                cell.setCellValue(rs.getDouble(colIdx));
                            }
                            break;
                        case 1:
                            cell.setCellStyle(detailStyle1);
                            cell.setCellValue(rs.getDouble(colIdx));
                            break;
                        case 2:
                            cell.setCellStyle(detailStyle2);
                            cell.setCellValue(rs.getDouble(colIdx));
                            break;
                        case 3:
                            cell.setCellStyle(detailStyle3);
                            cell.setCellValue(rs.getDouble(colIdx));
                            break;
                        case 4:
                            cell.setCellStyle(detailStyle4);
                            cell.setCellValue(rs.getDouble(colIdx));
                            break;
                        default:
                            cell.setCellStyle(detailStyle5);
                            cell.setCellValue(rs.getDouble(colIdx));
                            break;

                        }

                    }

                    if (i == 91) {
                        cell.setCellStyle(detailStyleDMY);
                        cell.setCellValue(HSSFDateUtil.parseYYYYMMDDDate(rs
                                .getDate(colIdx).toString()));
                    }

                    if (i == 92) {
                        cell.setCellStyle(detailStyleHMS);
                        cell.setCellValue(rs.getTime(colIdx));
                    }

                    if (i == 93) {
                        cell.setCellStyle(detailStyle);
                        cell.setCellValue(rs.getTimestamp(colIdx).toString());
                    }

                    colIdx++;
                    cellnum++;

                }
            }

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

        }

    }

    public String fmtDateYYYYMMDD(int d) {

        d = d + 19000000;
        String d1 = String.valueOf(d);
        return d1.substring(0, 4) + "/" + d1.substring(4, 6) + "/"
                + d1.substring(6, 8);

    }

    public static void main(String[] args) {
        PF2Excel c = new PF2Excel(args[0].trim(), args[1].trim(),
                args[2].trim());
        c.CreateFile();
    }
}


We wrote a simple CL program that uses the RUNJVA progrmam to call our class.
RUNJVA     CLASS('pf2excel/PF2Excel') PARM(&P1 &P2 &P3) +
             CLASSPATH('/java:/java/JT400/lib/jt400Nativ+
             e.jar:/java/poi/poi-3.6-20091214.jar:/java/+
             Test') PROP((java.version 1.5) +            
             (java.awt.headless true)) OUTPUT(*PRINT)    

We hope the following shows that converting Iseries data to Excel from within a program is easier then you might think!

If you need and help to  get it working just let us know.