Draw lines based on parameters in Excel Sheet

[ahickmang]

A large part of my job is drawing drip lines for septic systems. I've attached an image below showing them (changed color to show individual lines). Once given area dimentions, my excel sheet will spit out several different variations based on a certain set of parameters and I choose which works the best. The lines can be longer, shorter, and have more or less loops. The only thing that stays the same is they will always be separated by 2 feet. You can see the difference between them in the picture - Green has one loop, red has 3 loops, and magenta has 7 loops. What my sheet will give me is length of the leg and the number of loops, as well as the number of laterals (an entire segment of a lines). Do y'all know of a lisp routine that may be close to auto creating these lines, or know of someone who may be interested in creating this? It would REALLY speed up my work. Id be interested in learning to do it and having the programming knowledge, but I just don't have the time. 

[CodeDing]

@ahickmang ,

Well it definitely sounds like it is possible to accomplish what you're trying to do. However, there are so many unknown questions to get you started. Could you perhaps post an example of your Excel worksheet and also an example DWG file showing what your beginning state and end state looks like? I think having those 2 things would really provide good context to get you on track.

Best

~DD

[ahickmang]

Right, I had way too little detail in that post! Okay so I attached 2 additional images. You can see in the excel calc I have put in the dimentions of the area that I had to work with (66'x170'). From that, it spits out several different combinations of number of laterals with number of loops. A lateral would be one continuous line, and lateral leg will be each segment in a lateral. In this case, the one that worked best was 5 laterals with 3 loops. I knew my lateral leg length would be 61' and it would have 3 loops. Looking at the Drip Lines image, you'll see each lateral (5 per zone, 4 zones, so a total of 20) leg is 61' and loops 3 times. This program only works with block, so any irregular piece of land it will completely fail. On the bright side, that means the linework is always going to be the same/predictable, the only differences will be the length of the lateral legs, the number of loops, and the number of laterals, 

[Kent1Cooper]

You may find something you can use by Searching for terms related to radiant-slab heating, which uses similar kinds of routings.

[ahickmang]

It's actually pretty similar to that, I'll check that out.

[MrJSmith]

This looks pretty simple to do. You'd want it to read your excel and pick which one is best and then draw it? Or you just want to enter the numbers and it draws it from your entry?

[ahickmang]

I'd want to be able to pick the numbers and it draw it. Or I would enter the numbers into a separate area and it take those numbers and draw it from there.

[MrJSmith]

What would you enter? The loop amount and total length? Loop amount and length of each loop? Additional things I am not picking up on?

[ahickmang]

Loops, lateral leg length, and number of laterals. So for instance, in that image I linked previous (drip lines from excel), that system would be 20 laterals, 61' lateral leg length, and 3 loops in the lateral. Now, the better way would be to have the number of zones and it would draw a box around each zone, so it would be 5 laterals, 61' lateral leg length, 3 loops in the lateral, 4 zones (5 laterals per zone, 4 zones, 20 total laterals), but I'm not trying to complicate things more than they need to be complicated (at least for now).

[MrJSmith · Accepted solution]

Let's see if this works for you. Tried to write it very basic so you could learn/adapt it to your needs. I hard coded the variables except the insertion point, in the future it would be easy to change it to user input or just grab them from your excel file. I also made it go right to left, though you can do a negative on hortSpacing to do left to right (not sure if it matters though).

(defun c:drawLoops ()
	;Setup Variables
	(setq loops 3) ;Amount of loops
	(setq loopLength 61) ;Vertical length of a loop
	(setq laterals 20) ;Amount of laterals
	(setq hortSpacing 2) ;Spacing between each segment
	(setq vertSpacing 2) ;Vertical spacing from bottom
	
	(setq pt (getpoint "\nSelect Insertion Point To Draw Loops: "))
	(setq loopList nil)
	(setq loopList (cons pt loopList))
	
	;Helper Functions
	(defun addPts (p1 p2) (mapcar '+ p1 p2))
	(defun minusPts (p1 p2) (mapcar '- p1 p2))
	(defun LWPoly (lst cls) ; LM's entmake functions
	  (entmakex 
		(append 
		  (list 
			(cons 0 "LWPOLYLINE")
			(cons 100 "AcDbEntity")
			(cons 100 "AcDbPolyline")
			(cons 90 (length lst))
			(cons 70 cls)
		  )
		  (mapcar (function (lambda (p) (cons 10 p))) lst)
		)
	  )
	)
	
	;Main
	(repeat laterals
		(setq i 0)
		(repeat loops
			(setq i (+ i 1))
			(if (= (rem i 2) 0)
				(progn ;Even loop, moving down
					(setq pt (minusPts pt (list 0 (- loopLength vertSpacing) 0))) ;Go down Loop Length minus vertSpacing
				)
				(progn ;Odd Loop, moving up
					(if (= i 1) 
						(setq pt (addPts pt (list 0 loopLength 0))) ;Go up Loop Length
						(setq pt (addPts pt (list 0 (- loopLength vertSpacing) 0))) ;Go up Loop Length minus vertSpacing
					)
				)
			)
			(setq loopList (append loopList (list pt))) ;Add to the point list
			(setq pt (addPts pt (list hortSpacing 0 0))) ;Go right the amount of spacing
			(setq loopList (append loopList (list pt))) ;Add to the point list
		)
		(setq pt (minusPts pt (list 0 loopLength 0))) ;Go down all the way
		(setq loopList (append loopList (list pt))) ;Add to the point list
		(setq pt (addPts pt (list hortSpacing 0 0))) ;Go right the amount of spacing
		(setq loopList (append loopList (list pt))) ;Add to the point list
	)
	(setq loopList (reverse (cdr (reverse loopList))))
	(LWPoly loopList 0)
)

[ahickmang]

Waoh, you are awesome! Man, I can't believe you just knocked this out for this. Thank you so much. It definitely needs some tweaking, but this is a phenomenal starting point and is most of the way there. It'll also be a good point for me to learn by tweaking this myself. Thanks so much for this. I feel like I need to give you more than just an "Accept Solution"! Just wow!

[MrJSmith]

Thanks! If you need any additional help with the tweaks or want to incorporate the scrapping of your excel file, just let me know :). I also didn't localize any of the variables to allow you to debug it easier after the fact.

[ahickmang]

Yeah, I'm digging into it pretty deep now. Going to go through some tutorials and I think I should be able to go from there. This might be the kick in the pants I've needed to learn Lisp myself. I've got a lot of coding experience from college, just never had the right motivation to dive off the deep end. You've infected me Smith! I appreciate the offer, and I will more than likely take you up on that.

[Sea-Haven]

You can dynamically select a range in EXCEL then bring that range into Acad lisp so selecting the values would mean no typos, the answer to this was provided by the great FIXO. happy to provide code etc. Look at lines 3-7 in your code.

[ahickmang]

Let me take a crack at this bad boy for a couple days and see if I can't figure it out. If I can't figure it out, I'll come crying to you for the answers to my homework problems 😛 Thanks for the suggestion and I'll let you know how it goes!

[MrJSmith]

I'd recommend going based on highlighting of your row, since your excel sheet does multiple calculations. That way you have a visible indicator of which one you picked and you could easily change the highlighting and re-run the script. But either or, tons of ways to skin the cat.

[MrJSmith]

Looks like I've reached the max amount of messages today @ahickmang . You will have to wait until tomorrow for my response xD

[ahickmang]

Didn't realize there was a maximum amount! Brings me back to the good 'ole days, "Tune in next week for the next episode ooooooof...!"