1

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?

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
AtoMedia Design
  • 31
  • 3
  • Are you *sure* that setting (only!) `NumberFormat` (not NumberFormatLocal) to `dd.mm.yyyy` won't work? I just tried `myWorksheet.Columns(1).NumberFormat = "dd.mm.yyyy"` on my German Excel VBA, and the date was formatted successfully. `Debug.Print myWorksheet.Columns(1).NumberFormat` and `....NumberFormatLocal` now yield `dd.mm.yyyy` and `TT.MM.JJJJ`, respectively, as expected. – Heinzi Jun 29 '22 at 08:25
  • Yes, in VBA it is working exactly as you described. That was my old solution and this was really easy. Now when moving the code step by step to an Excel add-in solution in C# it doesn't work anymore. This was the reason why I wrote "That the first line is working as well is the first thing which was unexpected for me". – AtoMedia Design Jun 29 '22 at 09:04
  • 1
    I just did a bit more research and [found a few old newsgroup threads](https://groups.google.com/g/microsoft.public.de.german.entwickler.dotnet.csharp/c/Hw19UyNlDLw/m/_9XYZ-MfbuQJ) which indicate that (a) this is a known problem and (b) the solution is to temporarily change the thread culture to en-US while setting the NumberFormat property. – Heinzi Jun 29 '22 at 09:15
  • Thanks, I thought it would work, but it doesn't. ShortDatePattern changed from dd.MM.yyyy to M/d/yyyy, but Excel now shows it as "8/d/yyyy". It feels like the Excel sheet or something is still speaking German while the thread doesn't. ;-) I'm short before building a workaround and put all needed date formats for the different counties in one database table. But that would be a very sad solution. Current code follows in the next comment. – AtoMedia Design Jun 29 '22 at 11:27
  • `//Remember the current culture settings` `CultureInfo oldCulture = Thread.CurrentThread.CurrentCulture; //de-DE` `//Set the culture to US English` `Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");` `//Set the date format` `CurrentWorksheet.Columns[queryColumn.Position].NumberFormat = System.Globalization.DateTimeFormatInfo.CurrentInfo.ShortDatePattern;` `//Restore the old culture` `Thread.CurrentThread.CurrentCulture = oldCulture;` It's so bad formatted in the comment section, sorry. – AtoMedia Design Jun 29 '22 at 11:29
  • 1
    Hmmm.... that's a tough one, I really thought that would work. I found something else, though: https://stackoverflow.com/q/35394281/87698 – Heinzi Jun 29 '22 at 12:09
  • 1
    This was indeed VERY helpful! In your last link the problem was pretty well described and you get a feeling how complex (and bad in my opinion) the implementation in C# is. I used the described class and method to get the correct ShortDatePattern and translate it into the currently used Excel culture. I wouldn't have figured that out on my own! **Thank you so much!** Of course also Szabolcs Dézsi and Jordan from the other thread. :) – AtoMedia Design Jun 29 '22 at 13:01
  • You're welcome, glad to hear that it worked out! – Heinzi Jun 29 '22 at 13:13

1 Answers1

1

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!

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
AtoMedia Design
  • 31
  • 3