#4 Problems to handle real/float numbers

[Rene Pezo]

Hi,

While looking for a way to implement the generation of Excel files with multiple worksheets using ASP/vbScript, I recently downloaded and used the ASP version ASPBIFF8-0.4.1-alpha of this project. Everything seemed to work just fine, until the user noticed that the Excel reports had different data from the one displayed by its HTML equivalent.

The specific problem arises when querying a value like this:

sht.Cells.AddValueCell rw, 2, cDbl(Rs("volume"))

The value is 2825.48 but prints out 2621.44

so I tried different ways to get the correct value, but got the same wrong result:

sht.Cells.AddValueCell rw, 2, cSng(Rs("volume"))
sht.Cells.AddValueCell rw, 3, cSng(FormatNumber(Rs("volume"), 2, -1, 0, 0))

The same happens when using a fixed string value, instead of a value queried from a record:

I tried all this combinations and no success:

sht.Cells.AddValueCell rw, 10, cDbl("-2825.48")
Result: -2621.44 INCORRECT
sht.Cells.AddValueCell rw, 11, cDbl("-2825.00") + cDbl("0.48")
Result: -2621.44 INCORRECT
sht.Cells.AddValueCell rw, 12, cLng("-2825.00") + cDbl("0.48")
Result: -2621.44 INCORRECT
sht.Cells.AddValueCell rw, 13, cSng("-2825.00")
Result: -2621.44 INCORRECT
sht.Cells.AddValueCell rw, 14, cLng("12342825.48") + cDbl("0.48")
Result: 10737418,24 INCORRECT
sht.Cells.AddValueCell rw, 15, cLng("12342825.48")Result: 12342825 INCORRECT
sht.Cells.AddValueCell rw, 16, cDbl("-2025.48")
result: -2025.48 CORRECT
sht.Cells.AddValueCell rw, 17, cLng("2025.48")
result: 2025 CORRECT
sht.Cells.AddValueCell rw, 20, cSng("-2825.00")
result: -2621.44 INCORRECT
sht.Cells.AddValueCell rw, 23, cSng("-2525.48")
result: -2525.48 CORRECT
sht.Cells.AddValueCell rw, 24, cDbl("-2621.01")
result: -2621.01 CORRECT
sht.Cells.AddValueCell rw, 25, cSng("-2621.41")
result: -2621.40991 INCORRECT
sht.Cells.AddValueCell rw, 26, cDbl("-2621.43")
result: -2621.43 CORRECT
sht.Cells.AddValueCell rw, 26, cDbl("-2621.44")
result: -2621.44 CORRECT
sht.Cells.AddValueCell rw, 27, cDbl("-2621.45")
result: -2621.44 INCORRECT
sht.Cells.AddValueCell rw, 28, cDbl("-2621.46")
result: -2621.44 INCORRECT

As you can see from this results if the number is less than -2621.44, the result turns out WRONG. The same happens if the number is greater than 2621.44. All this when usign a real number and cDbl(). If cSng() is used the problem arises with a little smaller numbers such as 2621.41 resulting in 2621.40991.

Everything works fine when converting to cLng() or cInt() depending on the case, bt that is not a solution as I need to handle float numbers.

Also if I don't use any conversion, just plain:
sht.Cells.AddValueCell rw, 2, Rs("volume")
then I get a Typedef error, whenever the result is not a caracter string.

I wonder if there's something I'm doing wrong or there's a compatibily issue.
I have the same problems trying out this code at two different environments:

SERVER: WINDOWS NT 4.0 SP6
IIS 4.0.
VBSCRIPT 5.6
Client: Win XP, IE 7.0
Viewing results with Excel 97
Headers sent by the client:
HTTP_USER_AGENT: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322)

SERVER: WINDOWS 2003 SERVER (NT 5.2)
MSIE 6.0.
VBSCRIPT 5.6
Client: Win 2003 Terminal Server IE 6.0.3790
Viewing results with MS Excel 2003

Headers sent by the client:
HTTP_USER_AGENT: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30)

If is of any use, I have attached the ASP file, just as it's right at the moment.

Any help in this subject will be deeply appreciated.

Rene Pezo.

[Rene Pezo]

ASP source code that uses ASPBIFF8