Thanks David, I did have a brief look through the documentation for the latest version I had on hand - 3.7 Beta 1 - but could not see scoping mentioned. That is not to say it is not there of course; my brain is a bit addled at the moment as the sun has been beating down onto my head all day whilst we have been, quite literally, making hay, oh, and avoiding bee's nests (gorgeous little red tailed bees nesting in an abandoned burrow).
Having said that, I still feel the original 'problem' has to do with preceding the name of the cell/range with that of the sheet. When I was digging around in Excel, I found this caused an error there - I think it was the familiar #NAME message displayed in the cell. One other thing that I also noticed is that Excel substituted the name of the workbook for that of the sheet in some cases, I guess it was trying to disambiguate and make it obvious that the reference related to a cell with workbook scope but I do not know this to be true. This also raises the possibility of using Josh's ForkedEvaluator to deal with these sorts of problems but again, this is not something I have experimented with to date. Still, I believe that just the name of the cell or range should appear in the formula and that any reference to the sheet is superfluous; Excel should be able to determine the 'real' address of the cell from the information used to define the name. The only remaining concern I have is when there are two ranges with identical names but different scopes. I need to check to see what Excel does in that case and should have the chance to do so a little later; I feel this is when Excel tries to disambguate the name but am not certain by any means. Yours Mark B David Fisher wrote: > > 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] > > > -- View this message in context: http://old.nabble.com/DO-NOT-REPLY--Bug-49612--New%3A-problem-in-reading-Named-cells-tp29201508p29206359.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]
