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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
