4

I learnt that you could format the types of cells in an Excel like this:

:types => [nil, :integer, :string]

However I looked at the list of all types possible and I find only integer and float but I don't see a currency option.

How can I format a cell as a currency so that the $ prefix and thousand separators are included automatically?

Please help!

m.beginner
  • 389
  • 2
  • 18
  • Possible duplicate of [How to format currency columns in Axlsx?](https://stackoverflow.com/questions/50128473/how-to-format-currency-columns-in-axlsx) – Mirv - Matt Mar 06 '19 at 12:34

3 Answers3

3

You can define custom styles as you need them.

# an example of applying specific styles to specific cells
require "rubygems" # if that is your preferred way to manage gems!
require "axlsx"

p = Axlsx::Package.new
ws = p.workbook.add_worksheet

# define your styles    
currency = ws.styles.add_style(format_code: "$#,##0;[Red]$-#,##0",
                              border: Axlsx::STYLE_THIN_BORDER).
ws.add_row ["Q1", 4000, 40], style: [currency]

Additional styling documentation

Jared
  • 1,154
  • 2
  • 17
  • 32
  • Thank you very much for your reply - but this styling makes the cell type as string - so if I select 2 cells styled this way, I can't see the `sum` in the excel bottom. If 2 cells of type numbers are selected, excel automatically displays the `sum` in the bottom right. I need that for my currency cells as well. How can I achieve that? pls help! – m.beginner Mar 07 '19 at 06:55
  • 1
    The solution in this thread worked - https://stackoverflow.com/questions/50128473/how-to-format-currency-columns-in-axlsx but it does not explain how to format negative currencies in red – m.beginner Mar 07 '19 at 08:14
0

Below style will add 2 decimal places to currency eg: $4200.32

currency = ws.styles.add_style(format_code: "$#,##0.00;[Red]$-#,##0.00",
                              border: Axlsx::STYLE_THIN_BORDER)
Christopher Oezbek
  • 23,994
  • 6
  • 61
  • 85
Ravistm
  • 2,163
  • 25
  • 25
0

I know this is an old thread, but in case anyone stumbles across it like I did, this solution should also work, with the caxlsx gem (https://rubygems.org/gems/caxlsx):

# I discovered the 'num_fmt' usage in this source_code file: 
# https://github.com/caxlsx/caxlsx/blob/946f287a37c7a5d2b4fe2ae8d109848e257241b1/lib/axlsx/stylesheet/num_fmt.rb
xlsx_package = Axlsx::Package.new
wb = xlsx_package.workbook
currency_style = wb.styles.add_style({num_fmt: 8})

The 'format_code' option wb.styles.add_style(format_code: "$#,##0.00;[Red]$-#,##0.00") didn't work for my use-case. My spreadsheet reader (LibreOffice) would only recognize that option as a 'custom' formatted cell, not a 'currency' cell.

edit: They did use it in one of their examples. https://github.com/caxlsx/caxlsx/blob/946f287a37c7a5d2b4fe2ae8d109848e257241b1/examples/number_format_example.md

Crowleg
  • 1
  • 1