How to format currency columns in Axlsx?

Question

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 :-

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

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

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:-

Shortwinded

I think you should format the cell to the required currency, instead of appending a string. – Pyramid

But how do I do that from code itself(not from downloaded excelsheet)? – Shortwinded

Answer 1

I solved this by using custom format_code

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

Shortwinded

This was very helpful. Thanks! Is there a way to encapsulate the currency amount within braces if it is negative; and also display it in red? Pls advise – Jos

I tried this code - currency_style = wb.styles.add_style(:format_code => "($#,##0_);[Red]($#,##0)"). This formats currency with comma separators all right; it also puts parenthesis around currency values when they are negative. But the coloring in red does not happen for negative currencies. Any idea how to implement red coloring for negative currencies? – Jos

one small correction please. The code shown in my previous comment does these 2 things - 1. displays sum when i select multiple cells 2. displays comma separators for thousand separator. But it does NOT do these 2 things - 1. Display negative currency in red. 2. Display ONLY negative currencies within parenthesis (this code displays BOTH positive and negative values in parenthesis which is wrong). Please help! – Jos