;;===========================================;;
;; local defun
;; read used range of Excel sheet
;; ========================================= ;;


(defun EXD (/ ExcelApp ExcData FilePath Sht ShtNum UsdRange Wbk)

 (or (vl-load-com))
  (setq	FilePath (getfiled "Select file to read data :" 
			   "C:\\TEMP\\"
			   "xlsx" 
			   16
		 )
  )

  (setq ShtNum (getint "\nEnter sheet number : \n" ))

  (setq ExcelApp (vlax-get-or-create-object "Excel.Application" ))
  (vlax-put-property ExcelApp "DisplayAlerts"  :vlax-false)
  (vla-put-Visible ExcelApp :vlax-true)
  (setq	Wbk (vl-catch-all-apply
	      'vla-open
	      (list (vlax-get-property ExcelApp "WorkBooks" ) FilePath)
	    )
  )
  (setq	Sht (vl-catch-all-apply
	      'vlax-get-property
	      (list (vlax-get-property Wbk "Sheets" )
		    "Item" 
		    ShtNum
	      )
	    )
  )
  (vlax-invoke-method Sht "Activate" )
;;;  (setq	UsdRange (vlax-get-property Sht 'UsedRange)
    (setq	UsdRange (vlax-get-property Sht "Range" "A1:M1006")
	ExcData	 (vlax-safearray->list
		   (vlax-variant-value
		     (vlax-get-property UsdRange 'Value2)
		   )
		 )
  )	
  (setq
    ExcData (mapcar
	      (function (lambda (x) (mapcar 'vl-princ-to-string (mapcar 'vlax-variant-value x))))
	      ExcData
	    )
  )
  (vlax-put-property ExcelApp "DisplayAlerts"  :vlax-true)
;;;  (vl-catch-all-apply
;;;    'vlax-invoke-method
;;;    (list Wbk "close" )
;;;  )
;;;
;;;  (vl-catch-all-apply
;;;    'vlax-invoke-method
;;;    (list ExcelApp "quit" )
;;;  )

  (mapcar
    (function
      (lambda (x)
	(vl-catch-all-apply
	  (function (lambda ()
		      (progn
			(if (not (vlax-object-released-p x))
			  (progn
			    (vlax-release-object x)
			    (setq x nil)
			  )
			)
		      )
		    )
	  )
	)
      )
    )
    (list UsdRange Sht Wbk ExcelApp)
  )

  (gc)
  (gc)
  ExcData

)

 ;; main programm

 (defun C:LISTIN (/ adoc at atts attvalues axss blkinfo blkobj cnt data osm ss)

   (vl-load-com)
   (setq adoc (vla-get-activedocument
                (vlax-get-acad-object)
              ) 
   ) 
   (vla-endundomark adoc)
   (vla-startundomark adoc)


   (setq blkinfo (exd))
   (setq blkinfo (mapcar
                   (function (lambda (x)
                               (vl-remove-if 	(function (lambda (y)
                                                         (eq "lululululululululululululul" y)) )
                   x)))
                   (cdr blkinfo);<--remove headers
                 ) 
   )



   (setq blkinfo (vl-remove-if (function (lambda(x)
                               (or (not x)(wcmatch (car x) "T*"))));<-- remove 'TOTAL' row
                   blkinfo
                 ) 
   )
;;   (vl-cmdf "zoom" "a")
;;   (vl-cmdf "zoom" ".85x")
   (setq ss (ssget "_X" (list (cons 0 "INSERT") (cons 66 1))))
   (setq axss (vla-get-activeselectionset adoc))
   (vlax-for a axss
     (if 
     (setq data (assoc (vla-get-handle a) blkinfo))
     (progn
     (setq blkobj (vlax-ename->vla-object (handent (car data))))
     (setq atts (vlax-invoke blkobj 'GetAttributes))
     (setq attvalues (cddr data))
     (if (and atts attvalues (= (length attvalues) (length atts)))
       (progn
         (setq cnt 0)
         (repeat (length atts)
           (setq at (nth cnt atts))
           (vla-put-textstring at (nth cnt attvalues))
           (setq cnt (1+ cnt))
         ) 
       ) 
     )
   )
     )
     )
   (vla-endundomark adoc)
   (princ)
 ) 
(princ "\n\t\t***\tStart command with LISTIN...\t***")
(princ)
;;;(C:LISTIN)