ToCalc() Method to transfer xBrowse Data to OpenOffice Calc

[StefanHaupt]

Anser,

yes, "DE" stands for german, I forgot to mention it.

For the other languages I´m not sure
Spanish - "ES"
Portuguese - "PT"
Italian - "IT" ?
French - "FR" ?

[StefanHaupt]

Anser,

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

can you tell me the Url, where the API is located ?

Thanks.

[anserkk]

Dear Mr.Stefan,

Can you please test after replacing your existing Fuction GetNumberFormatId() to

Code: Select all  Expand view

STATIC Function GetNumberFormatId(oBook, cNumberFormat, cColHeader, cDataType)
  LOCAL cCharLocale,nFormatId
  cCharLocale = oBook:GetPropertyValue("CharLocale")
  IF cDataType == "D"  // Date
      cNumberFormat:=Upper(cNumberFormat)
      IF cCharLocale:Language == "es" .or. cCharLocale:Language == "pt" .or. cCharLocale:Language == "it"  // Spanish,Portuguese,Italian
        cNumberFormat:=StrTran(cNumberFormat,"Y","A")  // All Y should be replaced to A
      ELSEIF cCharLocale:Language == "de"  // German
         cNumberFormat:=StrTran(cNumberFormat,"D","T")  // All D should be replaced to T
         cNumberFormat:=StrTran(cNumberFormat,"Y","J")  // All Y should be replaced to J
      elseif cCharLocale:Language == "fr"  // French
         cNumberFormat:=StrTran(cNumberFormat,"D","J")  // All D should be replaced to J
         cNumberFormat:=StrTran(cNumberFormat,"Y","A")  // All Y should be replaced to A
      Endif
  Endif
  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

can you tell me the Url, where the API is located ?

I understand that information is wrong or misunderstood by me (I mean nFormatId:=37).
http://stuvel.eu/archive/31/article-abo ... and-python

Format index

Some index values:

0 => Standard
11 => Per cent
37 => Date
41 => Hour
60 => Scientific notation
70 => Fraction
99 => Boolean
100 => Text
105 => Personal
106 => Currency

But there's a lot more to find out )

com.sun.star.util.NumberFormat.DEFINED
com.sun.star.util.NumberFormat.DATE
com.sun.star.util.NumberFormat.TIME
com.sun.star.util.NumberFormat.CURRENCY
com.sun.star.util.NumberFormat.NUMBER
com.sun.star.util.NumberFormat.SCIENTIFIC
com.sun.star.util.NumberFormat.FRACTION
com.sun.star.util.NumberFormat.PERCENT
com.sun.star.util.NumberFormat.TEXT
com.sun.star.util.NumberFormat.DATETIME
com.sun.star.util.NumberFormat.LOGICAL
com.sun.star.util.NumberFormat.UNDEFINED
com.sun.star.util.NumberFormat.ALL

Meanwhile can you please test and let me know the date format is perfect in German.

Regards

Anser

[StefanHaupt]

Anser,

very good, it´s working perfectly

I got 2 warnings
Method ToCalc(), ambigious refence i, I added local i := 0
Function CalcSubTotal(), return statement with no value in function, I replaced return with Return (nil).

[anserkk]

Mr.Stepfan,

Thankyou very much. So German Language support is OK. Other languages not yet tested bu definitely should work.

Regards

Anser

[StefanHaupt]

Dear Anser,

I replaced all code of TCalc() with that one you posted on the first page (with all changes ?) and now I get this error:

Code: Select all  Expand view

Application
===========
   Path and name: D:\XBase\XHarbour\!XBrowse\OpenOffice\OCalc.exe (32 bits)
   Size: 1,794,048 bytes
   Time from start: 0 hours 0 mins 1 secs
   Error occurred at: 27-01-2009, 12:17:57
   Error description: Error BASE/1132  Bound error: array access
   Args:
     [   1] = A   { ... }
     [   2] = N   5

Stack Calls
===========
   Called from: xbrowse9.prg => PASTEUNFORMATTEDTEXT(5416)
   Called from: xbrowse9.prg => TXBROWSE:TOCALC(5185)
   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)

At the moment I´m completely at a loss, I don´t know why it´s not working. Did you change something important in PasteUnformattedtext () ?

[anserkk]

Dear MR.Stefan,

I have not changed anything in PasteUnformatedText

Can you tell me what is exactly on Line No:5416 on xBrowse

Called from: xbrowse9.prg => PASTEUNFORMATTEDTEXT(5416)

Are you using the new xBrowse 9.01, I have not tested xBrowse 9.01 with the changes which I have made. Anyway I'll test it with xBowse of version 9.01 and let you know.

Regards

Anser

[StefanHaupt]

Anser,

Can you tell me what is exactly on Line No:5416 on xBrowse

Code: Select all  Expand view

DO while i <= Len(oClipContent)
          if oClipContent[i] = 0 .OR. oClipContent[i] = 13 .OR. oClipContent[i] = 10
            oCol:=aCols[nCol+1]

Len (aCols) return 4 and nCol is also 4, so nCol+1 does not work

[anserkk]

Dear Mr.Stefan

Are you using xBrowse from FWH 9.01
I doubt that there is a change in the Method ClpRow of xBrowse in 9.01.

Please let me know.

Regards

Anser

[anserkk]

Dear Mr.Stefan,

Yes as doubted I find a change in the Method ClpRow

This is the new version of ClpRow 9.01

Code: Select all  Expand view

   if lFullRow
      for n := 1 to nLast
         if ! ::aCols[ n ]:lHide
            // if ! Empty( RetVal )
            //    RetVal   += Chr( 9 )
            // endif
            RetVal += strtran( ::aCols[ n ]:ClpText, CRLF, " ; " ) + Chr( 9 )
         endif
      next
   else
      RetVal := StrTran( ::SelectedCol():ClpText, CRLF, " ; " )
   endif


This is the Old version 8.12

Code: Select all  Expand view

   if lFullRow
      for n := 1 to nLast
         if ! ::aCols[ n ]:lHide
            if ! Empty( RetVal )
               RetVal   += Chr( 9 )
            endif
            RetVal += ::aCols[ n ]:ClpText
         endif
      next
   else
      RetVal   := ::SelectedCol():ClpText
   endif

I have adopted the Method ToExcel() and modified to support OpenOffice API's. PasteUnformattedText() rely on the data on the ClipBoard which uses the Method ClpRow() . Now I will have to Modify the PasteUnformattedText() to work with the changes in the Methos ClpRow()

Mr.Antonio, any suggestions/comments on this modification

Regards

Anser

[anserkk]

Dear Mr.Stefan,

I have changed the existing code of the Fuction PasteUnformattedText() to work with the new changes made in the Method ClpRow() in FWH 9.01

Code: Select all  Expand view

STATIC Function PasteUnformattedText(oCalc,oBook,oSheet,aCols)
  LOCAL oClipContType,oClipContent,oClip,cStr,i,nClipColNo
  LOCAL lFound,nRow,nCol,k,oCol

  oClip = oCalc:CreateInstance("com.sun.star.datatransfer.clipboard.SystemClipboard")
  oClipContType = oClip:Contents:getTransferDataFlavors

  lFound = .F. ;   i:= 1

  DO while i <= Len(oClipContType) .AND. !lFound
    if oClipContType[i]:HumanPresentableName = "OEM/ANSI Text"
      lFound = .T.
      k:=i
    else
      i:=i + 1
    endif
  Enddo

  if lFound

    nRow   = oBook:CurrentSelection:getRangeAddress():startrow
    oClipContent:=oClip:Contents:getTransferData( oClipContType[k] )

    i:=1 ;  cStr:="" ; nCol:=0

    DO while i <= Len(oClipContent)
       if oClipContent[i] = 0 .OR. oClipContent[i] = 13 .OR. oClipContent[i] = 10
        i=i+2 ; nRow:=nRow + 1 ; cStr:="" ;  nCol:=0
     ELSEIF oClipContent[i] = 9  // Tab
        oCol:=aCols[nCol+1]

        IF oCol:cDataType == "C"
            oSheet:getCellByposition(nCol,nRow):SetString(cStr)
        ELSEIF oCol:cDataType == "N"
            oSheet:getCellByposition(nCol,nRow):SetValue(cStr)
        ELSEIF oCol:cDataType == "D"
           oSheet:getCellByposition(nCol,nRow):SetFormula(cStr)
        ELSE
           oSheet:getCellByposition(nCol,nRow):SetString(cStr)
        Endif
        nCol:=nCol+1 ; cStr:="" ; i:=i+1
      else
        cStr:=cStr + chr(oClipContent[i])
        i:=i+1
      endif
    Enddo

  endif
RETURN NIL

[byte-one]

Hello, the changes of clprow are required:
If empty cells (especially if the first cell in row is empty, all the row is shift) or fields with CRLF in Text (memo)!

[anserkk]

No problem Mr.Günther ,

I understand. I have changed my functions accordingly.
Thankyou for the feedback.

Regards

Anser

[StefanHaupt]

Anser,

it works perfectly, many thanks for your contribution

[anserkk]

Dear Mr.Stefan,

I should thank you too. You helped me to test the function in languages other than English .

Regards

Anser