ExcelBuffer and NumFormat

Ryden

I'm having trouble with NumFormat while creating an Excel file

I'm looping a record and doing some calculations and then a second loop over time.
The results are written to ExcelBuffer with one line per record iteration and one column per time period

I got a request to reduce the number of decimals to two to enhance readabilty, so I passed '0.##' to the NumFormat parameter of CreateColumn as per Excel helpfile

This works for the first record iteration, all the columns get the correct value and number of decimals 1276,05733 shows as 1276,06 (In Sweden the dec. separator is a comma)

The following record iterations show the value as truncated to the first three figures with a period inserted after the first in all columns.
E.g. I get 4.42 instead of 441,838975.

Anyone have any ideas about this, and why does it work the first time round?

keoma

hi,

it seems that you have a problem with the NumFormat '0.##' and (internal) language/regional settings when applying the NumFormat to Values. so that means: NumFormat '0.##' converts 441,838975 to 4.42, because the calculation works the american way (. is the seperator sign and , is ignored).

best regards

Ryden

Yes, you are perfectly right. What threw me off was that it worked for the first row where all the sums where in the thousands.

The man who decided that what is in reality a programming language should be localised ought to drawn and quartered. And possibly shot on sight and someone should definitely dance on his grave to an out of tune accordion. ](*,)

I've had no end of trouble with Excel and localisation in a multinational environment over the years and I should have recognised this.

Someone said that Excel automatically translate all functions when you open the file in a different locale.

Bovine droppings!

The correct numformat should be 0,## for Sweden no matter what the reference says