[solved] Newbie question: Generating an Excel View

[YSutarko]

Hi.. I'm a newbie in java and spring framework.. so I'm sorry if my errors are a bit.. dumb or something..

I've tried the Countries sample provided with the spring framework distribution.. and it worked perfectly..

but when I try to use the same code to generate an excel view in my application.. I always encountered this exception:

java.lang.NullPointerException
	com...OvertimeExcelView.buildExcelDocument(OvertimeExcelView.java:44)
	org.springframework.web.servlet.view.document.AbstractExcelView.renderMergedOutputModel(AbstractExcelView.java:140)
	org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:219)
	org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:630)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:523)
	org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:342)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:328)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:810)

in my servlet xml:

	<bean id="overTimeFormController" 
			class="com...OverTimeFormController">
		<property name="commandName"><value>user</value></property>
		<property name="commandClass">
			<value>com...User</value>
		</property>
		<property name="formView">
			<value>overtimeView</value>
		</property>
		<property name="successView">
			<value>overTime.spring</value>
		</property>	
		<property name="validator"><ref bean="beanValidator" /></property>
	</bean>

I used a class named User to contain all the data to be shown in the view..
and my controller class code is like this:

public class OverTimeFormController extends SimpleFormController {
    
    /* (non-Javadoc)
     * @see org.springframework.web.servlet.mvc.SimpleFormController#onSubmit(javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse, java.lang.Object, org.springframework.validation.BindException)
     */
    protected ModelAndView onSubmit(HttpServletRequest request,
            HttpServletResponse response, Object command, BindException binder)
            throws Exception {
        
        if(request.getParameter("excel")!=null){

            RefreshablePagedListHolder listHolder = 
    		    (RefreshablePagedListHolder) 
    		    	request.getSession(true).getAttribute("user");
    		return new ModelAndView("overtime_excelView", "user", listHolder);
            
        }else{
	        User user = (User) command;
	        BindException errors = new BindException(user, "user");
	        return new ModelAndView("overtimeView", errors.getModel());            
        }
    }
    
}

everything is working perfectly, the validation using commons validator, viewresolving using resource bundles.. but when I try to export to excel I always get a null pointer exception

like you can see in the controller, the flow is like this..
1. the user submitted the form with some parameters (like employee number and date), and the view is returned back to the user listing his overtime data.. (the application is perfectly working until this step)
2. if the user pressed the export to excel button.. the controller will then generate the same form in excel format.. (this is where i get the exception)

I also tried supplying the User object in formBackingObject.. but that didnt seem to help..

what am I doing wrong? And am I supplying enough code? please tell me if i dont supply enough code...
any help would be appreciated.. thank you

[irbouho]

From the stackTrace, the exception is thrown in

  com...OvertimeExcelView.buildExcelDocument

could you provide the source, or an excerpt for this class?

[YSutarko]

Oh.. of course..

this is the source of OverTimeExcelView.java:

/*
 * Created on Aug 20, 2004
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
package com...view;

import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.support.RefreshablePagedListHolder;
import org.springframework.web.servlet.view.document.AbstractExcelView;

/**
 * @author Administrator
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
public class OvertimeExcelView extends AbstractExcelView {

    /* (non-Javadoc)
     * @see org.springframework.web.servlet.view.document.AbstractExcelView#buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
     */
    protected void buildExcelDocument(Map model, HSSFWorkbook wb,
            HttpServletRequest request, HttpServletResponse response)
    throws Exception {
        
        HSSFSheet sheet;
        HSSFRow sheetRow;
        HSSFCell cell;

		// We search the data to insert
		RefreshablePagedListHolder pgHolder = (RefreshablePagedListHolder) model.get("user");
		Locale loc = pgHolder.getLocale();

		sheet = wb.createSheet("Spring");

		if (null == pgHolder) {
			getCell(sheet, 0, 0).setCellValue(getMessageSourceAccessor().getMessage("nolist", loc));
			return;
		}        
        
        // Go to the first sheet
        // getSheetAt: only if wb is created from an existing document
        //sheet = wb.getSheetAt( 0 );
        sheet.setDefaultColumnWidth((short)12);

        // write a text at A1
        cell = getCell( sheet, 0, 0 );
        setText(cell,"Spring-Excel test");

        List words = (List ) model.get("list");
        for &#40;int i=0; i < words.size&#40;&#41;; i++&#41; &#123;
            cell = getCell&#40; sheet, 2+i, 0 &#41;;
            setText&#40;cell, &#40;String&#41; words.get&#40;i&#41;&#41;;
        &#125;

    &#125;

&#125;

but i guess the exception is thrown before this class is called/instantiated..
i just copied the example in the spring api for this class..

[irbouho]

java.lang.NullPointerException is thrown in line 44 in OvertimeExcelView.java

  Locale loc = pgHolder.getLocale();

this means that model.get("user") returns null.

Attribute "user" is extracted from HttpSession in OverTimeFormController

  RefreshablePagedListHolder listHolder = 
              (RefreshablePagedListHolder)
                 request.getSession(true).getAttribute("user");

can you check if the returned value is not null?

[YSutarko]

yes.. i guess that's the problem.. the object "user" is not in the session..
i already checked that

RefreshablePagedListHolder listHolder = 
              (RefreshablePagedListHolder) 
                 request.getSession(true).getAttribute("user");

returned null.. but like i said before.. I already tried to supply that object in the method formBackingObject in the controller.. but that didn't help...

i really dont know where the problem is.. or why that returned null... because when I tried to show the view in plain html.. using jstl.. there's no problem at all..

[irbouho]

Are the user use supply to jstl and the one you want to use in Excel the same?

[YSutarko]

yes of course.. all the data I want to process is in the user object..

[irbouho]

so why not just do

        User user = (User) command; 
        if(request.getParameter("excel")!=null){ 
          return new ModelAndView("overtime_excelView", "user", user); 
            
        }else{ 
           BindException errors = new BindException(user, "user"); 
           return new ModelAndView("overtimeView", errors.getModel());            
        }

[YSutarko]

oh... yes2 that worked.. thanks a lot..

silly me.. I thought you must always use a refreshablepagedlistholder to contain the object to be shown in the excel view..

i guess i still have a lot to learn about java...

so.. here's what i did.. just in case some newbie like me want to know how to do it :oops: :

in ModelAndView:

        User user = (User) command; 
        if(request.getParameter("excel")!=null){ 
          return new ModelAndView("overtime_excelView", "user", user); 
            
        }else{ 
           BindException errors = new BindException(user, "user"); 
           return new ModelAndView("overtimeView", errors.getModel());            
        }

in OvertimeExcelView.java:

public class OvertimeExcelView extends AbstractExcelView {

    /* (non-Javadoc)
     * @see org.springframework.web.servlet.view.document.AbstractExcelView#buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
     */
    protected void buildExcelDocument(Map model, HSSFWorkbook wb,
            HttpServletRequest request, HttpServletResponse response)
    throws Exception {
        
        HSSFSheet sheet;
        HSSFRow sheetRow;
        HSSFCell cell;

       sheet = wb.createSheet("Spring");
        sheet.setDefaultColumnWidth((short)12);

        // write a text at A1
        cell = getCell( sheet, 0, 0 );
        setText(cell,"Spring-Excel test");

        User user = (User) model.get("user");
        List words = user.getList();

        ...

    }

}

[qman]

how did you define overtime_excelView in configuration file? do you define it as a page or something else?

thanks