CODEWITHSUNDEEP
c#excelvstooffice-addinsexcel-addins
AtoMedia Design
AtoMedia Design

Reputation: 31

Culture dependent NumberFormat for dates in C# VSTO Excel

I'm relatively new to C# and I'm still learning a lot. I also searched stackoverflow for similar problems but got to a point where I'm stuck.

In my VSTO Excel project I'd like to output dynamic data coming from a database with a predefined number format per column also coming from the database. The user can define his own layout of the report. Everything is working just fine, I have just problems formatting the date value.

When it's about to output a date column, the format should be automatically be determined by the program instead of predefining it in the database as well. The date value gets already inserted in the Excel sheet as the decimal number it should be. Everything okay here. When I manually format it in Excel itself, it is displayed correctly.

But I want to format the date with C# like it would be formatted when I do it manually by clicking on the short date format in Excel. And it should work in every country, regardless in which language the office package was installed.

I'm working with a German installation and I expect the format to be "TT.MM.JJJJ". When formatting the column with this fixed string, it works perfectly (at least in Germany I guess).

It makes no difference if formatting it with

CurrentWorksheet.Columns[queryColumn.Position].NumberFormat = "TT.MM.JJJJ";

or

CurrentWorksheet.Columns[queryColumn.Position].NumberFormatLocal = "TT.MM.JJJJ";

Both end up in the same (correct) result. That the first line is working as well is the first thing which was unexpected for me, but that's not the main point. I tried to format it dynamically by replacing "TT.MM.JJJJ" with the following lines:

// Leads to dd.MM.yyyy
System.Globalization.DateTimeFormatInfo.CurrentInfo.ShortDatePattern;

// Fixed German culture still leads to dd.MM.yyyy
CultureInfo.CreateSpecificCulture("de-DE").DateTimeFormat.ShortDatePattern;

// MM/dd/yyyy - also not helpful
CultureInfo.InvariantCulture.DateTimeFormat.ShortDatePattern;

When using the first or second ShortDatePattern the date 27.08.2014 is shown as "dd.08.yyyy". Excel simply doesn't unterstand "dd" or "yyyy". I thought at least when using NumberFormat instead of NumberFormatLocal it would work, because it's the global format, but it doesn't. It feels like Microsoft has implemented the date formatting more complicated that it could be.

How would I determine the correct date pattern here?

Upvotes: 1

Views: 399

Answers (1)

AtoMedia Design
AtoMedia Design

Reputation: 31

The answer (it still is a kind of workaround) is pretty much found in another question:

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

There's just a very small difference I had to make in the method for constructing the ShortDatePattern (the Application parameter for DateFormatComponentCodes).

public static string ConstructExcelShortDatePattern()
        {
            var systemDateComponentCodes = new DateFormatComponentCodes();
            var excelDateComponentCodes = new DateFormatComponentCodes(Globals.ThisAddIn.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;
        }

Thanks to Heinzi for finding a great answer and Jordan for providing such a useful class!

Upvotes: 1

Related Questions