Getting a Position from a list

[Earlzii]

Hi,

 

I have found a routine that I have tried to use to get some information from a range of cells in excel into a list.

I got this to work originally looking at the cells in a column. My problem is that the excel spreadsheets I will be receiving may not always be in the same order. So I need to first search through the headers of the columns in excel to find the correct column.

I am trying to get the position of the word "Item" in the list so I can later get the column.

No matter what I try it returns nil, so I tested and told the list to print and everything I expected to be there was there. Next I have made sure that the list is in fact a list by using vl-consp and this returned T.

 

At the minute I am in the very early stages of developing this so I just keep testing each bit to make sure everything I do works. In the end I am hoping to get a routine that will be used to validate tags in a database and fill data in from the spreadsheet into the relevant blocks. I have a very limited knowledge of lisp so the code may not be the most efficient because of my lack of wider knowledge but I can get the simpler routines to work.

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;; All code between this taken from                                                                     ;;;
;;; https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/read-excel-sheet-column/td-p/4365329 ;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

(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 1))
			  lst)
			)
		)
	  (suminlist (vl-remove-if
		       '(lambda	(x)
			  (equal (car lst) x 1))
		       lst)
		     )
	  )
    )
  )

(defun c:test ()
  (setq filename (getfiled "Select Excel file : "
				"Dpipke"; (getvar "dwgprefix")
				 "xlsx;xls"
				 4
		       )
 sheetname "Sheet1"
 address "A1:ZZ1");<-- also you're could to put exact address , say B10:B23 etc

(setq ColumnHeader (GetXlRangeByAddress filename sheetname address))

(setq itempos (vl-position "Item" ColumnHeader))

(princ itempos)

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

This is the part that I have been able to get to work is below.

(defun c:test ()
(setq cell "G1:G1000")
  (setq filename (getfiled "Select Excel file : "
				"Dpipke"; (getvar "dwgprefix")
				 "xlsx;xls"
				 4
		       )
 sheetname "Sheet1"
 address cell);<-- also you're could to put exact address , say B10:B23 etc

(setq xlData (GetXlRangeByAddress filename sheetname address))

(setq assettag (getstring "\Enter the asset tag: "))

(setq tagcell (vl-position assettag xlData))
(setq tagcell_1 (1+ tagcell))
(setq desc (strcat "H" (rtos tagcell)))
(setq desc_1 (strcat "H" (rtos tagcell_1)))


(setq desccell (strcase (vl-string-right-trim ".0000" desc)))
(setq desccell_1 (strcase (vl-string-right-trim ".0000" desc_1)))

(setq desc_range (strcat desccell ":" desccell_1))


(setq descsheetname "Sheet1")
(setq descaddress desc_range);<-- also you're could to put exact address , say B10:B23 etc

  (setq descData (GetXlRangeByAddress filename descsheetname descaddress))

(setq desclst (last descData))

(command "layer" "n" desclst "")

(princ)

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

Like I said I have found this code elsewhere so I don't fully understand what it is doing but I know it works. What I don't understand is that I don't see what I am doing differently that would cause vl-position to return nil.

Whatever help / time you can give is appreciated.

[ronjonp]

Your issue is 'ColumnHeader' is a list of lists:

;; Change this
(setq itempos (vl-position "Item" ColumnHeader))
;; To this
(setq itempos (vl-position "Item"  (car ColumnHeader)))

 

[Earlzii]

Thanks ronjonp

 

Can I ask how did you know that it was a list of lists? Just so I know what to look out for in the future.

Edited August 14, 2019 by Earlzii

[Earlzii]

Oh and whilst you are here do you know of a way of converting the number for a letter ie. 0=A 1=B 2=C etc.

 

I was going to set up a cond for itempos to say 0=A etc. etc. but I may have to go up to ZZ so probably over 600 options.

 

I think it would work but it probably isn't the best way of doing it, I'm not sure how else it could be done though as I do have basic/limited knowledge.

[ronjonp]

34 minutes ago, Earlzii said:

Thanks ronjonp

 

Can I ask how did you know that it was a list of lists? Just so I know what to look out for in the future.

I debugged the code in the VLIDE. Do you code using a debugging editor?

[ronjonp]

33 minutes ago, Earlzii said:

Oh and whilst you are here do you know of a way of converting the number for a letter ie. 0=A 1=B 2=C etc.

 

I was going to set up a cond for itempos to say 0=A etc. etc. but I may have to go up to ZZ so probably over 600 options.

 

I think it would work but it probably isn't the best way of doing it, I'm not sure how else it could be done though as I do have basic/limited knowledge.

Converting numbers to letters is easy using ASCII and CHR. You will have to keep track when you go past Z then double triple the letters etc.

 

See if this gets you started:

(ascii "A");< - Get ascii code for "A"
(alert (chr (+ 0 65)))
(alert (chr (+ 1 65)))
(alert (chr (+ 2 65)))
(alert (chr (+ 3 65)))

 

[Earlzii]

No I did not do this. For some reason I thought I didn't have access to it on my computer because of admin rights. But I do, I'll try and figure out how to use this might save me a great deal of grief in the future. Thanks 

[Earlzii]

17 minutes ago, ronjonp said:

Converting numbers to letters is easy using ASCII and CHR. You will have to keep track when you go past Z then double triple the letters etc.

 

See if this gets you started:

(ascii "A");< - Get ascii code for "A"
(alert (chr (+ 0 65)))
(alert (chr (+ 1 65)))
(alert (chr (+ 2 65)))
(alert (chr (+ 3 65)))

 

Thanks ronjonp I have plenty to look at and learn tomorrow.

[ronjonp]

Glad to help. Ask questions if you have them.  

[Earlzii]

Thanks again for you're advice ronjonp I have the code working how I want it to at this stage. Using the ASCII I came up with this to get the column names so firstly when it has found the correct string in the spreadsheet it then outputs this as the number in the list then goes through this

(cond
((<= itempos 25) (single_alpha))
((>= itempos 25) (double_alpha))
)

Then these are the functions that I made to give the letters of the column.

 

(defun single_alpha ()

(setq itempos_1 (fix itempos))

(setq cellcolumn (chr (+ itempos_1 65)))

)

(defun double_alpha ()

(setq itempos_1 (fix itempos))

(setq itemposdiv (/ itempos_1 26.));can give deicmal so next line is to make it a whole number
(setq dblxclalpha (fix itemposdiv))
(setq dblxclalpha-1 (+ -1 dblxclalpha))
(setq xclalpha_1 (chr (+ dblxclalpha-1 65)))
(setq xclalpha* (* dblxclalpha 26));gives the number to be subtracted from itempos so should return a value that is < 25
(setq xclalpha*_1 (+ -1 xclalpha*))
(setq xclalphasum (- itempos_1 xclalpha*))
(setq xclalpha_2 (chr (+ xclalphasum 65)))
(setq cellcolumn (strcat xclalpha_1 xclalpha_2))

)

I'm not sure if this can be made any shorter/efficient but I'm quite happy with it and it works as I want it to.

Thanks again 

[ronjonp]

1 hour ago, Earlzii said:

Thanks again for you're advice ronjonp I have the code working how I want it to at this stage. Using the ASCII I came up with this to get the column names so firstly when it has found the correct string in the spreadsheet it then outputs this as the number in the list then goes through this

(cond
((<= itempos 25) (single_alpha))
((>= itempos 25) (double_alpha))
)

Then these are the functions that I made to give the letters of the column.

 

(defun single_alpha ()

(setq itempos_1 (fix itempos))

(setq cellcolumn (chr (+ itempos_1 65)))

)

(defun double_alpha ()

(setq itempos_1 (fix itempos))

(setq itemposdiv (/ itempos_1 26.));can give deicmal so next line is to make it a whole number
(setq dblxclalpha (fix itemposdiv))
(setq dblxclalpha-1 (+ -1 dblxclalpha))
(setq xclalpha_1 (chr (+ dblxclalpha-1 65)))
(setq xclalpha* (* dblxclalpha 26));gives the number to be subtracted from itempos so should return a value that is < 25
(setq xclalpha*_1 (+ -1 xclalpha*))
(setq xclalphasum (- itempos_1 xclalpha*))
(setq xclalpha_2 (chr (+ xclalphasum 65)))
(setq cellcolumn (strcat xclalpha_1 xclalpha_2))

)

I'm not sure if this can be made any shorter/efficient but I'm quite happy with it and it works as I want it to.

Thanks again 

Glad you got something working!  

[BIGAL]

I will try to find there is a convert rowcolumn to x y and like wise x y numbers to A23 etc

 

REWROTE GETEXCEL.LSP AND ADDED SEVERAL NEW SUB-FUNCTIONS
;                     INCLUDING COLUMNROW, ALPHA2NUMBER AND NUMBER2ALPHA WRITTEN
;                     BY GILLES CHANTEAU FROM MARSEILLE, FRANCE.