Search code examples
ruby-on-railsrubyexcelaxlsx

How to format currency columns in Axlsx?

I am using ruby Axlsx gem for writing my clients accounts data in xlsx. Now I am appending " Dr" string to all debit amounts which is making amount as a string e.g. "1000 Dr". I want to append " Dr" but the amount should still be an integer and excel should give sum at bottom when column is selected.

What I am doing, notice that excel shows sum = 0 in bottom-right even on selecting :- enter image description here

What I am doing, notice that cell remains a string on selecting (880 Dr should become 880.00 on selecting the cell):-enter image description here

What I want to do, excel should show sum of selected colums as shown by excel in bottom-right sum=1957.00 Dr:-enter image description here

What I want to do, the cell should be converted to number when double clicked (1341.00 Dr has become 1341 on selecting)but Dr should be visible on unselecting again:- enter image description here

Solution

  • I solved this by using custom format_code

    custom_format = sheet.styles.add_style(:format_code => "#.00  "Dr"")