HSSF

[liew deb]

Hi all!

I am having a bit of problem here regarding HSSF. I am supposed to write a program that retrieves data from XML and output to excel using HSSF (as requested by my supervisor). My program can be compile with warnings, but it gave runtime error.

Exception in thread "main" java.lang.ClassCastException: org.apache.xml.dtm.ref.DTMNodeList cannot be cast to com.sun.org.apache.xml.internal.dtm.ref.DTMNodeList
at XMLToExcel.generateExcel(XMLToExcel.java:48)
at XMLToExcel.main(XMLToExcel.java:102)

Is there anything that I missed that caused this error?

  InputSource inputSource = new InputSource(
        new FileInputStream(xmlDocument));
 
      XPathFactory factory = 
        XPathFactory.newInstance();
      XPath xPath = factory.newXPath();
      String expression = "/ALL/id/SQL";
 
      com.sun.org.apache.xml.internal.dtm.ref.
        DTMNodeList nodeList = (com.sun.org.apache.xml.internal.dtm.ref.
  DTMNodeList) (xPath.evaluate(expression,
        inputSource, XPathConstants.NODESET));
   

  public static void main(String[] argv) {
    File xmlDocument = new File(
      "C:/Documents and Settings/Home/Desktop/deb/dataprovider.xml");
 
    XMLToExcel excel = new XMLToExcel();
    excel.generateExcel(xmlDocument);
  }
   

Another thing would like to ask. My program will have to ask for user input and retrieve the data based on the input. I am using a if loop with GetElementByTag, but it seems to be give me commpile time error.

 System.out.print("Please enter the Query ID: ");
Scanner myScanner = new Scanner(System.in);
String query = myScanner.nextLine();

      for (int i = 0; i < nodeList.getLength(); i++) {

if(getElementByTagName("id").item(0).getFirstChild().getNodeValue() == query){
        HSSFRow row = spreadSheet.createRow((
          short) i);
 
        HSSFCell cell = row.createCell((short) 0);
        
        cell = row.createCell((short) 3);
        cell.setCellValue(((Element) (
          nodeList.item(i))).getElementsByTagName(
          "id").item(0).getFirstChild().
          getNodeValue());
 
        cell.setCellStyle(cellStyle);
}}
 
Any help will be greatly appreciated. Thank you.
   

[Ulf Dittmer]

(xPath.evaluate(expression, inputSource, XPathConstants.NODESET));

According to the javadocs, XPath.evaluate for a NODESET returns a "org.w3c.dom.NodeList", so that's what you should cats it to. Casting to anything else means relying on implementation details, and is liable to cause all sorts of problems as you have found out.

I am using a if loop with GetElementByTag, but it seems to be give me commpile time error.

What error? In which line does it occur?
[ May 26, 2007: Message edited by: Ulf Dittmer ]

[liew deb]

Hi!

Thank you for your fast reply.

That helps me real lot. Thank you.

The problem seems to lie with the if statement, if(getElementByTagName("id").item(0).getFirstChild().getNodeValue() == query). If I were to comment it, the program will compile smoothly with no errors. However, if I were to un-comment it, the error will be thrown to me.

XMLToExcel.java:60: cannot find symbol
symbol : method getElementByTagName(java.lang.String)
location: class XMLToExcel
if(getElementByTagName("id").item(0).getFirstChild().getNodeValue() == query){
^

[William Brogden]

getElementByTagName("id").item(0).getFirstChild().getNodeValue() == query)

getElementsByTagName is a method in the Element class - where is your Element reference?

getFirstChild() is VERY dangerous unless you are absolutely sure that the XML document is formatted without extra text elements.

General comment:
Great long constructions like that may look cool but they can hide any number of problems and create debugging chaos. I would prefer to plod through that logic step by step - confirming that the NodeList returned by getElementsByTagName is not empty before executing item(0) etc etc.

Bill

[liew deb]

Hmm... It seems that I can't use getFirstChild as the data is not converted to xls as expected. What would you suggest me to use instead of getFirstChild? Part of the XML file is as below. My apology if I am asking too much as I have limited time to understand HSSF and I am supposed to come up with the program tomorrow. =[

 - <ALL>
  <DATASOURCE pool="jdbc/CPDB" url="" /> 
  <CACHESETTINGS maxCacheSize="999" /> 
- <QUERY id="CCAAddStudentHouse" logging="false">
- <SQL>
- <![CDATA[  insert into CP_CCA_HOUSE_STUD_LINK (STUDENT_ID ,SCHOOL_CODE ,HOUSE_REFID ,RECORD_VERSION_NO ,CREATED_DATE ,CREATED_BY_ID ,LAST_UPDATED_DATE ,UPDATED_BY_ID ) values(?,?,?,1,SYSDATE,?,SYSDATE,?) 
  ]]> 
  </SQL>
  <CACHE enabled="false" ttl="1000" /> 
  <MAP paramno="1" input="StudentID" /> 
  <MAP paramno="2" input="SchoolCode" /> 
  <MAP paramno="3" input="HouseID" /> 
  <MAP paramno="4" input="UserID1" /> 
  <MAP paramno="5" input="UserID2" /> 
  </QUERY>
   

Thank you.

[Ulf Dittmer]

It seems that I can't use getFirstChild as the data is not converted to xls as expected.

I'm not sure what the premise is in this sentence, and what the conclusion.

If you are inferring that you can't use getFirstChild because you're not receiving the XLS file you'd like, then follow Williams suggestions and make sure that it really returns what you think it should be returning.

If you are inferring that you can't create an Excel file because you can't use getFirstChild, then that's not the proper conclusion. It merely means you may have to use other DOM methods. Again, as William suggests, make sure that at each intermediate step you're working with exactly the data you think you're working with. Lots of debugging output will help with this.

[liew deb]

Thank you all for your help. I have another question.

XPathFactory factory =
XPathFactory.newInstance();
XPath xPath = factory.newXPath();
String expression = "/id/SQL";
org.w3c.dom.NodeList
nodeList =( org.w3c.dom.NodeList)
xPath.evaluate(expression,
inputSource, XPathConstants.NODESET);


is this the way to write to provide the evaluate methods to evaluate compiled XPath expressions in an XML document based on my XML given above? I suspect I am not getting the result due to the String expression. I might have done it wrongly.

[Paul Clapham]

That's the right code in general to evaluate an XPath expression and apply it to a document. It selects all "SQL" elements that are children of an "id" element that is a child of the context node.

And yes, that will not select anything from the XML document you posted earlier (it doesn't contain any "id" elements, for one thing). Was that your question?

[liew deb]

Hmm... Thank you for your reply...

I am thinking how should I retrieve the data for

  <QUERY id="CCAAddStudentHouse" logging="false">
- <SQL>
- <![CDATA[  insert into CP_CCA_HOUSE_STUD_LINK (STUDENT_ID ,SCHOOL_CODE ,HOUSE_REFID ,RECORD_VERSION_NO ,CREATED_DATE ,CREATED_BY_ID ,LAST_UPDATED_DATE ,UPDATED_BY_ID ) values(?,?,?,1,SYSDATE,?,SYSDATE,?)   ]]>
   </SQL>  

Should it be

 XPathFactory factory = 
        XPathFactory.newInstance();
      XPath xPath = factory.newXPath();
      String expression = "/Query/SQL";
   

or

 XPathFactory factory = 
        XPathFactory.newInstance();
      XPath xPath = factory.newXPath();
      String expression = "/id/SQL";
   

or

  XPathFactory factory = 
        XPathFactory.newInstance();
      XPath xPath = factory.newXPath();
      String expression = "All/Query/SQL";
  

I tried all out, but it seem not to work as it keeps throwing nullPointerException.

(Edited to be not so extremely wide - PC)
[ May 28, 2007: Message edited by: Paul Clapham ]

[Ulf Dittmer]

The only part that differs in these 3 code excerpts (the String assignment) can't throw NullPointerExceptions, so I'm thinking that the problem you're facing right now lies somewhere else.

[liew deb]

Hmm... Is it ok if I post my code here and help me check where is the error?

 import org.apache.poi.hssf.usermodel.*;
import org.apache.xpath.NodeSet;
import org.w3c.dom.*;
import org.xml.sax.InputSource;
import java.io.*;
import javax.xml.xpath.*;
import java.util.Scanner;
 
public class XMLToExcel {
  public void generateExcel(File xmlDocument) {
    try {
      HSSFWorkbook wb = new HSSFWorkbook();
      HSSFSheet spreadSheet = wb.createSheet(
        "spreadSheet");
      HSSFCellStyle cellStyle = wb.createCellStyle();
      cellStyle.setBorderRight(
        HSSFCellStyle.BORDER_MEDIUM);
      cellStyle.setBorderTop(
        HSSFCellStyle.BORDER_MEDIUM);
 
      InputSource inputSource = new InputSource(
        new FileInputStream(xmlDocument));
 
      XPathFactory factory = 
        XPathFactory.newInstance();
      XPath xPath = factory.newXPath();
      String expression = "All/Query/SQL";
 
     org.w3c.dom.NodeList
 nodeList = ( org.w3c.dom.NodeList)
xPath.evaluate(expression,
         inputSource, XPathConstants.NODESET);

/*System.out.print("Please enter the Query ID: ");
Scanner myScanner = new Scanner(System.in);
String query = myScanner.nextLine();*/

      for (int i = 0; i <= nodeList.getLength(); i++) {
//if(nodeList.getLength()>=0){
System.out.println(nodeList.getLength());
//if((Element) (nodeList.item(i).getElementsByTagName("query").item(0).getFirstChild().getNodeValue()).equal(query)){
        HSSFRow row = spreadSheet.createRow((
          short) i);
 
        HSSFCell cell = row.createCell((short) 0);
        
        cell = row.createCell((short) 0);
        cell.setCellValue(((Element) (
          nodeList.item(i))).getElementsByTagName(
          "id").item(0).getFirstChild().
          getNodeValue()
);
 
        cell.setCellStyle(cellStyle);
 
        cell = row.createCell((short) 1);
        cell.setCellValue(((Element) (
          nodeList.item(i))).getElementsByTagName(
          "query").item(0).getFirstChild().
          getNodeValue()
);
 
        cell.setCellStyle(cellStyle);
//}
//}
}
 
      FileOutputStream output = new FileOutputStream(
        new File("C:/Documents and Settings/Home/Desktop/deb/ExampleExcel.xls"));
      wb.write(output);
      output.flush();
      output.close();
    } catch (IOException e) {
 System.out.println(e.getCause());
 System.out.println(e.getMessage());
    } catch (XPathExpressionException e) {
 System.out.println(e.getCause());
 System.out.println(e.getMessage());
 
    }
  }
 
  public static void main(String[] argv) {
    File xmlDocument = new File(
     "C:/Documents and Settings/Home/Desktop/deb/dataprovider.xml");
 
    XMLToExcel excel = new XMLToExcel();
    excel.generateExcel(xmlDocument);
  }
}   

Thank you.

[Ulf Dittmer]

You still have this statement that -as was pointed out before- is most likely a problem:

((Element) (nodeList.item(i))).getElementsByTagName("id").item(0).getFirstChild().getNodeValue()

Take my advice and print out the intermediate values to make sure that you're retrieving what you think you're retrieving.

Also, if you're getting exceptions, tell us in which line they occur. If they occur in the line quoted above, break it into its pieces and indicate which object or method call causes it.

[liew deb]

Hmm... I ass in these codes...

   Element root    = doc.getDocumentElement();
    Node configNode = root.getFirstChild();
 System.out.println(configNode);
    NodeList childNodes = configNode.getChildNodes();
    for (int childNum = 0; childNum < childNodes.getLength(); childNum++) 
    {
 
      if ( childNodes.item(childNum).getNodeType() == Node.ELEMENT_NODE ) {

Element child = (Element) childNodes.item( childNum ) ;
  

The problem seems to lie with the Element root = doc.getDocumentElement(). That was where the exception was thrown.

[Ulf Dittmer]

Then "doc" must be null, since you're getting a NullPointerException, correct?