ExcelBanter - Currency formatting (Issue with globalization/localization)

On Monday, January 28, 2013 7:16:21 AM UTC+5:30, Shashi wrote:
I am creating excel report from ASP.NET application. The Currency values in the report should be displayed based on the format of the selected country.

For example: When i choose US and generate report.

All the currency values should be displayed like, $99,999.35 ($##,##0.00)

When i choose Germany and generate the same report,

the values should be displayed like, 99.999,35 $ (##.##0,00 $)

However the report when opened in Excel shows correctly for US but not for Germany option. The Report generation approach is generating a HTML mark-up with style elements such as "mso-number-format" and writing into browser with Response.write method. I am not using VSTO object library.

Now is there something wrong in the approach?

How can i fix the problem with formatting?

Is there any way we can set culture information to the excel file from the application?

This is the code i am using to generate the report.

Imports System.Data
Imports System.IO
Imports System
Imports System.Globalization

Public Class ExcelReport
Private Sub CreateExcel()

Try
Dim attachment As String = "attachment; filename=VarianceReport.xls"

Response.AddHeader("content-disposition", attachment)
Response.ContentType = "application/ms-excel"

If sb.ToString().Length = 0 Then
Response.Write("No Data Found")
Else
Response.Write(sb.ToString())
End If

Response.End()
Catch ex As Exception
End Try
End Sub

Private Sub BuildReport()

sb.Append("<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'")
sb.Append("<html xmlns='http://www.w3.org/1999/xhtml'")
sb.Append("<head")
sb.Append("<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'/")
sb.Append("<titleExcel Report</title")
sb.Append("</head")
sb.Append("<body")
sb.Append("<table id='tblDataEntryBulkUpload' style='border-left:1px solid #000; border-top:1px solid #000; ' ")
sb.Append("<tr<td style='mso-number-format:[Black\][$$]\#\,\#\#0\.000;border-right:1px solid #000; border-bottom:1px solid #000; color:#e47f00 !important'12333</td")
sb.Append("<tr<td style='mso-number-format:[Black\]\#0\,000_\\[$$];border-right:1px solid #000; border-bottom:1px solid #000; color:#e47f00 !important'10073</td")
sb.Append("<tr<td style='mso-number-format:[Black\]\#\,\#\#0\.000_\\[$$];border-right:1px solid #000; border-bottom:1px solid #000; color:#e47f00 !important'6402231</td")
sb.Append("<tr<td style='mso-number-format:[Black\]\#\,\#\#\#\,\###_\\[$S];border-right:1px solid #000; border-bottom:1px solid #000; color:#e47f00 !important'6402231</td")
sb.Append("<tr<td style='mso-number-format:[Black\]\#\.\#\#\#\.\###_\\[$$];border-right:1px solid #000; border-bottom:1px solid #000; color:#e47f00 !important'6402231</td")
sb.Append("</table" & "</body" & "</html")
End Sub

Private Sub GenerateReport()
BuildReport()
CreateExcel()
End Sub
End Class