7

I 'm using Spreadsheet gem to create excel sheet. how can I apply 'currency' number format to a particular cell ?

Rutvij Pandya
  • 71
  • 1
  • 3

2 Answers2

6

Not tried it out but perhaps the following will point you in the right direction:

nb_format = Spreadsheet::Format.new  :number_format => '$#,###.##'
cell.set_format(0, nb_format)
Yule
  • 9,668
  • 3
  • 51
  • 72
  • 1
    currency_format = Spreadsheet::Format.new :number_format => '$#,##0.00_);[Red]($#,##0.00)' sheet.row(index).set_format(5, currency_format) This worked better for me. – Rutvij Pandya Apr 02 '12 at 11:10
2

If you want to use one of Excel's built in currency formats, one hack is to use Excel to format a spreadsheet cell (say Row 1, Col A) with the format you want. Save it, then use the spreadsheet gem to read the format Excel gave that cell. In my case, I got _([$$-409]* #,##0.00_);_([$$-409]* \\(#,##0.00\\);_([$$-409]* \"-\"??_);_(@_) as the format for USD. Then you can use it like so:

currency_format = Spreadsheet::Format.new number_format: "_([$$-409]* #,##0.00_);_([$$-409]* \\(#,##0.00\\);_([$$-409]* \"-\"??_);_(@_)"
sheet.row(r).set_format(col, currency_format)
jmera
  • 644
  • 4
  • 15