PureBoard

Verfasst: **05.09.2010 13:16**

Hallo,
seit ein paar Tagen versuche ich ein OpenOffice Calc – Dokument, das ich mit PB und COMatePLUS erzeugt habe, als Excel-Datei abzuspeichern. Das Speichern als Calc (ods) habe ich mittlerweile hin bekommen, aber der Export in ein Fremdformat will einfach nicht funktionieren.

Der Code für das Erzeugen und Speichen eines Calc-Dokuments stammt zum größten Teil aus den Beispielen des englischen Forums.

Code: Alles auswählen

XIncludeFile "COMatePLUS.pbi"
XIncludeFile "VariantHelper_Include.pbi"

Procedure.S ConvertToUrl(strFile.S)
  strFile = ReplaceString(strFile, "", "/")
  strFile = ReplaceString(strFile, ":", "|")
  strFile = ReplaceString(strFile, " ", "%20")
  strFile = "file:///" + strFile
  ProcedureReturn strFile
EndProcedure

Define.COMateObject oSM, oDesk, oCalc, oSheet, oCell

Define.safearray *openpar
Define.variant openarray
Define.variant value_pi_double
Define.d result
V_ARRAY_DISP(openarray) = safearray
V_DOUBLE(value_pi_double) = 3.1415926

oSM = COMate_CreateObject("com.sun.star.ServiceManager")
If oSM
  
  *openpar = saCreateSafeArray(#VT_DISPATCH, 0, 3)
  
  oSM = COMate_CreateObject("com.sun.star.ServiceManager")
 
  ;Creating instance of Desktop
  oDesk = oSM\GetObjectProperty("CreateInstance('com.sun.star.frame.Desktop')")
    If oDesk
      ;Opening a new calc Document  
      load.S = "loadComponentFromURL('private:factory/scalc', '_blank', 0, " + Str(openarray) + " as variant)"
      Debug load
      oCalc = oDesk\GetObjectProperty(load.S)
      
      If oCalc
       
        oSheet = oCalc\GetObjectProperty("Sheets\getByName('Tabelle1')")
        If oSheet
          oCell = oSheet\GetObjectProperty("getCellRangeByName('A3')")
          oCell\SetProperty("String = 'Hello World1 !' as string")
          oCell = oSheet\GetObjectProperty("getCellRangeByName('A1')")
          datums.S = FormatDate("%hh:%ii:%ss", Date())  
          oCell\SetProperty("String = '"+datums.S+"' as string")
          oCell = oSheet\GetObjectProperty("getCellRangeByName('B5')")
          oCell\SetProperty("value = "+Str(value_pi_double)+" as variant")
          Debug COMate_GetLastErrorDescription() 
          oSheet\Release()
        EndIf
        
        url.S = ConvertToUrl("C:/temp/test.ods")
        saveFile.S = "storeToURL('"+url+"' " +", "+ Str(openarray) + " As variant)"

        oCalc\Invoke(saveFile)
        Delay(100)
        oCalc\Invoke("dispose")
        oCalc\Release()
      EndIf
      oDesk\Release()   
    EndIf
  oSM\Release()
EndIf 
End

Vielen Dank für eure Hilfe

Verfasst: **12.09.2010 15:56**

Hallo an Alle,

nach noch einer Woche von Test's und suchen habe ich dann bei http://www.purebasic.fr/english/viewtopic.php?p=262066

den entscheidenden Hinweis gefunden, wie man die Parameter für das Speichern in Fremdformat übergeben kann.
Für Alle, die es interessiert, habe ich einen Beispielcode eingefügt . Auch wenn er vielleicht nicht der Elegantes ist, er funktioniert.

Code: Alles auswählen

  XIncludeFile "COMatePLUS.pbi"
  XIncludeFile "VariantHelper_Include.pbi"
  Declare OOoMakePropertyValue(cName.S, uValue.b)
  Declare OOoMakePropertyValue1(cName.S, uValue.S)
  Declare.S OOoConvertToUrl(strFile.S) 
  
  Define.COMateObject oSM, oDesk, oCalc, oSheet, oCell
  
  Define.safearray *openpar0
  Define.variant openarray0
  Define.safearray *openpar1
  Define.variant openarray1
    
  Define.variant value_pi_double

  *openpar0 = saCreateSafeArray(#VT_DISPATCH, 0, 3)
  ;Das unsichtbare öffnen funktioniert irgendwie nicht, soll aber ein Bug von OOO sein
  ;SA_DISPATCH(*openpar0, 0) = OOoMakePropertyValue("Hidden", #True) 
  V_ARRAY_DISP(openarray0) = *openpar0
  *openpar1 = saCreateSafeArray(#VT_DISPATCH, 0, 3)
  SA_DISPATCH(*openpar1, 0) = OOoMakePropertyValue1("'FilterName'", "'MS Excel 97'")
  ;Beispiel für den Export as PDF-Datei
    ;SA_DISPATCH(*openpar1, 0) = oooMakePropertyValue1("'FilterName'", "'calc_pdf_Export'") 
  V_ARRAY_DISP(openarray1) = *openpar1

  oSM = COMate_CreateObject("com.sun.star.ServiceManager")
  
  If oSM
    ;Creating instance of Desktop
    oDesk = oSM\GetObjectProperty("CreateInstance('com.sun.star.frame.Desktop')")
    If oDesk
      ;Opening a new calc Document 
     
      oCalc = oDesk\GetObjectProperty("loadComponentFromURL('private:factory/scalc', '_blank', 0, " + Str(openarray0) + " as variant)")
      
      If oCalc 
        oSheet = oCalc\GetObjectProperty("Sheets\getByName('Tabelle1')")
        If oSheet
          oCell = oSheet\GetObjectProperty("getCellRangeByName('A3')")
          oCell\SetProperty("String = 'Hello World !' as string")
        EndIf
        oCalc\Invoke("storeToURL('" + OOoConvertToUrl("c:/temp/test.xls") + "' " +", "+ Str(openarray1) + " As variant)")
        Delay(250)
        oCalc\Invoke("dispose")
        oCalc\Release()
      EndIf
      oDesk\Release()   
    EndIf
    oSM\Release()
  EndIf
  End

Procedure.S OOoConvertToUrl(strFile.S)
  strFile = ReplaceString(strFile, "\", "/")
  strFile = ReplaceString(strFile, ":", "|")
  strFile = ReplaceString(strFile, " ", "%20")
  strFile = "file:///" + strFile
  ProcedureReturn strFile
EndProcedure

Procedure OOoMakePropertyValue(cName.S, uValue.b)
  Define.COMateObject oServiceManager,oStruct
  Define *ret.variant, iDisp.iDispatch
  oServiceManager = COMate_CreateObject("com.sun.star.ServiceManager")
  If oServiceManager
    *ret = oServiceManager\GetVariantProperty("Bridge_GetStruct('com.sun.star.beans.PropertyValue')")
    If *ret
      If *ret\vt <> #VT_DISPATCH
        VariantChangeType_(*ret, *ret, 0, #VT_DISPATCH)
      EndIf
      If *ret\vt = #VT_DISPATCH
        iDisp = *ret\pdispVal
        oStruct = COMate_WrapCOMObject(iDisp)
        If oStruct
          oStruct\SetProperty("Name = " + cName)
          oStruct\SetProperty("Value = " + Str(uValue))
          oStruct\Release() ;Required to keep the reference count in check.
        EndIf
      Else
        VariantClear_(*ret)
      EndIf
      FreeMemory(*ret) ;We do not use VariantClear_() as we do not wish to release the iDisp object which we are returning from this function.
    EndIf
  EndIf
  ProcedureReturn iDisp
EndProcedure 

Procedure OOoMakePropertyValue1(cName.S, uValue.S)
  Define.COMateObject oServiceManager,oStruct
  Define *ret.variant, iDisp.iDispatch
  oServiceManager = COMate_CreateObject("com.sun.star.ServiceManager")
  If oServiceManager
    *ret = oServiceManager\GetVariantProperty("Bridge_GetStruct('com.sun.star.beans.PropertyValue')")
    If *ret
      If *ret\vt <> #VT_DISPATCH
        VariantChangeType_(*ret, *ret, 0, #VT_DISPATCH)
      EndIf
      If *ret\vt = #VT_DISPATCH
        iDisp = *ret\pdispVal
        oStruct = COMate_WrapCOMObject(iDisp)
        If oStruct
          oStruct\SetProperty("Name = " + cName)
          oStruct\SetProperty("Value = " + uValue)
          oStruct\Release() ;Required to keep the reference count in check.
        EndIf
      Else
        VariantClear_(*ret)
      EndIf
      FreeMemory(*ret) ;We do not use VariantClear_() as we do not wish to release the iDisp object which we are returning from this function.
    EndIf
  EndIf
  ProcedureReturn iDisp
EndProcedure

bis bald dann
romel

PureBoard

COMatePLUS und OpenOffice.org

COMatePLUS und OpenOffice.org

Re: COMatePLUS und OpenOffice.org