Excel Creation Using JAVA

Creating the Excel using JAVA

POI terminology

The key terms associated with Jakarta POI are as follows:

• POIFS (Poor Obfuscation Implementation File System): Java APIs for reading and writing OLE (Object Linking and Embedding) 2 compound document formats
• HSSF (Horrible Spreadsheet Format): Java API to read Microsoft Excel
• HDF (Horrible Document Format): Java API to read and write Microsoft Word 97
• HPSF (Horrible Property Set Format): Java API for reading property sets using (only) Java

Create an Excel document

The Jakarta POI API can be used to create an Excel document programmatically. The important steps involved are:

• Create a workbook: HSSFWorkbook workbook = new HSSFWorkbook();
• Create a new worksheet in the workbook and name the worksheet "Java Excels": HSSFSheet sheet = workbook.createSheet("Java Excels");
• Create a new row in the sheet: HSSFRow row = sheet.createRow((short)0);
• Create a cell in the row: HSSFCell cell = row.createCell((short) 0);
• Put some content in the cell: cell.setCellValue("Have a Cup of XL");
• Write the workbook into the filesystem: workbook.write(fileOutputStream);

The key steps in reading the Excel sheet are as follows:

• Create a new Excel document reference: HSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));.
• Refer to the sheet: By default, the first sheet in the Excel document is at reference 0: HSSFSheet sheet = workbook.getSheetAt(0);. A sheet can also be referred to by name. Let's assume that the Excel sheet has the default name "Sheet1". It can be referred to as follows:HSSFSheet sheet = workbook.getSheet("Sheet1");.
• Refer to a row: HSSFRow row = sheet.getRow(0);.
• Refer to a cell in the row: HSSFCell cell = row.getCell((short)0);.
• Get the values in that cell: cell.getStringCellValue();.

A Practical Example

Let's concentrate on just the interesting steps of Jakarta POI usage:

• Create a new Excel document: workbook = new HSSFWorkbook();
• Make a worksheet in that document and give the worksheet a name:sheet = workbook.createSheet("Java Class Info");
• Set the first three columns' widths:sheet.setColumnWidth((short)0,(short)10000 );
• Create the header line: HSSFRow row = sheet.createRow((short)0);
• Create and set font and cell style:

     HSSFFont font = workbook.createFont();    font.setColor(HSSFFont.COLOR_RED);    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    // Create the style       HSSFCellStyle cellStyle= workbook.createCellStyle();       cellStyle.setFont(font); 

• Use the cell style:

        HSSFCell cell = row.createCell((short) 0);       cell.setCellStyle(cellStyle);       cell.setCellType(HSSFCell.CELL_TYPE_STRING);       cell.setCellValue("Class Name "); 

• Write the output file:

        FileOutputStream fOut = new FileOutputStream(outputFile);       // Write the Excel sheet       workbook.write(fOut);       fOut.flush();       // Done deal. Close it.       fOut.close(); 

  
  

A Good Reference for the HSSFWorkBook is provided below:- http://poi.apache.org/spreadsheet/quick-guide.html