What is WSDL?

WSDL stands for Web Services Description Language. A WSDL file is an XML document that describes a Web service. It specifies the location of the service and the operations (or methods) the service exposes. For more information about WSDL, refer to the WSDL specification, or read the WSDL tutorial

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

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;
}