ToCalc() Method to transfer xBrowse Data to OpenOffice Calc

[StefanHaupt]

Silvio,

I ERASE ALSO THIS LINE : Set Date British

Please add

Code: Select all  Expand view

Set Date Italian

, to get the right dateformat

[anserkk]

Hi Stefan,

I uninstalled my OpenOffice 3.0 (English version) and about to install the German version. Anyway I am going to try it out

That was a very good hint. Waiting to get a reply from Silvio too.

To avoid the error we can use TRY catch but I still wonder why the error is happening. Any hint ?

I see the function CalcSubTotal() does not return a value, shouldn´t it return one ?

Do you mean to return a .T. or .F. flag ?.

I was palnning to avoid the Function CalcSubTotal() and do it straight away from the Method ToCalc() itself. CalcSubTotal() is only called once.

Regards

Anser

[anserkk]

Dear Stefan and Silvio,

I installed OpenOffice (German Version) and run the tested with and without changing the code
ie Set Date British

It worked fine without any errors. May be it is because of the Language and Regional settings on my XP. I even tried changing the Regional Settings in Control Panel to German (Germany)

Regards

Anser

[StefanHaupt]

Dear Anser,

I installed OpenOffice (German Version) and run the test without changing any code
ie I did not remove the line Set Date British

It worked fine without any errors. May be it is because of the Language and Regional settings on my XP.
Anser

Yes, maybe it depends on the laguage settings of XP, if I set date british this error occurs

Error description: Error com.sun.star.ServiceManager:CREATEINSTANCE:LOADCOMPONENTFROMURL:GETNUMBERFORMATS/3 DISP_E_MEMBERNOTFOUND: ADDNEW
Args:
[ 1] = C DD/MM/YYYY
[ 2] = O Object

I will make more tests on other pc this afternoon

[Silvio]

Silvio,

I ERASE ALSO THIS LINE : Set Date British

Please add

Code: Select all  Expand view

Set Date Italian

, to get the right dateformat

I add set date italian
but thereis thi s error

Code: Select all  Expand view

Application
===========
   Path and name: C:\work\errori\openoffice\OCalc.Exe (32 bits)
   Size: 1,797,632 bytes
   Time from start: 0 hours 0 mins 9 secs
   Error occurred at: 22-01-09, 16:42:43
   Error description: Error com.sun.star.ServiceManager:CREATEINSTANCE:LOADCOMPONENTFROMURL:CURRENTCONTROLLER/3  DISP_E_MEMBERNOTFOUND: SELECT
   Args:
     [   1] = O   Object

Stack Calls
===========
   Called from: source\rtl\win32ole.prg => TOLEAUTO:SELECT(0)
   Called from: xbrowse.prg => TXBROWSE:TOCALC(5202)
   Called from: Test.Prg => (b)MAIN(49)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:CLICK(0)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:LBUTTONUP(0)
   Called from:  => TWINDOW:HANDLEEVENT(0)
   Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT(0)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:HANDLEEVENT(0)
   Called from: .\source\classes\WINDOW.PRG => _FWH(0)
   Called from:  => WINRUN(0)
   Called from: .\source\classes\WINDOW.PRG => TWINDOW:ACTIVATE(0)
   Called from: Test.Prg => MAIN(55)

[Antonio Linares]

Anser,

Yes, we plan to include your code in the next FWH build.

Many thanks!

[StefanHaupt]

Anser,

here are the results of my tests.

Environment WindowsXP, WindowsXP64, Windows Vista (all german), OpenOffice 3 german

It´s always working with Set Date German
It´s always working with Set Date Italian
It´s not working with Set Date British

The only problem is the date format in OpenOffice, the column shows dates like "DD.06.YYYY" instead of "05.06.1991"

[anserkk]

Dear Mr.Stefan,

Really appreciate your effort. Thankyou very much.

Can't understand why in OpenOffice German version Set Date British is failing at your end. I tested OpenOffice German version and Set Date British is working fine here.

The only problem is the date format in OpenOffice, the column shows dates like "DD.06.YYYY" instead of "05.06.1991"

Did you mean that Date is Displayed as "DD.06.YYYY" instead of "05.06.1991 when Set Date British is ON in your OpenOffice German version? Did Set Date British give you a run time error ?.

Can you change the statement to Set( _SET_DATEFORMAT ) to LOWER( Set( _SET_DATEFORMAT ) )
in the following line in Method ToCalc()

Original Code

Code: Select all  Expand view

if lxlEnglish
  if ValType( oCol:cEditPicture ) == 'C' .and. !( oCol:cEditPicture = '@' )
     oSheet:GetColumns():GetByIndex( nCol-1 ):NumberFormat:=GetNumberFormatId(oBook,  oCol:cEditPicture  )
   else
     oSheet:GetColumns():GetByIndex( nCol-1 ):NumberFormat:=GetNumberFormatId(oBook,  Set( _SET_DATEFORMAT )  )
   endif
Endif

Please change to ( use LOWER() )

Code: Select all  Expand view

if lxlEnglish
  if ValType( oCol:cEditPicture ) == 'C' .and. !( oCol:cEditPicture = '@' )
     oSheet:GetColumns():GetByIndex( nCol-1 ):NumberFormat:=GetNumberFormatId(oBook,  oCol:cEditPicture  )
   else
     oSheet:GetColumns():GetByIndex( nCol-1 ):NumberFormat:=GetNumberFormatId(oBook,  LOWER( Set( _SET_DATEFORMAT ) ) )
   endif
Endif

Sorry to trouble you. Here I am not able to produce the error which you are getting there while using Set Date British, hence no chance to know whether using the command LOWER() solves the problem or not.

Regards

Anser

[StefanHaupt]

Anser,

Did you mean that Date is Displayed as "DD.06.YYYY" instead of "05.06.1991 when Set Date British is ON in your OpenOffice German version? Did Set Date British give you a run time error ?.

No, it´s displayed with Set Date German or Set Date Italian in the german version of OpenOffice, Set Date British always give me a runtime error with the german version of before OpenOffice is opened.

I will test your suggestion using Lower (...)

Thanks.

[anserkk]

Hi,

Modified the function GetNumberFormatId() to avoid Run Time Errors if any

Code: Select all  Expand view

STATIC Function GetNumberFormatId(oBook, cNumberFormat, cColHeader, cDataType)
  LOCAL cCharLocale,nFormatId
  cCharLocale = oBook:GetPropertyValue("CharLocale")
  nFormatId = oBook:GetNumberFormats:QueryKey(cNumberFormat, cCharLocale, .F.)
  IF nFormatId = -1 // 'Format is not yet defined
     TRY
        nFormatId = oBook:GetNumberFormats:AddNew(cNumberFormat, cCharLocale)
     CATCH
        MsgInfo("Could not set the format "+cNumberFormat+" to column "+cColHeader)
        IF cDataType == "D"  // Date
           nFormatId:=37
        Endif
     END
  ENDIF
RETURN nFormatId

I believe the obove code should solve the problem, because nFormatId:=37 should assign the dafault Date Format

The same has been updated in my post containing the code. ( 1st post on Page 2 of this thread)

Stefan & Silvio,

I was able to reproduce the error which Silvio and Stefan was getting when Set Date British format is specified. Now I am using OpenOffice German version. Now If I don't specify any date format xHarbour fuction Set(_SET_DATEFORMAT) will return the date format string as MM/DD/YYYY (ie American Date Format). Thru the function GetNumberFormatId() if I try to add the date format as MM/DD/YYYY, a run time error will occur because OpenOffice German version is expecting the format to be TT.MM.JJJJ

If I am not wrong TT is equal to DD, MM is OK ie MM itself, JJJJ is equal to YYYY.

If the above code ie nFormatId:=37 does not solve the problem then I think I should follow the below given steps

Thru API
1) Find out the Language of OpenOffice Installation
2) If the Language is not English then (For German language) Get the date format string and then search the string for DD and convert to TT, Covert YYYY to JJJJ, Convert / to .

Code: Select all  Expand view

German      : TT.MM.JJJJ
Spanish      : DD/MM/AA
Portuguese : DD/MM/AA  or  DD.MM.AA
French       : JJ/MM/AA  or   JJ.MM.AA
Italian        : GG/MM/AA

Can anybody confirm the above given Date format strings. To find out the format string, just try to format a cell containing Date. ie DD MM YYYY equivalent in other FiveWin supported languages.

But I believe that the code nFormatId:=37 should solve the problem.

Request : Test, please...

Regards

Anser


Regards
Anser

[StefanHaupt]

Hi Anser,

the runtime error is gone now, but the format problem remains.

OpenOffice does not know a dateformat like dd.mm.yyyy, it exspects it in this form tt.mm.jjjj.
nFormatID := 37 does not solve the problem, where did you get this value from ?

It seems, the only solution is to get the langauage of the installed OpenOffice.

[Silvio]

Now I used another class by O**a* and it run ok
Perhaps if you see it you can found a solution

[anserkk]

Silvio,

If u don't mind can I have a copy of the class which u have said. It may help me as a reference.

Stefan,

Thankyou for the feedback.

As said, I think we will have to find out the installed language and then convert dd/mm/yyyy to equivalent tt.mm.aaaa etc.

The nFormat id 37 is a API constant for date format. I thougt it will set the column to the default date format.

Unfortunately the api to check the installed language is always showing Country as US and Language as English. Can u please add the following code to the function GetNumberFormat after the line which define the cCharLocale.

msginfo(cCharLocale:Country)
Msginfo(cCharLocale:Language)

Can u tell me the values displayed via MsgInfo ?

Regards

Anser

[StefanHaupt]

Dear Anser,

msginfo(cCharLocale:Country) returns "DE"
Msginfo(cCharLocale:Language) returns "de"

[anserkk]

Dear Anser,

msginfo(cCharLocale:Country) returns "DE"
Msginfo(cCharLocale:Language) returns "de"

Dear Mr.Stefan,

Thankyou for the test result. I hope "DE" is for German right ?. Based on this value we can do strtran of all DD to TT etc.

Now I should get the country and language codes for French, Italian, Portuguese and Spanish. Let me search for it now. Any hint ?.

Regards

Anser