CODEWITHSUNDEEP
ruby-on-railsexcelaxlsx
m.beginner
m.beginner

Reputation: 409

How to format currency in custom color using axlsx in Ruby on Rails?

I am using axlsx gem to generate Excel sheets.

I used the following code to format currency cell in Excel:

wb.styles.add_style(:format_code => '[Gray][A6A6A6]$#,##0_;[Red]($#,##0_)')

The above code works except for one shortcoming - negative currencies are rendered in red but positive currencies are NOT rendered in Gray. Why is that?

Also, I referred this link - https://support.office.com/en-us/article/Number-format-codes-5026BBD6-04BC-48CD-BF33-80F18B4EAE68

The above link has this para:

To specify the color for a section in the format code, type the name of one of the following eight colors in the code and enclose the name in square brackets as shown. The color code must be the first item in the code section.
[Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
  1. So does this mean I should use ONLY ONE OF the colors mentioned above?
  2. Can I use a hex code to render a custom color - say a light gray with hex code A6A6A6?

Please help!

Upvotes: 1

Views: 933

Answers (1)

engineersmnky
engineersmnky

Reputation: 29328

  1. So does this mean I should use ONLY ONE OF the colors mentioned above?

If you intend to do this entirely through the format code then yes, as those are the only supported color translations and anything else will result in an invalid format. (Usually referred to as "unreadable content" by Excel)

2.Can I use a hex code to render a custom color - say a light gray with hex code A6A6A6?

Yes you can because you do not need to specify the color for the positive values in the format_code instead you just need to specify the default color in the fg_color option like so 

  wb.styles.add_style(fg_color: 'A6A6A6', format_code: '$#,##0.00_);[Red]($#,##0.00)')

Now the default color will be 'A6A6A6' and if the number is negative the [Red] will override the default color.

[Implied] 3. What if I want different colors for zeros too (Part 3 in the linked page)

If you need additional functionality you could also look into conditional formatting like so: 

  zero_style = wb.styles.add(fg_color: 'FF69B4')  
  ws.add_conditional_formatting("A:A", # Indicates the cell range
      { type: :cellIs, 
        operator: :equal, 
        formula: "0", 
        dxfId:  zero_style, 
        priority: 1 
      })

Now: (assuming all of the above)

  • Positive values will be gray (#A6A6A6) [Default]
  • Negative values will be red ([Red]) and formatted as ($#,##0.00) [Format Code]
  • Exactly zero values will be Hot Pink (#FF69B4) [Conditional Formatting]

Upvotes: 1

Related Questions