Apache OpenOffice (AOO) Bugzilla – Issue 52509
Problem with setting date through automation
Last modified: 2013-02-24 21:08:58 UTC
Fourth, I tried to automatically create StarCalc documents with the following C-code in Visual C++ 6 Code: static HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs, ...) { // This code was copied from http://www.oooforum.org/forum/viewtopic.phtml?t=9815 // with only minor changes /* Variables used... */ va_list marker; DISPPARAMS dp = { NULL, NULL, 0, 0 }; DISPID dispidNamed = DISPID_PROPERTYPUT; DISPID dispID; HRESULT hr; char szName[200]; /* Allocate memory for arguments, should be on the safe-side with mul 100 */ VARIANT *pArgs = (VARIANT *)malloc(sizeof(VARIANT) * 100); int i = 0; va_start(marker, cArgs); if(!pDisp) return -1; /* Convert down to ANSI */ WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL); /* Get DISPID for name passed... */ pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID); /* Extract arguments... */ for(i=0; i<cArgs; i++) pArgs[i] = va_arg(marker, VARIANT); /* Build DISPPARAMS */ dp.cArgs = cArgs; dp.rgvarg = pArgs; /* Handle special-case for property-puts! */ if(autoType & DISPATCH_PROPERTYPUT) { dp.cNamedArgs = 1; dp.rgdispidNamedArgs = &dispidNamed; } /* Make the call! */ hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL); // char buffer[256]; // FormatMessage( FORMAT_MESSAGE_FROM_SYSTEM, 0, hr, 0, buffer, sizeof(buffer), 0); // MessageBox( 0, buffer, "Bla", MB_OK); if(FAILED(hr)) return hr; va_end(marker); free(pArgs); return hr; } // This is a function for statements // of the type "Set cell name XXXX to YYYY" // in which we want to ignore errors // Possibly, it will show debug messages void IGNORE_COM_ERRORS( IDispatch *pSheet, const TCHAR *name, VARIANT value) { if (!pSheet || !name) return; IDispatch *pCell; VARIANT result; VARIANT parm[1]; HRESULT h1; VariantInit(&parm[0]); parm[0].vt = VT_BSTR | VT_BYREF; _bstr_t cbs = name; BSTR bs = SysAllocString(cbs); parm[0].pbstrVal = &bs; h1=AutoWrap(DISPATCH_METHOD, &result, pSheet, L"getCellRangeByName", 1, parm[0]); pCell = result.pdispVal; VariantClear( &parm[0]); if (pCell && SUCCEEDED(h1)) if (SUCCEEDED(AutoWrap(DISPATCH_METHOD, &result, pCell, L"setFormula", 1, value))) return; if (bDebugTemplate) { CString s; s.Format( cLanguageInfo->pLang->msgDebug, name); MessageBox( 0, s, cLanguageInfo->pLang->msgDebugCaption, MB_OK | MB_ICONINFORMATION); } } void IGNORE_COM_ERRORS( IDispatch *pSheet, const TCHAR *name, DATE value) { VARIANT val; VariantInit( &val); val.vt = VT_DATE | VT_BYREF; val.pdate = &value; IGNORE_COM_ERRORS( pSheet, name, val); } main( ) { COleDateTime dt1( trans->when); COleDateTime dt2( 2005, 8, 3, 0, 0, 0); IGNORE_COM_ERRORS( pSheet, _T("DateIssue"), (DATE)dt2); } Well, this results in the month and day being swapped, ie. OOO reporst March 8th, 2005 instead of August 3rd, 2005.
I am very confused about this function: void IGNORE_COM_ERRORS( IDispatch *pSheet, const TCHAR *name, DATE value) { VARIANT val; VariantInit( &val); val.vt = VT_DATE | VT_BYREF; val.pdate = &value; IGNORE_COM_ERRORS( pSheet, name, val); } Maybe I am blind this late hour of the night, but this seems like something really bad to me (does your code ever exit?)
There's no problem. void IGNORE_COM_ERRORS( IDispatch *pSheet, const TCHAR *name, DATE value) simply creates a VARIANT of type VT_DATE and calls void IGNORE_COM_ERRORS( IDispatch *pSheet, const TCHAR *name, VARIAN value). There, the code ends.
sw->jl: any ideas ?
setFormula takes a string as argument. However, you provida a Variant containing a Date. The OleAutomation bridge uses VariantChangeType to convert the Date to a BSTR. This string must already contain a wrong version format. The string must contain the date in ISO format (jjjj-mm-dd) or in us-eng (mm/dd/jjj) in order to be recognized. Maybe the VariantChangeType depends on a locale that is not properly set in your application. What you could do is convert your Date with VariantChangeType into a BSTR in your appliccation and check if the string has the proper format.
Small correction, because the conversion from Date to BSTR occurs in the office process, the date format depends on the locale set in that process. To be on the safe side you could make the conversion in the client application and pass the string.
OK, I changed the code to BSTR bs = SysAllocString( L"2005-08-03"); IGNORE_COM_ERRORS( pSheet, _T("DateIssue"), bs);//(DATE)dt1); The problem still persists. I'm getting the data April (!!!) 8th, 2005 instead of August 3rd, 2005. Note that I'm getting it when the month is displayed in words so the problem is not a display one but OOo really must parse this string incorrectly.
I just learned that the ISO format is new in OOo2. So please try the 08/03/2005.
Changed code to BSTR bs = SysAllocString( L"08/03/2005"); IGNORE_COM_ERRORS( pSheet, _T("DateIssue"), bs);//(DATE)dt1); The results is still the same, ie. I get April 8th, 2005. Note that I even get April instead of March :-(
That is really strange. Could you strip down your code, so that it is still buildable and attach the source? Then I could have a closer look.
Sorry, I was working with a group of projects and had the active project set to something else than the OOo project. So the DLL that was exporting to OOo was not recompiled and therefore I got wrong results. The code is now working fine, passing 08/04/2005 instead of variant of type VT_DATE really helped. Thanks for the help. BTW, I was porting the code from a library exporting to Excel and there no problems occur if I pass VT_DATE instead of string.
Dear jl, may I just ask you a few questions? First of all, if I pass 08/04/2005, will the date be shown properly independently of international settings? Ie. will OOo always parse it as month/day/year and just change the display, even though the user might have set the date format to day/month/year? Or should I pass it as an integer value, ie. convert date to int and pass the result? Second, how do I pass currency and double types? I suppose that I use strings again but what should I use as decimal separator? , or . ? And will this really be independent of the user's settings? Thanks
JL->NN: Do you have an idea?
adding myself to cc. Seems like a real issue to me.
NN is currently on vacation. So be a patient :)
The string that's passed to setFormula is always assumed to be in US-English format, regardless of the system locale. From that string, the cell content (value) is determined, but the cell's number format is not changed, so the resulting date display can be in a different order, even depending on the system locale. The same is true for numbers. If you want to set a decimal number using setFormula, the string has to use "." as the decimal separator. Of course, you can also set a value using setValue, without involving any strings.
cyb->nn: So it's not a defect? Can you resolve the issue?
Yes, this isn't a defect and can be closed.
closing