extracting individual numbers from one Excel table cell text string such as 12x5.5x36

[elmoleaf]

Hi,

I'm attempting to modify Fixo's code at bottom to read data from an excel spreadsheet that's formatted a bit differently than assumed by the code. The code here assumes X, Y, and Z scale each live in a separate exel column/cell. However, I have existing spreadsheets where these dimensions were entered as a text string in a single cell. For example, the cell might read 6x5.5x24. The only constant is the "x" between the dimensions. The text numbers might be one, two or even three characters and may or may not include decimals.

 

My thought is to modify these lines below to somehow check the same string for the X's and truncate according its position to extract the desired X, Y or Z value. But, I don't know enough yet to even know if this is possible.

xscale (atof (vl-princ-to-string (nth 5 item)))
       yscale (atof (vl-princ-to-string (nth 6 item)))
       zscale (atof (vl-princ-to-string (nth 7 item)))

Failing this, I'll make a second version of the existing spreadsheets and have users copy data into new columns formatted to work with the existing code.

Many thanks!

 

 
;;;---------------------------------------------------------------------------;
;;;
;;;   getexel.lsp   

;;;
;;;   based on code posted by "fixo" on [url]http://forums.augi.com/showthread.php?t=112689&highlight=blocks+excel[/url]
;;;   This lisp is used to insert a block multiple times based on coordinates and scale factors listed in an excel spreadsheet
;;;   Data in spreadsheet must follow this format:
;;; first row is headers/labels for data
;;; cola-empty, colb-x coord, colc-y coord, cold-z coord, colf-width/x scale, colg-depth/y scale, colh-height/z scale,
;;; coli-layer destination name, colj-equip tag number.
;;;

;;;---------------------------------------------------------------------------;
;;;
;;;   DESCRIPTION
;;;
;;;   Configure AutoCAD
;;;
;;;******************************************************
;;; Section 1
;;;******************************************************
;;;
;;; --- Load Visual Lisp Support
;;;
(VL-LOAD-COM) 
;;;******************************************************
;;; Section 2
;;;******************************************************
;;; read used range from Excel 
;;;
(defun getexcelinfo  (Wbk ShtNum / Sht UsdRange ExcData)
 (setq Sht (vl-catch-all-apply
      'vlax-get-property
      (list (vlax-get-property Wbk "Sheets")
     "Item"
     ShtNum
     )
      )
)
 (setq UsdRange (vlax-get-property Sht "UsedRange")
ExcData  (vlax-safearray->list
    (vlax-variant-value
      (vlax-get-property UsdRange "Value")
      )
    )
)
 (setq
   ExcData (mapcar
      (function (lambda (x) (mapcar 'vlax-variant-value x)))
      ExcData
      )
   )
 ExcData
 )
;;;******************************************************
;;; Section 3
;;;******************************************************
(defun C:getexcel  (/ *error* acsp adoc aexc att1 att2 att3 att4 attribs block_data blockname
        block_obj dwgpath layer rot x xlbook xlbooks xlpath xscale y yscale z zscale)
 (defun *error* (msg)
 (vl-bt)  
 (if
   (vl-position
     msg
     '("console break"
"Function cancelled"
"quit / exit abort"
)
     )
    (princ "Error!")
    (princ msg))
 (vla-endundomark adoc)

 (vl-exit-with-value "Program bombed, sorry")
)

 (if
   (setq xlpath (getfiled "* Select Excel File To Read Data*"
     ""
     "xlsx"
     4)
  )
    (progn
      (setq aexc    (vlax-get-or-create-object "Excel.Application")
     xlbooks (vlax-get-property aexc "Workbooks"))
      (setq xlbook (vlax-invoke-method xlbooks "Open" xlpath))

      (setq block_data (getexcelinfo xlbook 1));<-- 1 is excel sheet number, change to suit
      (setq block_data
      (vl-remove-if-not
 (function (lambda (x) (car x)))
 block_data))
      (setq block_data (cdr block_data))
      (vl-catch-all-apply
 (function (lambda ()
      (vlax-invoke-method
        xlbook
        "Close"
        :vlax-false)))
 )
      (mapcar
 (function (lambda (x)
      (vl-catch-all-apply
        (function (lambda ()
      (progn
        (if x
          (vlax-release-object x)))))))
    )
 (list xlbook
       xlbooks
       aexc)
 )
      (setq aexc nil)
      (gc)
      (gc)
      (setq dwgpath (getfiled "* Select Drawing With Block To Insert *"
         ""
         "dwg"
         4)
     )
      (setq blockname (vl-filename-base dwgpath))
      (setq adoc (vla-get-activedocument (vlax-get-acad-object)))
      (setq acsp (vla-get-block (vla-get-activelayout adoc)))
      (vla-startundomark adoc)

 (vl-catch-all-apply
   (function (lambda ()
       (progn (setq block_obj (vlax-invoke-method
     acsp
     'InsertBlock
     (vlax-3d-point '(0 0 0))
     dwgpath
     1
     1
     1
     0
     )
      )
      (vla-delete block_obj)
      (vlax-release-object block_obj))))
   )
      ;; I know nothing what is data format in your Excel
      ;; thus I have added extrafluous convertion of them
      (foreach item  block_data
 (setq x      (atof (vl-princ-to-string (nth 1 item)))
       y      (atof (vl-princ-to-string (nth 2 item)))
       z      (atof (vl-princ-to-string (nth 3 item)))
       rot    (atof (vl-princ-to-string (nth 4 item)))
       xscale (atof (vl-princ-to-string (nth 5 item)))
       yscale (atof (vl-princ-to-string (nth 6 item)))
       zscale (atof (vl-princ-to-string (nth 7 item)))
       layer  (vl-princ-to-string (nth 8 item))
       att1   (vl-princ-to-string (nth 9 item))
       att2   (vl-princ-to-string (nth 5 item))
att3   (vl-princ-to-string (nth 6 item))
att4   (vl-princ-to-string (nth 7 item))
       )
 (setq block_obj (vlax-invoke-method
     acsp
     'InsertBlock
     (vlax-3d-point (list x y z))
     dwgpath
     xscale
     yscale
     zscale
     rot)
       )
 (if (tblsearch "layer" layer)
   (vla-put-layer block_obj layer)
   (princ (strcat "\nLayer " "\"" layer "\"" " does not exist"))
   )
 (setq attribs (vlax-invoke block_obj 'GetAttributes))
 (foreach att  attribs
   (cond ((eq "EQ-TAG" (vla-get-tagstring att));<--"EQ-TAG" is the first tag
   (vla-put-textstring att att1))
  ((eq "EQ-WIDTH" (vla-get-tagstring att));<--"EQ-WIDTH" is the second tag
   (vla-put-textstring att att2))
  ((eq "EQ-DEPTH" (vla-get-tagstring att));<--"EQ-DEPTH" is the THIRD tag
   (vla-put-textstring att att3))
  ((eq "EQ-HGHT" (vla-get-tagstring att));<--"EQ-HGHT" is the FOURTH tag
   (vla-put-textstring att att4))
  ;<-- add other tags here by the same way as above
  (T nil)
  )
   )
 )
      )
    )
 (*error* nil)
 (prin1)
 )
(prompt
 "\n\t\t>>>\tType getexcel to execute\t<<<")
(prin1)
(vl-load-com)~'J'~

[elmoleaf]

Here is my attempted modification. I believe the combined xyz info is in the 9th item, so I'm attempting to substitute blank spaces for the "x"s within there and assign the result to the variable convertscale. For example, 24x35x87 would become 24 35 87.

Entire lsp not working yet...don't know if it's this part or elsewhere.

 

 
(setq cntr 0)
(foreach item block_data
 (setq convertscale (subst "" x (nth 9 item))

      ;; I know nothing what is data format in your Excel
      ;; thus I have added extrafluous convertion of them
       x (cntr (+ cntr 48))
       y 0
       z 0
       rot 0
       xscale (atof (car convertscale))
       yscale (atof (cadr convertscale))
       zscale (atof (caddr convertscale))
       layer  (vl-princ-to-string (nth 8 item))
       att1   (vl-princ-to-string (nth 1 item))
       att2   (vl-princ-to-string (car convertscale))
       att3   (vl-princ-to-string (cadr convertscale))
       att4   (vl-princ-to-string (caddr convertscale))
       )

[BlackBox]

This may help you figure it out....

 

[color=seagreen];; Assuming this:[/color]
(setq cell "6x5.5x24")

(setq xCoord (substr cell 1 (setq xPos1 (vl-string-position (ascii "x") cell))))
(setq yCoord (substr cell [color=seagreen];| ...Continue from here |;[/color]))

[BIGAL]

I would use renderman method the problem with elmoleaf method is if your strings change "6x5.5x24" v/s "6.0x5.5x24.0" which you would read as being identical, this method also allows you do any string search combo, comma , or space by just changing the search variable (ascii "x") or (setq variable "x")(ascii variable)

[elmoleaf]

Many thanks. I was able to get that part functioning with the code posted below.

The problem now is the x value counter. It represents a x value for insertion of a block in model space. I want it to increment for each item from in my list, but it doesn't work.

I also need to learn more about getting a used range from an excel table. The code I originally posted doesn't get the range I need to work with.

Any pointers or links to examples and basic information about these topics would be helpful. I'm a novice. Thank you.

 
(foreach item block_data
 (setq convertscale (vl-princ-to-string (nth 9 item))
       xscale (atof (substr convertscale 1 (setq xPos (vl-string-position (ascii "x") convertscale))))
       yPos (vl-string-position (ascii "x") convertscale nil t)
yscale (atof (substr convertscale (+ xPos 2) (- yPos (+ xPos 1))))
       zscale (atof (substr convertscale (+ yPos 2)))
       x (+ cntr 48)
       y 0
       z 0
       rot 0
       layer "1E-EQUIP"
       att1 (vl-princ-to-string (nth 1 item))
       att2 xscale
       att3 yscale
       att4 zscale
       )