Example 2: Using the Apache POI framework

This example shows how to create an XLS file, using the Apache POI framework. You must download and install the Apache POI JAR file and make the CLASSPATH environment variable point to the POI JAR in order to compile and run this example. After execution, you should find a file named "itemlist.xls" in the current directory, which can be loaded with Microsoft™ Excel or Open Office Calc:

IMPORT JAVA java.io.FileOutputStream
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFWorkbook
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFSheet
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFRow
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFCell
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFCellStyle
IMPORT JAVA org.apache.poi.hssf.usermodel.HSSFFont
IMPORT JAVA org.apache.poi.ss.usermodel.IndexedColors

MAIN
    DEFINE fo FileOutputStream
    DEFINE workbook HSSFWorkbook
    DEFINE sheet HSSFSheet 
    DEFINE row HSSFRow 
    DEFINE cell HSSFCell 
    DEFINE style HSSFCellStyle 
    DEFINE headerFont HSSFFont 
    DEFINE i, id INTEGER, s STRING

    LET workbook = HSSFWorkbook.create()

    LET style = workbook.createCellStyle()
    CALL style.setAlignment(HSSFCellStyle.ALIGN_CENTER)
    CALL style.setFillForegroundColor(
                 IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    CALL style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    LET headerFont = workbook.createFont()
    CALL headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
    CALL style.setFont(headerFont);

    LET sheet = workbook.createSheet()

    LET row = sheet.createRow(0)
    LET cell = row.createCell(0)
    CALL cell.setCellValue("Item Id")
    CALL cell.setCellStyle(style)
    LET cell = row.createCell(1)
    CALL cell.setCellValue("Name")
    CALL cell.setCellStyle(style)

    FOR i=1 TO 10
        LET row = sheet.createRow(i)
        LET cell = row.createCell(0)
        CALL cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC)
        LET id = 100 + i 
        CALL cell.setCellValue(id)
        LET cell = row.createCell(1)
        LET s = SFMT("Item #%1",i)
        CALL cell.setCellValue(s)
    END FOR

    LET fo = FileOutputStream.create("itemlist.xls");
    CALL workbook.write(fo);
    CALL fo.close();

END MAIN