Created attachment 43731 [details]
Excel spreadsheet for import

Hi,
Thanks for your question.
You should use:
Format > Cells > Numbers > Currency (and choose your desired format) to add a
dollar-sign.
To add leading spaces you should use:
Format > Cells > Alignment > Left > Indent (and choose for exampel 10 pt....

That xl works with numbers, containing characters and spaces is actualy a bug in
xl...
Closing this issue....

OO Calc 2.1 works with numbers that begin with leading spaces and a dollar sign.
 Try it.  Open an empty spreadsheet and key in: "   $123"  OO Calc will treat it
like a numeric value.  Enter a formula in another cell that references the
value.  It will work.

In the case of OO, the new cell is assigned the format "Currency" without
explicit user instruction.  In the case of Excel, it is assigned the format
"General", yet Excel will operate successfully with the value as I suppose it
would with any "General" value that can be reasonably interpreted as numeric.

Both behaviors are reasonable or justifiable; this isn't religion.

Now, I suppose I could say that if it's a bug for Excel to interpret a
particular string as numeric, then it must be a bug for OO Calc to interpret the
identical string as numeric when it is keyed in (as I described in my original
report).  But I don't care to point a finger; I live in the real world and need
to solve a real problem while competing in a real marketplace...

And that is why this all misses the most important point.  Perhaps there is a
reference document for Excel that I am not aware of, but my definition of what
must happen when I import data in .xls format is that OO can interpret it in
such a way that is functionally equivalent to Excel.  It's .xls format.  If
Excel does the "wrong" thing, then so should OO Calc!

All around me there are organizations that I can't convince to use OO instead of
Microsoft.  They are not interested in geeky philosophical discussions about who
is to blame when their applications don't work.  They want applications that
solve their problems.  And now, for practical purposes, OO cannot use Excel
spreadsheets, because someone might be too stubborn to make the process work. 
Are you suggesting I complain to Microsoft?  Yeah, right.  They've already got
most of the customers, so they can be as arrogant as they want!

Are you sure, that the cell is a number in Excel? I get an calculation error, if
I open your spreadsheet in German Excel 97. The field is not transformed to the
German comma decimal delimiter, therefore I guess that it is a text in Excel. On
an English Excel you will not notice it, because Excel also calculates with
text, if it looks like a number. 

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.

Do you have access to Excel? If yes then please try the formula =ISNUMBER(A3) in
your file 'probsamp.xls'. Do you get TRUE? (I get FALSE.) If FAlSE, how should
OOo know, that it should tread it as number? 

Concerning "Excel calculation compatitbility mode": That would be a feature
request not a defect, see issue 5658. Perhaps you can add your proposal
"stripped only if there are dependent calculations in the spreadsheet" there? 

Forgive me for posting distracting discussion.  I do not wish to propose
anything with regard to conditional logic based on the existence of dependent
calculations, nor am I asking for a compatibility mode.  I only raised that
discussion to consider (and distinguish from) the case of spreadsheets wherein
the author explicitly entered the leading apostrophe, which is not the case
here.  It is a peripheral discussion.

With regard to the ISNUMBER function, I see FALSE too.  But this is only mildly
interesting.  I pose the same the question to you: How DOES my Excel program
successfully return the reasonable and correct product in cell b3 (1274.4) if it
does not recognize a3 as a number?  It DOES complete the calculation just so.

More relevant, and at the risk of being monotonous, why does OO Calc regard the
identical string as Currency when encountered as keyboard input, yet not so when
encountered as a General string from the .xls?

Issue http://www.openoffice.org/issues/show_bug.cgi?id=73783 seems to be 
related.

Issue 73793 is related. It is the other way round.

The decription here is clear enough. I set it to "NEW" and "Enhancement", so
that the developer can have a look at it. I propose to close this issue as
duplicate to issue 5658.

I looked forward to the day that Calc is "enhanced" to the point that it will
successfully import .xls files such that they work properly.  Until then, I
suppose it will malfunction flawlessly.

I'll report back to my users about interoperability accordingly.

Hi,

talked to the developer responsible for the Excel filter and also had a look at
the fattached file with Excel and got a #VALUE Error for the calculation, so
this is indeed a double to Issue 5658.

Frank

*** This issue has been marked as a duplicate of 5658 ***

closed double

billmichaelson,
I strongly suggest voting for 5658.