Libreoffice Calc link to BricsCAD

Chad Wade

 I have seen a number of add-ons, etc. that will link Excel spreadsheets to BricsCAD. Has anyone had any success in doing this with Libreoffice Calc?

Any thoughts or sources would be greatly appreciated.

Terry Dotson

Coincidentally we're working on something that reads spreadsheets directly, what format do you keep your files in?

Chad Wade

 Terry:

I would like to keep them in the native Libreoffice format, but allow the link so I could update the spreadsheet and have it automatically update the table in BricsCAD.

Roy Klein Gebbinck

@ Chad:
It seems that Libre Office and OpenOffice are very similar and that they share the same API.
A preliminary test shows that a Lisp function created for OpenOffice will also work on Libre Office.
Here is the link to the Lisp function I have tested with:
http://www.theswamp.org/index.php?topic=31362.0

Note:
When I use the Lisp function for the first time there is a timing issue caused by the slow start of LO.

Roy Klein Gebbinck

...
Note 2:
Toward the end of the Lisp code you have to change:
[code](vlax-safearray->list Data)[/code]
Into:
[code](vlax-safearray->list (vlax-variant-value Data))[/code]

Terry Dotson

Looks like the default format is .ODS which we have recently began working with.  There are two approaches, one is to place the data only into a TABLE object (that could come quickly) and a second approach would include all linework, graphics in a block for a more accurate representation.  This second approach would take more time.  Feel free to email me examples of your spreadsheets.

Chad Wade

 Roy:

I copied the text and saved it as a .lsp. I am not near a CAD workstation and will test it out later, hopefully tonight. Not being very savvy with code and never really using the benefit id lisp routines, what is the command prompt to initiate the lisp.

Thanks again.

Roy Klein Gebbinck

Chad, to test the code you have to create a file C:\Temp\Book1.ods and on the first sheet (partially) fill the first 11x11 cell range. Loading the Lisp will automatically start the function. Note: the Lisp file only reads from the spreadsheet it does not create or fill a table.

Bruce Fillmore

@Roy Klein Gebbinck:

Chad, to test the code you have to create a file C:\Temp\Book1.ods and on the first sheet (partially) fill the first 11x11 cell range. Loading the Lisp will automatically start the function. Note: the Lisp file only reads from the spreadsheet it does not create or fill a table.

Chad,

You cvan just drag and drop the lisp file right into an open Bricscad Drawing and the Lisp file will load.  (Fastest way to do it).

Roy Klein Gebbinck

Regarding the timing issue I have mentioned earlier:

If I restart my computer and a run a .vbs file with this code:
[code]Set objServiceManager= WScript.CreateObject("com.sun.star.ServiceManager")[/code]
There is a considerable delay, which is understandable because the office application has to start up.

If I restart my computer, start BricsCAD and run this Lisp code:
[code](setq objServiceManager (vlax-create-object "com.sun.star.ServiceManager"))[/code]
Again there is a considerable delay, but, and this is a bigger problem, a component busy dialog is also displayed.
IMO this dialog is a bit of a show-stopper as it will confuse most users.

My question is: Why is this dialog displayed when I use the Lisp code and not when I use the .vbs code?

Torsten Moses

Dear Roy,

I just tried
(setq objServiceManager (vlax-create-object "com.sun.star.ServiceManager"))
and I do not get that message box ... LibreOffice is active (I see it in TaskManager), when closing BricsCAD the LibreOffice core remains in memory
(also compared with AutoCAD, same behaviour there, so I suspect, the LibreOffice core does not properly unload itself ...)

I', running Win 7 x64, LibreOffice 4.1.1.2 ...
Many greetings !

Roy Klein Gebbinck

@Torsten:
Thank you for giving this a try.

I have done some more testing with the Writer component of OpenOffice this time. But, as mentioned earlier, OpenOffice and LibreOffice are very similar regarding this type of automation. The testing was done with a more extended .vbs file and a Lisp "Port" of that file.

In my tests running the Lisp file caused a 'component is busy' dialog to appear at least once.
When I tested with the .vbs file this dialog never appeared.

My conclusion: BricsCAD appears to have a problem with the 'ActiveX delay'?

I am attaching a .zip file with the mentioned files and a text file with a description of my tests.

OpenOfficeWriterTest.zip

Kra Wk

 Hello,

I installed latest v15 trial version. Bricscad still doesn't support inserting tables from libreoffice . When this feature will be added? 

We want to switch from MS office to LibreOffice.

What formulas bricscad tables support? If you add "sumifs" maybe we can use bricscad without inserting tables from office.

Brian Marr

I am sure many Linux fans would like to make use of this feature as well.

Hans De Backer

The 'From Data' option on the Insert Table dialog allows to directly create tables from spreadsheets saved in 'Excel 2003 XML' format, also known as 'xmlss'.
Unfortunately 'Save As Excel 2003 XML' is, both in LibreOffice and OpenOffice, very fragile. E.g. it suffices that the spreadsheet contains 3 consecutive empty rows to trigger an error message that the file could not be created.

Roy Klein Gebbinck

I have come up with a solution for the 'ActiveX delay' (see posts #10 and #12).
The trick is to use a vbs script to start LibreOffice (or OpenOffice.org) and also open the (new) document from the script. The script is started using the vle-startapp Lisp function. The vbs script has no problem with the 'ActiveX delay' and the vle-startapp correctly waits for the script to finish.

Content of the script (OfficeStartDoc.vbs):
[code]' This script must be started with an argument. Examples:
'   "private:factory/swriter"
'   "private:factory/scalc"
'   "file:///C:/Name%20With%20Spaces.ods"
'   "file:///C:/Test%20Folder/Another%20File.odt"
Set objService = WScript.CreateObject("com.sun.star.ServiceManager")
Set objDesktop = objService.CreateInstance("com.sun.star.frame.Desktop")
Dim args()
Set objComponent = objDesktop.LoadComponentFromURL(WScript.Arguments(0), "_blank", 0, args)[/code]

The Lisp code:
[code]; New Writer file:
;   (OfficeStartDoc (findfile "OfficeStartDoc.vbs") "private:factory/swriter")
; New Calc file:
;   (OfficeStartDoc (findfile "OfficeStartDoc.vbs") "private:factory/scalc")
; Open existing file:
;   (OfficeStartDoc (findfile "OfficeStartDoc.vbs") "file:///C:/Name%20With%20Spaces.ods")
;   (OfficeStartDoc (findfile "OfficeStartDoc.vbs") "file:///C:/Test%20Folder/Another%20File.odt")
(defun OfficeStartDoc (vbs arg / objService objDesktop objComponent url)
  (setq url (if (wcmatch (strcase arg) "PRIVATE:FACTORY*") "" arg))
  (vl-catch-all-apply
    (function
      (lambda ()
        (vle-startapp "wscript.exe" (strcat "\"" vbs "\" " arg) T)
        (setq objService (vlax-create-object "com.sun.star.ServiceManager"))
        (setq objDesktop (vlax-invoke objService 'CreateInstance "com.sun.star.frame.Desktop"))
        (setq objComponent
          (if
            (and
              (setq objComponent (vlax-get objDesktop 'CurrentComponent))
              (= (strcase url) (strcase (vlax-get objComponent 'URL)))
            )
            objComponent
          )
        )
      )
    )
  )
  (if objService (vlax-release-object objService))
  (if objDesktop (vlax-release-object objDesktop))
  objComponent
)[/code]