MAKING LISP THAT WILL USE DATA FROM MS EXCEL FILE TO MAKE AUTOCAD DRAWING

[miroslav.pristov]

Hello all

First off all i am amateur in making lisp commands. I was making only some easy ones before.

Last few months i was making some excel tables for work, from which i use data later for making some drawings. I realized that I was losing too much time in making drawing, and that it would be much faster to automatize my work using lisp command which will somehow get data from predefined Excel table to make a drawing.

I never do this before and i even don't know if this is possible at all. So for the beginning i need answer if this is possible and latter if it is, i will give some easy example for someone ready to help me and make me lisp to understand the link between two programs

Thank You very much for your time

[hak_vz]

Yes, it is possible to use excel sheet or CSV to create Autocad drawings. Attach excel data sheet and sample drawing. Elaborate in more details what you want to achieve. There are many lisp codes already written and posted to this forum so you can try to search it and see what my work for you.

[devitg]

@miroslav.pristov 

It is possible, please , upload your xls and some work done at the dwg with the xls data , by reading it on sight  , a few entities will  last  

[Sea-Haven]

Just a throw the baby out with the bath water, you can get excel to draw objects in CAD you make a excel VBA macro, it has a series of functions, draw line, circle, arc, pline and so on. 

Have a play with this a simple start. 

But I found reading excel direct easier from CAD.

[miroslav.pristov]

Hello all and thanks for replying.

I didn't have too much free time for the holidays to present my problem but now I'm presenting to you.

in attacment there is 3 files

Excel file GROUND 1 with required data for drawing

DWG file EXPLANATION where i give detailed explanation what do I need to be on a drawing

DWG file TEMPLATE where you can try the result of your help

In the description in dwg explanation I tried to explain the procedure how i will manually draw a drawing, if you have other quick-drawing suggestions for lisp you can definitely change it.

Actually as i said before i am amateur in making lisp commands. I was making only some easy ones before. I can do something, but not everything what i explained in file EXPLANATION. 

Also, I was losing too much time in making this kind of drawings, and it would be very nice if someone help me to make this lisp command. I would be very grateful if you helped me make this lisp

Thank You very much for your time

[Sea-Haven · Accepted solution]

This is 1st step reads all the A & B values into lisp variables. The multi radios is used to check if you have multiple sheets, that is another bit of code say select another sheet and do again. Just accept Sheet1 if only sheet. Make sure Multi radios buttons.lsp is in a support directory so can be found else edit load to point to it.

; read values from excel sheet by AlanH Feb 2023
;https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/making-lisp-that-will-use-data-from-ms-excel-file-to-make/td-p/11611519

(defun rtd (a)
(/ (* a 180.0) pi)
)

(defun isopenexcel ( / ans lst)
(alert "Close excel if open already then press ok ")
(setq fname (getfiled "Select a Excel File" "c:/" "xl*" 8))
(setq myxl (vlax-get-object "Excel.Application"))
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)
(setq myBook (vl-catch-all-apply 'vla-open (list (vlax-get-property myXL "WorkBooks") fName)))
(setq  xlSheets (vlax-get-property myBook "Sheets"))
(setq xlCount (vlax-get-property xlSheets 'Count))
(setq xlst '())
(vlax-for Worksheet xlsheets
      (setq xlst (cons (vlax-get-property Worksheet "Name") xlst))
)
(setq xlst (reverse xlst))
(setq xlst (cons "Please choose sheet" xlst))
(if (not AH:Butts)(load "Multi Radio buttons.lsp"))
(ah:butts 1 "V"  xlst)
(setq x 1)
(vlax-for Worksheet xlsheets
(if (= but x)
        (vlax-invoke-method Worksheet "Activate")
)
(setq x (1+ x))
)
(princ)
)

(defun getcell (cellname / )
(setq myRange (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" cellname))
(princ  (vlax-variant-value (vlax-get-property myRange 'Value2)))
)

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


(defun c:miro ( / oldsnap x cell ck obj1 obj2 obj3 obj4 p1 p2 p3 p4)

(setq oldsnap (getvar 'osmode))
(setvar 'osmode 0)

(isopenexcel)
 
(setq x 0)
(repeat 6
(setq cell (strcat "A" (rtos (setq x (1+ x)) 2 0)))
(set (read (eval cell)) (getcell cell))
)
(setq x 0)
(repeat 6
(setq cell (strcat "B" (rtos (setq x (1+ x)) 2 0)))
(set (read (eval cell)) (getcell cell))
)
; 3
(setq ck (getpoint "\nPick point for CK "))
(setq ll  (mapcar '+ ck (list -4500 -4000 0.0)))
(setq ur (mapcar '+ ck (list 4500 2000 0.0)))
; 4
(command "rectang" ll ur)
(setq obj1 (vlax-ename->vla-object  (entlast)))
; 5
(setq p1  (mapcar '+ ck (list 0 2000 0.0)))
(setq p2  (mapcar '+ ck (list 0 -4000 0.0)))
(command "line" p1 p2 "")
; 6
(setq p1  (mapcar '+ ck (list 4500 0.0 0.0)))
(setq p2  (mapcar '+ ck (list -4500 0.0 0.0)))
(command "line" p1 p2 "")
; 7
; 8 9 10
(setq p1 (mapcar '+ ck (list A3 A2 0.0))
p2 (mapcar '+ ck (list A5 A4 0.0))
p3 (mapcar '+ ck (list B3 B2 0.0))
p4 (mapcar '+ ck (list B5 B4 0.0))
)
(command "point" p1)
(command "point" p2)
(command "point" p3)
(command "point" p4)

(setq pt (mapcar '+ ck (list 0.0 2500 0.0)))
(command "text" "J" "MC" pt 150 0.0 (strcat "SECTION " a1 "-" b1))
(setq pt (mapcar '+ ck (list 4000 25 0.0)))
(command "text" pt 150 0.0 "±0.00")
(setq pt (mapcar '+ ck (list -50 1550 0.0)))
(command "text" pt 150 90.0 "AXIS")
(setq pt (mapcar '+ ck (list -275 25.0 0.0)))
(command "text" pt 150 0.0 "CK")

(command "xline" p1 p2 "")
(setq obj2 (vlax-ename->vla-object  (entlast)))
(setq pts (vlax-invoke obj2 'intersectWith obj1 acExtendnone))
(vla-delete obj2)
(command "line" p2 (list (nth 3 pts)(nth 4 pts)(nth 5 pts)) "")
(setq obj2 (vlax-ename->vla-object  (entlast)))

(command "xline" p3 p4 "")
(setq obj3 (vlax-ename->vla-object  (entlast)))
(setq pts (vlax-invoke obj3 'intersectWith obj1 acExtendnone))
(vla-delete obj3)
(command "line" p4 (list (nth 3 pts)(nth 4 pts)(nth 5 pts)) "")
(setq obj3 (vlax-ename->vla-object  (entlast)))

(command "xline" 
(mapcar '+ ck (list -4500 B6 0.0))
(mapcar '+ ck (list 4500 B6 0.0))
"")
(setq obj4 (vlax-ename->vla-object  (entlast)))
(setq p6 (vlax-invoke obj4 'intersectWith obj3 acExtendnone))
(vla-delete obj4)

(command "xline" 
(mapcar '+ ck (list -4500 A6 0.0))
(mapcar '+ ck (list 4500 A6 0.0))
"")
(setq obj4 (vlax-ename->vla-object  (entlast)))
(setq p7 (vlax-invoke obj4 'intersectWith obj2 acExtendnone))
(vla-delete obj4)

(command "xline" p6 p7 "")
(setq obj4 (vlax-ename->vla-object  (entlast)))
(setq pts (vlax-invoke obj4 'intersectWith obj1 acExtendnone))
(vla-delete obj4)

(setq p1 (list (nth 0 pts)(nth 1 pts)(nth 2 pts)) p2 (list (nth 3 pts)(nth 4 pts)(nth 5 pts)))
(command "line" p1 p2 "")
(setq ang (angle p1 p2))
(setq dist 800)
(setq pt (polar p1 ang dist))
(command "text" pt 150 (rtd (+ Pi ang)) "GROUND")

(setvar 'osmode oldsnap)

(princ)
)

Next step is to draw objects on correct layer that is your homework, (setvar 'clayer "defpoints") 

[Sea-Haven]

HELLO is any one home ?

[miroslav.pristov]

yes it do what i want. sorry i didnt answer before i was on vacation and didnt chack my mails.