Format numbers with custom format code

[Bishma Stornelli]

I'm trying to format numbers with Venezuela format code: #.##0,00 but it's not working. My code is this:

number = s.add_style :format_code => "#.##0,00"

sheet.add_row [2.5] , :style => [number]

When I open the xlsx file, the cell has 2,50000 and the format code is #,##000

To open the document I'm using LibreOffice 3.5.4.2 and the default language for this is Spanish( Venezuela ). I don't know if this has something to do with it.

I'm using axlsx_rails, ruby 1.9.3 and rails 3.2.6.

[randym]

I'll have a look

[Bishma Stornelli]

Hi randym, could you take a look on this issue? 

[Randy Morgan]

Hi Bishma

Please accept my sincerest apologies for the delay, and my ignorance regarding formatting of Venezuela currencies.

It looks like the comma after a decimal in a format code is invalid in excel. I have a feeling that there may be some locale setting that enable this, but I need a bit more time to really work it out.

Based on some limited research, you can teach your system to use a comma as the decimal separator, but I highly doubt your users are going to want to jump thru that particular, OS dependent hoop.

It seems that Excel looks for something like this, but as you can see the comma and decimal are in the wrong place.

[$VEB] #,##0.00_);[Red]([$VEB] #,##0.00)

I'll keep working on this. It may require some configuration in axlsx, and possibly even a dependent setting on the OS to use the comma as the decimal separator but I am hoping to get to the bottom of this.

Thanks for your patience!

Randy 

[Randy Morgan]

Hi Bishma

I've worked this out. 

It turns out that excel gives that comma special treatment when it is used in a formatting code. 

We need to specify it as

"#.##0\,00"

So that the comma is not treated as a 'thousands separator'

Please give it a go and let me know if that works for you. 

Warmest regards, 

Randy

[Bishma Stornelli]

Thanks for helping and I'm sorry for answering so late, I didn't receive any mail for this.

I tried to print the number 15.5 (USA format) and this is what I visualize:

case format

when "#.##0,00" then "15,50000"   # The dot is changed by a comma and the comma is erased

when "#.##0\,00" then "15,500,00" # The dot is changed by a comma

when "#\.##0\,00" then ".0,16" # weird, isn't it?
end

Do you have any other idea to solve this? :S

[Bishma Stornelli]

Hi again,

I think I found the answer.

The zipped file (xlsx) doesn't use internationalization. It means that numbers format is always "#,##0.00". The software which open the file is the one who formats it accordingly to the configuration of it. Nothing to do with the file itself.

I haven't tried this in other locale but I think it is what's happening. The format "#,##0.00" was ok after all.

El miércoles, 14 de noviembre de 2012 05:57:44 UTC-5, randym escribió:

[Bishma Stornelli]

PD: Already tested. I changed the configuration of my locale in openoffice and the format changed. The representation in xml is always USA format.