Extracting data from Excel 2010 into CAD using LISP

[vladimir.karatsupa982899]

Hello,

I need some guidance in extracting data from Excel into CAD using LISP. The excel spreadsheet that I have has all the necessary information for me to create folder names in the job directory. I would like to learn how to extract text/data from a specific cell and insert text/data into lisp fuction to create folders.

[LSElite]

Hey mate

I only found this the other day and it seems to have fulfilled all my excel needs.

http://web2.airmail.net/terrycad/AutoLISP-Code.htm

look for "GetExcel.lsp"

Cheers

[fixo]

Upload a small picture of this tata table
to make a sence for me if you want

[vladimir.karatsupa982899]

Thank you for the quick reply,

Attached you will find a picture of an excel spreadsheet example. The text in red is the information I need to extract from the spreadsheet to fill out the attributed titleblock.

[fixo]

Try this lisp to propagte selected range from Excel to AutoCAD,
switch between AutoCAD and Excel using Notify icon ,
This code will propagate list of pairs in the current session
better yet recreate table using two columns:
first one for TAG and the second one for attribute value


(defun C:XLREF (/ fname msg ref_list result sheetnum title x xlapp xlbook xldata xlresult xlreflist xlsheet )
;; *xlresult* is global variable !

;;local defun

;; ;;
;; = Select Range on screen and return Range object = ;;
;; ;;

(defun xlreflist(xlapp msg title / address c1 c2 r1 r2 xlrange)
(vlax-put-property xlapp 'visible :vlax-true)
(vlax-put-property xlapp 'screenupdating :vlax-true)
(vlax-put-property xlapp 'displayalerts :vlax-false)
(if (not (vl-catch-all-error-p
(setq xlrange
(vl-catch-all-apply
(function (lambda ()
(vlax-variant-value
(vlax-invoke-method
(vlax-get-property (vlax-get-property xlapp 'activeworkbook) 'application);redirect reference
'Inputbox
msg
title
nil
nil
nil
nil
nil
8))))))))
(progn
(vlax-put-property xlapp 'displayalerts :vlax-true)
(setq r1 (vlax-get-property xlrange 'row))
(setq c1 (vlax-get-property xlrange 'column))
(setq r2 (vlax-get-property (vlax-get-property xlrange 'rows) 'count))
(setq c2 (vlax-get-property (vlax-get-property xlrange 'columns) 'count))
(setq address (strcat (chr (+ 64 c1))
(itoa r1)
":"
(chr (+ (ascii (chr (+ 64 c1))) (1- c2)))
(itoa (+ r1 (1- r2)))))
(setq xlrange (vlax-get-property (vlax-get-property xlapp 'activesheet) 'range address))
(vlax-invoke xlrange 'select)
(vlax-put-property xlapp 'displayalerts :vlax-true)
)
)
(list xlrange address)
)


;; main program
(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLSX;XLS" 4)
sheetnum 1 ;<-- sheet number (could be used the sheet name instead)
)

(setq xlapp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible xlapp :vlax-true)
;(vlax-put-property xlapp 'DisplayAlerts :vlax-true)
(setq xlbook (vl-catch-all-apply
'vla-open
(list (vlax-get-property xlapp 'WorkBooks) fname)
)
)
(setq xlsheet (vl-catch-all-apply
'vlax-get-property
(list (vlax-get-property xlbook 'Sheets)
'Item
sheetnum ;< --- sheet name or number
)
)
)
(vlax-invoke-method xlsheet 'activate)



(setq ref_list (xlreflist xlapp "Select range:" "Range selection"))

(if (equal 1 (vlax-get-property (car ref_list) 'count))
(setq xlresult (vlax-variant-value
(vlax-get-property (car ref_list) 'value2)
)
)
(progn
(setq xldata (vlax-safearray->list
(vlax-variant-value
(vlax-get-property (car ref_list) 'value2)
)
)
)
(setq
xlresult (mapcar '(lambda (x) (mapcar 'vlax-variant-value x))
xldata
)
)
)
)
(vl-catch-all-apply 'vla-close (list xlbook))
(gc)
(vl-catch-all-apply 'vla-quit (list xlapp))

(mapcar '(lambda(x)(vl-catch-all-apply 'vlax-release-object (list x)))
(list xlsheet xlbook xlapp))
(gc)
(alert (vl-princ-to-string xlresult));for imagination only, might be removed
(if (listp xlresult)
(progn
;; clean result from empty values if these were within the list
(setq *xlresult* (vl-remove-if '(lambda (x)(vl-some 'not x))xlresult))
;; if you have a result as two columns table
;; convert them to list of pairs, e.g. '(TAG . VALUE)
(setq *xlresult* (mapcar '(lambda (x)(apply 'cons x))*xlresult*))


)
(setq *xlresult* result)
)
(vl-propagate '*xlresult*); propagate list to be available in every drawing in the current session
(alert (vl-princ-to-string *xlresult*));to make sure we could be rich at this list
;;_____________________________________________________________________;;
(princ)
)
(prompt
"\n\t\t>>>\tType XLREF select Excel range \t<<<")
(prin1)
(or (vl-load-com)(princ))

;; test: (C:XLREF)

[vladimir.karatsupa982899]

Thank you very much fixo,

I need some time to digest this, I will get back with you shortly...

[ajazraj398398]

Try this lisp to propagte selected range from Excel to AutoCAD,
switch between AutoCAD and Excel using Notify icon ,
This code will propagate list of pairs in the current session
better yet recreate table using two columns:
first one for TAG and the second one for attribute value


(defun C:XLREF (/ fname msg ref_list result sheetnum title x xlapp xlbook xldata xlresult xlreflist xlsheet )
;; *xlresult* is global variable !

;;local defun

;; ;;
;; = Select Range on screen and return Range object = ;;
;; ;;

(defun xlreflist(xlapp msg title / address c1 c2 r1 r2 xlrange)
(vlax-put-property xlapp 'visible :vlax-true)
(vlax-put-property xlapp 'screenupdating :vlax-true)
(vlax-put-property xlapp 'displayalerts :vlax-false)
(if (not (vl-catch-all-error-p
(setq xlrange
(vl-catch-all-apply
(function (lambda ()
(vlax-variant-value
(vlax-invoke-method
(vlax-get-property (vlax-get-property xlapp 'activeworkbook) 'application);redirect reference
'Inputbox
msg
title
nil
nil
nil
nil
nil
8))))))))
(progn
(vlax-put-property xlapp 'displayalerts :vlax-true)
(setq r1 (vlax-get-property xlrange 'row))
(setq c1 (vlax-get-property xlrange 'column))
(setq r2 (vlax-get-property (vlax-get-property xlrange 'rows) 'count))
(setq c2 (vlax-get-property (vlax-get-property xlrange 'columns) 'count))
(setq address (strcat (chr (+ 64 c1))
(itoa r1)
":"
(chr (+ (ascii (chr (+ 64 c1))) (1- c2)))
(itoa (+ r1 (1- r2)))))
(setq xlrange (vlax-get-property (vlax-get-property xlapp 'activesheet) 'range address))
(vlax-invoke xlrange 'select)
(vlax-put-property xlapp 'displayalerts :vlax-true)
)
)
(list xlrange address)
)


;; main program
(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLSX;XLS" 4)
sheetnum 1 ;<-- sheet number (could be used the sheet name instead)
)

(setq xlapp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible xlapp :vlax-true)
;(vlax-put-property xlapp 'DisplayAlerts :vlax-true)
(setq xlbook (vl-catch-all-apply
'vla-open
(list (vlax-get-property xlapp 'WorkBooks) fname)
)
)
(setq xlsheet (vl-catch-all-apply
'vlax-get-property
(list (vlax-get-property xlbook 'Sheets)
'Item
sheetnum ;< --- sheet name or number
)
)
)
(vlax-invoke-method xlsheet 'activate)



(setq ref_list (xlreflist xlapp "Select range:" "Range selection"))

(if (equal 1 (vlax-get-property (car ref_list) 'count))
(setq xlresult (vlax-variant-value
(vlax-get-property (car ref_list) 'value2)
)
)
(progn
(setq xldata (vlax-safearray->list
(vlax-variant-value
(vlax-get-property (car ref_list) 'value2)
)
)
)
(setq
xlresult (mapcar '(lambda (x) (mapcar 'vlax-variant-value x))
xldata
)
)
)
)
(vl-catch-all-apply 'vla-close (list xlbook))
(gc)
(vl-catch-all-apply 'vla-quit (list xlapp))

(mapcar '(lambda(x)(vl-catch-all-apply 'vlax-release-object (list x)))
(list xlsheet xlbook xlapp))
(gc)
(alert (vl-princ-to-string xlresult));for imagination only, might be removed
(if (listp xlresult)
(progn
;; clean result from empty values if these were within the list
(setq *xlresult* (vl-remove-if '(lambda (x)(vl-some 'not x))xlresult))
;; if you have a result as two columns table
;; convert them to list of pairs, e.g. '(TAG . VALUE)
(setq *xlresult* (mapcar '(lambda (x)(apply 'cons x))*xlresult*))


)
(setq *xlresult* result)
)
(vl-propagate '*xlresult*); propagate list to be available in every drawing in the current session
(alert (vl-princ-to-string *xlresult*));to make sure we could be rich at this list
;;_____________________________________________________________________;;
(princ)
)
(prompt
"\n\t\t>>>\tType XLREF select Excel range \t<<<")
(prin1)
(or (vl-load-com)(princ))

;; test: (C:XLREF)

fixo... it shows,AutoCAD variable setting rejected: "ATTREQ" nil, what to do?

[fixo]

I didn't see where in my code I've store / restore
the system variable ATTREQ

[vladimir.karatsupa982899]

Hello Fixo,

I am trying to digest this code but it seems like this is just too hard.

Can you please explain your process in "simpler" terms?

Thank you

[fixo]

Shortly, please open both Excel and AutoCAD,
switch to drawing and run code, let me know if
this will not work, I've tested it in Excel2007 only
Other things just tomorrow only, here is a nap time for me now
Cheers :)

[peter]

Simplifying the process


I would format the text in the excel file like this

JobName, XYZ Job
JobAddress, 1234 Somewhere Street
JobCity, Somecity
JobState, SomeState
JobZip,99999
etc...

You see you can save the excel file into a .csv (comma separated value) format that is an ascii text file that LISP can read very easily.

Below you can see several functions that do this easily.

You can read a csvfile into a list and manipulate it and write it back out.

Just cut and paste these functions into you lisp file.

Call the functions and read the lists...





; Written By: Peter Jamtgaard P.E. copr 2004
; Import a CSV File to a list of Sublists
; Syntax (csvFileToList (findfile "myexcelsheet.csv") ",")


(defun csvFiletoList (strFilename strChar / lstOfSublists strText z)
(setq z (open strFilename "r"))
(while (setq strText (read-line z))

(setq lstOfSublists (cons (mapcar 'isNumber (CSVStringToList strText strChar)) lstOfSublists))
)
(close z)
(reverse lstOfSublists)
)

(defun IsNumber (strItem / blnReturn intChar lstNumbers)
(setq lstNumbers (vl-string->list ",.0123456789"))
(foreach intChar (setq lstItem (vl-string->list strItem))
(if (not (member intChar lstNumbers))
(setq blnReturn T)
)
)
(if (not blnReturn)
(if (or (member 44 lstItem)
(member 46 lstItem)
)
(atof (vl-list->string lstItem))
(atoi (vl-list->string lstItem))
)
(vl-list->string lstItem)
)
)

; Parsing a textstring to a list.

(defun CSVStringToList (strText strChar / intPosition lstStrings)
(while (setq intPosition (vl-string-search strChar strText 0))
(setq lstStrings (cons (substr strText 1 intPosition) lstStrings)
strText (substr strText (+ intPosition 1 (strlen strChar)))
)
)

(reverse (cons strText lstStrings))
)

(defun SubListToCSVString (lstStrings strChar / strPart strReturn )
(setq strReturn (car lstStrings))
(foreach strPart (cdr lstStrings)
(setq strReturn (strcat strReturn strChar strPart))
)
strReturn
)


;Export a list of sublists of strings to a text file


(defun ListTOCSVFile (strFilename lstOfSublists strChar / strText strText2)
(setq z (open strFileName "w"))
(foreach lstSubList lstOfSublists
(setq strText (vl-princ-to-string (nth 0 lstSubList)))
(if (= (type (cdr lstSublist)) 'LIST)
(foreach strText2 (cdr lstSubList)
(setq strText (strcat strText strChar (vl-princ-to-string strText2)))
)
(setq strText (strcat strText strChar (vl-princ-to-string (cdr lstSubList))))
)
(write-line strText z)
)
; (startapp "C:\\Program Files\\Microsoft Office\\Office10\\EXCEL.EXE" strFilename)
(prin1)
)
(vl-load-com)
(princ)

[bhull1985403354]

Do either of these routine have anything to do with block attributes?
I have managed to test fixo's routine and it does group the excel information into an autocad parsed list
but it just alerts with the list information, and gives an error as it does so:


; error: bad argument type: VLA-OBJECT :vlax-false


I'm just wondering how to use this to update block attributes......the XL-GET lisp that was linked to above will update attribute values in blocks within autocad only thing is that it requires the excel column and row to be *named what the block attributes are named* and the only way to do this is to edit the blocks to be A1, B1, etc...not very descriptive tagnames and incidentally requires an overhaul of block attributes to even test this appropriately....but I am still hoping to find a tool that will take information from excel and populate block attributes in autocad.

We would not use CSV's, but, we are *already* using excel which is the reason why I'd want to get working a routine that pulls in info from excel but not from a csv- not having others reinvent their own wheels when they have perfectly good working ones. I'm just trying to maximize their efficiency.....anyone can help in finding this working routine, it'd be much appreciated

[fixo]

Do either of these routine have anything to do with block attributes?
I have managed to test fixo's routine and it does group the excel information into an autocad parsed list
but it just alerts with the list information, and gives an error as it does so:


; error: bad argument type: VLA-OBJECT :vlax-false



Which line of code is throw this error?
Also please show a screenshot of your excel file (1-2 rows)

[bhull1985403354]

The error occurs when the dotted pair list is displayed in the alert.
augis image uploaded sucks and wont take mine but its just 2 colums of text...

[fixo]

I could not see a piccy of your excel file, didn't you want to
worst your time to upload this one, or I need to have
a magic ball to know where is your problem?

[bhull1985403354]

No I'd just expect you to *read* the post I put up heh
Augi's image uploader isn't working for me but i'll try again...
Yeah, just doesn't do anything when I click "upload"

[fixo]

I understand about error message, it's not a problem,
just a different formatting for your current data return,
there is need to fit them accordingly to this data

I'm using Google Chrome to upload pictures and other files,
do you have this one on your machine?
Cheers

[Opie]

Have you tried the advanced editor?

[fixo]

Advanced editor is not working in Yandex for me too,
perhaps problem with anti-virus program I think
I have AVG installed on my end, not sure about
if I'm right though.
Google Chrom is my friend for augi and dwg.ru forums,
in other frorums I haven't have this dillemma

[Opie]

Can you list the steps you are taking to upload an image? Maybe we can figure out what is going wrong from there.

[fixo]

Hmm, now it is working good, sorry
Right from Yandex :)
I'm using Basic Uploader

[virusavio2512]

hi hello DUDE...
plz help me.. lot of confussion fr me. to convert EXCEL DATA content to AUTOCAD data to import such a tabular column manner.

so i look for lisp for this.. i attached my excel content here.96626

[mananiparesh622278]

hello Sir,

thanks for useful lisps from your side. i am beginner in lisp creations, I request your help me to read excel cell value, Cell Color & Cell text color extract from lisp.


thanks