To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=75433
------- Additional comments from [EMAIL PROTECTED] Sun Mar 18 15:49:04 +0000 2007 ------- Thank you for your diligent attention. To answer you precisely, the field is identified by (presumably American English) Excel as "General" format, and the program (Excel 2000 - 9.0.2720) recognizes the value as numeric for the purpose of calculation. The dependent cell in the problem sample displays a result of $1274.4 as expected/intended. This is not a contrived example. The original problem was discovered when I received an Excel spreadsheet containing teacher salary and health benefit costs from my local school district with hundreds of such values. As a school board member who is negotiating a new contract with the union, I use these data as a basis for calculating costs of proposals. Be assured, the spreadsheet I was given performs useful calculations with Excel; it does not transfer seamlessly to OO Calc. Essentially, it "breaks." At the risk of being repetitious, I'll remind all that if I key in " $123" in my version of OO Calc (2.1), it is implicitly recognized as "Currency/English(USA)" and data-typed accordingly. That OO Calc performs this numeric identification upon input, whereas Excel defers it until recalculation seems like just a different, but equally reasonable approach. That OO Calc ultimately breaks the functional intent of the Excel spreadsheet is not reasonable. There is an even more complex consideration here which relates to whether the input field contains a leading apostrophe (which presumably is explicit user instruction to treat as non-numeric). That is *not* the problem at hand, but in such a case (my version of) Excel actually allows this to be used as the basis of calculation anyway - which makes little sense to me. Such a case would be exceedingly difficult to handle on import because we know that conversion to numeric (stripping the apostrophe) could wreak havoc on the user's intended formatting. Yet leaving it in place could damage calculations which presumably are the primary function of a spreadsheet! It would seem that only an Excel compatibility mode - replicating unreasonable Excel behavior - would handle this well, but this discussion does not belong here. The immediate problem is simpler: It seems that OO Calc is actually inserting an apostrophe upon import, thus forcing it to be non-numeric. But it is extremely reasonable to assume that the user of an American spreadsheet would intend to use " $123" as a number in the absence of explicit instruction to the contrary - just as OO does assume so when encountering this string input from the keyboard. It's also worth noting that if I subsequently change data types with OO to All/General, OO forces it to Number/General, but removes the leading spaces and dollar sign. That's perfectly fine - the dependent cells still calculate appropriately. I suggest that the best solution to this problem is to change OO Calc such that it no longer adds the apostrophe upon import from the .xls (and types the cell in a manner consistent with keyboard input). Perhaps there is something inside the .xls of which I'm not aware that's causing this behavior. But I experimented with Excel by explicitly placing a leading apostrophe in the field as a test case. It had no effect on the behavior of the OO import function. I don't know how best to handle the (hypothetical) case where there actually is an apostrophe in the .xls, because there will still be a compatibility problem. But that doesn't immediately concern me. In the absence of an Excel calculation compatitbility mode, perhaps it should actually be stripped, or stripped only if there are dependent calculations in the spreadsheet. --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- 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]
