AutoCAD use LibreOffice instead of Excel

[Tharwat]

Using exit function with cancel tile without handling the concequences should leave your device very lazy very soon.

[GLAVCVS]

@Tharwat: is this code working fine for you?

[Tharwat]

34 minutes ago, GLAVCVS said:

@Tharwat: is this code working fine for you?

I don't recommend testing such spaghetti codes copied from here and there and for the sake of increasing the number of posts.

[GLAVCVS]

Whether it's 'spaghetti' code or 'Made in myself' code, what matters is that, on this forum, there are people who spend part of their time trying to help other people.

And that's worth more than the best code.

Edited Monday at 10:35 AM by GLAVCVS

[SLW210]

On 2/9/2025 at 7:21 AM, GLAVCVS said:

If this is so, there is still another possibility....
Does anyone know how to find out the structure of these .ods files?

I posted a few links on LibreOffice and the source code from Sun Microsystems.

 

I still do not believe LISP is the best approach for AutoCAD. Autodesk seems to want only MS Office products accessible, a BricsCAD solution would still be nice.

 

When I can get back to working on programming, I am still going to look at a VBA, Python and/or C# solution. I might give LISP one more try.

 

I may have posted this, but any way. Calc Macros   Calc API overview.

[GLAVCVS]

3 hours ago, SLW210 said:

Publiqué algunos enlaces sobre LibreOffice y el código fuente de Sun Microsystems.

 

Todavía no creo que LISP sea la mejor opción para AutoCAD. Autodesk parece querer que solo los productos de MS Office sean accesibles, pero una solución BricsCAD sería buena.

 

Cuando pueda volver a trabajar en programación, seguiré buscando una solución en VBA, Python o C#. Quizás le dé otra oportunidad a LISP.

 

Es posible que haya publicado esto, pero de todos modos. Macros de Calc    Descripción general de la API de Calc .

 

Thanks

I found out that Libreoffice files are compressed in zip and that the contents of the tables are saved in an xml called 'content.xml'

 

[BIGAL]

@SLW210 the code posted was tested in Bricscad V24.

 

; https://www.cadtutor.net/forum/topic/79565-autocad-use-libreoffice-instead-of-excel/page/3/

; CAD to LIbre Calc subfunctions
; Version 1.0 Feb 2025 By Alan H
; Version 1.1 Feb 2025 By Alan H Blank or new dcl added
; Version 1.x ????

(vlax-invoke-method (vlax-invoke-method (list oController 'setActiveSheet 'name "Sheet1"))

(defun libgetcurrsheet ( / )
(setq oSheet (vl-catch-all-apply 'vlax-invoke-method (list oController 'getActiveSheet)))
(setq shname (vlax-get osheet 'Name))
)

; (vlax-get-property (vlax-get-property ocontroller 'activesheet) 'name)

(defun libsetactivesheet (sheetname / )
(setq mySheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property ocontroller "Sheets") "Item" sheetName)))
(vlax-invoke-method mySheet "Activate")

; not working
(defun libsheets ( / osheets cnt)
(setq oSheets (vlax-get-property oCalcdoc 'Sheets))
(setq cnt (vlax-get osheets 'count))

; need a get item here for names of sheets
)

(defun libgetusedrange ( / )
(setq uRange	(if (and (not (vl-catch-all-error-p
		(setq oCursor (vl-catch-all-apply 'vlax-invoke-method
						  (list oSheet 'createCursor)
			      )
		)
	      )
	 )
	 (not (vl-catch-all-error-p
		(vl-catch-all-apply 'vlax-invoke-method
				    (list oCursor 'gotoEndOfUsedArea 0)
		)
	      )
	 )
	 (not (vl-catch-all-error-p (setq oEndOfUsedArea
					   (vl-catch-all-apply 'vlax-invoke-method
							       (list oCursor 'getRangeAddress)
					   )
				    )
	      )
	 )
    )
  (list	0
	0
	(vlax-get oEndOfUsedArea 'EndColumn)
	(vlax-get oEndOfUsedArea 'EndRow)
  )
  )
)
(princ urange)
(princ)
)

; crange column row as list (2 2)
(defun libgetcell (crange / orange)
(setq oRange (vl-catch-all-apply 'vlax-invoke-method (cons oSheet (cons 'getCellByPosition crange))))
(setq cellval (vl-catch-all-apply 'vlax-invoke-method (list oRange 'getvalue)))
(princ cellval)
(princ)
)

; crange column row  column row as list (0 0 2 7)
(defun libgetrange (brange / orange data ar arr arl x)
(setq oRange (vl-catch-all-apply 'vlax-invoke-method (cons oSheet (cons 'getCellrangeByPosition brange))))
(setq Data (vl-catch-all-apply 'vlax-invoke-method (list oRange 'getDataArray)))
(princ "\nBeing worked on now")
)

; crange column row as list (2 2)
(defun libputcell (crange val / orange)
(setq oRange (vl-catch-all-apply 'vlax-invoke-method (cons oSheet (cons 'getCellByPosition crange))))
(vl-catch-all-apply 'vlax-invoke-method (list oRange 'setvalue val))
; (vlax-put-property orange 'value val)
(princ)
)

; defun change borders not working
(defun libborders (brange / )
(setq oRange (vl-catch-all-apply 'vlax-invoke-method (cons oSheet (cons 'getCellrangeByPosition brange))))
(setq brds (vl-catch-all-apply 'vlax-invoke-method (cons 'getBorder orange)))
(vlax-put-property brds 'LineStyle 1)
)

; Thanks to Tim_n for this code via Bricscad forums
; defun get a range of cells to a List
; (setq lst (STARCALCSHEET-GETLISPARRAY osheet 0 0 2 7))

(defun librange2list (calcheet col1 row1 col2 row2)
(defun AxVariant-Value (o) (vlax-variant-value o))
(defun AxSafeArray->List (o) (vlax-SafeArray->list o))
(defun AxVariant->List (o) (vlax-SafeArray->list (vlax-variant-value o)))
(defun starDataArray->LispArray (dataArray / rows lArray)
(setq rows (AxVariant->List dataArray))
(foreach row (mapcar 'AxVariant->List rows)
(setq lArray (cons (mapcar 'AxVariant-value row) lArray))
)
(reverse lArray)
)
(defun starCalcRange-getDataArray (calcRange ) (vlax-invoke-method calcRange "getDataArray"))
(defun starCalcSheet-getCellByPosition (calcSheet col row)
(setq col (if col col 0) row (if row row 0))
(vlax-invoke-method calcSheet "getCellByPosition" (vlax-make-variant col vlax-vbLong) (vlax-make-variant row vlax-vbLong))
)
(defun starCalcSheet-getCellRangeByPosition (calcSheet left top right bottom )
(vlax-invoke-method calcSheet "getCellRangeByPosition" (vlax-make-variant left vlax-vbLong) (vlax-make-variant top vlax-vbLong) (vlax-make-variant right vlax-vbLong) (vlax-make-variant bottom vlax-vbLong))
)
(defun starCalcSheet-getLispArray (calcSheet left top right bottom )
(starDataArray->LispArray (starCalcRange-getDataArray (starCalcSheet-getCellRangeByPosition calcSheet left top right bottom )))
)
(princ)
)

;;;;;;;;;;;;;;;;;;; starts here ;;;;;;;;;;;;;;;;;;;;

(defun openlibre ( / )

(setq fo (open (setq fname (vl-filename-mktemp "" "" ".dcl")) "w"))
(write-line "AHbutts : dialog 	{" fo)
(write-line "	label =\"Please choose\" ;" fo)
(write-line "	: row	{" fo)
(write-line "	: boxed_radio_column 	{" fo)
(write-line " width = 22 ;" fo)
(write-line "	: radio_button	{" fo)
(write-line "key = \"Rb1\"; " fo)
(write-line "label = \"Blank sheet\";" fo)
(write-line "	}" fo)
(write-line "spacer_1 ;" fo)
(write-line "	: radio_button	{" fo)
(write-line "key = \"Rb2\"; " fo)
(write-line "label = \"File\"; " fo)
(write-line "	}" fo)
(write-line "spacer_1 ;" fo)
(write-line "	}" fo)
(write-line "	}" fo)
(write-line "spacer_1 ;" fo)
(write-line "	ok_cancel;" fo)
(write-line "	}"  fo)
(close fo)
(setq dcl_id (load_dialog fname))
(if (not (new_dialog "AHbutts" dcl_id) )
(exit)
)
(set_tile "Rb1" "1")

(action_tile "accept" "(setq ans (get_tile \"Rb1\" ))(done_dialog)")
(action_tile "cancel" "(done_dialog)(exit)")
(start_dialog)
(unload_dialog dcl_id)
(vl-file-delete fname)

(if (= ans "1")
 (setq FileName "D:\\acadtemp\\blank.ods")
 (setq filename (getfiled "ODS or XLS" "d:\\" "ods,xls" 16))
)

(setq oServiceManager (vlax-get-or-create-object "com.sun.star.ServiceManager"))
(setq oDesktop (vl-catch-all-apply
	     'vlax-invoke-method
	     (list oServiceManager 'createInstance "com.sun.star.frame.Desktop")
	   )
)
(setq oCalcDoc (vl-catch-all-apply
   'vlax-invoke-method
   (list oDesktop
   'loadComponentFromURL
   (strcat "file:///" (vl-string-translate "\\" "/" FileName))
   "_blank"
   0
   (vlax-make-safearray vlax-vbObject (cons 0 0))
   )
 )
)
(setq oController (vl-catch-all-apply 'vlax-invoke-method
    (list oCalcDoc 'getCurrentController)
	)
)
(setq oSheet (vl-catch-all-apply 'vlax-invoke-method (list oController 'getActiveSheet)))

(princ)
)

 

Edited Monday at 10:49 PM by BIGAL