CODEWITHSUNDEEP
c++excelcomnativeoffice-automation
Naryoril
Naryoril

Reputation: 601

Setting Excel NumberFormat for Dates ignoring the locale in native C++

I'm using Excel COM automation from native C++. My goal is to set the NumberFormat of a range for dates. The Problem is, the format string Excel expects depends on the user's locale.

Example: If the locale is english i have to set the NumberFormat to "dd.mm.yyyy" If the locale is german i need to use "TT.MM.JJJJ" to get the same result.

I'v found a lot of solutions for .NET, unfortunately i don't have that luxury. The usual solution was to temporarily set the locale of the the Excel thread to english, but it doesn't seem possible with native C++.

I could read the default system locale and then hardcode different format strings, but this won't work as soon as someone uses a locale i haven't included explicitely, so this is a bad solution.

So, how do i solve this problem so it works no matter which locale is used?

Upvotes: 0

Views: 2542

Answers (1)

mfc
mfc

Reputation: 565

One possible solution is to use the system time formatter (like: _tcsftime, strftime, wcsftime) to format the date/time structure to the locale of the running system. Since both your program and excel is expected to run on the same PC, it should work properly without even knowing how the local date/time string look like.

As a fall-back solution, you may have to provide a series of hard-coded format string and/or a user-manually-entered string in the option-configuration section of your program, just in case the default locale format fails.

Additional informations after first reply comment from OP: --------------

locale setting affect the text-display presentation of a datetime-binary-object in the UI only. Such datetime-binary-object is alway stored as a binary structure in a very consistent way, irregardless of locale setting even in its each different contexts, be it in Excel (Date/Time format cell), C++ (various structure depends on api), VisualBasic (Variant).

So if your intention is to export the datetime binary structure to excel, not as a strng, you can alway use the "variant" type to store the date value as excel-automation uses the IDispatch interface. You can forget about the locale setting totally.

Adding of code fragment to export datetime variant and format the cell by code: ----------------

I am using MFC so the the code is very simple, you may have to convert to winapi youself

_Application app;
if (!app.CreateDispatch("Excel.Application"))
{   AfxMessageBox("Cannot start Excel !");
    return;
}
app.SetVisible(TRUE);   
COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
Workbooks objbooks = app.GetWorkbooks();
_Workbook objbook = objbooks.Add(VOptional);
Worksheets objsheets = objbook.GetWorksheets();
_Worksheet objsheet = objsheets.GetItem(COleVariant((short)1));

// get current date time
COleDateTime timeCurrent = COleDateTime::GetCurrentTime();

// setting date values
Range range;
range = objsheet.GetRange(COleVariant("A2"), COleVariant("B3"));
range.SetValue(COleVariant(timeCurrent));
//  range.SetNumberFormat(COleVariant("dd/mm/yyyy"));
range.SetNumberFormat(COleVariant("dddd, mmmm dd, yyyy"));

AfxMessageBox("Done...");

Upvotes: 2

Related Questions