LISP Routine - Importing Polylines

[LeoCAD]

I'm looking for a LISP routine (should be the only way to do it) to import an excel file with a list of specified lengths at specific angles and each line also to have an X & Y coordinate associated to it.

 

For example.

Length - Angle - X - Y

10m - 270degress - 30 - 40

12m - 90degrees - 50 - 60

 

This might not be the best way to explain. I will try to provide more details in an image. 

 

 

[BIGAL]

Fairly straightforward the use of Polar command in lisp is the same (setq newpt (polar pt ang dist)) then draw a line connecting the 2 points. pt & newpt.

 

You can do it also in excel making a column of the required answer. Use concatenate to join cells making the formula. Then copy the column to the command line. Need to know direction of  0.0 angle.

 

Post a sample excel.

 

 

Edited December 15, 2022 by BIGAL

[LeoCAD]

Hi, Thanks for the reply.

 

We only have specific data to work with. We do not have a starting coordinate and ending coordinate for each line. We only have a starting coordinate, a length, and the angle of the line based on true north being 0/360 degrees. 

 

I have attached the official data. 

CURB OFFSET DATA T.xlsx

[BIGAL]

Ok we have a problem you have lat and long values, It may be possible with CIV3D to read the lat long and make cogo points as 1st step, 2nd step is then do the polar function and draw the new line. CIV3D supports the import of lat long, you set up your world zone so get X&Y values.

 

There is some lat long to world software out there. I had some for AUS put out by the state surveyors board. 

 

So before even starting what is your world zone ?  I am not a surveyor, but need this. Try googling for "convert lat long lisp autocad"  to your part of the world. 

[LeoCAD]

Sorry I did know about the issue with the Long and Lat. The new file attached has them converted properly. 

 

Copy of TroutCreekTest.xls

[devitg]

Hi , where in the las xls there are this values 

Length - Angle - X - Y

10m - 270degress - 30 - 40

12m - 90degrees - 50 - 60

 

Or you need to draw a line from  east [x]  north [y] curb distance at angle 357.69 . I guess your 0 angle base is at   x axis

 

 

 

[LeoCAD]

Hi Devitg,

 

I apologize, I made it confusing but let me clarify.

 

1. The column "curb_offset" is the length of the line

2. The Column "curb offset.bearing" is the angle of that line

3. The Column "northing" and "easting" is the starting point of the line

[devitg]

28 minutes ago, LeoCAD said:

Hi Devitg,

 

I apologize, I made it confusing but let me clarify.

 

1. The column "curb_offset" is the length of the line

2. The Column "curb offset.bearing" is the angle of that line

3. The Column "northing" and "easting" is the starting point of the line

Please upload  a sample dwg  with at least , one point ,  the line, and bearing just to work on it as your standards 

[LeoCAD]

I was able to create something with excel but the only issue now is that each line automatically joins to each other instead of being independent of each other.

 

We I copy the S column into CAD it does what I need but I can't have them all join together. So close to the fix here. 

FORMULA.xlsx

[devitg]

@Leocad ,  I change some things , a new csv only hold east north dist ang. 

 

Find attached , dwg, csv and lisp 

 

 

 

 

 

 

line from poin dist and bear.dwg no formula just data.csv lines and point from csv.LSP

[BIGAL]

Now that we understand can read from excel direct, the other thing is can skip entries where the curb offset is blank. Thats probably why the copy column S is not working.

 

Watch this space going for lunch. Back from lunch.

 

; https://www.cadtutor.net/forum/topic/76488-lisp-routine-importing-polylines/

; formula.xlsx

; By AlanH DEC 2022

; thanks to Lee-mac for this defun 
; www.lee-mac.com
; 44 is comma 9 is tab 34 is space 58 is colon
(defun _csv->lst58 ( str / pos )
	(if (setq pos (vl-string-position 58 str))
		(cons (substr str 1 pos) (_csv->lst58 (substr str (+ pos 2))))
		(list str)
    )
)

; ColumnRow - Returns a list of the Column and Row number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (ColumnRow "ABC987") = '(731 987)
;default to "A1" if there's a problem
;-------------------------------------------------------------------------------
(defun ColumnRow (Cell$ / Column$ Char$ Row#)
  (setq Column$ "")
  (while (< 64 (ascii (setq Char$ (strcase (substr Cell$ 1 1)))) 91)
    (setq Column$ (strcat Column$ Char$)
          Cell$ (substr Cell$ 2)
    )
  )
  (if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
    (list (Alpha2Number Column$) Row#)
    '(1 1)
  )
)

; Number2Alpha - Converts Number into Alpha string
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Num# = Number to convert
; Syntax example: (Number2Alpha 731) = "ABC"
;-------------------------------------------------------------------------------
(defun Number2Alpha (Num# / Val#)
  (if (< Num# 27)
    (chr (+ 64 Num#))
    (if (= 0 (setq Val# (rem Num# 26)))
      (strcat (Number2Alpha (1- (/ Num# 26))) "Z")
      (strcat (Number2Alpha (/ Num# 26)) (chr (+ 64 Val#)))
    )
  )
);defun Number2Alpha

; Alpha2Number - Converts Alpha string into Number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Str$ = String to convert
; Syntax example: (Alpha2Number "ABC") = 731
;-------------------------------------------------------------------------------
(defun Alpha2Number (Str$ / Num#)
  (if (= 0 (setq Num# (strlen Str$)))
    0
    (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#)))
       (Alpha2Number (substr Str$ 2))
    )
  )
)



(defun getrangexl2 ( / lst UR CR RADD )
(vl-catch-all-error-p
	   (setq Rng
		  (vl-catch-all-apply
		    (function (lambda ()
				(vlax-variant-value
				  (vlax-invoke-method
				    (vlax-get-property myxl 'Application)
				    'Inputbox
				    "Select a Range: "
				    "Range Selection Example"
				    nil
				    nil
				    nil
				    nil
				    nil
				    8))))))
)
(setq xrng (vlax-get-property rng "address"))
(setq xxrng xrng)
(repeat 4 (setq xxrng(vl-string-subst "" "$" xxrng)))
(setq xxxrng (_csv->lst58 xxrng))
(setq rngst (columnrow (nth 0 xxxrng)) rngend (columnrow (nth 1 xxxrng)))
(setq *ExcelData@ nil )
(setq Row# (nth 1 rngst))
(repeat (+ (- (nth 1 rngend)(nth 1 rngst) ) 1)
(setq Data@ nil)
(setq Column# (nth 0 rngst))
(repeat (+  (- (nth  0 rngend)(nth 0 rngst) ) 1)
(setq Range$ (strcat (Number2Alpha Column#)(itoa Row#)))
(setq ExcelRange (vlax-get-property myxl "Range" range$))
(setq ExcelVariant (vlax-get-property ExcelRange 'Value))
(setq ExcelValue (vlax-variant-value ExcelVariant))
(setq Data@ (append Data@ (list ExcelValue)))
(setq Column# (1+ Column#))
)
(setq ExcelData (append ExcelData (list Data@)))
(setq Row# (1+ Row#))
)
(princ)
)

(defun dtr (a)
(* pi (/ a 180.0))
)



;;;;;; starts here
(defun c:lxl ( / myxl oldsnap )
(alert "You must have the excel file open \n\nIf not do it now\n\nYou will see excel blink then select range")

(setq oldsnap (getvar 'osmode))
(setvar 'osmode 0)

(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)

(getrangexl2)

(setvar 'ctab "Model")

(setq k 0)
(repeat (length exceldata)
(setq exline (nth k exceldata))
(setq x (nth 0 exline)
  Y (nth 2 exline)
  dist (nth 5 exline)
)
(if (= (eval dist) nil)
(progn
(princ "Miss")
)
(progn
(setq pt (list x y))
(setq ang (dtr (nth 7 exline)))
(setq newpt (polar pt ang dist))
(command "Line" pt newpt "")
)
)
(setq k (1+ k))
)

(command "zoom" "E")

(if (not (vlax-object-released-p myXL))(progn(vlax-release-object myXL)(setq myXL nil)))
(princ)
)
(c:LXL)

 

A couple of rules its based on what you supplied if you remove or add columns it will not work, the excel must be open, when asked for range can select in this case C2 to J483 or type C2:J483. Then click on AutoCAD screen it may take a moment to appear.

 

I am not sure if angles are correct please let me know.

 

 

Edited December 17, 2022 by BIGAL