Visual LISP, AutoLISP and General Customization
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Read Excel Sheet column

9 REPLIES 9
SOLVED
Reply
Message 1 of 10
Dpipke
3511 Views, 9 Replies

Read Excel Sheet column

Hi All,

I'm trying to get an excel sheet to open and count cells based on the text inside a selected column then compare it to cad.

the column says "red" or "green" and I want to compare it to a count of red and green hatches in cad.

I managed to get them to be counted in cad but I'm not sure how to get the excel part to work


Note: I posted an earlier post thinking to count the actauly colour in the excel cells rather than the words but that ended up being quite complicated.

Any help would be greatly appreciated

9 REPLIES 9
Message 2 of 10
Hallex
in reply to: Dpipke

Post a screenshot of your excel file,
without of seeing this one, anything is just a wild guess
_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Message 3 of 10
Dpipke
in reply to: Dpipke

Screen shot attached. The column I'm looking to read/Count is IOL-AB-SO-PHC

Message 4 of 10
Hallex
in reply to: Dpipke

Try this code, make sure you renamed Excel sheet name and column address

within the code

(defun C:XLCR (/ *error* GetXlRangeByMatch address filename sheetname x xlapp xlbook xldata xlrange)
(vl-load-com)
   (defun *error* (msg)    
    (if xlbook 
(vl-catch-all-apply 'vlax-invoke-method (list xlbook 'Close :vlax-false)))
    (gc)
    (if xlapp (vl-catch-all-apply 'vlax-invoke-method (list xlapp 'Quit)))
    (gc)
    (cond ((not msg))
          ((member msg '("Function cancelled" "quit / exit abort")))
          ((princ (strcat "\n** Error: " msg " ** ")))) 
    (princ)
)                                                                 

(defun GetXlRangeByAddress(filename sheetname address / xlbook xlrange xlsheet xlapp skv_records)
  
    (setq xlapp(vlax-get-or-create-object "Excel.Application"))
    
(vla-put-visible xlapp :vlax-true)
    (vlax-put-property xlapp 'DisplayAlerts :vlax-false)
  (if (zerop 
(vlax-get-property(vlax-get-property xlapp 'workbooks) 'count))
     (setq xlbook (vl-catch-all-apply 'vla-open
     (list (vlax-get-property xlapp 'WorkBooks) fileName))
      
)
  (setq xlbook (vl-catch-all-apply 'vlax-get-property (list xlapp 'activeworkbook))))
  ;;__________________________________________________________________________________;;
(if (numberp sheetname)
(setq xlsheet (vl-catch-all-apply 'vlax-get-property  (list (vlax-get-property xlbook 'Sheets)
    'Item
   sheetname ;|"Sheet1" or 1 maybe|;;< --- sheet name or number
   ) 
)
     )
 (progn
 (vlax-for sht (vlax-get-property xlbook 'Sheets)
   (if (eq (vlax-get-property sht 'Name) sheetname)
     (setq xlsheet sht)))))
;;_____________________________________________________________________________________;;
    (vlax-invoke-method xlsheet 'Activate)
    (setq xlrange (vlax-get-property (vlax-get-property xlsheet 'Cells) 'Range address))
    (if (eq :vlax-true (vlax-variant-value(vlax-get-property xlrange 'HasFormula)))
     (setq xldata (vlax-get-property xlrange 'formulalocal))
     (setq xldata(vlax-get-property xlrange 'value2)))

 
  (setq xldata (mapcar '(lambda(x)
			  (mapcar 'vlax-variant-value x))
(vlax-safearray->list
  (vlax-variant-value  xldata)))
	)
(if (eq 1(length (car xldata)))
	   (setq xldata (vl-remove-if 'not(mapcar 'car xldata)))
  (setq xldata (vl-remove-if 'not  xldata)))
 

   (vl-catch-all-apply 'vlax-invoke-method (list xlbook 'close :vlax-false))
  (vlax-put-property xlapp 'DisplayAlerts :vlax-true)
  (gc);; before QUIT
    
(vl-catch-all-apply 'vlax-invoke-method (list xlapp 'quit))
  (mapcar '(lambda(x)(if (and x (not (vlax-object-released-p x)))        
(progn(vlax-release-object x)(setq x nil))))
   (list xlrange xlsheet xlbook xlapp))  
  (gc);; after QUIT

      xldata
  )

 
  ;;___________________________  main part  _______________________;;

 
  ;; e.g.: 
  (setq filename (getfiled "Select Excel file : "
				"Dpipke"; (getvar "dwgprefix")
				 "xlsx;xls"
				 4
		       )
 sheetname "Sheet1"
 address "B1:B1000");<-- also you're could to put exact address , say B10:B23 etc
  (setq xlData (GetXlRangeByAddress filename sheetname address))
  (alert (strcat "Return column data&colon;\n\n"(vl-princ-to-string xlData) "\n\nrest your mojo in acad is goes here."))
  
  ;;;   ---   rest your mojo with acad is goes here   ---   ;;;

  (*error* nil)
  (princ)
  )
(princ 
"\n\t\t---\tStart command with XLCR\t---")
(prin1)
(or 
(vl-load-com)(princ))

 ;;; If you need some more help with this  so let me know about

_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Message 5 of 10
Dpipke
in reply to: Hallex

Thanks for the help. This is a little above me so I'm not sure exactly whats going on. Is it putting the column data into a string? I know when I run it it returns an alert with the data content of the cells. Does Lisp have an equivalent of a string count function? So count the number of time "red" and "green" appears in the string then store it into variables?

Message 6 of 10
Hallex
in reply to: Dpipke

Then you have to grouping the result with counts,
I will be back later
_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Message 7 of 10
Hallex
in reply to: Dpipke

I've added function to count elements in column,

Do your rest work by yourself:

(defun C:XLCR (/ *error* GetXlRangeByMatch address filename gruppen sheetname x xlapp xlbook xldata xlrange)
(vl-load-com)
   (defun *error* (msg)    
    (if xlbook 
(vl-catch-all-apply 'vlax-invoke-method (list xlbook 'Close :vlax-false)))
    (gc)
    (if xlapp (vl-catch-all-apply 'vlax-invoke-method (list xlapp 'Quit)))
    (gc)
    (cond ((not msg))
          ((member msg '("Function cancelled" "quit / exit abort")))
          ((princ (strcat "\n** Error: " msg " ** ")))) 
    (princ)
)                                                                 

(defun GetXlRangeByAddress(filename sheetname address / xlbook xlrange xlsheet xlapp skv_records)
  
    (setq xlapp(vlax-get-or-create-object "Excel.Application"))
    
(vla-put-visible xlapp :vlax-true)
    (vlax-put-property xlapp 'DisplayAlerts :vlax-false)
  (if (zerop 
(vlax-get-property(vlax-get-property xlapp 'workbooks) 'count))
     (setq xlbook (vl-catch-all-apply 'vla-open
     (list (vlax-get-property xlapp 'WorkBooks) fileName))
      
)
  (setq xlbook (vl-catch-all-apply 'vlax-get-property (list xlapp 'activeworkbook))))
  ;;__________________________________________________________________________________;;
(if (numberp sheetname)
(setq xlsheet (vl-catch-all-apply 'vlax-get-property  (list (vlax-get-property xlbook 'Sheets)
    'Item
   sheetname ;|"Sheet1" or 1 maybe|;;< --- sheet name or number
   ) 
)
     )
 (progn
 (vlax-for sht (vlax-get-property xlbook 'Sheets)
   (if (eq (vlax-get-property sht 'Name) sheetname)
     (setq xlsheet sht)))))
;;_____________________________________________________________________________________;;
    (vlax-invoke-method xlsheet 'Activate)
    (setq xlrange (vlax-get-property (vlax-get-property xlsheet 'Cells) 'Range address))
    (if (eq :vlax-true (vlax-variant-value(vlax-get-property xlrange 'HasFormula)))
     (setq xldata (vlax-get-property xlrange 'formulalocal))
     (setq xldata(vlax-get-property xlrange 'value2)))

 
  (setq xldata (mapcar '(lambda(x)
			  (mapcar 'vlax-variant-value x))
(vlax-safearray->list
  (vlax-variant-value  xldata)))
	)
(if (eq 1(length (car xldata)))
	   (setq xldata (vl-remove-if 'not(mapcar 'car xldata)))
  (setq xldata (vl-remove-if 'not  xldata)))
 

   (vl-catch-all-apply 'vlax-invoke-method (list xlbook 'close :vlax-false))
  (vlax-put-property xlapp 'DisplayAlerts :vlax-true)
  (gc);; before QUIT
    
(vl-catch-all-apply 'vlax-invoke-method (list xlapp 'quit))
  (mapcar '(lambda(x)(if (and x (not (vlax-object-released-p x)))        
(progn(vlax-release-object x)(setq x nil))))
   (list xlrange xlsheet xlbook xlapp))  
  (gc);; after QUIT

      xldata
  )

 ;;  group with sum elements in list
;; fixo () 2006 * all rights released
(defun suminlist  (lst)
  (if (car lst)
    (cons (cons	(car lst)
		(length	(vl-remove-if-not
			  '(lambda (x) (equal (car lst) x 0.001))
			  lst)
			)
		)
	  (suminlist (vl-remove-if
		       '(lambda	(x)
			  (equal (car lst) x 0.001))
		       lst)
		     )
	  )
    )
  )
  ;;___________________________  main part  _______________________;;

 
  ;; e.g.: 
  (setq filename (getfiled "Select Excel file : "
				"Dpipke"; (getvar "dwgprefix")
				 "xlsx;xls"
				 4
		       )
 sheetname "Sheet1"
 address "B1:B1000");<-- also you're could to put exact address , say B10:B23 etc
  (setq xlData (GetXlRangeByAddress filename sheetname address))
   
  ;;;   ---   rest your mojo with acad is goes here   ---   ;;;
  
  (setq gruppen (suminlist xldata))
  (alert (strcat "Return column data with count:\n"(vl-princ-to-string gruppen)))
  (*error* nil)
  (princ)
  )
(princ 
"\n\t\t---\tStart command with XLCR\t---")
(prin1)
(or 
(vl-load-com)(princ))

 

_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Message 8 of 10
Dpipke
in reply to: Hallex

I appreciate the help. I didn't mean to imply for you to make it for me, and since this all seems to be gibberish to me I think I may have been trying to get ahead of myself at the beginner level that I'm at.
Once again thanks very much for the help!
Message 9 of 10
Hallex
in reply to: Dpipke

This is reasonable,

do it,

Cheers Smiley Happy

_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Message 10 of 10
bhull1985
in reply to: Dpipke

from one newbie to another, dissecting the program you're familiar with will help you understand the code that is contained within.
its tough but far better than reading descriptions of functions then attempting to use them properly. for me at least it's much more effective to see examples in use that youve free reign to modify and tweak , checking the results often , to get a grasp on how youd use the same functions but for different purposes later. or so I hope 🙂
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Please use code tags and credit where credit is due. Accept as solution, if solved. Let's keep it trim people!

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report

”Boost