topic Re: Lisp to extract excel data into text to be inserted to autocad in Visual LISP, AutoLISP and General Customization Forum

Lisp to extract excel data into text to be inserted to autocad

clerigoaaron — Mon, 08 Jul 2024 02:15:38 GMT

I have a dxf file with multiple drawings and the drawing names are in an excel spreadsheet. These drawings are in the 20s-100s so manually using the text command will take time. I was wondering if there is a lisp code to extract the names in excel and place them in the drawing. 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. Also wondering if this will require autocad add ins. Thank you

Re: Lisp to extract excel data into text to be inserted to autocad

pbejse — Mon, 08 Jul 2024 05:04:05 GMT

@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

Re: Lisp to extract excel data into text to be inserted to autocad

clerigoaaron — Mon, 08 Jul 2024 05:37:22 GMT

@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."

Re: Lisp to extract excel data into text to be inserted to autocad

pbejse — Mon, 08 Jul 2024 08:45:45 GMT

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

👆

Re: Lisp to extract excel data into text to be inserted to autocad

Sea-Haven — Tue, 09 Jul 2024 00:25:40 GMT

Need a Excel sample also that matches a dwg.

Re: Lisp to extract excel data into text to be inserted to autocad

clerigoaaron — Tue, 09 Jul 2024 12:05:12 GMT

@pbejse @Sea-Haven This is the code I am working with

(defun c:Insertdxf (/directory files)

(setq directory "C:\\Users\\Desktop\\dxf")

(setq doc (vla-get-ActiveDocument (vlax-get-acad-object)))

(if (setq files (vl-directory-files directory "*.dxf" 1))

(foreach dxf files

(setq dxf_file_name (vl-filename-base dxf))

(command "_.-insert"

(strcat directory "\\" dxf)

'(0.0.0.)

)

(command "_.text" "0,0" "12" "0" "proj-123")

(command "_.text" "0,-30" "12" "0" (strcat "dwg no: " dxf_file_name))

(command "_.text" "0,-45" "12" "0" "pipe no:" *insert pipe number from excel)

(command "move" "all" "" "0,0,0" "0,500,0")

)

(princ "\n No DXF files found in that folder!")

)

(princ)

)

This code merges multiple dxf files into 1. the number of files sometimes goes to 50 - 100 which is very tiring to do manually. The code works and I want to improve on it. The excel file is just a regular file with column A as the dwg no with column B as the corresponding pipe no. I would also wish to change the line that takes the file name as string and places it as text next to the "dwg no:" since that information is also in the excel file.

Thank you.

If my explanation is too vague i can clarify. sorry I have little knowledge in lisp.

Re: Lisp to extract excel data into text to be inserted to autocad

Moshe-A — Tue, 09 Jul 2024 13:17:27 GMT

@clerigoaaron hi,

You're been asking to post a sample xlsx file at least two times.

if you do not have one, then create. experts here are trying to understand how this file looks like?

Moshe

Re: Lisp to extract excel data into text to be inserted to autocad

clerigoaaron — Tue, 09 Jul 2024 13:42:35 GMT

Here is a sample of the excel file. The lisp code also inserts the dxf files based on the file name which are named numerically based on the excel file

Re: Lisp to extract excel data into text to be inserted to autocad

Sea-Haven — Mon, 15 Jul 2024 02:19:42 GMT

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)

Re: Lisp to extract excel data into text to be inserted to autocad

clerigoaaron — Wed, 10 Jul 2024 11:12:46 GMT

@Sea-Haven Thanks for the reply. Im a complete newbie and dont know how to use this. Is this all a single lisp file? or are these separate? like each defun is a different lisp file and you gave me alot to try? Also how will the code know which excel file to get the data from. Thank you.

Re: Lisp to extract excel data into text to be inserted to autocad

Sea-Haven — Thu, 11 Jul 2024 00:08:28 GMT

Ok 1st step copy all the code and save in Notepad a file say xldxf.lsp.

Open the correct excel file. I can add select a file for moment looks at current Excel.

Use appload and load the file.

Type xldxf it should all happen.

Re: Lisp to extract excel data into text to be inserted to autocad

clerigoaaron — Sun, 14 Jul 2024 08:28:19 GMT

@Sea-Haven I tried the code and it says error: bad argument type: numberp: nil. I am using autocad 2024 for this. Is there a need for some add-ins for this to work?

Re: Lisp to extract excel data into text to be inserted to autocad

Sea-Haven — Mon, 15 Jul 2024 02:19:05 GMT

I have retested and found that some how a line was deleted in the code so I have updated the code above.

Re: Lisp to extract excel data into text to be inserted to autocad

pbejse — Mon, 15 Jul 2024 04:50:10 GMT

@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.

Re: Lisp to extract excel data into text to be inserted to autocad

clerigoaaron — Mon, 15 Jul 2024 09:23:42 GMT

@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.

Re: Lisp to extract excel data into text to be inserted to autocad

pbejse — Tue, 16 Jul 2024 08:55:05 GMT

@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? "

Re: Lisp to extract excel data into text to be inserted to autocad

clerigoaaron — Mon, 15 Jul 2024 10:25:50 GMT

@pbejse Thats actually a good idea. I will try it tomorrow, I only have autocad in my work PC. Thank you so much.

Re: Lisp to extract excel data into text to be inserted to autocad

clerigoaaron — Tue, 16 Jul 2024 03:42:12 GMT

@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

Re: Lisp to extract excel data into text to be inserted to autocad

pbejse — Tue, 16 Jul 2024 04:24:24 GMT

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

Re: Lisp to extract excel data into text to be inserted to autocad

clerigoaaron — Tue, 16 Jul 2024 05:24:07 GMT

@pbejse yes i do.