Saturday, August 13, 2011

JXL, great java API for parsing excel documents

Most of the times i use Apache POI for my ms document parsing, but the other day i found an interesting tool, very easy to use, that allows you to parse excel. It is called JXL.
Here a very simple program that will parse the contents of an excel sheet and display the values in the console:

 import java.io.BufferedWriter;  
 import java.io.File;  
 import java.io.FileWriter;  
 import java.io.IOException;  
 import java.io.PrintWriter;  
 import jxl.Cell;  
 import jxl.CellType;  
 import jxl.Sheet;  
 import jxl.Workbook;  
 import jxl.read.biff.BiffException;  
 public class Convertor {  
      private String inputFile;  
      private FileWriter fosStream;  
      private PrintWriter out = null;  
      private String outputFilePath = "";  
      public void setInputFile(String inputFile) {  
           this.inputFile = inputFile;  
      }  
      public void read() throws IOException {  
           File inputWorkbook = new File(inputFile);  
           Workbook w;  
           try {  
                w = Workbook.getWorkbook(inputWorkbook);  
                // Get the first sheet  
                Sheet sheet = w.getSheet(0);  
                fosStream = new FileWriter(outputFilePath + "output.xml");  
                out = new PrintWriter(new BufferedWriter(fosStream));  
                out.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");  
                out.println("<Document>");  
                for (int j = 0; j < sheet.getColumns(); j++) {  
                     for (int i = 0; i < sheet.getRows(); i++) {  
                          Cell cell = sheet.getCell(j, i);  
                          CellType type = cell.getType();  
                          if (cell.getContents() == null  
                                    || cell.getContents().equals("")) {  
                               continue;  
                          }  
                          out.println("<" + cell.getColumn() + "" + cell.getRow()  
                                    + ">");  
                          out.println(cell.getContents());  
                          out.println("</" + cell.getColumn() + "" + cell.getRow()  
                                    + ">");  
                     }  
                }  
           } catch (BiffException e) {  
                e.printStackTrace();  
           }  
           out.write("</Document>");  
           out.flush();  
           out.close();  
      }  
      public static void main(String[] args) throws IOException {  
           Convertor test = new Convertor();  
           test.setInputFile("input.xls");  
           test.read();  
      }  
 }  

Just a mini pragmatical example of how to pass parameters from a composite component back to a template


The template layout:
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"  
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
 <html xmlns="http://www.w3.org/1999/xhtml"  
    xmlns:ui="http://java.sun.com/jsf/facelets"    
    xmlns:h="http://java.sun.com/jsf/html"  
    xmlns:f="http://java.sun.com/jsf/core">  
 <h:head>  
  <title><ui:insert name="pagetitle">Default page title</ui:insert></title>  
 </h:head>  
 <h:body>  
  <ui:insert name="content">  
                 Default page content  
  </ui:insert>  
  <!-- USE THE PARAMETER NAME TO CALL VARIABLES THAT ARE PASSED AS PARAMETERS FROM SOME COMPONENTS -->  
  #{someParam}  
 </h:body>  
 </html>  
 The composite component  
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"   
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
 <html xmlns="http://www.w3.org/1999/xhtml"  
      xmlns:ui="http://java.sun.com/jsf/facelets"  
      xmlns:h="http://java.sun.com/jsf/html"  
      xmlns:f="http://java.sun.com/jsf/core">  
 <ui:composition template="WEB-INF/templates/masterLayout.xhtml">  
      <ui:define name="pagetitle">  
                     This is the index page  
      </ui:define>  
      <ui:define name="content">  
           <p>A value can be passed from the composition to a template using the ui:param tag</p>  
           <ui:param name="someParam" value="#{someBB.something}"/>  
      </ui:define>       
 </ui:composition>  
 </html>  
And now the backing bean that holds the value:
 package backingbeans;  
 import javax.enterprise.context.RequestScoped;  
 import javax.inject.Named;  
 @Named("someBB")  
 @RequestScoped  
 public class SomeBB {  
      private String something;  
      public SomeBB() {  
           something = "THIS IS SOME DEFAULT VALUE";  
      }  
      public String getSomething() {  
           return something;  
      }  
      public void setSomething(String something) {  
           this.something = something;  
      }  
 }  


Share with your friends