Java Code to insert Image in the MS Excel sheet using POI

Hi All,

Today, I have been asked to develop a code which will insert an image into the excel sheet in Java.

Earlier we have developed code which will populate the data in MS excel using POI.

At first attempt, I have tried to do it with POI 2.5, but went in vain.

After my (re)search in the internet, I found a code which is using POI3.1-beta2.jar.

http://www.ibiblio.org/maven/org.apache.poi/jars/- to download the POI3.1-beta2.jar

http://poi.apache.org/apidocs/index.html - API to refer.

Code:

import org.apache.poi.hssf.usermodel.*;
import java.io.*;

public class InsertImageExcel {
public static void main(String[] args) {
int col=1,row=1;
HSSFWorkbook wb=new HSSFWorkbook();
HSSFSheet testsheet=wb.createSheet("test");
System.out.println("The work book is created");
try {
FileOutputStream fos=new FileOutputStream("C:/sample.xls");
System.out.println("File sample.xls is created");
FileInputStream fis=new FileInputStream("C:/home.jpg");
ByteArrayOutputStream img_bytes=new ByteArrayOutputStream();
int b;
while((b=fis.read())!=-1)
img_bytes.write(b);
fis.close();
int colnew = 10; // last col
int rownew = 25; // last row
// This will embed the picture from (1,1) cell to (10,10) cell of excel sheet.
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,0,0,(short)col,row,(short)colnew,rownew);
int index=wb.addPicture(img_bytes.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG);
HSSFSheet sheet=wb.getSheet("test");
HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
patriarch.createPicture(anchor,index);
anchor.setAnchorType(2);
wb.write(fos);
System.out.println("Writing data to the xls file");
fos.close();
System.out.println("File closed");
} catch(IOException ioe) {
ioe.printStackTrace();
}
}
}

Java code to parse XML using DOM (Document Object Model)

In the earlier article, we have gone through the SAX parsing concept. Here I am going to put source code of parsing an XML document using DOM.

There are two codes, one of which will be using JDK 1.5 for parsing the document and the second one is the code which will use xerces to parse the document (which we have used in SAX parsing).

Here we are going to use the same XML (Company.xml) which we have mentioned in the SAX parsing article.






/*Relevant APIs - http://java.sun.com/j2se/1.5.0/docs/api/org/w3c/dom/package-summary.html
http://java.sun.com/j2se/1.5.0/docs/api/javax/xml/parsers/package-summary.html
*/
//Using Parser available with standard JDK 1.5
import java.io.File;
import java.io.IOException;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class DOMParser
{
private Document doc = null;
public DOMParser()
{
try
{
doc = parserXML(new File("Company.xml"));
visit(doc, 0);
}
catch(Exception error)
{
error.printStackTrace();
}
}

//Recursively call the nodes and the elements and text nodes

public void visit(Node node, int level)
{
NodeList nl = node.getChildNodes();
for(int i=0, cnt=nl.getLength(); i < cnt; i++)
{
System.out.println("Nodes: "+nl.item(i));
visit(nl.item(i), level+1);
}
}

public Document parserXML(File file) throws SAXException, IOException, ParserConfigurationException
{
return DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(file);
}

public static void main(String[] args)
{
new DOMParser();
}
}










/*
To download the jar (xercesImpl.jar) -> http://www.apache.org/dist/xerces/j/
APIs - http://xerces.apache.org/xerces-j/apiDocs/org/apache/xerces/parsers/DOMParser.html
http://java.sun.com/j2se/1.5.0/docs/api/org/w3c/dom/package-summary.html
http://java.sun.com/j2se/1.5.0/docs/api/javax/xml/parsers/package-summary.html
*/

//DOM parsing example using Xerces Parser

// Core Java APIs
import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.net.MalformedURLException;

// Apache - Xerces Parser
import org.apache.xerces.parsers.DOMParser;

// DOM
import org.w3c.dom.Node;
import org.w3c.dom.Element;
import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.w3c.dom.NamedNodeMap;

// SAX
import org.xml.sax.SAXException;

public class DOMParserXerces {

private static void printNode(Node node, String indent) {

switch (node.getNodeType()) {

case Node.DOCUMENT_NODE:
// recurse on each child
NodeList nodes = node.getChildNodes();
if (nodes != null) {
for (int i=0; i < nodes.getLength(); i++) {
printNode(nodes.item(i), "");
}
}
break;

case Node.ELEMENT_NODE:
String name = node.getNodeName();
System.out.println("Element Name = " + name);
NamedNodeMap attributes = node.getAttributes();
for (int i=0; i < attributes.getLength(); i++) {
Node current = attributes.item(i);
System.out.println(" " + current.getNodeName() +"=\"" + current.getNodeValue() + "\"");
}

// recurse on each child
NodeList children = node.getChildNodes();
if (children != null) {
for (int i=0; i < children.getLength(); i++) {
printNode(children.item(i), indent + " ");
}
}

break;

case Node.TEXT_NODE:
System.out.println("Text Node Value = " + node.getNodeValue());
break;
}

}


/**
* Sole entry point to the class and application.
* @param args Array of String arguments.
*/
public static void main(String[] args) {

// Create URL String from incoming file
String url = "file:" + "./Company.xml";

System.out.println();
System.out.println("Object to Parse (String URL) : " + url);
System.out.println();

try {
// Create a DOM Parser
DOMParser parser = new DOMParser();
// Parser the incoming file (URL)
parser.parse(url);
// Obtain the document
Document doc = parser.getDocument();
// Print the document from the DOM tree and feed it an initial
// indentation of nothing
printNode(doc,"");
System.out.println("\n");
} catch (IOException ioe) {
ioe.printStackTrace();
} catch (SAXException saxe) {
saxe.printStackTrace();
}
}

}

Java code to read the windows registry

The following code will show us the proxy settings of the internet explorer and the version of the same.
Useful APIs

Preferences
Method

import java.lang.reflect.Method;
import java.util.prefs.Preferences;


public class JavaRegistryReader {
private static final int HKEY_CURRENT_USER = 0x80000001;
private static final int KEY_QUERY_VALUE = 1;
private static final int KEY_SET_VALUE = 2;
private static final int KEY_READ = 0x20019;
public static void main(String args[]) {
final Preferences userRoot = Preferences.userRoot();
final Preferences systemRoot = Preferences.systemRoot();
final Class clz = userRoot.getClass();
try {
final Method openKey = clz.getDeclaredMethod("openKey",byte[].class, int.class, int.class);
openKey.setAccessible(true);
final Method closeKey = clz.getDeclaredMethod("closeKey",int.class);
closeKey.setAccessible(true);
final Method winRegQueryValue = clz.getDeclaredMethod("WindowsRegQueryValueEx", int.class, byte[].class);
winRegQueryValue.setAccessible(true);
final Method winRegEnumValue = clz.getDeclaredMethod("WindowsRegEnumValue1", int.class, int.class, int.class);
winRegEnumValue.setAccessible(true);
final Method winRegQueryInfo = clz.getDeclaredMethod("WindowsRegQueryInfoKey1", int.class);
winRegQueryInfo.setAccessible(true);
byte[] valb = null;
String vals = null;
String key = null;
Integer handle = -1;
//Query Internet Settings for Proxy
key = "Software\\Microsoft\\Windows\\CurrentVersion\\Internet Settings";
handle = (Integer) openKey.invoke(userRoot, toCstr(key), KEY_READ, KEY_READ);
valb = (byte[]) winRegQueryValue.invoke(userRoot, handle.intValue(),toCstr("ProxyServer"));
vals = (valb != null ? new String(valb).trim() : null);
System.out.println("Proxy Server = " + vals);
closeKey.invoke(Preferences.userRoot(), handle);
// Query for IE version
key = "SOFTWARE\\Microsoft\\Internet Explorer";
handle = (Integer) openKey.invoke(systemRoot, toCstr(key), KEY_READ, KEY_READ);
valb = (byte[]) winRegQueryValue.invoke(systemRoot, handle, toCstr("Version"));
vals = (valb != null ? new String(valb).trim() : null);
System.out.println("Internet Explorer Version = " + vals);
closeKey.invoke(Preferences.systemRoot(), handle);
} catch (Exception e) {
e.printStackTrace();
}
}
private static byte[] toCstr(String str) {
byte[] result = new byte[str.length() + 1];
for (int i = 0; i < str.length(); i++) {
result[i] = (byte) str.charAt(i);
}
result[str.length()] = 0;
return result;
}
}

Thanks

SAX XML Parsing using Java

If you are into development world of any language (except probably Mainframe related language) you will find XML very handy. The reason might be the compatibility of this data storing language rather then data processing language with almost every known language.

XML stores data and if the data needs to be passed from one system to another system (system might be applications or any other computer languages), it needs to be parsed.Basically parsing is a way for reading data from and XML document


There are number of ways in which you can parse the XML document. Two most popular ways of the parsing is SAX parsing and DOM (Document Object Model).


Here we are going to have a look onto the SAX parsing. I took some important points from wiki which I am jotting down in a nutshell.


SAX or Simple API for XML is a serial access parser API for XML. It implements SAX functions as a stream parser, with an event-driven API.


The user defines a number of callback methods that will be called when events occur during parsing.

The SAX events include:
XML Text nodes, XML Element nodes, XML Processing Instructions, XML Comments.


Events are fired when each of these XML features are encountered, and again when the end of them is encountered. XML attributes are provided as part of the data passed to element events.


SAX parsing is unidirectional; previously parsed data cannot be re-read without starting the parsing operation again.



This XML document, when passed through a SAX parser, will generate a sequence of events like the following:
XML Element start, XML Text node, XML Element end.


To perform SAX parsing using JAVA, there are APIs available. I found xerces most useful.


The link for the API is xercesAPI

To download the installable (xercesImpl.jar) click here.

The XML which we are going to parse is:

< company name="TelComm Company Inc.">
< employees>
< employee>
< name>
< first>Virendra< /first>
< last>Sehwag< /last>
< /name>
< office>Delhi< /office>
< telephone>123456< /telephone>
< /employee>
< employee>
< name>
< first>Yuvraj< /first>
< last>Singh< /last>
< /name>
< office>Punjab< /office>
< telephone>123457< /telephone>
< /employee>
< /employees>
< /company>



The code for the SAX parsing is:

//Overridden methods are being called while parsing, there are still more methods which are being called
//However we have overridden only three methods (startElement(),endElement(),getText()).

/*

The significance of parameters of the startElement and endElement methods
uri - The Namespace URI, or the empty string if the element has no Namespace URI or if Namespace processing is not being performed.
localName - The local name (without prefix), or the empty string if Namespace processing is not being performed.
qName - The qualified XML 1.0 name (with prefix), or the empty string if qualified names are not available.
attributes - The specified or defaulted attributes of the tag
*/

import java.util.*;
import java.io.*;
import org.xml.sax.*;
import org.apache.xerces.parsers.*;
import org.xml.sax.helpers.DefaultHandler;

public class SAXParserDemo extends DefaultHandler {
CharArrayWriter text = new CharArrayWriter();
SAXParser parser = new SAXParser();

String firstName;
String lastName;
String office;
String telephone;

public void parse(InputStream is) throws SAXException, IOException {
parser.setContentHandler(this);
parser.parse( new InputSource(is));
}


//startElement - Overridden method of the DefaultHandler class, being called when parsing the XML
//this method to take specific actions at the start of each element(such as allocating a new tree node or writing output to a file)
public void startElement(java.lang.String uri, java.lang.String localName, java.lang.String qName, Attributes attributes) throws SAXException {
text.reset();
if (qName.equals("company")) {
String name = attributes.getValue("name");
String header = "Employee Listing For "+name;
System.out.println(header);
System.out.println();
}

}

//endElement - Overridden method of the DefaultHandler class, being called when parsing the XML
//this method to take specific actions at the end of each element (such as finalising a tree node or writing output to a file).

public void endElement(java.lang.String uri, java.lang.String localName, java.lang.String qName) throws SAXException {
if (qName.equals("first")) {
firstName = getText();
}

if (qName.equals("last")) {
lastName = getText();
}

if (qName.equals("office")) {
office = getText();
}

if (qName.equals("telephone")) {
telephone = getText();
}

if (qName.equals("employee")) {
System.out.println(office + "\t " + firstName + "\t" + lastName + "\t" + telephone);
}

}

public String getText() {
return text.toString().trim();
}

//characters - Overridden method of the DefaultHandler class
//this method to take specific actions for each chunk of character data (such as adding the data to a node or buffer, or printing it to a file).
public void characters(char[] ch, int start, int length) {
text.write(ch,start,length);
}

public static void main(String[] args) throws Exception {
SAXParserDemo saxParser = new SAXParserDemo();
saxParser.parse(new FileInputStream("Company.xml"));
}

}


Thanks. Hope you all will find it useful.

Wake on LAN using Java

Wake on LAN (WoL) is a standard that allows a computer to be turned on remotely by a message sent through the network.


It is platform independent technology that sends Magic Packet (containing its MAC address) to wake up computers.


When the listening computer receives this packet, the network card checks the packet for the correct information. If the Magic Packet is valid, the network card takes the computer out of hibernation or standby, or starts it up (Source: wiki).


In order for Wake on LAN to work, parts of the network interface need to stay on.


This consumes a standby power, small compared to the computer's normal operating power. If Wake on LAN is not needed, disabling it may reduce power consumption while the computer is switched off but still plugged in.
[Source: wiki]


To send magic packets in command prompt wolcmd command is used.


This is the java code to send Magic Packets to a machine provided its IP address and MAC address are available (compatible with java 1.6).



import java.io.*;
import java.net.*;

public class WakeOnLan {

public static final int PORT = 9;

public static void main(String[] args) {

String ipStr = "<>";
String macStr = "<>";
try {
byte[] macBytes = getMacBytes(macStr);
byte[] bytes = new byte[6 + 16 * macBytes.length];
for (int i = 0; i < 6; i++) {
bytes[i] = (byte) 0xff;
}
for (int i = 6; i < bytes.length; i += macBytes.length) {
System.arraycopy(macBytes, 0, bytes, i, macBytes.length);
}

InetAddress address = InetAddress.getByName(ipStr);
DatagramPacket packet = new DatagramPacket(bytes, bytes.length, address, PORT);
DatagramSocket socket = new DatagramSocket();
socket.send(packet);
socket.close();

System.out.println("Wake-on-LAN packet sent.");
}
catch (Exception e) {
System.out.println("Failed to send Wake-on-LAN packet:" + e);
System.exit(1);
}

}

private static byte[] getMacBytes(String macStr) throws IllegalArgumentException {
byte[] bytes = new byte[6];
String[] hex = macStr.split("(\\:\\-)");
if (hex.length != 6) {
throw new IllegalArgumentException("Invalid MAC address.");
}
try {
for (int i = 0; i < 6; i++) {
bytes[i] = (byte) Integer.parseInt(hex[i], 16);
}
}
catch (NumberFormatException e) {
throw new IllegalArgumentException("Invalid hex digit in MAC address.");
}
return bytes;
}
}

Finding MAC Address using Java

MAC Address stands for Media Access Control Address. It is a unique identifier to most network interface cards (LAN Card) by the manufacturer for identification. The MAC which is also known as a physical address or adapter address usually encodes the registered identification number of the manufacturer.


You can always find the MAC address of the computer by going to command prompt and type: ipconfig /all. The physical address of the machine is printed like the below format: 08-00-27-DC-4A-9E.


Here I am going to put the code to find out MAC address of a machine using JAVA.
We will do it using getHardwareAddress() method of NetworkInterface class of java.net package. The method has been introduced in the JDK 1.6. So the below program is compatible with JDK 1.6.

import java.net.NetworkInterface;
import java.net.SocketException;
import java.net.UnknownHostException;

public class MacAddress {

public static void main(String[] args) {
try {

InetAddress address = InetAddress.getLocalHost();
System.out.println("Address "+address);
/*
* Get NetworkInterface for the current host and then read the
* hardware address.
*/
NetworkInterface ni = NetworkInterface.getByInetAddress(address);
System.out.println("NI = "+ni);
byte[] mac = ni.getHardwareAddress();


/*
* Extract each array of mac address and convert it to hexa with the
* following format 08-00-27-DC-4A-9E.
*/

System.out.println("MAC Address is ");
for (int i = 0; i < mac.length; i++) {
System.out.format("%02X%s", mac[i], (i < mac.length - 1) ? "-" : "");
}
} catch (UnknownHostException e) {
e.printStackTrace();
} catch (SocketException e) {
e.printStackTrace();
}
}
}

Transforming XML into another format of XML based on a XSL using Java

This article will enlighten us with the transformation of XML from one format to another format. If you have a definite XML (SampleInputXML.xml) and definite XSL (SampleXSL.xsl) which is suitable to change format, the below code will bring you the XML with definite format.


The code is given below.


import java.io.File;
import java.io.FileOutputStream;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerConfigurationException;


public class XMLTransformation {


XMLTransformation() {

}


private static void applyTransformAndSave() {
try {
String sampleInputXML= "SampleInputXML.xml";
File sampleInputFile = new File(sampleInputXML);
String sampleOutputXML = "SampleOutputFile.xml";
//Convert input file to output batch file according to the XSL
TransformerFactory tFactory1 = TransformerFactory.newInstance();
Transformer transformer1;
transformer1 = tFactory1.newTransformer(new StreamSource("SampleXSL.xsl"));
transformer1.setOutputProperty(OutputKeys.INDENT, "yes");
transformer1.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2");
transformer1.transform(new StreamSource(sampleInputFile), new StreamResult(new FileOutputStream(sampleOutputXML)));
} catch (TransformerConfigurationException tCEx){
tCEx.printStackTrace();
} catch (TransformerException tEx){
tEx.printStackTrace();
} catch(Exception ex){
ex.printStackTrace();
}
}



public static void main(String args[]) {
XMLTransformation xmlTransformation = new XMLTransformation();
XMLTransformation.applyTransformAndSave();
}

}


Sample Input XML file:

       <?xml version="1.0" encoding="UTF-8" standalone="no" ?>

              <Order>

         <CustomerID>0123456789</CustomerID>

         <Title>Mr</Title>

           <FirstName>Vicky</FirstName>

            <Initial>VV</Initial>

           <Surname>Verma</Surname>

             <TelephoneNumber>0987654321</TelephoneNumber>

              <SubPremise>RakhsakNagar</SubPremise>

            <BuildingName>RakhsakNagar</BuildingName>

             <StreetNumber>207</StreetNumber>

             <StreetName>KharadiByPass</StreetName>

             <Locality>Kharadi</Locality>

           <PostTown>Pune</PostTown>

          <County>Pune</County>

         <Postcode>411014</Postcode>

        <OrderDate>20052009 15:18:11</OrderDate>

       <CADDate>05062009 15:23:54</CADDate>

       <OrderReference>022-123456789</OrderReference>

        <DealerChannel>Airtel Voice</DealerChannel>

       <InstallationType>Self</InstallationType>

     <AssetDescription>Airtel TV</AssetDescription>

     <Action>Add</Action>

    <ReplacementType>Active</ReplacementType>

    <CurrentDate>20090611T11:15:08</CurrentDate>

  </Order>

Sample Input XSL File:

    <?xml version="1.0" encoding="UTF-8" ?>

      <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

     <xsl:strip-space elements="*" />

     <xsl:output method="xml" version="1.0" encoding="UTF-8" standalone="yes" indent="yes" />

<xsl:template match="Order">

      <OrderForProduct xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      <UniqueKey>

      <xsl:attribute name="Id">

     <xsl:value-of select="CustomerID" />

  </xsl:attribute>

  </UniqueKey>

      <ProductsDetails>

      <Product Name="Airtel_TV" />

  </ProductsDetails>

      <PriorSubmission>

      <xsl:attribute name="UserName">AirtelUser</xsl:attribute>

      <xsl:attribute name="SubmitterRef">

     <xsl:value-of select="OrderReference" />

  </xsl:attribute>

  </PriorSubmission>

  </OrderForProduct>

  </xsl:template>

  </xsl:stylesheet>





Sample Output XML file:

   <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

     <OrderForProduct xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

     <UniqueKey Id="0123456789" />

        <ProductsDetails>

       <Product Name="Airtel_TV" />

    </ProductsDetails>

     <PriorSubmission UserName="AirtelUser" SubmitterRef="022-123456789" />

   </OrderForProduct>

Thanks.