2

I am working on a .Net application. I have a requirement such that I may getting input value as "-21455.254746" and I would like to handle such that this will be displayed as (21455.25)" on UI and even in export to word or excel.

I mean negative values should display in Parenthesis and decimal point to rounded to two.

The below Formatting is working fine on UI and Export to word. but in excel export, parenthesis is missing.

string resultValue = Convert.ToDouble("-21455.254746").ToString("#,##0.00;(#,##0.00);0.00");

This value will be binded to the gridview (asp.net).

Please someone advise the best way to do it and works for excel as well...

Bizhan
  • 16,157
  • 9
  • 63
  • 101
Tim
  • 231
  • 3
  • 4
  • 13
  • I think excel converts `(n)` to `-n` in numeric format cells. try changing the format to text: https://stackoverflow.com/a/2068060/366064 – Bizhan Jan 08 '20 at 12:24
  • @Bizhan: Sorry, I didn't get you exactly what you mean. – Tim Jan 08 '20 at 12:32
  • I am not assigning the values to the cells of an excel sheet directly. the whole result set as a dataTable or grdview will be passed to the export functionality (which is a common functionality). – Tim Jan 08 '20 at 12:35
  • Please paste the code for export functionality – MBB Jan 08 '20 at 13:08
  • I'm saying it's not the problem with your code, it's an excel feature that removes paranthesis from numbers. If you change the cell format to "text" it should work fine. – Bizhan Jan 08 '20 at 14:15
  • @Mahesh: I can't share the export functionality. its a separate project. – Tim Jan 08 '20 at 14:36
  • @Bizhan: sure will take a look in export functionality.thanks. – Tim Jan 08 '20 at 14:37
  • The reason being I had tried exporting the data to excel and using both NPOI and Microsoft Interop and worked! If you have the code theen we can try to fix it! – MBB Jan 09 '20 at 07:45

1 Answers1

0

For Word and Web I totally understand why you want to render as a string, but for Excel it's exceptional at numeric formatting, so let it handle that for you. Just pass the value in its native (decimal) format and give Excel the format string:

ws.Cells[1, 1].NumberFormat = "0.00_);(0.00)";
ws.Cells[1, 1].Value = -21455.254746;

To give you some confidence this works, here is a screen shot from the actual run:

enter image description here

Hambone
  • 15,600
  • 8
  • 46
  • 69