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

[clerigoaaron]

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

[pbejse]

---

@clerigoaaron wrote:

@pbejse yes i do.

---

Only thing I can think of now is the list is not matching with the DXF filenames. the way it works, make a list of the DXF files from the selected folder, read the XLSX file and save the data on a list.

Every drawing name is checked against the Xlsx list. if the name is not found, the program will process the next one

Can you post here a snapshot of the dxf folder? for the meantime i will add a check where the program stops so you it will spit out a message for the error.

Code at post #16 updated

[clerigoaaron]

@pbejse here

 

[pbejse]

And does it show the same name in column A of your excel file?

[clerigoaaron]

@pbejse sorry it was my bad i did not notice that the excel file started at column B. The code worked but for some reason some pipes from other project appeard also the text are so far away even though the code sets it as -30 and -45. Also pipe no. 24 is not appearing

[pbejse · Accepted solution]

---

@clerigoaaron wrote:

i did not notice that the excel file started at column B. The code worked but for some reason some pipes from other project appeard also the text are so far away even though the code sets it as -30 and -45. Also pipe no. 24 is not appearing

---

Did you swap the column A with B as the source of the drawing name?

Now that;s my bad 🙂 [ I noticed it too, now fixed ]

The name of the dxf filename should match the value on the drawing name column.

(Defun c:MakeThisHAppen ( /  _Entnext ExtractFromXls el moveAll fileName XL-App Workbooks exData f textObj moveAll)
(defun _Entnext ( e )
    (if (setq e (entnext e)) (cons e (_Entnext e)))
  )  
(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 (mapcar 'vl-princ-to-string 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
		  (setq el (entlast))
		  (command "_DXFIN" (strcat directory "\\" fname) '(0.0 0.0 0.0))
		  (if (null el)(and (setq el (entnext)) (ssadd el moveAll)))
		  (mapcar '(lambda ( x ) (ssadd x moveAll)) (_Entnext el))		  
		  (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: " (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)
  )

[clerigoaaron]

@pbejse Thank you so much. The code did exactly what I wanted. Im still trying to piece how the code worked. Lets say for example I want to start extracting data from column B row 26 which part of the code is that? Also what does 16 signify in this line?

		(setq fileName (getfiled "Excel Spreadsheet File"
                                   (if fileName fileName (getvar 'dwgprefix))"XLSX;XLS" 16 ))

[Sea-Haven]

Also what does 16 signify in this line?

https://help.autodesk.com/view/ACDLT/2024/ENU/?guid=GUID-AD65DF88-5218-4655-B877-B4D33B9FB6D1

"want to start extracting data from column B row 26", in the line below the 1 signifies start at row 1 & 2 = B1, the ask start at 1 or another row could be added, I am sure Pbe can help.

(setq exData (extractfromxls 1 XL-App 2))

 Just a PS have code for select a range in Excel By Fixo can change the start cell.

Happy to post

[clerigoaaron]

@pbejse @Sea-Haven I tried to do it by looping the first 25 rows so it may start at row 26 but I cant seem to make it work. I am also not sure which part of the code compares the file name to the excel data so that it knows the excel and dxf match. Sorry if the question is basic just started learning this 2 months ago.