Mark, I recall that Yegor made improvements in the scoping of names. I think this was since POI 3.6, but may be just prior to that release. I thiknk that it is possible to control the scoping between worksheet and workbook.
Regards, Dave On Jul 19, 2010, at 8:27 AM, MSB wrote: > > 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] > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
