CODEWITHSUNDEEP
exceldate
Hahn
Hahn

Reputation: 97

Excel: How to apply Date format based on locale using c#?

I have a problem with formatting dates in Excel.

I'm trying to format a date in the form dd/MM/yyyy.

If I use Excel in English, it exports correctly, but if I use Excel in Spanish, it exports incorrectly.

For example 15/02/yyyy instead of 15/02/2013.

I have to change the format for Excel in Spanish in the form dd/MM/aaaa to work correctly.

Is there a form to use a standard way in order to use the same syntax independently of the language of Excel?

Thanks in advance.

Talking about the C# I'm using:

I'm using a formmated DataGridView in the columns corresponding to dates:

dgvData.Columns[x].DefaultCellStyle.Format = "dd/MM/yyyy";

And then using Microsoft.Office.Interop.Excel class I'm comparing formats in the following manner:

Range.EntireColumn.NumberFormat = dgvData.Columns[x].DefaultCellStyle.Format;

Thanks.

Upvotes: 0

Views: 708

Answers (1)

SeanC
SeanC

Reputation: 15923

You can read the "Short Date" format from the registry (along with other types of formats is desired.

A short piece of code will allow access to read the registry:

Option Explicit

Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function

you can then call this function with the appropriate key to get the required format:

LclDteFormat = _
    RegKeyRead("HKEY_CURRENT_USER\Control Panel\International\sShortDate")

Other formats available in the International section include:

  • sLongDate
  • sShortTime
  • sTimeFormat
  • s1159 and s2359 (for AM and PM settings)

Upvotes: 1

Related Questions