Java Excel API

Where to get the jexcelapi?

Get the API download form --> http://jexcelapi.sourceforge.net/

What is jexcelapi ?

A Java API to read, write, and modify Excel spreadsheets.

Now java developers can read Excel spreadsheets, modify them with a convenient and

simple API, and write the changes to any output stream (e.g. disk, HTTP, database, or

any socket).

Because it is Java, the API can be invoked from within a servlet, thus giving access to

Excel spreadsheets over internet and intranet web applications.

Features of jexcelapi

Reads data from Excel 95, 97, 2000, XP, and 2003 workbooks Reads and writes formulas (Excel 97 and later only) Generates spreadsheets in Excel 2000 format Supports font, number and date formatting Supports shading, bordering, and coloring of cells Modifies existing worksheets Is internationalized, enabling processing in almost any locale, country, language, or character encoding (formulas are currently only supported in English, French, Spanish, and German, but more can be added if translated) Supports copying of charts Supports insertion and copying of images into spreadsheets Supports logging with Jakarta Commons Logging, log4j, JDK 1.4 Logger, etc ...and much more.

Technical notes ==> http://www.andykhan.com/jexcelapi/technotes.html

JExcelApi JavaDoc ==> http://jexcelapi.sourceforge.net/resources/javadocs/index.html

Pre-Requirements:

• Should be knowing basic concepts of Java.
• Should know how to set class path to use third party Api's.
  

Now we will see how to read a spread sheet using this API:

First of all we will create a spread sheet like the one below and store it in our local folder.

I am saving this file in "D:testmyFile.xls"

To read the spread sheet content using jxl Api, first we have to create an object called
Workbook. Once you create the Workbook then you will get access to individual sheets.
Note that these sheets are Zero indexed.
So you have to use some thing like workbook.getSheet(0);

Once you get the sheet then you can easily get the cells and their content as string.
If you want it is also possible to get the data with out changing the type as it is.

See the sample code below (SpreadsheetReader.java).

package com.jxl.dhanago;

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
* This Java program is used to read the spread sheet and print it in
* console output.
*
* @author dhanago
*/
public class SpreadsheetReader
{

/**
* This method is used to read a spread sheet and print it in console.
*
* @param xlsPath
*/
public void readSpreadSheet( String xlsPath )
{
try
{
/*
* To read the spread sheet , first we have to create a workbook
* object like one shown below.
*/
Workbook workbook = Workbook.getWorkbook( new File( xlsPath ) );
/*
* then get the sheet index 0. Note the index starts with 0.
*/
Sheet sheet = workbook.getSheet( 0 );
/*
* get the cell form the sheet object like below.
*/
Cell cell00 = sheet.getCell( 0, 0 );
Cell cell01 = sheet.getCell( 0, 1 );
Cell cell02 = sheet.getCell( 0, 2 );

/*
* now we will display the cell values as string in console output.
*/
System.out.println( "Cell00 value: " + cell00.getContents() );
System.out.println( "Cell01 value: " + cell01.getContents() );
System.out.println( "Cell02 value: " + cell02.getContents() );
// free up memory
workbook.close();
}
catch (BiffException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}

/**
* This is the main executable method used to test the spread sheet
* reader.
*
* @param args
*/
public static void main( String[] args )
{
SpreadsheetReader readSpreadsheet = new SpreadsheetReader();
String xlsPath = "D:\test\myFile.xls";
readSpreadsheet.readSpreadSheet( xlsPath );
}
}

OutPut for the above code:

Cell00 value: Name
Cell01 value: Muthukumar Dhanagopal
Cell02 value: Krish

The above code displays the cell values as string. How to get the same
data type and display them with out converting them to string. Is that
possible using this API?

Yes, it is possible.
Here is the code which does the same for you.
It checks the cell type for LABEL, NUMBER or DATE and then gets the value from
the cell type cast the value to that particular data type and displays it on the console.

package com.jxl.dhanago;

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
* This java program is used to get and display the xls data according to
* the type.
*
* @author dhanago
*/
public class ReadXLWithExactDataType
{

/**
* This method is used to display the xls data with exact type.
*
* @param xlsPath
*/
public void readDataWithType( String xlsPath )
{
try
{
/*
* To read the spread sheet , first we have to create a workbook
* object like one shown below.
*/
Workbook workbook = Workbook.getWorkbook( new File( xlsPath ) );
/*
* then get the sheet index 0. Note the index starts with 0.
*/
Sheet sheet = workbook.getSheet( 0 );
/*
* get the cell form the sheet object like below.
*/
Cell cell00 = sheet.getCell( 0, 0 );

if (cell00.getType() == CellType.LABEL)
{
System.out.println( "Type LABEL" );
LabelCell labelCell = (LabelCell) cell00;
System.out.println( "Label Cell: " + labelCell.getString() );
}
else if (cell00.getType() == CellType.NUMBER)
{
System.out.println( "Type NUMBER" );
NumberCell numberCell = (NumberCell) cell00;
System.out.println( "Number Cell: " + numberCell.getValue() );
}
else if (cell00.getType() == CellType.DATE)
{
System.out.println( "Type DATE" );
DateCell dateCell = (DateCell) cell00;
System.out.println( "Date Cell: " + dateCell.getDate() );
}
else
{
System.out.println( "Type not supported." );
}

/*
* now we will display the cell values as string in console output.
*/
System.out.println( "Cell00 value: " + cell00.getContents() );
// free up memory
workbook.close();
}
catch (BiffException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}

/**
* This is the main executable method used to test the spread sheet
* reader.
*
* @param args
*/
public static void main( String[] args )
{
ReadXLWithExactDataType readXL = new ReadXLWithExactDataType();
String xlsPath = "D:\test\myFile.xls";
readXL.readDataWithType( xlsPath );
}

}

The Output for the above code is:
Type LABEL
Label Cell: Name
Cell00 value: Name

Rule Engine - Drools

What is a Rule Engine?

Artificial Intelligence (A.I.) is a very broad research area that focuses on "Making computers think like people" and includes disciplines such as Neural Networks, Genetic Algorithms, Decision Trees, Frame Systems and Expert Systems. Knowledge representation is the area of A.I. concerned with how knowledge is represented and manipulated. Expert Systems use Knowledge representation to facilitate the codification of knowledge into a knowledge base which can be used for reasoning - i.e. we can process data with this knowledge base to infer conclusions. Expert Systems are also known as Knowledge-based Systems and Knowledge-based Expert Systems and are considered 'applied artificial intelligence'. The process of developing with an Expert System is Knowledge Engineering. EMYCIN was one of the first "shells" for an Expert System, which was created from the MYCIN medical diagnosis Expert System. Where-as early Expert Systems had their logic hard coded, "shells" separated the logic from the system, providing an easy to use environment for user input. Drools is a Rule Engine that uses the Rule Based approached to implement an Expert System and is more correctly classified as a Production Rule System.

The term "Production Rule" originates from formal grammar - where it is described as "an abstract structure that describes a formal language precisely, i.e., a set of rules that mathematically delineates a (usually infinite) set of finite-length strings over a (usually finite) alphabet" (wikipedia).

For more information visit the following URL
https://hudson.jboss.org/hudson/job/drools/lastSuccessfulBuild/artifact/trunk/target/docs/html/ch02.html

JBoss server in Eclipse

Defining JBoss server in Eclipse:

Step 1 : Open Eclipse WTP all in one pack in a new work space.

Step 2 : Change the perspective to J2EE Perspective if it is not currently in J2EE Perspective.

Step 3 : Once the Perspective is changed to J2EE, you can see a tab called Servers in the bottom right panel along with Problems, Tasks, Properties.

Step 4 : If the Servers tab is not found. Go to Eclipse menu : Windows > Show view and click on Servers, so that Server tab will be displayed.

Step 5 : Go to Servers tab window and right click the mouse. You will get a pop up menu called "New".

Step 6 : Clicking on the New menu you will get one more pop up called "Server". Click on it.

Step 7 : Now you will get Define New Server Wizard.

Step 8 : In the wizard there are options to define many servers. One among them is JBoss. Click on JBoss and Expand the tree.

Step 9 : Select JBoss v 4.0 and click next.

Step 10 : Now give the JDK directory and JBoss home directory. Click Next.

Step 11 : Now the wizard will show you the default Address, port, etc., Leave it as it is and click on Next.

Step 12 : Click on finish.

Step 13 : Now you can see the JBoss server listed in the Servers window and the status is Sopped.

Step 14 : JBoss server is now defined in Eclipse now and its ready to use from with in Eclipse IDE.

DOM parsing technique

This class follows the DOM parsing technique and does the following.

1.Parse the demoxml.xml file.
2.Search for the xml node by the name "parent1" from the xml.
3.Create a new child node by the name "newChild"
4.Create a text("new child of the parent1") to the "newChild" node
5.Append the "newChild" node to "parent1" node.
6.Save the changes (transform) to the xml.


Sample Code: (DOMParser.java)

package com.dom.dhanago;

/*
* This class follows the DOM parsing technique and does the following.
* 1.Parse the demoxml.xml file. 2.Search for the xml node by the name
* "parent1" from the xml. 3.Create a new child node by the name "newChild"
* 4.Create a text("new child of the parent1") to the "newChild" node
* 5.Append the "newChild" node to "parent1" node. 6.Save the changes
* (transform) to the xml.
*/

import java.io.File;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

public class DOMParser
{

public static void main( String[] args )
{
try
{
DocumentBuilderFactory factory = DocumentBuilderFactory
.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document document;
document = builder.parse( new File( "C:\\demoxml.xml" ) );
/*
* You can pass either xml file or string.To fetch the root element
* of the xml document.
*/
Element rootEle = document.getDocumentElement();
/*
* To fetch all the child nodes of the root element.
*/
NodeList childNodes = rootEle.getChildNodes();
/*
* To search an element from the whole document.
*/
NodeList parentNode = document.getElementsByTagName( "parent1" );
/*
* To create a new child element by the name "newChild".
*/
Element newChild = document.createElement( "newChild" );
/*
* Adding text data to "newChild".
*/
newChild.appendChild( document
.createTextNode( "new Child of the parent1 node" ) );

/*
* To append the "newChild" ,created above.
*/
parentNode.item( 0 ).appendChild( newChild );

TransformerFactory tranFactory = TransformerFactory.newInstance();
Transformer aTransformer = tranFactory.newTransformer();

/*
* Preparing the source which is the document created above.
*/
Source srcDocument = new DOMSource( document );
/*
* Destination will be same different xml.
*/
Result destxml = new StreamResult( new File( "C:\\demoxml.xml" ) );
/*
* Transforming document to destination xml.
*/
aTransformer.transform( srcDocument, destxml );
System.out.println( "Successfully transformed" );
}
catch (Exception e)
{
System.out.println( "exec " + e );
}
}
}

Code to convert from Xml-String to Document and Document to String

package ora.in;

import java.io.IOException;
import java.io.StringReader;
import java.io.StringWriter;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.FactoryConfigurationError;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

/**
* Code to convert from Xml-String to Document and Document to String.
*
* @author Muthu
*/
public class S2DandD2S
{
public static Document loadXmlFileToDocument( String strXml,
boolean ignoreComments )
{
Document docRet = null;
try
{
DocumentBuilderFactory factory = DocumentBuilderFactory
.newInstance();
factory.setIgnoringComments( ignoreComments );
DocumentBuilder docBuilder = factory.newDocumentBuilder();
docRet = docBuilder.parse( new InputSource( new StringReader(
strXml ) ) );
}
catch (SAXException e)
{
System.out.println( "SAXException" );
}
catch (IOException e)
{
System.out.println( "IOException" );
}
catch (ParserConfigurationException e)
{
System.out.println( "ParserConfigurationException" );
}
catch (FactoryConfigurationError e)
{
System.out.println( "FactoryConfigurationError" );
}
return docRet;
}

/**
* @param args
*/
public static void main( String[] args )
{
try
{
String xml = "Some exception file as string";

Document document = loadXmlFileToDocument( xml, false );
TransformerFactory tranFactory = TransformerFactory.newInstance();
Transformer aTransformer = tranFactory.newTransformer();

Source srcDocument = new DOMSource( document );

// Preparing the source which is the document created above.
StringWriter writer = new StringWriter();
Result destxml = new StreamResult( writer );

// Transformingdocument to destination xml.
aTransformer.transform( srcDocument, destxml );

System.out.println( writer.toString() );
System.out.println( "Successfully transformed" );
}
catch (Exception e)
{
System.out.println( "exec " + e );
}
}
}

Parsing Java InputStream To Text and Vice Versa

Here are couple of useful methods to convert from InputStream to String
and vice versa.

Input Stream to String

public String parseISToString(java.io.InputStream is){
java.io.DataInputStream din = new java.io.DataInputStream(is);
StringBuffer sb = new StringBuffer();
try{
String line = null;
while((line=din.readLine()) != null){
sb.append(line+"\n");
}
}catch(Exception ex){
ex.getMessage();
}finally{
try{
is.close();
}catch(Exception ex){}
}
return sb.toString();
}

String to InputStream

public java.io.InputStream parseStringToIS(String xml){
if(xml==null) return null;
xml = xml.trim();
java.io.InputStream in = null;
try{
in = new java.io.ByteArrayInputStream(xml.getBytes("UTF-8"));
}catch(Exception ex){
}
return in;
}