Lisp to extract excel data into text to be inserted to autocad
@clerigoaaron wrote:
I was wondering if there is a lisp code to extract the names in excel and place them in the drawing.
Yes
@clerigoaaron wrote:
Like the content of cell A1-A20 will be inserted as text at points 0,0 then the next one will be placed at increments of 200mm down. A2 will be inserted at 0,-200 A3 at 0,400 etc.
What are the properties of the TEXT entity to be inserted/created at 0,0?
Best that you post a sample drawing showing the intended result.
HTH
@clerigoaaron wrote:
@pbejse Imagine a rectangle and inside the rectangle says drawing no. (Insert drawing number here from excel). the text property will be the same as the property of "drawing no."
And how would the code know what object property of the "drawing no." ? is this a TEXT entity? an Attribute?
👆
Ok a couple of suggestions can get a selection from excel. Either read all cells or select range, then make the list of file names and dxfin, make a string of Filename & Text then just make a mtext with that string.
For me I would add cell C1 as C:\Users\Desktop\dxf so know where to find the dxf's.
Limited testing please try this.
; https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/lisp-to-extract-excel-data-into-text-to-be-inserted-to-autocad/td-p/12883210
; read a excel and import dxf plus text
; By AlanH July 2024
(defun c:xldxf ( / pt1 st end row str path)
;; Thanks to fixo ;;
(defun getcell2 (row column / )
(setq cells (vlax-get-property (vlax-get-property myxl "ActiveSheet") "Cells"))
(setq cell (vlax-get (vlax-variant-value (vlax-get-property cells "Item" row column)) 'value))
)
; thanks to Lee-mac for this defun
; www.lee-mac.com
; 44 is comma 9 is tab 34 is space 58 is colon
(defun _csv->lst58 ( str / pos )
(if (setq pos (vl-string-position 58 str))
(cons (substr str 1 pos) (_csv->lst58 (substr str (+ pos 2))))
(list str)
)
)
; ColumnRow - Returns a list of the Column and Row number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
; Cell$ = Cell ID
; Syntax example: (ColumnRow "ABC987") = '(731 987)
;default to "A1" if there's a problem
;-------------------------------------------------------------------------------
(defun ColumnRow (Cell$ / Column$ Char$ Row#)
(setq Column$ "")
(while (< 64 (ascii (setq Char$ (strcase (substr Cell$ 1 1)))) 91)
(setq Column$ (strcat Column$ Char$)
Cell$ (substr Cell$ 2)
)
)
(if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
(list (Alpha2Number Column$) Row#)
'(1 1)
)
)
; Alpha2Number - Converts Alpha string into Number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
; Str$ = String to convert
; Syntax example: (Alpha2Number "ABC") = 731
;-------------------------------------------------------------------------------
(defun Alpha2Number (Str$ / Num#)
(if (= 0 (setq Num# (strlen Str$)))
0
(+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#)))
(Alpha2Number (substr Str$ 2))
)
)
)
(defun getrangexl ( / lst UR CR RADD cnt)
(setq lst '())
(setq UR (vlax-get-property (vlax-get-property myxl "ActiveSheet") "UsedRange"))
(setq CR (vlax-get-property UR "CurrentRegion"))
(setq RADD (vlax-get-property CR "Address"))
(setq cnt (vlax-get-property CR "Count"))
(setq lst (_csv->lst58 radd))
(setq st (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 0 lst) )))
(setq end (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 1 lst) )))
(setq st (columnrow st))
(setq end (columnrow end))
)
;;;;;;;;;;;;;;;;;;;; starts here
(or (setq myxl (vlax-get-object "Excel.Application"))
(setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)
(getrangexl)
(setq path (getcell2 1 3))
(setq row 2)
(repeat (- (cadr end) 1)
(setq dwg (strcat path (getcell2 row 1) ".dxf"))
(command "_.-insert" dwg "0,0,0" 1 1 0)
(setq row (+ row 1))
)
(setq row 2)
(setq str "")
(repeat (- (cadr end) 1)
(setq c2 (getcell2 row 2))
(if (or (= (TYPE c2) 'REAL)(= (TYPE c2) 'INT)) (setq c2 (RTOS c2 2 0)))
(setq str (strcat str (getcell2 row 1) (chr 9) c2 "\\P"))
(setq row (+ row 1))
)
(setq pt1 (getpoint "\nPick point for mtext "))
(entmakex (list (cons 0 "MTEXT")
(cons 100 "AcDbEntity")
(cons 100 "AcDbMText")
(cons 10 pt1)
(cons 1 str))
)
(if (not (vlax-object-released-p myXL))(progn(vlax-release-object myXL)(setq myXL nil)))
(princ)
)
(c:xldxf)
@clerigoaaron wrote:
@pbejse This is the code I am working with
(command "_.text" "0,0" "12" "0" "proj-123")
What of the project number source? I'm sure its not always "PRJ-123" ? A project folder would be a good source. Let us know what you think of that approach.
@clerigoaaron wrote:
@pbejse Yes it is not always PRJ123 but project codes are assigned per project based on when we receive the order so i just edit that part of the code every time a new project comes. There is no way to know the project code before hand.
You can however add that data as the first item on your xlsx file.
Project name | Project-123
Drawing No. | Pipe No.
01 | 12L
Or you can be prompted for the project name and dxf folder
command: Enter Project Name:
Command: Select DXF files source
for now try this
(Defun c:MakeThisHAppen ( / ExtractFromXls moveAll fileName XL-App Workbooks exData f textObj moveAll)
(defun ExtractFromXls (st app col / ncol val dataCollection)
(repeat col
(setq ncol (cons col ncol))
(setq col (1- col))
)
(while
(vl-every 'eval
(setq val
(mapcar '(lambda (i / cv)
(if (Setq cv (vlax-get-property (vlax-get (vlax-get app "ActiveSheet") 'Cells) 'Item st i))
(vlax-variant-value
(vlax-get-property
(vlax-variant-value cv)
'Value
)
)
)
) ncol)
)
)
(setq dataCollection (cons val dataCollection))
(setq st (1+ st))
)
dataCollection
)
(if (and
(setq Directory (acet-ui-pickdir
"Select Project Folder" (if Directory Directory (getvar 'dwgprefix))))
(setq moveAll (ssadd)
dxfFiles (vl-directory-files directory "*.dxf" 1))
(setq fileName (getfiled "Excel Spreadsheet File"
(if fileName fileName (getvar 'dwgprefix))"XLSX;XLS" 16 ))
(setq XL-App (vlax-get-or-create-object "Excel.Application"))
(Setq Workbooks (vlax-invoke-method (vlax-get-property XL-App 'WorkBooks)
'Open fileName ))
(setq exData (extractfromxls 1 XL-App 2))
)
(progn
(foreach fname dxfFiles
(if (Setq f (Assoc (strcase (vl-filename-base fname)) exData))
(progn
(command "_insert" (strcat directory "\\" fname) '(0.0 0.0 0.0) "" "" "")
(ssadd (entlast) moveAll)
(foreach strdata (list
'((0.0 0.0 0.0) 12 "Proj-123")
(list '(0.0 -30.00 0.0) 12
(strcat "dwg no: " (vl-filename-base fname)
)
)
(list '(0.0 -45.0 0.0) 12 (strcat "pipe no: " (vl-princ-to-string (Cadr f)))
)
)
(setq textObj (entmakex (list (cons 0 "TEXT")
(cons 10 (car strdata))
(cons 40 (cadr strdata))
(cons 1 (caddr strdata))
)
)
)
(ssadd textObj moveAll)
(command "_move" moveAll "" "_non" "0,0,0" "0,500,0")
)
)
(princ (Strcat "\n" (vl-filename-base fname) " Not found on source list"))
)
)
(vlax-invoke-method Workbooks 'close fileName )
(vlax-release-object XL-App)
(vlax-release-object Workbooks)
)
(Vl-some '(lambda (l)
(if (null (eval (Car l)))(print (cadr l))))
'((Directory "No folder selected/Cancelled by user")
(dxfFiles "No DXF files found on selected folder")
(fileName "No xls:x;sx file found on selected folder / Cancelled by user")
(XL-App "Excel application not found")
)
)
)
(princ)
)
HTH
EDIT: Added message for "Nothing happened!, What's going on here? "
@pbejse Tried the code it first asked me to select a folder which i think it means where the dxf files are saved then asked me to select an excel file. After doing that noting happened. Both the dxf files and excel are in the same folder. Using autocad 2024. Here is the actual result in the command prompt
@clerigoaaron wrote:
After doing that noting happened. Both the dxf files and excel are in the same folder
Quick question: Do you have Microsoft Excel on your workstation?
