Jump to content

Recommended Posts

Posted (edited)

Getting somewhere, thanks to Virgil at Bricscad forum.

 

There seems not to be much information on this topic.

I could find a few articles (not all in english) with some references about how to connect from Calc to Autocad, using StarBasic.
https://forums.autodesk.com/t5/vba/interconnection-between-autocad-and-libreoffice-calc-via-vba/td-p/9734036
https://cadxp.com/topic/43274-connection-libre-office-calc-et-autocad/
https://www.redchar.net/pages/?x=libreoffice-scrivere-dwg

Regarding the reverse connection, I could only find this old forum post, but there is nothing specific.
https://forum.bricsys.com/discussion/21458/libreoffice-calc-link-to-bricscad

 

Lots of good code to look at can run VBA code from Lisp, but will probably look at convert the VBA to lisp. I guess may have to bite the bullet and down load Libreoffice calc if to go any further. Bit busy at moment, may just do some try to open code. 

 

definitely look at Creating DWG files with LibreOffice and progeCAD - Roberto Rossi (redchar.net) try the sample file, let me know how it goes.

Edited by BIGAL
Posted

I really don't see doing this at the LISP level. Really need to just use LISPs for generic text files and bypass Calc, IMHO.

 

I looked at all of this a while ago, seems no major changes since then.

 

LibreOffice also is not compatible with Microsoft VBA, it has it's own language, a version of BASIC. LibreOffice BASIC Programming Guide - The Document Foundation Wiki

 

  Quote

Note:
VBA : Compatibility between LibreOffice Basic and VBA relates to the LibreOffice Basic language as well as the runtime library. The LibreOffice API and the Dialog Editor are not compatible with VBA (standardizing these interfaces would have made many of the concepts provided in LibreOffice impossible).

 

Import MS-Word Macros - English - Ask LibreOffice

 

 

 

Posted

I don't want to waste any more of your time with this

Thank you very much for everything...

Posted (edited)

Well this works for put cells try this using VBA in Acad or Bricscad. Opens a blank document and puts some values so a big success. 

 

image.thumb.png.c20c76aa8d14134453c46c153443d50b.png

 

Sub Main()
    'VARIABLES:
        Dim obL_Service_Manager As Object
        Dim obL_Core_Reflection As Object
        Dim obL_Desktop As Object
        Dim srL_Url As String
        Dim obL_Calc_Document As Object
        Dim obL_Sheet As Object
        Dim obL_Range_First_Column As Object
        Dim obL_Range_ToSort As Object
        Dim a1L_Arguments()
        Dim lnL_iLast_Row As Long
        Dim lnL_iLast_Column As Long
    'PROCESS:
        Set obL_Service_Manager = CreateObject("com.sun.star.ServiceManager")
        Set obL_Core_Reflection = obL_Service_Manager.createInstance("com.sun.star.reflection.CoreReflection")
        Set obL_Desktop = obL_Service_Manager.createInstance("com.sun.star.frame.Desktop")
        srL_Url = "private:factory/scalc"
        Set obL_Calc_Document = obL_Desktop.loadComponentFromURL(srL_Url, "_blank", 0, a1L_Arguments)
        Set obL_Sheet = obL_Calc_Document.Sheets.getByIndex(0)

        obL_Sheet.getCellRangeByName("A1").String = "PAPERINO"
      obL_Sheet.getCellRangeByName("C6").Value = 200
      obL_Sheet.getCellRangeByName("C7").Value = 100
      End Sub

I did down load Libre office only way to work out anything. Next step is open a known Ods file. See srl = 

Edited by BIGAL
Posted

No LibreOffice at work, maybe tonight I will have time at home.

 

I am still reading through all of the information I have linked here.

 

One of those sites has a link to a Sun Microsystems StarOffice programming guide which OpenOffice and LibreOffice is from the source code that was released, that's why all of (com.sun.star.) references. From what I have found on StarOffice, they had a Macro Converter for converting Microsoft Office VBA macros to StarOffice Basic. I am not sure if it was ever open sourced.

 

There is an online converter, not sure how well it works... Excel VBA to OpenOffice Basic Converter

 

Here is a PDF on converting VBA to StarBasic from OpenOffice VbaStarBasicXref.pdf

 

I also found that adding 

 

option vbasupport 1

 

to the top of  VBA code will work in some cases (newer releases).

Posted

I would still like to see a before and after example of what exactly is being modified.

 

There still may be a better method to get this done.

Posted

Here I sent a sample of what it looks like before and after 

  On 2/9/2024 at 8:34 AM, Truski said:

 

Hi SLW210, I am familiar with the Batch Attribute Editor application from LEE MAC. It works very well but in my case not all attributes are the same, so I can't use that application.

 

 

TEST.7z 1.98 MB · 3 downloads

Expand  

 

Posted (edited)

I amended the test code, removed the call to last row etc.

 

If you go into VBAMAN and make a DVB can load it. Or do this, it should open a Calc sheet. The paperino is a string and the numbers are just that.

(vl-vbaload "D:\\Acadtemp\\Libre\\test1.dvb")
(vl-vbarun "Test1")

 

image.png.05e61102ae93df5c5104105e9ca43a6a.png

Test1.dvbFetching info...

 

 

Edited by BIGAL
Posted

Truski, next step is to work out how to do for multiple dwgs but only maintain 1 Libreoffice calc. Even with lisp this is not an easy task, simplest way is to use a script and make a csv file. Just google and you will find many examples.

 

I will continue to play with Libreoffice calc and try to convert to VL.

  • 5 months later...
Posted

I haven't tested this since no LibreOffice at work. I'll take it home and if I find time at home check on it.

 

I started with this one by fixo, I'll check on others if it works...

 

;;; Extract block count information from AutoCAD and exporting it to an LibreOffice Calc file.                                             |
;;;                                                                                                                                        |
;;; https://www.cadtutor.net/forum/topic/79565-autocad-use-libreoffice-instead-of-excel/                                                   |
;;;                                                                                                                                        |
;;; By SLW210 (Steve Wilson)                                                                                                               |
;;;________________________________________________________________________________________________________________________________________|
;;;                                                                                                                                        |
;;; Original- August 7th, 2024                                                                                                             |
;;;                                                                                                                                        |
;;;________________________________________________________________________________________________________________________________________|
;;;                                                                                                                                        |
;;; Adapted the original LISP code to work with LibreOffice instead of Excel, I used LibreOffice's COM API, which is similar to Excel's    |
;;; but with some differences in method names and object handling. (I put the internet to good use for this.)                              |
;;;////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////|
;;;                                                                                                                                        |
;;; Thanks to all of the people on various LibreOffice, OpenOffice, StarOffice by Sun Microsystems (original source code)                  |
;;; and related content forums including some AutoCAD related Forums, mostly CADTutor.net and BIGAL.                                       |
;;;________________________________________________________________________________________________________________________________________|
;;; Originally BCEX.lsp modified to work with LibreOffice Calc (LOC).                                                                      |
;;;________________________________________________________________________________________________________________________________________|
;;;***********************************************************Original header**************************************************************|
;;;                                                                                                                                        |
;;; From man to man                                                                                                                        |
;;;                                                                                                                                        |
;;; written by Fatty 2005 All rights removed                                                                                               |
;;;                                                                                                                                        |
;;; helper function                                                                                                                        |
;;;                                                                                                                                        |
;;; group list into separate sublists                                                                                                      |
;;;                                                                                                                                        |
;;;****************************************************************************************************************************************|
(defun group-by-first (lst / ret tmp)
  (while (car lst)
    (setq tmp (list (vl-remove-if-not
		      (function	(lambda	(a)
				  (eq a (car lst))
				)
		      )
		      lst
		    )
	      )
    )
    (setq ret (cons (car tmp) ret))
    (setq lst (vl-remove-if
		(function (lambda (a)
			    (eq a (car lst))
			  )
		)
		lst
	      )
    )
    (setq tmp nil)
  )
  (setq	ret (mapcar (function (lambda (x)
				(list (car x) (length x))
			      )
		    )
		    (reverse ret)
	    )
  )
)

(defun C:BCEX_LOC (/	    acsp     adoc     aoffice  adoc
		   adoc_path	     aoffice_path      all_data
		   awb	    blk_lst  bname    bnm      brds
		   cll	    clls     colm     cols     com_lst
		   csht	    data     data_list	       datum
		   en	    filt     fnt      header_list
		   i	    intr     nwb      rang     row
		   scol	    sht	     srow     ss
		  )

  (vl-load-com)

  ;; Initialize LibreOffice
  (setq
    aoffice (vlax-get-or-create-object "com.sun.star.ServiceManager")
  )
  (setq	adoc (vlax-invoke-method
	       aoffice
	       'createInstance
	       "com.sun.star.frame.Desktop"
	     )
  )
  (setq	adoc (vlax-invoke-method
	       adoc		   'loadComponentFromURL
	       "file:///"	   "private:factory/swriter"
	       :vlax-false	   :vlax-false
	       nil		   nil
	      )
  )
  (setq aoffice_path (vlax-get-property adoc 'URL))
  (setq adoc_path (vl-string-left-trim "file://" aoffice_path))

  ;; Initialize other variables
  (setq	blk_lst	nil
	data nil
	com_lst	nil
  )

  ;; Prompt for block name
  (setq
    bname (getstring T
		     "\nEnter a block name (case-sensitive) <*> : \n"
	  )
  )					; press enter to count all blocks
  (if (eq bname "")
    (setq bname "*")
  )					; change to default block name

  (setq	filt (list (cons 0 "INSERT")
		   (cons 2 bname)
		   (cons 410 (getvar 'ctab))
	     )
  )
  (if (setq ss (ssget "_X" filt))
    (progn
      (setq i -1)
      (repeat (sslength ss)
	(setq en (ssname ss (setq i (1+ i))))
	(setq bnm (vla-get-effectivename (vlax-ename->vla-object en)))
	(setq blk_lst (cons bnm blk_lst))
      )
      (setq com_lst (group-by-first blk_lst))

      (vlax-for	blkdef (vla-get-blocks adoc)
	(foreach record	com_lst
	  (if (eq (vla-get-name blkdef) (car record))
	    (setq com_lst
		   (subst
		     (append record (list (vla-get-comments blkdef)))
		     record
		     com_lst
		   )
	    )
	  )
	)
      )

      (setq data     (append
		       (append (list (list "Layout" (getvar 'ctab))) com_lst)
		     )
	    data     (append data (list (list "Subtotal : " (length blk_lst))))
	    all_data (cons data all_data)
	    blk_lst  nil
	    com_lst  nil
	    data     nil
      )
    )
  )

  ;; Prepare data for LibreOffice
  (setq all_data (apply 'append (reverse all_data)))

  ;; Initialize LibreOffice Writer
  (setq
    aoffice (vlax-get-or-create-object "com.sun.star.ServiceManager")
  )
  (setq	adoc (vlax-invoke-method
	       aoffice
	       'createInstance
	       "com.sun.star.frame.Desktop"
	     )
  )
  (setq	adoc (vlax-invoke-method
	       adoc
	       'loadComponentFromURL
	       (strcat "file:///" adoc_path)
	       "private:factory/swriter"
	       :vlax-false
	       :vlax-false
	       nil
	       nil
	     )
  )
  (setq csht (vlax-get-property adoc 'Sheets))
  (setq sht (vlax-get-property csht 'Item '1))

  ;; Set up header
  (setq header_list '("Name" "Quantity" "Description"))
  (setq cols (length header_list))
  (setq	row 1
	colm 1
  )
  (vlax-put-property
    (vlax-get-property sht 'Text)
    'String
    "Block Count Info"
  )

  ;; Write header to LibreOffice
  (repeat (length header_list)
    (vlax-put-property
      (vlax-get-property sht 'Text)
      'String
      (car header_list)
    )
    (setq colm (1+ colm)
	  header_list
	   (cdr header_list)
    )
  )

  ;; Merge header cells
  (setq	srow "A1"
	scol (strcat (chr (1- (+ (ascii "A") cols))) "1")
  )
  (vlax-invoke-method (vlax-get-property sht 'Range) 'Merge)
  (vlax-put-property
    (vlax-get-property (vlax-get-property sht 'Range) 'Text)
    'HorizontalAlignment
    2
  )					; center alignment

  ;; Fill cells with data
  (setq	row  (1+ row)
	colm 1
  )
  (repeat (length data_list)
    (setq datum (car data_list))
    (repeat (length datum)
      (vlax-put-property
	(vlax-get-property sht 'Text)
	'String
	(car datum)
      )
      (setq datum (cdr datum))
      (setq colm (1+ colm))
    )
    (setq colm 1
	  row  (1+ row)
    )
    (setq data_list (cdr data_list))
  )

  ;; Draw borders and change font
  (setq clls (vlax-get-property (vlax-get-property sht 'Range) 'Cells))
  (setq brds (vlax-get-property clls 'Borders))
  (vlax-put-property brds 'LineStyle 1)
  (setq fnt (vlax-get-property (vlax-get-property sht 'Range) 'Font))
  (vlax-put-property fnt 'Italic 1)
  (vlax-put-property fnt 'Size 10)

  ;; Autofit columns
  (vlax-invoke-method
    (vlax-get-property sht 'Range)
    'Columns
    'AutoFit
  )

  ;; Save LibreOffice document
  (vlax-invoke-method
    adoc
    'StoreToURL
    (strcat "file:///" adoc_path)
    (vlax-make-variant nil)
  )

  ;; Clean up
  (vlax-release-object cll)
  (vlax-release-object rang)
  (vlax-release-object csht)
  (vlax-release-object sht)
  (vlax-release-object nwb)
  (vlax-release-object awb)
  (vlax-release-object aoffice)

  (princ)
)

(prompt
  "\n\t\t***\tBlock count info to LibreOffice program is loaded\t***\n"
)
(prompt "\n\t\t***\tType BCEX_LOC to execute\t***\n")
(princ)

 

 

 

 

 

Posted

Not working on my 2000i at home. ; error: Automation Error. [automation bridge] Too many parameters

 

New version still gets ; error: Automation Error. [automation bridge] Too many parameters after some tweaks.

 

I'll get back on this when I get more time.

  • 5 months later...
Posted (edited)

I have started again with this and started making defuns for each type of task. 

 

 A start,  making defuns rather than just code.

See updated code below

 

Edited by BIGAL
  • Like 2
Posted

Added some more defuns, stuck on get a range of cells can use a double loop read rows and columns but should be able to read a range. Hope to post something soon. 

Posted

Hi @BIGAL
I'm trying to test your functions but I'm not sure how to go about it. I've had some errors while running them, probably my fault.
Can you attach an example of how to get a record and another of how to write it?

Thanks in advance

Posted (edited)

I have updated the code getting a range of cells as a list, here is a how to use.

 

If I have done it correctly, just appload the saved lisp file. This way all the defuns are ready to use. 

 

Ok you need to Open Libre Calc and save a "Blank" ODS file. Also edit this section of code and save the lisp. You should be ready then to start using.

 

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

 

Do (openlibre) it should open Blank or your chosen ODS.

 

PLEASE NOTE A1= 0 0  Excel is 1 1

 

You can get a cell using (libgetcell '(col row))

You can put a cell using (libputcell '(crange) val) crange again '(col row)

You can get a range of cells into a list (libgetrange "(col1 row1 col2 row2)) I am working on that right now, have some help from Tim_n via Bricscad forum.

 

Hope this helps.

; 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 ????



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

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

; 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 by BIGAL
Posted (edited)


I have a feeling this won't work in AutoCAD

Edited by GLAVCVS
Posted (edited)

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

Edited by GLAVCVS

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...