Topic: Get List of Sheets from an Openoffice Calc Spreadsheet (ODS)

[FELIX]

I have the program below that opens ODS spreadsheets and I need to have a list of the sheets.

Code: [Select]

(DEFUN C:TEST ()
(VL-LOAD-COM)
(setq fileName "c:\\test.ODS")
(setq oCalc    (ooffice_calc_open filename))
(setq oSheets  (vl-catch-all-apply 'vlax-invoke-method (list oCalc 'getSheets)))
;
;(setq lsheets ...);list of osheets
;(print lsheets)
;
(vl-catch-all-apply 'vlax-invoke-method (list oCalc 'Dispose))
(princ)
)
;...
(defun ooffice_calc_open ( filename / 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
      (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" "Show")
      (vlax-put-property oStruct "Value" :vlax-true)
      (setq arrProps (vlax-make-safearray vlax-vbvariant (cons 0 0)))
      (vlax-safearray-put-element arrProps 0 oStruct)
      (setq filename (strcat "file:///" (vl-string-translate "\\" "/" FileName)))
      (setq oCalc (vlax-invoke-method oDesktop "LoadComponentFromURL" filename  "_blank" 0 arrProps))
      (vlax-release-object oStruct)
      (vlax-release-object oDesktop)
      (vlax-release-object oService)
     )
 )
 oCalc
)


[roy_043]

Code: [Select]

(defun Calc_SheetObjectList (documentObject / idx lst sheetsObject)
  (if
    (and
      (setq sheetsObject (vlax-invoke documentObject 'getSheets))
      (setq idx (vlax-invoke sheetsObject 'getCount))
    )
    (repeat idx
      (setq lst
        (cons (vlax-invoke sheetsObject 'getByIndex (setq idx (1- idx))) lst)
      )
    )
  )
)

[FELIX]

The function returns a list of # <VLA-OBJECT 0000000035d891e8>. I need the string name of the sheets and the order index.
How to obtain?

[roy_043]

Code: [Select]

(defun Calc_SheetNameList (documentObject)
  (mapcar
    '(lambda (sheetObject) (vlax-invoke sheetObject 'getName))
    (Calc_SheetObjectList documentObject)
  )
)The list returned by Calc_SheetObjectList is in the correct sheet tab order.

[FELIX]

Ok. Thank you.