Currency formatting (Issue with globalization/localization)

S

Shashi

I am creating excel report from ASP.NET application. The Currency values inthe 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-upwith 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?
 
S

shashidhar.ramadugu

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 browserwith 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?
Click to expand...

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("<title>Excel 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

custom currency formatting 3
Conditional Formatting Problem with currency. 6
Currency Localization Issue 18
Currency Settings 1
Currency Localization Issue - Part 2 1
Currency format with 2 decimals not showing on report 2
Localization issue with master page. 5
Currency designations 2

Top