Create object - Open Office - Error

[hvcrou]

Hello
have to generate an integration with the ods file open office, I am connecting to it using (vlax-get-or-create-object "com.sun.star.ServiceManager") which returns nil and no object and loaded. Can anyone help me?
Thanks.

[BazzaCAD]

This seems to be a VLisp issue & not an OpenDCL issue. I'd try posting over at: http://www.theswamp.org & you may get a better response. Let me know if you get it working though, as we use Open Office also.

[Fred Tomke]

Hi, have a look here.

Fred

[BazzaCAD]

Ya I looked at that too, but it doesn't look like he ever got it to work...

All he did was:

(defun lancer_oOo()
  (vlax-get-or-create-object "com.sun.star.ServiceManager")
)

then does nothing with it...

[Fred Tomke]

Ok, now I'm not sure whether this topic is solved or not. If not, I'd copy some code to address OOffice worksheet into this topic. But I'm not sure if I should...
  :-\
Fred

[BazzaCAD]

If not, I'd copy some code to address OOffice worksheet into this topic. But I'm not sure if I should...
  :-\
Fred

If you have some working code, I'd like to see it....

[Fred Tomke]

Hi, let's do it:

So you get access to an empty StarCalc Document:

Code (autolisp) Select Expand


(defun ooffice_calc_new (/ strDocType strVer strNam strFile oDesktop oService oStruct oCalc arrProps)
  (if (and ;; check if OOffice is installed
   (setq strDocType "StarCalcDocument")
   (setq strVer (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\soffice." strDocType "\\CurVer")))
   (setq strNam (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\" strVer)))
   (setq strFile (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\" strVer "\\protocol\\StdFileEditing\\server")))
   (findfile strFile))
    (progn
      (grtext -1 "I'm loading OpenOffice StarCalc, please wait...")
      (setq oService (vlax-get-or-create-object "com.sun.star.ServiceManager"))
      (setq oDesktop (vlax-invoke-method oService "createInstance" "com.sun.star.frame.Desktop"))
      (setq oStruct (vlax-invoke-method oService "Bridge_GetStruct" "com.sun.star.beans.PropertyValue"))
      (vlax-put-property oStruct "Name" "Hidden")
      (vlax-put-property oStruct "Value" :vlax-false)
      (setq arrProps (vlax-make-safearray vlax-vbvariant (cons 0 0)))
      (vlax-safearray-put-element arrProps 0 oStruct)
      (setq oCalc (vlax-invoke-method oDesktop "LoadComponentFromURL" "private:factory/scalc" "_blank" 0 arrProps))
      (vlax-release-object oStruct)
      (vlax-release-object oDesktop)
      (vlax-release-object oService)
    ); progn
  (grtext -1 "")
  ); if
  oCalc
); ooffice_calc_new


Get active sheet:

Code (autolisp) Select Expand


(defun ooffice_sheet_getactive (oDoc / oController)
  (setq oController (vlax-get oDoc 'CurrentController))
  (setq oSheet (vlax-get oController 'ActiveSheet))
  (vlax-release-object oController)
  oSheet
); ooffice_sheet_getactive

Set content

Code (autolisp) Select Expand


(defun ooffice_cell_setcontent (oSheet nRow nCol doDestroy uValue lstFormat / oCell symbValType)
  (setq oCell (vlax-invoke-method oSheet 'GetCellByPosition nCol nRow)
symbValType (type uValue))
  (cond
    ((= symbValType 'STR) (vlax-put oCell 'String uValue))
    ((= symbValType 'INT) (vlax-put oCell 'NumberFormat 3) (vlax-put oCell 'Value uValue))
    ((= symbValType 'REAL) (vlax-put oCell 'NumberFormat 4) (vlax-put oCell 'Value uValue))
    ((= symbValType 'LIST) (vlax-put oCell 'String (vl-prin1-to-string uValue)))
  ); cond
  (if lstFormat (mapcar '(lambda (x) (vl-catch-all-apply 'vlax-invoke-method (list oCell 'setPropertyValue (car x) (cdr x)))) lstFormat))
  (if doDestroy
    (progn
      (vlax-release-object oCell)
      T
    ); progn
    oCell
  ); if
); ooffice_cell_setcontent

In the end you can use

Code (autolisp) Select Expand


(if (and (setq oCalc (ooffice_calc_new))
           (setq oSheet (ooffice_sheet_getactive oCalc)))
  (ooffice_cell_setcontent  oSheet 0 0 T "MyData" (list (cons "CharHeight" 12.0) (cons "CharWeight" 150)))
); if


Fred

[jbuzbee]

wow

[AutoKAD]

Fred, that is awesome.  Thanks for posting.  Good example to learn from, so we might be able to do other things with Open Office.

-Kevin

[BazzaCAD]

Wow Fred, you da man, thx. :)

[Patrick_35]

If not, I'd copy some code to address OOffice worksheet into this topic. But I'm not sure if I should...
 :-\
Fred

If you have some working code, I'd like to see it....

Hello

I come to stumble upon this post, and the tools available to work.

You just get a little  ;)

For example

Code Select Expand


(setq My_Appli (lancer_ooo)) ; Launch open Office
(setq My_File (ouvrir_fichier My_Appli "c:/test/toto1.xls")) ; Open File
(ecrire_cellule My_File (feuille_active My_File) "A1" "Value") ; Write Cell
(setq Value (lire_cellule My_File (feuille_active My_File) "A1")) ; Read Cell
(sauver_fichier My_File) ; Save File
(fermer_fichier My_File) ; Close File
(fermer_appli (list My_Appli My_File)) ; Close Open Office


And the same work with Excel or Open Office

Code Select Expand


(or (setq My_Appli (lancer_excel)) ; Launch Excel
 (setq My_Appli (lancer_ooo)) ; Launch open Office
)
(setq My_File (ouvrir_fichier My_Appli "c:/test/toto1.xls")) ; Open File
(ecrire_cellule My_File (feuille_active My_File) "A1" "Value") ; Write Cell
(setq Value (lire_cellule My_File (feuille_active My_File) "A1")) ; Read Cell
(sauver_fichier My_File) ; Save File
(fermer_fichier My_File) ; Close File
(fermer_appli (list My_Appli My_File)) ; Close Excel or Open Office


@+

[Fred Tomke]

Thanks Patrick, but where did you get the API from (I mean functions like lancer_ooo, ouvrir_fichier, ...)?

Fred

[Fred Tomke]

Thanks Patrick, but where did you get the API from (I mean functions like lancer_ooo, ouvrir_fichier, ...)?

Fred

Sorry, Patrick, I've found it. I had tomatoes on my eyes  ::)

Fred

[Fred Tomke]

Hi, Barry asked me in a PM how to get access to an odt-file. After several hours of head crashing I leave it as is and give you the low level sample. I wanted to get it paragraph by paragraph. But unfortunately, it's hard for me to translate Java samples to AutoLisp.

I think, Patrick has much more experiences with OpenOffice than I. Maybe he can uncover the secrets of the OpenOffice API.

Here is my sample:

Code (autolisp) Select Expand

(defun ooffice_writer_content (strDoc / strDocType strVer strNam strFile oDesktop oService oStruct
                oWriter strContent oText arrProps string_subst)
  (if (and ;; check if OOffice is installed
   (setq strDocType "StarWriterDocument")
   (setq strVer (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\soffice." strDocType "\\CurVer")))
   (setq strNam (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\" strVer)))
   (setq strFile (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\" strVer "\\protocol\\StdFileEditing\\server")))
   (findfile strFile))
    (progn
      (grtext -1 "I'm loading OpenOffice Writer, please wait...")

      (defun string_subst (strNew strOld strText)
(while (vl-string-search strOld strText)
  (setq strText (vl-string-subst strNew strOld strText))
); while
strText
      ); string_subst

      (if (and strDoc (member (substr strDoc 2 1) (list ":" "\\")))
(setq strDoc (strcat "file:///" (string_subst "/" "\\" strDoc)))
(setq strDoc "private:factory/swriter")
      ); if
     
      (setq oService (vlax-get-or-create-object "com.sun.star.ServiceManager"))
      (setq oDesktop (vlax-invoke-method oService "createInstance" "com.sun.star.frame.Desktop"))
      (setq oStruct (vlax-invoke-method oService "Bridge_GetStruct" "com.sun.star.beans.PropertyValue"))
      (vlax-put-property oStruct "Name" "Hidden")
      (vlax-put-property oStruct "Value" :vlax-false)
      (setq arrProps (vlax-make-safearray vlax-vbvariant (cons 0 0)))
      (vlax-safearray-put-element arrProps 0 oStruct)
      (setq oWriter (vlax-invoke-method oDesktop "LoadComponentFromURL" strDoc "_blank" 0 arrProps))
      (setq oText (vlax-invoke oWriter 'getText))
      (setq strContent (vlax-invoke oText 'getString))
      (vlax-release-object oText)
      (vlax-release-object oWriter)
      (vlax-release-object oStruct)
      (vlax-release-object oDesktop)
      (vlax-release-object oService)
    ); progn
  (grtext -1 "")
  ); if
  strContent
); ooffice_writer_content

Here you can find the Java sample: http://wiki.services.openoffice.org/wiki/API/Samples/Java/Writer/TextDocumentStructure .

Regards, Fred

[BazzaCAD]

Thanks Fred.