poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: DO NOT REPLY [Bug 49612] New: problem in reading Named cells
Date Mon, 19 Jul 2010 15:27:46 GMT

Typically, you do not use names in formulas in the way you specify, i.e.
'Sheet2'!name, rather you use the 'simple name' to use Java terminology.
Therefore, the 'Sheet2'!name example above would really be just name. Names
have a scope, they can be declared to relate to the workbook or worksheet
(at least they can is they are created using Excel, how this works with POI,
I do not yet know), and the scope determines where the name can be used. For
example, a name with worksheet scope can only be used in one sheet. For
example, suppose we define a name called test_name, make that name refer to
cell A1 of Sheet2 and set it's scope to worksheet, then you can only refer
to this name within cells on Sheet2. Any attempt to use that name in a
formula on a different sheet will result in an error; and this is true
whther you are using POI or Excel.

If you want to use a name to refer to a cell and then use that name in a
formula on another sheet then the name's scope should be set to workbook and
not worksheet. Using the example above again, we can create the name
test_name, link that to cell A1 on Sheet2 and set it's scope as workbook.
Now, it is possible to refer to this cell using the name within a formula.

In both cases though, you would not use the syntax you included in your
original e-mail - 'Sheet2'!test_name, but would use just test_name. Excel
would then look up in it's names table what the name referred to, interpret
the scope and then return the contents of the appropriate cell.

If you are creating the names using POI, then I do not think there is any
way to set the scope explicitly - and I am going to assume they all have
workbook scope as a result though I do not know if this is the case - but do
believe that the same basic principles will still hold sway; that is to say
you will identify the sheet and cell when you create the name then, when you
the name appears in a formula, you simply use the name without prefixing the
sheet's name to it.

So, to summarise, do not prefix the name of the cell or area with that of
the sheet on which it appears and you should not see this error again. Hope
this helps.


Mark B

Bugzilla from bugzilla@apache.org wrote:
> https://issues.apache.org/bugzilla/show_bug.cgi?id=49612
>            Summary: problem in reading Named cells
>            Product: POI
>            Version: 3.6
>           Platform: PC
>         OS/Version: Windows XP
>             Status: NEW
>           Severity: normal
>           Priority: P2
>          Component: POI Overall
>         AssignedTo: dev@poi.apache.org
>         ReportedBy: ranvijayps@gmail.com
> hi,
>     I have facing an issue in reading excel sheets though poi.
> within sheet1 lets A8 is denoted by some_name.when i try to read
> this as 'sheet1'!some_name within sheet2 in any cell then this 
> cell type changes to 
> org.apache.poi.hssf.record.formula.eval.NameXEval and value 
> return is null
> while 'sheet1'!A8 work fine and in this case the type of cell is 
> org.apache.poi.hssf.record.formula.eval.NumberEval
> due this all the formulas are getting fail which has 
> 'sheet1'!some_name. and throws exceltion 
> Unexcepted eval type 
> (org.apache.poi.hssf.record.formula.eval.NameXEval)
> can you plz, help me 
> Thanks
> ranvijay
> -- 
> Configure bugmail:
> https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
> ------- You are receiving this mail because: -------
> You are the assignee for the bug.
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org

View this message in context: http://old.nabble.com/DO-NOT-REPLY--Bug-49612--New%3A-problem-in-reading-Named-cells-tp29201508p29205905.html
Sent from the POI - Dev mailing list archive at Nabble.com.

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

View raw message