locked
Showing Number/Currency in German Format. RRS feed

  • Question

  • User-1548831661 posted

    Hi all,

    We are developing an application in asp.net 2.0 where we have a functionality to export data to Excel file. We used Microsoft.Office.Interop.Excel to export data to Excel.

    Everything is working fine. But, we are facing a problem in formating numbers which need to be shown in German format(example: 345.789,78). For this, we added German culture info("de-DE")in code as well as web.config file. But, still it showing in US format( only.

    Please find below the code to add and format the data in excel cells.

    oWorkSheet.Cells[ExrowIndex + 1, ColumnIndex] =Convert.ToDouble(ContractAmount)

    oWorkSheet.get_Range(oWorkSheet.Cells[ExrowIndex + 1, ColumnIndex], oWorkSheet.Cells[ExrowIndex + 1, ColumnIndex]).NumberFormatLocal = "0,00.00";

    Please help me if anybody is having idea about it. 

    Wednesday, March 17, 2010 8:47 AM

Answers

  • User-1200560727 posted

    For datetime in german, you need to set dd/mm/yyyy format and for that you can add key for the same in web.config file. In German, you need to set "," for number seperator format.

    You need to initialize culture for the same.

    If you have any problem then you can go through the below post:

    http://forums.asp.net/p/1514920/3621409.aspx#3621409


    Regards,

    Tapan

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 18, 2010 12:41 AM

All replies

  • User-1952482173 posted

    If I am not wrong,  when you use Microsoft.Office.Interop.Excel to export data to Excel, you have to use culture "en-US" so far. Therefore, the output format should be US number.

    Wednesday, March 17, 2010 9:35 AM
  • User-1548831661 posted

    Hi ,

    Thank you for your response. This application is going to be used in Germany. So, we need to show all the numbers/dates in German format only.

     

    Thanks,

    Nagaraju.K

     

     

     

    Wednesday, March 17, 2010 10:04 AM
  • User-1200560727 posted

    For datetime in german, you need to set dd/mm/yyyy format and for that you can add key for the same in web.config file. In German, you need to set "," for number seperator format.

    You need to initialize culture for the same.

    If you have any problem then you can go through the below post:

    http://forums.asp.net/p/1514920/3621409.aspx#3621409


    Regards,

    Tapan

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 18, 2010 12:41 AM
  • User-1636183269 posted

    Yes Tapan is correct, Just keep in mind that you have to create commonBasePage which will inherited from System.Web.UI.Page.

    Other details I have already mentioned in this post http://forums.asp.net/p/1514920/3621409.aspx. You have to change as per your requirement de-DE instead of en-US.

    Please let me know if you have any queries.

    Thursday, March 18, 2010 4:45 AM
  • User-1548831661 posted

    Hi all,

    Thank you for your response. Sorry for the confusion here. Actually, I am facing this issue when data is exported to excel.

    As I set German culture in web.config file, application is showing date, currency , number and etc.. in German format on

    ASPX pages. But,only the issue is number/currency is not formatted when data is exported to excel. 

    After setting the German culture in Web.config file, I wrote the following code to format number in excel file.

    oWorkSheet.get_Range(oWorkSheet.Cells[ExrowIndex + 1, ColumnIndex], oWorkSheet.Cells[ExrowIndex + 1, ColumnIndex]).NumberFormatLocal = "0,00.00";

    It did not resolve my problem. Numbers in excel cells are shown in US format only.

    So, please let me know your valuable suggestions.

     

    Thanks,

    Nagaraju.K

     

    Tuesday, March 23, 2010 5:58 AM
  • User-1636183269 posted

    No you can change at your level as I have already told you to go through http://forums.asp.net/p/1514920/3621670.aspx

    Similar you have to do.

    Protected Overloads Overrides Sub InitializeCulture()
            Dim objCultureInfo As New CultureInfo("el-GR")
            Thread.CurrentThread.CurrentUICulture = objCultureInfo

            Dim objDateTimeFormatInfo As DateTimeFormatInfo = objCultureInfo.DateTimeFormat
            objDateTimeFormatInfo.ShortDatePattern = "dd/MM/yy"
            objDateTimeFormatInfo.LongDatePattern = "dd/MM/yyyy"
            objCultureInfo.DateTimeFormat = objDateTimeFormatInfo
            objCultureInfo.NumberFormat.NumberGroupSeparator = "."
            objCultureInfo.NumberFormat.NumberDecimalSeparator = ","
            objCultureInfo.NumberFormat.NumberGroupSeparator = "."
            objCultureInfo.NumberFormat.CurrencyDecimalSeparator = ","
            objCultureInfo.NumberFormat.CurrencyGroupSeparator = "."

            objCultureInfo.NumberFormat.CurrencySymbol = "€"
            Thread.CurrentThread.CurrentCulture = objCultureInfo
            objDateTimeFormatInfo = Nothing
            objCultureInfo = Nothing
            MyBase.InitializeCulture()
        End Sub


    Tuesday, March 23, 2010 6:14 AM
  • User-1636183269 posted

    Refer:

    http://msdn.microsoft.com/en-us/library/aa168494%28office.11%29.aspx

    http://support.microsoft.com/kb/320369/en-us

    im oApp As New Excel.Application()
    oApp.Visible = True
    oApp.UserControl = True
    Dim oldCI As System.Globalization.CultureInfo = _
    System.Threading.Thread.CurrentThread.CurrentCulture
    System.Threading.Thread.CurrentThread.CurrentCulture = _
    New System.Globalization.CultureInfo("de-DE")
    oApp.Workbooks.Add()
    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI


    Tuesday, March 23, 2010 6:26 AM