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. Yours Mark B Bugzilla from [email protected] 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: [email protected] > ReportedBy: [email protected] > > > 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: [email protected] > For additional commands, e-mail: [email protected] > > > -- 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: [email protected] For additional commands, e-mail: [email protected]
