I am using VS2008,ASP.net,C#.net.
I have a web applicaion which uses NPOI dll to export to excel 2003. How do I display a number with thousand separator in Indian style( 12, 12,34,567.89)in the exported excel sheet? These cells with the number should applicable to apply formula (sum())
Depending on your CultureInfo, it should work with the format string "N".
Check your CultureInfo.CurrentCulture.NumberFormat.NumberGroupSizes. If it is {3, 2, }, you are happy. So:
yourNumber.ToString("N")
You can give another culture if your current culture is not good, for example:
yourNumber.ToString("N", new CultureInfo("hi-IN"))
On my system, (121234567.89).ToString("N", new CultureInfo("hi-IN")) gives "12,12,34,567.89". Those are the group sizes you desire.
As you may know, formatting can also take place like this:
string.Format(new CultureInfo("hi-IN"), "The number {0:N} is well formatted", yourNumber)
Addition: Out of curiosity, I just tested which specific cultures on my system use the {3, 2, } number group sizes, and they are:
hi-IN
bn-IN
pa-IN
gu-IN
or-IN
ta-IN
te-IN
kn-IN
ml-IN
as-IN
mr-IN
sa-IN
kok-IN
si-LK
ne-NP
bn-BD
en-IN
Try this
int value 773740;
Response.Write(value.ToString("N"));
//or
Response.Write(value.ToString("#,#"));
To format double to string with use of thousands separator use zero and comma separator before an usual float formatting pattern, e.g. pattern „0,0.0“ formats the number to use thousands separators and to have one decimal place.
String.Format("{0:0,0.0}", 12345.67); // "12,345.7"
String.Format("{0:0,0}", 12345.67); // "12,346"
Refer more here http://www.csharp-examples.net/string-format-double/
with NPOI try this magic to format with Indian style:
ICell cell = row.GetCell(0);
cell.SetCellValue(1234567.89d);
IDataFormat dataFormatCustom = workbook.CreateDataFormat();
cell.CellStyle.DataFormat = dataFormatCustom.GetFormat("[>=10000000]##\\,##\\,##\\,##0;[>=100000] ##\\,##\\,##0;##,##0.00");
