Code for creating and writing to a Excel Sheet

JExcel API provides various classes to create, read, write data to Excel documents at runtime. The required platform is JVM which means the code developed with JExcel can be run on Windows and Linux without any modification.

The example below creates a new document and writes data into different sheets of the new Excel document.

import java.io.*;

import jxl.*;

import java.util.*;

import jxl.Workbook;

import jxl.write.DateFormat;

import jxl.write.Number;

import jxl.write.*;

import java.text.SimpleDateFormat;

class create

{

public static void main(String[] args)

{

try

{

String filename = "input.xls";

WorkbookSettings ws = new WorkbookSettings();

ws.setLocale(new Locale("en", "EN"));

WritableWorkbook workbook =

Workbook.createWorkbook(new File(filename), ws);

WritableSheet s = workbook.createSheet("Sheet1", 0);

WritableSheet s1 = workbook.createSheet("Sheet1", 0);

writeDataSheet(s);

writeImageSheet(s1);

workbook.write();

workbook.close();

}

catch (IOException e)

{

e.printStackTrace();

}

catch (WriteException e)

{

e.printStackTrace();

}

}

private static void writeDataSheet(WritableSheet s)

throws WriteException

{

/* Format the Font */

WritableFont wf = new WritableFont(WritableFont.ARIAL,

10, WritableFont.BOLD);

WritableCellFormat cf = new WritableCellFormat(wf);

cf.setWrap(true);

/* Creates Label and writes date to one cell of sheet*/

Label l = new Label(0,0,"Date",cf);

s.addCell(l);

WritableCellFormat cf1 =

new WritableCellFormat(DateFormats.FORMAT9);

DateTime dt =

new DateTime(0,1,new Date(), cf1, DateTime.GMT);

s.adCell(dt);

/* Creates Label and writes float number to one cell of sheet*/

l = new Label(2,0,"Float", cf);

s.addCell(l);

WritableCellFormat cf2 = new WritableCellFormat(NumberFormats.FLOAT);

Number n = new Number(2,1,3.1415926535,cf2);

s.addCell(n);

n = new Number(2,2,-3.1415926535, cf2);

s.addCell(n);

/* Creates Label and writes float number upto 3

decimal to one cell of sheet */

l = new Label(3,0,"3dps",cf);

s.addCell(l);

NumberFormat dp3 = new NumberFormat("#.###");

WritableCellFormat dp3cell = new WritableCellFormat(dp3);

n = new Number(3,1,3.1415926535,dp3cell);

s.addCell(n);

/* Creates Label and adds 2 cells of sheet*/

l = new Label(4, 0, "Add 2 cells",cf);

s.addCell(l);

n = new Number(4,1,10);

s.addCell(n);

n = new Number(4,2,16);

s.addCell(n);

Formula f = new Formula(4,3, "E1+E2");

s.addCell(f);

/* Creates Label and multipies value of one cell of sheet by 2*/

l = new Label(5,0, "Multipy by 2",cf);

s.addCell(l);

n = new Number(5,1,10);

s.addCell(n);

f = new Formula(5,2, "F1 * 3");

s.addCell(f);

/* Creates Label and divide value of one cell of sheet by 2.5 */

l = new Label(6,0, "Divide",cf);

s.addCell(l);

n = new Number(6,1, 12);

s.addCell(n);

f = new Formula(6,2, "F1/2.5");

s.addCell(f);

}

private static void writeImageSheet(WritableSheet s)

throws WriteException

{

/* Creates Label and writes image to one cell of sheet*/

Label l = new Label(0, 0, "Image");

s.addCell(l);

WritableImage wi = new WritableImage(0, 3, 5, 7, new File("image.png"));

s.addImage(wi);

/* Creates Label and writes hyperlink to one cell of sheet*/

l = new Label(0,15, "HYPERLINK");

s.addCell(l);

Formula f = new Formula(1, 15,

"HYPERLINK(\"http://www.andykhan.com/jexcelapi\", "+

"\"JExcelApi Home Page\")");

s.addCell(f);

}

}

Add Rich Media Content to Your J2EE Apps with Enterprise Media Beans

Add Rich Media Content to Your J2EE Apps with Enterprise Media Beans

Today's users demand animated images, interactive movies, high-quality sound videos, and much more from their application experience. Thankfully, the EMB specification proposes a simple, lightweight media framework you can use to integrate rich media content into your J2EE applications.

Streamline Your JSP Management with Enhydra

Streamline Your JSP Management with Enhydra

JSP pages can be difficult to manage. They contain hundreds of HTML tags and stylesheets and when business logic is added, they become near-impossible to modify. Enhydra's application server solves this problem with an XML/DOM tree-based structure that's easy to manipulate and simple to maintain.

Six Steps to Faster J2EE Apps: Performance Tuning with JSP and Servlets

 Six Steps to Faster J2EE Apps: Performance Tuning with JSP and Servlets

Can your J2EE application sustain a large number of client requests simultaneously? Or does it become sluggish, with painfully slow response times? Learn about six simple steps you can take to enhance your app's performance.

Creating Content and Protocol Handlers in Java

 Creating Content and Protocol Handlers in Java

Did you know you can mimic the way your browser handles MIME types in your Java apps? Learn how by creating a Web client, server, and a dedicated viewer for a new image type.

Processing: Open Source Language Brings You Closer to Web 2.0

 Processing: Open Source Language Brings You Closer to Web 2.0

With Web 2.0 forcing Web developers to focus on the end-user experience and creating immersive environments, Processing is a welcome addition to the playing field of tools enabling images, animation, and sound. Find out why this open source language/environment is giving the Java 3D API a run for its money.

Accessing a Database with the JSTL 1.1 SQL Tag Library

Accessing a Database with the JSTL 1.1 SQL Tag Library

The JavaServer Pages Standard Tag Library (JSTL) provides core tags for common structural tasks such as iteration and conditional processing, XML tags for XML processing, internationalization tags for formatting, and SQL tags for database processing. Learn how to use JSTL's SQL tags in a JSP app to retrieve data from a database and display it in a table.

Recipes for Cookie Management in J2SEs Tiger and Mustang

 Recipes for Cookie Management in J2SEs Tiger and Mustang

Learn the basics of cookie management: receiving, processing, creating, and sending—plus how to implement the abstract CookieHandler class in J2SE Tiger and J2SE Mustang.

HTML output to the browser from a Servlet based on XML data using Jakarta Xalan 2 and Xerces 2.

Creating HTML output to the browser from a Servlet based on XML data often comes up.
Here's a fully functional example on how to achieve this using Jakarta Xalan 2 and Xerces 2.

package somepackage;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.TransformerFactoryConfigurationError;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;

import org.w3c.dom.Document;

/**
* Generate servlet output based on XML input and an XSLT document.
* filename, XML Document (DOM Object), and an optional contenttype
* are to be passed in as requestparameters.
* @author Jeroen Wenting
*/
public class Generator extends HttpServlet
{
public static final String defContentType = "text/html; charset=UTF-8";
public static final String contentTypeHTML = defContentType;
// public static final String contentTypeXML = "text/xml; charset=UTF-8";
// public static final String contentTypeCSV = "application/vnd.ms-excel";
public static final String errorTemplate = "+++ERRORS+++";
private static HashMap cache;
/**
*
*/
public Generator()
{
super();
}

/**
* @see javax.servlet.http.HttpServlet#doGet(javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
doPost(request, response);
}

/**
* @see javax.servlet.http.HttpServlet#doPost(javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
String contentType = null;
Document doc = null;
String xsl = null;

contentType = (String)request.getAttribute("contenttype");
if (contentType == null) contentType = defContentType;
doc = (Document)request.getAttribute("xml-doc");
xsl = (String)request.getAttribute("xsl-file-name");
String path = getServletContext().getRealPath("/WEB-INF/xsl/");
// Output goes in the response stream.
PrintWriter out = response.getWriter();

if (doc == null xsl == null)
{
String errorMsg = "";
if (doc == null)
errorMsg += "XML document missing from call to OutputGenerator<br />";
if (xsl == null)
errorMsg += "XSL filename missing from call to OutputGenerator<br />";
StringBuffer buf = generateError(errorMsg);
log(errorMsg);
out.write(buf.toString());
return;
}

// The servlet returns HTML.
response.setContentType(contentType);
if (cache == null) cache = new HashMap();
Transformer t = null;
// Get the XML input document and the stylesheet.
Source xmlSource = new DOMSource(doc);
// Perform the transformation, sending the output to the response.

// XSL processing can be time consuming, but this is mainly due to the overhead
// of compiling the XSL sheet.
// By caching the compiled sheets, the process is speeded up dramatically.
// Time saved on subsequent requests can be 99% or more (hundreds of milliseconds).
try
{
// check if the XSL sheet was found in cache, and use that if available
if (cache.containsKey(xsl)) t = (Transformer)cache.get(xsl);
else
{
// otherwise, load the XSL sheet from disk, compile it and store the compiled
// sheet in the cache
TransformerFactory tFactory = TransformerFactory.newInstance();
Source xslSource = new StreamSource(new File(path, xsl+".xsl"));
t = tFactory.newTransformer(xslSource);
cache.put(xsl, t);
}
// perform the XSL transformation of the XML Document into the servlet outputstream
t.transform(xmlSource, new StreamResult(out));
}
catch (TransformerConfigurationException e)
{
e.printStackTrace();
throw new ServletException(e);
}
catch (TransformerFactoryConfigurationError e)
{
e.printStackTrace();
throw new ServletException(e);
}
catch (TransformerException e)
{
e.printStackTrace();
throw new ServletException(e);
}

}

private StringBuffer generateError(String error) throws IOException
{

String path = getServletContext().getRealPath("/WEB-INF/templates/");
File f = new File(path, "callerror.html");
FileInputStream fs = new FileInputStream(f);
BufferedInputStream bis = new BufferedInputStream(fs);
int numBytes = bis.available();
byte b[] = new byte[numBytes];
// read the template into a StringBuffer (via a byte array)
bis.read(b);
StringBuffer buf = new StringBuffer();
buf.append(b);
int start = buf.indexOf(errorTemplate);
int end = start + errorTemplate.length();
// replace placeholder with errormessage
// (will automatically adjust to take longer or shorter data into account)
buf.replace(start, end, error);
return buf;
}

}
[/code]

======================================================
Sample XSLT:

[code]
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:import href="title.xsl" />

<xsl:output method="html" indent="yes"/>
<xsl:template match="ehwa-doc">
<html>
<xsl:call-template name="title"/>
<body><xsl:attribute name="onload"><xsl:apply-templates select="focuscontrol"/></xsl:attribute>
<form>
<input type="hidden" value="1" name="runset" id="runset"/>
</form>
<xsl:call-template name="hello"/>
</body>
</html>
</xsl:template>

<xsl:template match="focuscontrol">
<xsl:value-of select="."/>
</xsl:template>

<xsl:template name="hello">
<xsl:value-of select="data" />
</xsl:template>

</xsl:stylesheet>
[/code]
======================================================
Error HTML template:

[code]
<?xml version="1.0"?>
<!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" lang="en-US" xml:lang="en-US">
<head>
<title></title>
</head>

<body>
+++ERRORS+++
</body>
</html>
[/code]

======================================================
Sample servlet to generate the XML and forward to the generator:

[code]
package somepackage;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.xerces.dom.DocumentImpl;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

/**
* @author Jeroen Wenting
*/
public class XSLTest extends HttpServlet
{

/**
* @see javax.servlet.http.HttpServlet#doGet(javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
*/
protected void doGet(HttpServletRequest arg0, HttpServletResponse arg1)
throws ServletException, IOException
{
doPost(arg0, arg1);
}

/**
* @see javax.servlet.http.HttpServlet#doPost(javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
String xslFilename = getInitParameter("xslfile-html");
String beanName = getInitParameter("formbean");
String contentType = getInitParameter("contenttype");
request.setAttribute("xsl-file-name", xslFilename);
if (contentType != null) request.setAttribute("contenttype", contentType);
Document doc = new DocumentImpl();

Element root = doc.createElement("ehwa-doc");

// generate XML for transformation
Element elem = doc.createElement("focuscontrol");
elem.appendChild(doc.createTextNode("alert('loaded')"));
root.appendChild(elem);
elem = doc.createElement("data");
elem.appendChild(doc.createTextNode("Hello World!"));
root.appendChild(elem);

doc.appendChild(root);
request.setAttribute("xml-doc", doc);
RequestDispatcher rd = request.getRequestDispatcher("/servlet/generator");
rd.forward(request, response);
}
}

Reading Excel files using Java code

POI library will allow to read xls files in java
Click here for (Faq)
http://jakarta.apache.org/poi/hssf/how-to.html
& to Download(jar) Click
http://www.apache.org/dyn/closer.cgi/jakarta/poi/

Work with SSL/LDAP Using Java

Work with SSL/LDAP Using Java

Most applications nowadays authenticate through LDAP (directory service). To set this up, first register the SSL certificate using the keytool utility, as shown below:

  Register ssl certificate using keytool:  

keytool -import -alias &ltcertname> -file <filename.crt> -keystore "..yourpath\java\jre\lib\security\cacerts" 

The following code shows how to connect to LDAP and display the values specific to a username:

 ..class name.. ..  

public static void main(String args[])

{  

String keystore = System.getProperty("" + "/lib/security/cacerts");

 System.setProperty(LDAPConstants.LDAP_SSL_TRUST_STORE,keystore);

  try{ 

Hashtable env = new Hashtable();

 env.put(Context.INITIAL_CONTEXT_FACTORY,

"com.sun.jndi.ldap.LdapCtxFactory"); 

env.put(Context.PROVIDER_URL, ldap://yourservername:636);

 env.put(Context.SECURITY_AUTHENTICATION, "simple");

 env.put(Context.SECURITY_PROTOCOL, "ssl"); 

 env.put(Context.SECURITY_PRINCIPAL, "yourusername");

 env.put(Context.SECURITY_CREDENTIALS, "yourpassword");

dirCtx = new InitialLdapContext(env, null); 

 NamingEnumeration ne = null; 

SearchControls controls =  new SearchControls(); 

controls.setSearchScope(SearchControls.SUBTREE_SCOPE);

 ne = dirCtx.search("OU=Users,DC=yourcompany, DC=com, DC=au",

"userName="+userName,controls);   

if (ne != null) 

{   

if (ne.hasMore()) 

{   

  SearchResult item = (SearchResult) ne.next();

display(item.getAttributes());

   }

  }

catch(javax.naming.AuthenticationException e)

{  

 e.printStackTrace(); 

}

catch(NamingException e) 

{  

 e.printStackTrace(); 

} 

} 

private static void display(Attributes attr) 

throws NamingException{ 

  NamingEnumeration ne = attr.getAll(); 

  while(ne.hasMore())

{  Attribute  obj = (Attribute)ne.next();  

System.out.println(obj.getID()+"\t"+(String)obj.get(0));  

 } }