Tip

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.