Create native excel files (XLS) on the iSeries using Java

Here a way to create native Excel files on the iSeries that will give you programming control over the cells formatting.

Here you'll find another way to create native Excel files on the iSeries that will give you programming control over the cells formatting (font assigning, color filling, alignment, border styling, data type assigning, data formatting, formula addition, resizing of rows/columns width and height, Header/Footer customizing , etc). In place of the creation of .csv type Excel files using the CPYTOIMPF command.

In order to understand the features that are given using the POI JavaAPIs, a sample code is presented with which an Excel file is created containing a simple Gantt chart that cannot be produced using the CPYTOIMPF command.

For the manipulation of Excel files, we are using the assistance of an open-source project offering of the Apache Software Foundation, known as POI project, which provides JavaAPIs for manipulating file formats that are based on Microsoft's OLE 2 Compound Document format. The project is divided into several components, including one called HSSF (HSSF stands for Horrible Spreadsheet Format!), which reads and writes Excel files, manipulating them extensively. You can read more about the overall structure of the POI project on the POI website here.

The HSSF component lets programmers read and write to Microsoft Excel 97-2002 files (in the BIFF8 file format) using Java. In particular, we are using the org.apache.poi.hssf.usermodel package in the POI jar file. You can obtain the JAR file here.. And because the JAR file may have long name, you can rename it as POI.JAR. In order to run the Java sample ensure that the POI.JAR has included in the classpath parameter.


 The sample code is presented below: 
  
// name: ExcelFileTest.java
import java.io.IOException;
import java.io.FileOutputStream;
// poi.jar must be included into the classpath
import org.apache.poi.hssf.usermodel.*;

public class ExcelFileTest {

// declare a workbook, rows, cell, sheet, styles and font object reference
static HSSFWorkbook wb;
static HSSFRow row, row0;
static HSSFCell cell;
static HSSFSheet sheet1;
static HSSFCellStyle style, style1;
static HSSFFont font;

public static void main(String[] args) {
try {   
  FileOutputStream fileOut = new FileOutputStream("test.xls"); // create a new file   
  wb = new HSSFWorkbook();  // create a new workbook
  sheet1 = wb.createSheet("sheet1");  // create a new sheet

  style = wb.createCellStyle();  // create two cell styles
  style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
  style1 = wb.createCellStyle();

  font = wb.createFont();  // Create a new font and alter it.
  font.setFontHeightInPoints((short)12);
  font.setFontName("Arial New");
  style1.setFont(font);
  
  headerline(); // create the first row of the sheet

  // create the detailed rows of the excel
  detailline(1,"Task1",3,5);
  detailline(2,"Task2",1,7);
  detailline(3,"Task3",2,12);
  detailline(4,"Task4",5,8);
  detailline(5,"Task5",9,10);
  detailline(6,"Task6",10,11);
 
  wb.write(fileOut);
  fileOut.close();       
  System.out.println("Done");
} catch (IOException e) {
  e.printStackTrace();
  }
}

public static void headerline(){
  row0 = sheet1.createRow((short)0); // create the row 0  

  HSSFCell cell = row0.createCell((short) 0); // create the cell 0 of row 0 and assign the value "Task"
  cell.setCellValue("Task");
  cell.setCellStyle(style1);

  cell = row0.createCell((short) 1); // create the cell 1 of row 0 and assign the value "From"
  cell.setCellValue("From");
  cell.setCellStyle(style1);

  cell = row0.createCell((short) 2);
  cell.setCellValue("To");
  cell.setCellStyle(style1);

  for (int i=1; i<=12; i++){
     row0.createCell((short) (i+2)).setCellValue(i);
     sheet1.setColumnWidth((short) (i+2),(short)1000);
  }
}

public static void detailline(int line,String task,int fromtime,int totime){
  row = sheet1.createRow((short)line);

  HSSFCell cell = row.createCell((short) 0);
  cell.setEncoding( HSSFCell.ENCODING_UTF_16 ); // Unicode Encoding for Multilanguage strings
  cell.setCellValue(task);
  cell.setCellStyle(style1);

  cell = row.createCell((short) 1);
  cell.setCellValue(fromtime);
  cell.setCellStyle(style1);

  cell = row.createCell((short) 2);
  cell.setCellValue(totime);
  cell.setCellStyle(style1);

  for (int i=3; i<13; i++){
    // if the value of the i cell of the row 0 is between the [from..to] values 
    if ((row0.getCell((short) i).getNumericCellValue() >= fromtime) &&
       (row0.getCell((short) i).getNumericCellValue() < totime))    
     {
     row.createCell((short) (i)).setCellStyle(style);
     }
  }
}
}

 
This was first published in March 2003

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close