Problem referencing a cell in a different workbook in a CellFormula

[Chris Niermeier <cn...@consumercontact.com>]

Hello,

I am trying to use POI to make several workbooks, where one book is used 
to average and display the values in the other books. The books should 
all be in the same directory once they are filled in.

My first attempt to do this was to set a cell formula of the form 
"[otherBook.xls]Sheet1!$B$16" to try to access the values in the other 
books. This results in a parse exception:

com.sun.rave.web.ui.appbase.ApplicationException: 
#{SelectEmployees.submitButton_action}: 
javax.faces.el.EvaluationException: java.lang.RuntimeException: Cannot 
Parse, sorry : Integer Expected @ 1 [Formula String was: 
'[otherBook.xls]Sheet1!$B$16;']
    at 
com.sun.rave.web.ui.appbase.faces.ViewHandlerImpl.cleanup(ViewHandlerImpl.java:559)
    at 
com.sun.rave.web.ui.appbase.faces.ViewHandlerImpl.renderView(ViewHandlerImpl.java:276)
    at 
com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:107)
    at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:245)
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:137)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:214)
    at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
    at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
    at com.sun.rave.web.ui.util.UploadFilter.doFilter(UploadFilter.java:198)
    at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
    at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
    at 
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:368)
    at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
    at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
    at 
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
    at 
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
    at 
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
    at 
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
    at 
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
    at 
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
    at 
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
    at 
org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
    at 
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
    at 
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
    at 
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
    at java.lang.Thread.run(Thread.java:595)
Caused by: javax.faces.FacesException: 
#{SelectEmployees.submitButton_action}: 
javax.faces.el.EvaluationException: java.lang.RuntimeException: Cannot 
Parse, sorry : Integer Expected @ 1 [Formula String was: 
'[otherBook.xls]Sheet1!$B$16;']
    at 
com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:98)
    at 
com.sun.rave.web.ui.appbase.faces.ActionListenerImpl.processAction(ActionListenerImpl.java:57)
    at javax.faces.component.UICommand.broadcast(UICommand.java:332)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:287)
    at 
javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:401)
    at 
com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:95)
    at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:245)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:110)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:213)
    ... 20 more
Caused by: javax.faces.el.EvaluationException: 
java.lang.RuntimeException: Cannot Parse, sorry : Integer Expected @ 1 
[Formula String was: '[otherBook.xls]Sheet1!$B$16;']
    at com.sun.faces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:150)
    at 
com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:92)
    ... 28 more
Caused by: java.lang.RuntimeException: Cannot Parse, sorry : Integer 
Expected @ 1 [Formula String was: '[otherBook.xls]Sheet1!$B$16;']
    at org.apache.poi.hssf.model.FormulaParser.Abort(FormulaParser.java:114)
    at 
org.apache.poi.hssf.model.FormulaParser.Expected(FormulaParser.java:121)
    at 
org.apache.poi.hssf.model.FormulaParser.GetNum(FormulaParser.java:255)
    at 
org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:512)
    at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:606)
    at 
org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:654)
    at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:758)
    at 
org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:609)
    at 
mailreviewforms.ApplicationBean1.mailReviewForm(ApplicationBean1.java:215)
    at 
mailreviewforms.SelectEmployees.submitButton_action(SelectEmployees.java:356)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at com.sun.faces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:146)
    ... 29 more

I then tried adding ' to make a formula like 
"'[otherBook.xls]Sheet1'!$B$16". This formula does not throw an 
exception, but when I log cell.getCellFormula(), it returns that the 
fomula is null. When I open up the spreadsheet, the cell shows a value 
of #REF!, and a formula like "=#REF!$B$16".

A tiny snippet of code of how I did this logging, where fileName is the 
name of the file being pointed to, and rowNumber is a number from a foreach:
    HSSFCell cell = row.getCell((short) columnNumber);
    if (cell == null)
        cell = row.createCell((short) columnNumber);
    cell.setCellFormula("'[" + fileName + "]Sheet1'!$B$" + 
rowNumber.toString());
    log.error("Formula got set to " + cell.getCellComment());


I would greatly appreciate any help finding a way to point to a cell 
from another book.

Thanks,
Chris Niermeier

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org