6

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

lacostenycoder
  • 10,623
  • 4
  • 31
  • 48
Umesh Malhotra
  • 967
  • 1
  • 10
  • 25

1 Answers1

7

I solved this by using custom format_code

custom_format = sheet.styles.add_style(:format_code => "#.00  "Dr"")
Umesh Malhotra
  • 967
  • 1
  • 10
  • 25
  • 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 – Biju Mar 07 '19 at 07:45
  • 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? – Biju Mar 07 '19 at 10:35
  • 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! – Biju Mar 07 '19 at 12:33