porting to Excel: Number formatting problem

[vcplearner]

Hi everyone,

I have a C# .NET [Any .NET soln. will do] application that exports a DataGrid to Excel. I am also required to format the numbers in the cells. There are two kinds of numbers that I deal with:

1. Currency format
2. Number format

Now, the problem I am facing is with Negative numbers in the number format. I need to represent them with parenthesis for e.g., a ‘-12’ by (12) [highlighted in red]

The code that I use is in itemDataBound function of the DataGrid.

int i;
string s;
i = Convert.ToInt32(e.Item.Cells[colPos1].Text);

s = String.Format("{0:n}",i);
e.Item.Cells[colPos1].Text = s;

But, when I get the Excel, all the +ve numbers are in “number” format, while all the –ve numbers in “general” format. Also, when I try this code for representing negative numbers in parenthesis:

if(i<0)
{
i = 0-i;
s = String.Format("{0:n}",i);
s = "(" + s + ")";
}

,the (12) or whatever number, is turned back to -12 in the Excel, because the –ve number cell is still in general format (I think this is the reason). But the above code works perfectly in currency format, which has the same code other than “{0:c}” format.

Can anyone suggest a method to either force the -ve numbered cell into a number format or still better get the negative numbers in parenthesis and Red forecolor?

I have also tried

e.Item.Cells[colPos1].Style.Add("vnd.ms-excel.numberformat","{0:N}");

and

e.Item.Cells[colPos1].Style.Add("vnd.ms-excel.numberformat","@");

Thanks.

 

[ca8msm]

You could try using the method that they use in the following article where each row is looped through end written out in a kind of HTML format:

http://www.thecodeproject.com/csharp/export2excel.asp

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vcplearner]

wow, its way complicated especially since I ve to change the whole design. But thanks ca8msm, for the response.