Read excel file using jXls

[Amar Jee]

Hi,
I'm trying to read excel rows to java bean but keep getting it keeps failing with null pointer exception.
Below is the code extract from XLSForEachBlockReaderImpl where itemsCollection comes out to be null.
PFA the xlsx file used for the below code (please replace .txt with .xlsx).
Please advice.

    private void createNewCollectionItem(Collection itemsCollection, Map beans) {
        Object obj = null;
        try {
            obj = varType.newInstance();
        } catch (Exception e) {
            String message = "Can't create a new collection item for " + items + ". varType = " + varType.getName();
            readStatus.addMessage(new XLSReadMessage(message));
            if (!ReaderConfig.getInstance().isSkipErrors()) {
                readStatus.setStatusOK(false);
                throw new XLSDataReadException(message, readStatus);
            }
            if (log.isWarnEnabled()) {
                log.warn(message);
            }
        }
        itemsCollection.add(obj);
        beans.put(var, obj);
    }

Following are the files I'm using:
RowImport.java
------------------------

public class RowImport {
	public static void main(String[] args) throws IOException, SAXException, InvalidFormatException{
		String xmlConfig = "config.xml";
		String dataXLS = "test.xlsx";
		InputStream inputXML = new BufferedInputStream(new FileInputStream(xmlConfig));
	    XLSReader mainReader = ReaderBuilder.buildFromXML( inputXML );
	    InputStream inputXLS = new BufferedInputStream(new FileInputStream(dataXLS));
	    IR ir = new IR();
	    List irs = new ArrayList();
	    Map beans = new HashMap();
	    beans.put("IR", ir);
	    XLSReadStatus readStatus = mainReader.read( inputXLS, beans);
	}
}

config.xml
-------------------------
<?xml version="1.0" encoding="ISO-8859-1"?> <workbook> <worksheet name="Sheet1"> <loop startRow="2" endRow="2" items="IR" var="incident" varType="IR"> <section startRow="2" endRow="2"> <mapping row="2" col="0">incident.id</mapping> <!-- mapping row="2" col="3">incident.openDate</mapping --> <mapping row="2" col="6">incident.summary</mapping> </section> <loopbreakcondition> <rowcheck offset="0"> <cellcheck offset="0">""</cellcheck> </rowcheck> </loopbreakcondition> </loop> </worksheet> </workbook>

IR.java
-----------------

import java.util.Collections;
import java.util.Date;


public class IR{
	private int id;
	private Date openDate;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public Date getOpenDate() {
		return openDate;
	}
	public void setOpenDate(Date openDate) {
		this.openDate = openDate;
	}
	public String getSummary() {
		return summary;
	}
	public void setSummary(String summary) {
		this.summary = summary;
	}
	private String summary;
}

[Rob Spoor]

Welcome to the Ranch! Could you please UseCodeTags next time? I've added them for you this time, and see how much easier it is to read your code?

Can you also please post your exception? That will make it easier to see where the problem is.

[Amar Jee]

Sure. Thanks!

Oct 23, 2010 7:11:29 AM net.sf.jxls.reader.XLSReaderImpl readSheet
INFO: Processing sheet Sheet1
Exception in thread "main" java.lang.NullPointerException
	at net.sf.jxls.reader.XLSForEachBlockReaderImpl.createNewCollectionItem(XLSForEachBlockReaderImpl.java:67)
	at net.sf.jxls.reader.XLSForEachBlockReaderImpl.read(XLSForEachBlockReaderImpl.java:43)
	at net.sf.jxls.reader.XLSSheetReaderImpl.read(XLSSheetReaderImpl.java:25)
	at net.sf.jxls.reader.XLSReaderImpl.readSheet(XLSReaderImpl.java:45)
	at net.sf.jxls.reader.XLSReaderImpl.read(XLSReaderImpl.java:31)
	at RowImport.main(RowImport.java:42)

[marco fago]

Try

List irs = new ArrayList();  
Map beans = new HashMap();  
beans.put("IR", irs);  
XLSReadStatus readStatus = mainReader.read( inputXLS, beans);

instead. It should work.
M@rco

[Martijn Verburg]

And welcome marco fago!

[Paul Clapham]

Lakshmi Arun,
Your post was moved to a new topic.