How to set localized short-date format for a cell in Excel with C#?

Question

Using C# and VSTO, the type of a cell in Excel can be set with the following code:

worksheet.Cells[i, j].NumberFormat = magicString;

, where worksheet is an object of the Microsoft.Office.Interop.Excel.Worksheet class, i is a row number of the cell, j is a column number of the cell, magicString is some string defining the type of the cell (note: Excel calls types as format, but below I'm using the word type).

The following magicStrings define the following Excel types:

• string magicString = ""; - defines the "General" Excel type;
• string magicString = "@"; - defines the "Text" Excel type;
• string magicString = "0%"; - defines the "Percent" Excel type.

The situation is more complicated when I would like to set the "Date" Excel type. The complexity is related to the localization of the Excel and to the localization of the Windows system.

So, for example, I have a Russian version of Excel (in particular, all types are written in Excel in Russian) and my Windows system has the following short-date format: "dd.MM.yyyy" (one can find this setting in Control Panel > Region and Language > Formats). I have an English version of Windows but this plays absolutely no role.

As a result, if I will use the following magicString in my code, the type of the cell will be set to the short-date type:

• string magicString = "ДД.ММ.ГГГГ"; - defines the "Date" (or, more exactly, the "Short-Date") Excel type;

As you can see, here the magicString is a combination of the Russian letters (Russian - because the Excel is Russian) and of the format set in the Windows settings.

If, instead of this magicString, I use the magicString equal to "DD.MM.YYYY" (i.e. the English letters), errors appear.

Thus, if I would want that my Excel add-in would be able to set correctly the "Short-Date" type for all (English, Russian, German and all other) versions of Excel and for all localization settings of Windows, I have to be able to use some universal magicString, that is independent of two mentioned factors.

As an option, I can read the short-date format from the Windows settings with this code:

string shortDatePattern = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;

and then, replace the letters in the obtained shortDatePattern string with those that correspond to the language of Excel. However, this way seems to me too complicated.

My question is: is there some universal magicString that is valid for all Excel languages and for all Windows localization settings, like it takes place for other Excel types like "General", "Text", and "Percent"? Or, maybe, someone knows other simple way to reach such an universality?

Answer 1

You should be able to do it like this:

Application xlApp = new Application();
Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws = wb.Worksheets[1];

var yearCode = xlApp.International[XlApplicationInternational.xlYearCode];
var monthCode = xlApp.International[XlApplicationInternational.xlMonthCode];
var dayCode = xlApp.International[XlApplicationInternational.xlDayCode];

ws.Cells[1, 1].NumberFormat = string.Format("{0}{1}.{2}{3}.{4}{5}{6}{7}", dayCode, dayCode, monthCode, monthCode, yearCode, yearCode, yearCode, yearCode);

On the Application there is the International property. You can query it using the XlApplicationInternational enumeration. For me xlYearCode returns é for example. That should be Г for you.

After that you can just construct your NumberFormat using the previously queried format codes.

Answer 2

Thanks a lot to Szabolcs Dézsi for the hint. But it solves only a part of my question. Another part is how to extract date-format codes from the Windows-system localization settings? I have not found the answer in internet and provide my own solution, in combination with the solution of Szabolcs Dézsi.

First of all, let's create the following class:

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;

using Excel = Microsoft.Office.Interop.Excel;

namespace MyNamespace
{
    internal sealed class DateFormatComponentCodes
    {
        private readonly char year;
        private readonly char month;
        private readonly char day;

        // Constructs the object based on the system localization.
        public DateFormatComponentCodes()
        {
            DateTimeFormatInfo dateTimeFormatInfo = CultureInfo.CurrentCulture.DateTimeFormat;

            var yearMonth = new HashSet<char>(new HashSet<char>(dateTimeFormatInfo.YearMonthPattern.ToCharArray()).Where(c => char.IsLetter(c)));
            var monthDay = new HashSet<char>(new HashSet<char>(dateTimeFormatInfo.MonthDayPattern.ToCharArray()).Where(c => char.IsLetter(c)));

            var monthHashSet = new HashSet<char>(yearMonth);
            monthHashSet.IntersectWith(monthDay);
            this.month = monthHashSet.First();

            yearMonth.ExceptWith(monthHashSet);
            this.year = yearMonth.First();

            monthDay.ExceptWith(monthHashSet);
            this.day = monthDay.First();
        }

        // Constructs the object based on the Excel localization.
        public DateFormatComponentCodes(Excel.Application application)
        {
            this.year = application.International[Excel.XlApplicationInternational.xlYearCode].ToString()[0];
            this.month = application.International[Excel.XlApplicationInternational.xlMonthCode].ToString()[0];
            this.day = application.International[Excel.XlApplicationInternational.xlDayCode].ToString()[0];
        }

        public char Year
        {
            get
            {
                return this.year;
            }
        }

        public char Month
        {
            get
            {
                return this.month;
            }
        }

        public char Day
        {
            get
            {
                return this.day;
            }
        }
    }
}

And now let's create two objects of this class and use them to generate short-date format pattern (called above as "magic string") for Excel:

private string ConstructExcelShortDatePattern()
{
    var systemDateComponentCodes = new DateFormatComponentCodes();
    var excelDateComponentCodes = new DateFormatComponentCodes(this.application);

    string systemShortDatePattern = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
    string excelShortDatePattern = systemShortDatePattern.Replace(systemDateComponentCodes.Year, excelDateComponentCodes.Year).Replace(systemDateComponentCodes.Month, excelDateComponentCodes.Month).Replace(systemDateComponentCodes.Day, excelDateComponentCodes.Day);

    return excelShortDatePattern;
}

The returned string can be used to set the short-date format for all Windows localizations and all Excel localizations, like

worksheet.Cells[i, j].NumberFormat = ConstructExcelShortDatePattern();