Results 1 to 6 of 6
Like Tree1Likes
  • 1 Post By Tolls

Thread: Apache POI excel sheet formula reference to another sheet.

  1. #1
    Vinay S V is offline Member
    Join Date
    Nov 2014
    Posts
    2
    Rep Power
    0

    Unhappy Apache POI excel sheet formula reference to another sheet.

    I have a excel sheet which has a formula reference to another sheet of the same workbook.
    Currently, I'm on sheet1 and trying to set a formula for a cell using cell.setFormula(Sheet2[#All]).
    While I'm doing that, I'm encountering the following error-
    Specified named range 'Sheet2' does not exist in the current workbook. org.apache.poi.ss.formula.FormulaParseException
    at org.apache.poi.ss.formula.FormulaParser.parseNonRa nge(FormulaParser.java:569)
    at org.apache.poi.ss.formula.FormulaParser.parseRange able(FormulaParser.java:429)
    at org.apache.poi.ss.formula.FormulaParser.parseRange Expression(FormulaParser.java:268)
    at org.apache.poi.ss.formula.FormulaParser.parseSimpl eFactor(FormulaParser.java:1119)
    at org.apache.poi.ss.formula.FormulaParser.percentFac tor(FormulaParser.java:1079)
    at org.apache.poi.ss.formula.FormulaParser.powerFacto r(FormulaParser.java:1066)
    at org.apache.poi.ss.formula.FormulaParser.Term(Formu laParser.java:1426)
    at org.apache.poi.ss.formula.FormulaParser.additiveEx pression(FormulaParser.java:1526)
    at org.apache.poi.ss.formula.FormulaParser.concatExpr ession(FormulaParser.java:1510)
    at org.apache.poi.ss.formula.FormulaParser.comparison Expression(FormulaParser.java:1467)
    at org.apache.poi.ss.formula.FormulaParser.Arguments( FormulaParser.java:1051)
    at org.apache.poi.ss.formula.FormulaParser.function(F ormulaParser.java:936)


    However, in the workbook, I have created the necessary sheet. The sheet name is 'Sheet2'. But still the code is not able to refer to that sheet.
    Please let me know if there is a way to fix this issue or any workaround.

    Thanks.
    Last edited by Vinay S V; 11-10-2014 at 03:20 PM.

  2. #2
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,983
    Rep Power
    8

    Default Re: Apache POI excel sheet formula reference to another sheet.

    "Sheet2" is not the same as "Sheet 2".
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  3. #3
    Vinay S V is offline Member
    Join Date
    Nov 2014
    Posts
    2
    Rep Power
    0

    Default Re: Apache POI excel sheet formula reference to another sheet.

    I'm sorry. I made a mistake. It is actually Sheet2. I have edited the post.

  4. #4
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    5,114
    Rep Power
    12

    Default Re: Apache POI excel sheet formula reference to another sheet.

    well not that I know anything about this but the error gives a clear hint that POI is interpreting the formula value as a named range within the same sheet and not an external sheet - thus my initial assumption is that it is simply formatted wrong. This stackoverflow post that I found by Googling "apache poi formula other sheet" seems to back that up:

    java - Referencing sheets in Apache POI Formulas - Stack Overflow

    You apparently reference your external sheet as 'sheet2!', ending with the exclamation mark.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  5. #5
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,983
    Rep Power
    8

    Default Re: Apache POI excel sheet formula reference to another sheet.

    Can you show some code we could run?
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: Apache POI excel sheet formula reference to another sheet.

    Quote Originally Posted by gimbal2 View Post
    well not that I know anything about this but the error gives a clear hint that POI is interpreting the formula value as a named range within the same sheet and not an external sheet - thus my initial assumption is that it is simply formatted wrong. This stackoverflow post that I found by Googling "apache poi formula other sheet" seems to back that up:

    java - Referencing sheets in Apache POI Formulas - Stack Overflow

    You apparently reference your external sheet as 'sheet2!', ending with the exclamation mark.
    Which is, of course, not a POI thing, but simply how you reference cells on other sheets in Excel.
    gimbal2 likes this.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. Creating Spinner in excel sheet using apache poi
    By mahesh.sanka in forum Apache POI
    Replies: 0
    Last Post: 06-12-2013, 01:35 PM
  2. Replies: 5
    Last Post: 08-02-2012, 12:55 PM
  3. excel sheet
    By sam.jj12 in forum NetBeans
    Replies: 1
    Last Post: 02-15-2012, 05:01 PM
  4. How to create excel sheet?
    By kishan in forum Advanced Java
    Replies: 3
    Last Post: 07-13-2010, 01:15 PM
  5. Replies: 0
    Last Post: 08-02-2007, 12:31 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •