org.zkoss.poi.ss.formula.FormulaParseException: The specified formula 'cell!$A$16:$D$0' contains an error. Expects number, string, or defined name.

ZK-Tracker | paowang | 4 years ago
tip
Your exception is missing from the Samebug knowledge base.
Here are the best solutions we found on the Internet.
Click on the to mark the helpful solution and get rewards for you help.
  1. 0

    Enable autofilter at some cells will throw a formula error message such as "*The specified formula 'cell!$A$16:$D$0' contains an error. Expects number, string, or defined name.*" But, if enable other cells's autofilter successfully before enabling the cell, that won't get any error and enabled autofilter normally. Reproducing steps: # load the attached file *OperationTest2007.xlsx* # select cell *D:18* and *right click* to popup menu # click menu *Filter* > *Filter* #* it will show an error message and won't enable autofilter #* !error.png! But enable other cell before enabling *D:18* won't get error: # load the attached file *OperationTest2007.xlsx* # select cell *D:4* #* enable autofilter and then disable autofilter # select cell *D:18* and enable autofilter #* it enabled autofilter normally #* !success.png! stack trace: {noformat} Jun 28, 2013 11:21:37 AM org.zkoss.zk.ui.impl.UiEngineImpl handleError:1359 SEVERE: >>org.zkoss.poi.ss.formula.FormulaParseException: The specified formula 'cell!$A$16:$D$0' contains an error. Expects number, string, or defined name. >> at org.zkoss.poi.ss.formula.FormulaParser.expected(FormulaParser.java:1625) >> at org.zkoss.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:569) >> at org.zkoss.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:447) >> at org.zkoss.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:291) >> at org.zkoss.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1149) >> at org.zkoss.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1109) >> at org.zkoss.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1096) >> at org.zkoss.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1456) >> at org.zkoss.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1556) >> at org.zkoss.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1540) >> at org.zkoss.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1497) >> at org.zkoss.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1477) >> at org.zkoss.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1598) >> at org.zkoss.poi.ss.formula.FormulaParser.parse(FormulaParser.java:194) >> at org.zkoss.poi.xssf.usermodel.XSSFName.setRefersToFormula(XSSFName.java:195) >> at org.zkoss.poi.xssf.usermodel.XSSFSheet.setAutoFilter(XSSFSheet.java:3409) >> at org.zkoss.zss.model.sys.impl.XRangeImpl.autoFilter(XRangeImpl.java:2070) >> at org.zkoss.zss.api.impl.RangeImpl.enableAutoFilter(RangeImpl.java:552) >> at org.zkoss.zss.api.SheetOperationUtil.toggleAutoFilter(SheetOperationUtil.java:45) >> at org.zkoss.zss.ui.DefaultUserActionHandler.doFilter(DefaultUserActionHandler.java:1168) >> at org.zkoss.zss.ui.DefaultUserActionHandler.dispatchAction(DefaultUserActionHandler.java:255) >> at org.zkoss.zssex.ui.DefaultExUserActionHandler.dispatchAction(DefaultExUserActionHandler.java:240) >> at org.zkoss.zss.ui.DefaultUserActionHandler.onEvent(DefaultUserActionHandler.java:1387) >> at org.zkoss.zssex.ui.DefaultExUserActionHandler.onEvent(DefaultExUserActionHandler.java:83) >> at org.zkoss.zss.ui.Spreadsheet$3.onEvent(Spreadsheet.java:343) >> at org.zkoss.zk.ui.AbstractComponent.onEvent(AbstractComponent.java:2748) >> at org.zkoss.zk.ui.AbstractComponent.service(AbstractComponent.java:2719) >> at org.zkoss.zk.ui.AbstractComponent.service(AbstractComponent.java:2660) >> at org.zkoss.zk.ui.impl.EventProcessor.process(EventProcessor.java:136) >> at org.zkoss.zk.ui.impl.UiEngineImpl.processEvent(UiEngineImpl.java:1717) >> at org.zkoss.zk.ui.impl.UiEngineImpl.process(UiEngineImpl.java:1502) >> at org.zkoss.zk.ui.impl.UiEngineImpl.execUpdate(UiEngineImpl.java:1212) >> at org.zkoss.zk.au.http.DHtmlUpdateServlet.process(DHtmlUpdateServlet.java:600) >> at org.zkoss.zk.au.http.DHtmlUpdateServlet.doGet(DHtmlUpdateServlet.java:482) >> at org.zkoss.zk.au.http.DHtmlUpdateServlet.doPost(DHtmlUpdateServlet.java:491) >> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) >> at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) >> at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511) >> at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:401) >> at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) >> at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182) >> at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:766) >> at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:450) >> at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) >> at org.mortbay.jetty.Server.handle(Server.java:326) >> at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542) >> at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:945) >> at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756) >> at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218) >> at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404) >> at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:410) >> at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582) {noformat}

    ZK-Tracker | 4 years ago | paowang
    org.zkoss.poi.ss.formula.FormulaParseException: The specified formula 'cell!$A$16:$D$0' contains an error. Expects number, string, or defined name.
  2. 0

    Enable autofilter at some cells will throw a formula error message such as "*The specified formula 'cell!$A$16:$D$0' contains an error. Expects number, string, or defined name.*" But, if enable other cells's autofilter successfully before enabling the cell, that won't get any error and enabled autofilter normally. Reproducing steps: # load the attached file *OperationTest2007.xlsx* # select cell *D:18* and *right click* to popup menu # click menu *Filter* > *Filter* #* it will show an error message and won't enable autofilter #* !error.png! But enable other cell before enabling *D:18* won't get error: # load the attached file *OperationTest2007.xlsx* # select cell *D:4* #* enable autofilter and then disable autofilter # select cell *D:18* and enable autofilter #* it enabled autofilter normally #* !success.png! stack trace: {noformat} Jun 28, 2013 11:21:37 AM org.zkoss.zk.ui.impl.UiEngineImpl handleError:1359 SEVERE: >>org.zkoss.poi.ss.formula.FormulaParseException: The specified formula 'cell!$A$16:$D$0' contains an error. Expects number, string, or defined name. >> at org.zkoss.poi.ss.formula.FormulaParser.expected(FormulaParser.java:1625) >> at org.zkoss.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:569) >> at org.zkoss.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:447) >> at org.zkoss.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:291) >> at org.zkoss.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1149) >> at org.zkoss.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1109) >> at org.zkoss.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1096) >> at org.zkoss.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1456) >> at org.zkoss.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1556) >> at org.zkoss.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1540) >> at org.zkoss.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1497) >> at org.zkoss.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1477) >> at org.zkoss.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1598) >> at org.zkoss.poi.ss.formula.FormulaParser.parse(FormulaParser.java:194) >> at org.zkoss.poi.xssf.usermodel.XSSFName.setRefersToFormula(XSSFName.java:195) >> at org.zkoss.poi.xssf.usermodel.XSSFSheet.setAutoFilter(XSSFSheet.java:3409) >> at org.zkoss.zss.model.sys.impl.XRangeImpl.autoFilter(XRangeImpl.java:2070) >> at org.zkoss.zss.api.impl.RangeImpl.enableAutoFilter(RangeImpl.java:552) >> at org.zkoss.zss.api.SheetOperationUtil.toggleAutoFilter(SheetOperationUtil.java:45) >> at org.zkoss.zss.ui.DefaultUserActionHandler.doFilter(DefaultUserActionHandler.java:1168) >> at org.zkoss.zss.ui.DefaultUserActionHandler.dispatchAction(DefaultUserActionHandler.java:255) >> at org.zkoss.zssex.ui.DefaultExUserActionHandler.dispatchAction(DefaultExUserActionHandler.java:240) >> at org.zkoss.zss.ui.DefaultUserActionHandler.onEvent(DefaultUserActionHandler.java:1387) >> at org.zkoss.zssex.ui.DefaultExUserActionHandler.onEvent(DefaultExUserActionHandler.java:83) >> at org.zkoss.zss.ui.Spreadsheet$3.onEvent(Spreadsheet.java:343) >> at org.zkoss.zk.ui.AbstractComponent.onEvent(AbstractComponent.java:2748) >> at org.zkoss.zk.ui.AbstractComponent.service(AbstractComponent.java:2719) >> at org.zkoss.zk.ui.AbstractComponent.service(AbstractComponent.java:2660) >> at org.zkoss.zk.ui.impl.EventProcessor.process(EventProcessor.java:136) >> at org.zkoss.zk.ui.impl.UiEngineImpl.processEvent(UiEngineImpl.java:1717) >> at org.zkoss.zk.ui.impl.UiEngineImpl.process(UiEngineImpl.java:1502) >> at org.zkoss.zk.ui.impl.UiEngineImpl.execUpdate(UiEngineImpl.java:1212) >> at org.zkoss.zk.au.http.DHtmlUpdateServlet.process(DHtmlUpdateServlet.java:600) >> at org.zkoss.zk.au.http.DHtmlUpdateServlet.doGet(DHtmlUpdateServlet.java:482) >> at org.zkoss.zk.au.http.DHtmlUpdateServlet.doPost(DHtmlUpdateServlet.java:491) >> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) >> at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) >> at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511) >> at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:401) >> at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) >> at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182) >> at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:766) >> at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:450) >> at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) >> at org.mortbay.jetty.Server.handle(Server.java:326) >> at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542) >> at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:945) >> at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756) >> at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218) >> at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404) >> at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:410) >> at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582) {noformat}

    ZK-Tracker | 4 years ago | paowang
    org.zkoss.poi.ss.formula.FormulaParseException: The specified formula 'cell!$A$16:$D$0' contains an error. Expects number, string, or defined name.

    Root Cause Analysis

    1. org.zkoss.poi.ss.formula.FormulaParseException

      The specified formula 'cell!$A$16:$D$0' contains an error. Expects number, string, or defined name.

      at org.zkoss.poi.ss.formula.FormulaParser.expected()
    2. org.zkoss.poi
      XSSFSheet.setAutoFilter
      1. org.zkoss.poi.ss.formula.FormulaParser.expected(FormulaParser.java:1625)
      2. org.zkoss.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:569)
      3. org.zkoss.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:447)
      4. org.zkoss.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:291)
      5. org.zkoss.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1149)
      6. org.zkoss.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1109)
      7. org.zkoss.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1096)
      8. org.zkoss.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1456)
      9. org.zkoss.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1556)
      10. org.zkoss.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1540)
      11. org.zkoss.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1497)
      12. org.zkoss.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1477)
      13. org.zkoss.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1598)
      14. org.zkoss.poi.ss.formula.FormulaParser.parse(FormulaParser.java:194)
      15. org.zkoss.poi.xssf.usermodel.XSSFName.setRefersToFormula(XSSFName.java:195)
      16. org.zkoss.poi.xssf.usermodel.XSSFSheet.setAutoFilter(XSSFSheet.java:3409)
      16 frames
    3. org.zkoss.zss
      DefaultUserActionHandler.dispatchAction
      1. org.zkoss.zss.model.sys.impl.XRangeImpl.autoFilter(XRangeImpl.java:2070)
      2. org.zkoss.zss.api.impl.RangeImpl.enableAutoFilter(RangeImpl.java:552)
      3. org.zkoss.zss.api.SheetOperationUtil.toggleAutoFilter(SheetOperationUtil.java:45)
      4. org.zkoss.zss.ui.DefaultUserActionHandler.doFilter(DefaultUserActionHandler.java:1168)
      5. org.zkoss.zss.ui.DefaultUserActionHandler.dispatchAction(DefaultUserActionHandler.java:255)
      5 frames
    4. org.zkoss.zssex
      DefaultExUserActionHandler.dispatchAction
      1. org.zkoss.zssex.ui.DefaultExUserActionHandler.dispatchAction(DefaultExUserActionHandler.java:240)
      1 frame
    5. org.zkoss.zss
      DefaultUserActionHandler.onEvent
      1. org.zkoss.zss.ui.DefaultUserActionHandler.onEvent(DefaultUserActionHandler.java:1387)
      1 frame
    6. org.zkoss.zssex
      DefaultExUserActionHandler.onEvent
      1. org.zkoss.zssex.ui.DefaultExUserActionHandler.onEvent(DefaultExUserActionHandler.java:83)
      1 frame
    7. org.zkoss.zss
      Spreadsheet$3.onEvent
      1. org.zkoss.zss.ui.Spreadsheet$3.onEvent(Spreadsheet.java:343)
      1 frame
    8. ZK Kernel
      DHtmlUpdateServlet.doPost
      1. org.zkoss.zk.ui.AbstractComponent.onEvent(AbstractComponent.java:2748)
      2. org.zkoss.zk.ui.AbstractComponent.service(AbstractComponent.java:2719)
      3. org.zkoss.zk.ui.AbstractComponent.service(AbstractComponent.java:2660)
      4. org.zkoss.zk.ui.impl.EventProcessor.process(EventProcessor.java:136)
      5. org.zkoss.zk.ui.impl.UiEngineImpl.processEvent(UiEngineImpl.java:1717)
      6. org.zkoss.zk.ui.impl.UiEngineImpl.process(UiEngineImpl.java:1502)
      7. org.zkoss.zk.ui.impl.UiEngineImpl.execUpdate(UiEngineImpl.java:1212)
      8. org.zkoss.zk.au.http.DHtmlUpdateServlet.process(DHtmlUpdateServlet.java:600)
      9. org.zkoss.zk.au.http.DHtmlUpdateServlet.doGet(DHtmlUpdateServlet.java:482)
      10. org.zkoss.zk.au.http.DHtmlUpdateServlet.doPost(DHtmlUpdateServlet.java:491)
      10 frames
    9. JavaServlet
      HttpServlet.service
      1. javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
      2. javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
      2 frames
    10. Jetty Server
      SelectChannelEndPoint.run
      1. org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
      2. org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:401)
      3. org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
      4. org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
      5. org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:766)
      6. org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:450)
      7. org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
      8. org.mortbay.jetty.Server.handle(Server.java:326)
      9. org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
      10. org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:945)
      11. org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
      12. org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
      13. org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
      14. org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:410)
      14 frames
    11. Jetty Util
      QueuedThreadPool$PoolThread.run
      1. org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
      1 frame