Delphi to Excel - Date Format Issue with German Locale

Using Delphi 2007 - Windows 7 - German(Switzerland) locale.

In my application, I'm displaying a DateTime field with Displayformat as "MMM-YY".

Date 3-Mar-11 is displayed as Mai-11.

When exporting to Excel, application first writes grid data to TXT file and then imports it to Excel using OpenText() API.

Then we apply the grid properties to the cells. Thus, the date column in Excel is applied a NumberFormat = MMM-YY (custom format).

But, in Exc开发者_如何学运维el date is displayed as "Mai-YY", because in German the date format in Excel is MMM-JJ.

How do I apply the correct date format from Delphi OR correct Excel behaviour so that I can have a solution across all locales?

Excel and dates are a bit of a muddle. If you stick to using an intermediate file, then you are probably stuck. Excel uses its own date format settings and they may/will differ from the Windows' date format settings. So even if you export using the Windows locale settings, Excel may not behave as expected when it is using a different set of values.

Using automation you can interrogate Excel for its settings.

General recipe for exporting dates to Excel

Be aware of the characters used in Windows date and time settings.

WindowsYearCharacter: string = 'y';   // Do no localize!
WindowsMonthCharacter: string = 'm';  // Do no localize!
WindowsDayCharacter: string = 'd';    // Do no localize!
WindowsHourCharacter: string = 'h';   // Do no localize!
WindowsMinuteCharacter: string = 'm'; // Do no localize!

Please note that the Month and minute character are both a 'm', though for dates it should be uppercased. This is needed when you have a format string that takes both a date and a time. As we export either dates or times, we opted to cater for case-insensitive specification of Windows formats.

Interrogate Excel for its xlYearCode, xlMonthCode, xlDayCode, xlHourCode and xlMinuteCode formatting characters using the International collection.

ExcelDayChar := ExcelApplication.International[xlDayCode];

Convert your Windows locale settings to use the Excel characters.

ExcelDateFormat := ConvertWindowsLocalDateStringToExcel(ShortDateFormat);

This uses the function following below. Please note again that this function can only be used for either a date format or a time format. Adapt as necessary when you need formats combining both date and time.

function ConvertWindowsLocalDateStringToExcel(const aString: string): string;
begin
  // Year character vervangen :
  Result := StringReplace(aString, UpperCase(WindowsYearCharacter), UpperCase(ExcelYearCharacter), [rfReplaceAll]);
  Result := StringReplace(Result, LowerCase(WindowsYearCharacter), LowerCase(ExcelYearCharacter), [rfReplaceAll]);

  // Month character vervangen :
  Result := StringReplace(Result, UpperCase(WindowsMonthCharacter), UpperCase(ExcelMonthCharacter), [rfReplaceAll]);
  Result := StringReplace(Result, LowerCase(WindowsMonthCharacter), LowerCase(ExcelMonthCharacter), [rfReplaceAll]);

  // Day character vervangen :
  Result := StringReplace(Result, UpperCase(WindowsDayCharacter), UpperCase(ExcelDayCharacter), [rfReplaceAll]);
  Result := StringReplace(Result, LowerCase(WindowsDayCharacter), LowerCase(ExcelDayCharacter), [rfReplaceAll]);
end;

Then export your data to Excel and then set the number format for the columns (or rows, or cells) containing dates to use the ExcelDateFormat you determined earlier.

aRange.NumberFormat := ExcelDateFormat;

Where aRange is (an OleVariant with an) Excel Range type.

I had the same problem here, and the best solution I found was to declare the cell (or range) as text before you insert the date:

Excel.WorkBooks[1].Sheets[1].Range['A'+inttostr(row),'M'+inttostr(row)].NumberFormat := '@'; where row is a variable of my cycle, which also use to enter data in excel