;;;Moss Designs
;;;www.mossdesigns.com
;;;December 2002
;;;This routine allows the user to select an Excel spreadsheet.
;;;The spreadsheet data is then imported into your AutoCAD drawing and placed as a block
;;;;

(defun placetext ()
  (setq textno 0)
  (setq text-item (nth textno row-list))
  (setq newpt ins-point)
   (if (numberp text-item)
      (setq text-item (rtos text-item 2 0 ))
    )
  (setq counter 0)
  
  (while (< textno list-len)

    (if	(= th nil)
      (command "text" newpt "" "" text-item "")
    )
    (if (/= th nil)
            (command "text" newpt "" "" text-item "")
    )
    ; set up column x points
    (setq x-pt (car newpt))
    (setq colx-list (append colx-list (list x-pt)))
    (setq text (entlast))
    (setq block-list (append block-list (list text)))
    (setq text-list (entget text))
    (setq texth (cdr (assoc 40 text-list)))
    (setq texth (+ 0.15 texth))
    (setq text-length (strlen text-item))
    (setq col-wid (* text-length 0.25))
    (setq x-pt (+ x-pt col-wid))
    (setq y-pt (cadr ins-point))
    (setq newpt (list x-pt y-pt))
    (setq textno (+ 1 textno))
    (setq text-item (nth textno row-list))
    (if (numberp text-item)
      (setq text-item (rtos text-item 2 0 ))
    )
  )					; end while  
)
;;;;
;;;;****************************************************************

(defun placetext2 ()
  (setq textno 0)
  (setq text-item (nth textno row-list))
  (setq y-pt (- y-pt texth))
  (setq xno 0)
  (setq x-pt (nth xno colx-list))
  (setq ins-point (list x-pt y-pt))
  (setq newpt ins-point)
  
   (if (numberp text-item)
      (setq text-item (rtos text-item 2 0 ))
    )
  (setq counter 0)
  
  (while (< textno list-len)

    (if	(= th nil)
      (command "text" newpt "" "" text-item "")
    )
    (if (/= th nil)
            (command "text" newpt "" "" text-item "")
    )
    ; set up column x points
    (setq text (entlast))
    (setq block-list (append block-list (list text)))
    (setq text-list (entget text))
    (setq xno (+ xno 1))
    (setq x-pt (nth xno colx-list))
    (setq newpt (list x-pt y-pt))
    (setq textno (+ 1 textno))
    (setq text-item (nth textno row-list))
    (if (numberp text-item)
      (setq text-item (rtos text-item 2 0 ))
    )

  )					; end while
  
); end defun
;
(defun Link_2_Excel ()
  (vl-load-com)
  (setq refname "excel9.olb")
  (setq
    fil	(findfile
	  "c:/programme/Microsoft Office/Office/excel9.olb"
	)
  )
  (if (= fil nil)			; then excel file not found
    (setq
      fil (getfiled "Locate Excel application" "excel9" "olb" 8)
    )
    
  )
  (if (not excm-open)
    (vlax-import-type-library
      :tlb-filename	fil		  :methods-prefix
      "excm-"		:properties-prefix
      "excp-"		:constants-prefix "excc-"
     )
  )
  (setq excelApp (vlax-create-object "Excel.Application"))
  (setq oWbks (vlax-get-property excelApp 'Workbooks))
  (setq
    filepath (getfiled "Select Excel file to insert"
		       ""
		       "xls"
		       4
	     )
  )
  (setq oWbk (excm-open oWbks filepath))
  (vla-Put-Visible excelApp 1) ; hide excel session
  (setq oWkshts (vlax-get-property oWbk 'Worksheets))
  (setq oWksht (excp-get-item oWkshts 1))
  

;; get information from spreadsheet

 (setq oRng (excp-get-range oWksht "A1:Z1"))
  (setq array (excp-get-value oRng))	;array is a safearray variant filled with variants
  (vlax-release-object oRng)

					;conversion
  (setq sfarray (vlax-variant-value array))
  (setq valList (car (vlax-safearray->list sfarray)))
  (setq entry 0)
  (setq row-list nil)
  (while (/= entry nil)
    (progn
      (setq var (nth entry valList))
      (setq cellVal (vlax-variant-value var)) ; get value in cell
      (if (/= cellVal nil)
	(progn
	  (setq row-list (append row-list (list cellVal)))
	  (setq entry (+ 1 entry))
	)				;end progn
      )					;end if
      (if (= cellVal nil)
	(setq entry nil)
      )
    )					; end progn
  )					; end while
 (setq list-len (length row-list))
 (setq ins-point (getpoint "\nInsertion point for Excel data? "))
  (setq th (getvar "TEXTSIZE"))
 

); end defun
;;;;

(defun firstrow	()

  (placetext)
					; that's the first row
  (setq rowno "2")
					; how many items in the row?  This tells us how many columns to read

  (if (= list-len 3)
    (setq col "C")
  )
  (if (= list-len 4)
    (setq col "D")
  )
  (if (= list-len 5)
    (setq col "E")
  )
  (if (= list-len 6)
    (setq col "F")
  )
  (if (= list-len 7)
    (setq col "G")
  )
  (if (= list-len 8)
    (setq col "H")
  )
  (if (= list-len 9)
    (setq col "I")
  )
  (if (= list-len 10)
    (setq col "J")
  )
  (if (= list-len 11)
    (setq col "K")
  )
  (if (= list-len 12)
    (setq col "L")
  )
  (if (= list-len 13)
    (setq col "M")
  )
  (if (= list-len 14)
    (setq col "N")
  )
  (if (= list-len 15)
    (setq col "O")
  )
  (if (= list-len 16)
    (setq col "P")
  )
  (if (= list-len 17)
    (setq col "Q")
  )
  (if (= list-len 18)
    (setq col "R")
  )
  (if (= list-len 19)
    (setq col "S")
  )
  (if (= list-len 20)
    (setq col "T")
  )
  (if (= list-len 21)
    (setq col "U")
  )
  (if (= list-len 22)
    (setq col "V")
  )
  (if (= list-len 23)
    (setq col "W")
  )
  (if (= list-len 24)
    (setq col "X")
  )
  (if (= list-len 25)
    (setq col "Y")
  )
  (setq range (strcat "A" rowno ":" col rowno))

  (setq oRng (excp-get-range oWksht range))
  (setq array (excp-get-value oRng))	;array is a safearray variant filled with variants
  (vlax-release-object oRng)

					;conversion
  (setq sfarray (vlax-variant-value array))
  (setq valList (car (vlax-safearray->list sfarray)))
  (setq entry 0)
  (setq row-list nil)
  (while (/= entry nil)
    (progn
      (setq var (nth entry valList))
      (if (/= var nil)
	(progn
	  (setq cellVal (vlax-variant-value var)) ; get value in cell
	  (setq row-list (append row-list (list cellVal)))
	  (setq entry (+ 1 entry))
	)				;end progn
      )					;end if
      (if (= var nil)
	(setq entry nil)
      )
    )					; end progn
  )					; end while
)					;end defun

;;;;
;;;;******************************************************
(defun filltext()
  (placetext2)
  (setq rowno (atoi rowno))
  (setq rowno (+ 1 rowno))
  (setq rowno (itoa rowno))
  (setq range (strcat "A" rowno ":" col rowno))

  (setq oRng (excp-get-range oWksht range))
  (setq array (excp-get-value oRng))	;array is a safearray variant filled with variants
  (vlax-release-object oRng)

					;conversion
  (setq sfarray (vlax-variant-value array))
  (setq valList (car (vlax-safearray->list sfarray)))
  (setq entry 0)
  (setq row-list nil)
  (while (/= entry nil)
    (progn
      (setq var (nth entry valList))
      (if (/= var nil)
	(setq cellVal (vlax-variant-value var)) ; get value in cell
      )

      (if (/= var nil)
	(progn
	  (setq row-list (append row-list (list cellVal)))
	  (setq entry (+ 1 entry))
	)				;end progn
      )					;end if
      (if (= var nil)
	(setq entry nil)
      )
    )					; end progn
  )					; end while
  (setq flag (nth 0 row-list))
);end defun

;;;;

(defun closeExcel ()

					; save worksheet chan
  (excm-close oWbk :vlax-true)


					; commented out here, this object not used when adding a new workbook
					;(vlax-release-object oWbks)

  (vlax-invoke-method excelApp 'Quit)


					; known problem.
					; EXTERNAL COM PROCESSES DO NOT TERMINATE, IF CREATED WITH (VLAX-GET-OR-CREATE-OBJECT)
					; As a workaround, you can force a
					; garbage-collection with the lisp function (gc).
					; After you invoke Excel's Quit method, insert the call to (gc) into your code. 
  (gc)
)
;;;
;;;**********************

(defun placelines ()
 (setq vertlineno (1+ list-len))
 (setq horlineno (atoi rowno))
 (setq ent (nth 0 block-list))
 (setq ent-list (entget ent))
 (setq y1 (caddr (assoc 10 ent-list)))
 (setq y1 (+ texth y1))
 (setq xno 0)
 (setq col-length (length colx-list))
;;;create horizontal lines
  
 (setq x1 (nth xno colx-list))
 (setq x1 (- x1 texth))
 (setq stpt (list x1 y1))
 (setq col-length (- col-length 1))
 (setq x2 (nth col-length colx-list))
 (setq x2 (+ x2 col-wid))
 (setq endpt (list x2 y1))
 (command "line" stpt endpt "")
 (setq text (entlast))
 (setq block-list (append block-list (list text)))
 (setq y2 (cadr ins-point))
 (setq y2 (- y2 0.06))
 (setq diff (- y1 y2))
 (setq delta1 (- horlineno 0.9))
 (setq delta (/ diff delta1))
 (setq count 1)
 (while (< count horlineno)
   (setq stpt2 (list x1 y2))
   (setq endpt2 (list x2 y2))
   (command "line" stpt2 endpt2 "")
   (setq text (entlast))
   (setq block-list (append block-list (list text)))
   (setq y2 (+ y2 delta))
   (setq count (1+ count))
 ); end while

 ;; create vertical lines
   (setq y2 (cadr ins-point))
 (setq y2 (- y2 0.06))
   (setq stpt2 (list x1 y2))
 (command "line" stpt stpt2 "")
   (setq text (entlast))
 (setq block-list (append block-list (list text)))
 (setq xno 1)
 (setq col-length (length colx-list))
 (while (< xno col-length)
   (setq x1 (nth xno colx-list))
   (setq x1 (- x1 0.06))
   (setq stpt (list x1 y1))
   (setq endpt (list x1 y2))
   (command "line" stpt endpt "")
   (setq text (entlast))
   (setq block-list (append block-list (list text)))
   (setq xno (1+ xno))
 )
   (setq stpt (list x2 y1))
   (setq endpt (list x2 y2))
 (command "line" stpt endpt "")
 (setq text (entlast))
 (setq block-list (append block-list (list text)))
 ); end defun
;;;
;;;;**********************************


(defun createblock ()
   (setq blocklen (length block-list)) ; how many entities for the block
   (setq count 0)
   (setq ss nil)
    (setq en (nth count block-list))
     (setq ss (ssadd en))
     (setq count (1+ count))
   (while (< count blocklen)
   (setq en (nth count block-list))
     (setq ss (ssadd en ss))
     (setq count (1+ count))
   )
   (setq sscheck (sslength ss))
   (command "-block" "excel-ins" ins-point ss "")
)
;;;
;;;**********************************
(defun c:insxls()
  ; ask you to locate an xls spreadsheet
  (setq colx-list nil)
  (setq block-list nil)
  (setq row-list nil)
  
   ; create layer called "EXCEL-IMPORT" COLOR YELLOW
  (command "-layer" "neu" "excel-import" "fa" "2" " " "se" "excel-import" "")
  ; open excel spreadsheet 
  (Link_2_Excel)
  ; creates a block with data from the spreadsheet and gridlines
  ; prompt for insertion point
  ; use textscale
  ; use rotation of 0
  (firstrow)
  (setq flag 1)
  (while (/= flag nil)
     (filltext)
  )
  (closeExcel)
  (placelines)
  (createblock)
  (command "-einfüge" "excel-ins" ins-point "" "" "")
)