display a number with thousand separator in Indian style( 12, 12,34,567.89)in the exported excel sheet using program

Feb 10, 2014 at 8:50 AM
I am using VS2008,ASP.net,C#.net,NPOI 1.2.3 dll.

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 using program? The built in formula sum() should be applied on these exported cells with number format.

thanks in advance
george n t

The code is mentioned below.

protected void btnGenerateReport_Click(object sender, EventArgs e) { // Get the data to report on //var userAccounts = Membership.GetAllUsers();

// Create a new workbook and a sheet named "User Accounts"
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet("User Accounts");
//var sheet = workbook.CreateSheet("User Accounts");

// Add header labels
int rowIndex = 0;
Row row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("Username");
row.CreateCell(1).SetCellValue("Email");
row.CreateCell(2).SetCellValue("Joined");
row.CreateCell(3).SetCellValue("Last Login");
row.CreateCell(4).SetCellValue("Approved?");
row.CreateCell(5).SetCellValue("Qty");
rowIndex++;



//NUMBER FORMAT BEGIN
CellStyle detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
Font detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
detailCurrencySubtotalCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("##,##,##,##0.00");

//NUMBER FORMAT END

row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("preejo");
row.CreateCell(1).SetCellValue("preejo@gmail.com"); row.CreateCell(2).SetCellValue("01/Jan/2014"); row.CreateCell(3).SetCellValue("27/Jan/2014"); row.CreateCell(4).SetCellValue("true"); //row.CreateCell(5).SetCellValue(((121234567.89).ToString("N", new CultureInfo("hi-IN"))));
//row.CreateCell(5).SetCellValue(Convert.ToDouble(123456.78));
row.CreateCell(5).SetCellValue(123456.78);
//row.GetCell(5).CellStyle = cellStyle;
row.GetCell(5).CellStyle = detailCurrencySubtotalCellStyle;




rowIndex++;
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("joby");
row.CreateCell(1).SetCellValue("joby@gmail.com"); row.CreateCell(2).SetCellValue("01/Jan/2013"); row.CreateCell(3).SetCellValue("27/Jan/2013"); row.CreateCell(4).SetCellValue("false"); row.CreateCell(5).SetCellValue(Convert.ToDouble(2323313.43)); //row.GetCell(5).CellStyle = cellStyle;
row.GetCell(5).CellStyle = detailCurrencySubtotalCellStyle;

//}



// Auto-size each column
for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++)
sheet.AutoSizeColumn(i);


// Add row indicating date/time report was generated...
sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString());

//formula
rowIndex++;
row = sheet.CreateRow(rowIndex);
Cell cell = row.CreateCell(5);

cell.SetCellType(CellType.FORMULA);
cell.CellFormula = string.Format("SUM(F{0}:F{1})", 2, 3);


cell.CellStyle = detailCurrencySubtotalCellStyle;



// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (MemoryStream exportData = new MemoryStream())
{
workbook.Write(exportData);

string saveAsFileName = string.Format("MembershipExport-{0Big Grin | :-D }.xls", DateTime.Now).Replace("/", "-");

Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
Coordinator
Feb 16, 2014 at 1:39 AM
Please upgrade to the latest version 2.0. Let me know if you can still reproduce the issue.
Dec 19, 2014 at 10:20 AM
Edited Dec 19, 2014 at 10:23 AM
Hello,
     I am using latest NPOI version 2.1.3 but i didn't get any solution for making particular column as number with thousand separator. 
For example i want to display 550550.56 as like 5,50,550.56 when exporting to excel.
Can you please help me ?

Thanks,
Herin
Dec 19, 2014 at 5:18 PM
Hi Herin,
I think the inbuilt format your looking for is "#,##0.00" as opposed to "##,##,##,##0.00"
There's a list/description of Excel's built in number formats somewhere on the web (apologies, I don't have a link) which you'll be able to use with GetBuiltinFormat

Good luck.