Alignment sheet Profile Data to excel

[bit_Cad2018]

Dear User, 

                 I have 254 Alignment sheet file, i want to make List from Alignment sheet data 

as following

1) Chainage with Ground Level & Some Points Crossing or TP 

Please see Attachment

[hak_vz]

Not an easy task to do.

For a start try to use lisp from this site

https://www.publicnengineers.com/2019/04/how-to-generate-cross-section.html 

[bit_Cad2018]

Thanks For Reply....

This Link Lisp Help for making Profile (Cross Section) I have already Profile Data . I am asking help for export profile data to excel....

[CADmgrMike]

What version of C3D?

I believe the new Project Explorer add-on in C3D 2021 can do reporting like that.

[hak_vz]

Will try to create some function to extract your data to csv. Have an idea on my mind.

Its getting late here at my location, so expect my solution tommorow.

[bit_Cad2018]

@CADmgrMike  Sorry I don't have C3D..

Sorry Mistakenly Accept Solution Clicked.

[bit_Cad2018]

@hak_vz  Thanks For helping me. I am waiting for your solution of this topic. 🙏

[hak_vz · Accepted solution]

@bit_Cad2018  Here is my starting code.

I've created basic code that I will optimize tomorrow and add some more stuff. It doesn't write in Excel but to system console.

It is here to show you how it will work and to point out upon some possible problems.

Exit row to Excel will not be complete if all texts at some station don't have equal distance i.e. text alignment x.

Code asks you to select inside window all station text from section start end, it filters it according to distance from left to right, and creates data that will be later exported to Excel. Select how shown in image.

I hope you know how to run lisp.

(defun c:sef nil (sef))

(defun sef ( / data data1 data2 data3  p1 p2 ss i n ent dist cont e xcords a b c str)

(setq p1 (getpoint "\nSelect first window point (down left) >"))
(setq p2 (getcorner p1 "\nSelect second window point (up right) >"))
(setq ss (ssget "_W" p1 p2 '((0 . "TEXT"))))
(setq i -1 n (sslength ss))
(while (< (setq i (+ i 1)) n)
	(setq ent (entget (ssname ss i)))
	(setq dist (cadr (assoc 11 ent)))
	(setq cont (cdr (assoc 1 ent)))
	(setq data (cons (cons dist cont) data))
)
(setq i -1 )
(while (< (setq i (+ i 1)) n)
	(setq e (nth i data))
	(cond 
		((and (vl-string-position (ascii "+") (cdr e)) (vl-string-position (ascii ".") (cdr e))(< (strlen (cdr e)) 12 )) (setq data1 (cons e data1)))
		((and (not(vl-string-position (ascii "+") (cdr e)))  (< (strlen (cdr e)) 12 )) (setq data2 (cons e data2)))
		((> (strlen (cdr e)) 12 ) (setq data3 (cons e data3)))
	)
)
(setq data1 (vl-sort data1 '(lambda (a b) (< (car a)(car b)))))
(setq data2 (vl-sort data2 '(lambda (a b) (< (car a)(car b)))))
(setq data3 (vl-sort data3 '(lambda (a b) (< (car a)(car b)))))
(setq xcords (mapcar 'car data1))
(foreach x xcords
	(if (setq a (assoc x data1)) (setq a (cdr a)) (setq a nil))
	(if (setq b (assoc x data2)) (setq b (cdr b)) (setq b nil))
	(if (setq c (assoc x data3)) (setq c (cdr c)) (setq c nil))
	(setq str a)
	(if b (setq str (strcat a ";" b)))
	(if c (setq str (strcat a ";" b ";" c)))
	(princ (strcat "\n" str))
	
(setq str nil)

)
(princ)
)

You will receive something like (errors are intentional to show upon problematics:

192+500.00
192+540.23
192+633.63;227.110
192+702.03;230.190
192+794.65;232.900
192+848.27;232.210
192+919.94;228.530
192+994.92;229.670
193+068.40;230.770;CART TRACK  CH. 193+068.40
193+161.98;229.420
193+266.00;229.250;440V POWER LINE 5.2M HEIGHT CH. 193+225.75

[pbejse · Accepted solution]

---

@hak_vz wrote:

 ... it filters it according to distance from left to right,

---

My approach would be pairing the Levels and Chainage based on their position on the profile

Crossings based on their chainage value.

		CR3		CR5
---------------------------------------
LV1	LV2	LV3	LV4	LV5
---------------------------------------
CH1	CH2	CH3	CH4	CH5

Select the Levels and chainage TEXT objects 

Select Crossing TEXT objects. [ If any ]

("192+500.00" "222.170")
("192+540.23" "223.310")
("192+633.63" "227.110")
("192+702.03" "230.190")
("192+794.65" "232.900")
("192+848.27" "232.210")
("192+919.94" "228.530")
("192+994.92" "229.670")
("193+068.40" "230.770" "CART TRACK  CH. 193+068.40")
("193+161.98" "229.420")
("193+225.75" "229.250" "440V POWER LINE 5.2M HEIGHT CH. 193+225.75")

** Added for completeness

(Defun c:PSS (/ _SOrtThis chainColl levelColl ChainAndLevels comments
	       	chainColl levelColl crossing crossingString		) ;; Pair Selection and Some
;;;	 		pBe Oct. 2020			;;;
  (Defun _SOrtThis (l)
    (mapcar 'cdr
	    (vl-sort l '(lambda (a b) (< (car a) (car b))))
    )
  )

(princ "\nSelect LEVELS and CHAINAGE")
  (if
    (setq ChainAndLevels (ssget '((0 . "TEXT") (1 . "*#.#*"))))
     (progn
       (repeat (setq i (sslength ChainAndLevels))
	 (setq ent (entget (ssname ChainAndLevels (setq i (1- i)))))
	 (setq data (cons
		      (cadr (assoc 10 ent))
		      (cdr (assoc 1 ent))
		    )
	 )
	 (if (vl-string-position 43 (Cdr data))
	   (Setq chainColl (cons data chainColl))
	   (setq levelColl (cons data levelColl))
	 )
       )
       (setq ChainAndLevels (mapcar 'list
			      (_SOrtThis chainColl)
			      (_SOrtThis levelColl)
		      )
	     )
       (princ "\nSelect Crossings[Enter for none]")
       (if (setq crossing (ssget  '((0 . "TEXT")(1 . "*#+#*"))))
		(foreach itm (vl-remove-if 'listp
                			(mapcar 'cadr (ssnamex crossing)))
		  (setq crossingString (getpropertyvalue itm "TextString"))
		  (if (setq found (vl-some
			'(lambda (cl)
			   (if (vl-string-search (Car cl) crossingString) cl)
			   )  ChainAndLevels ))
		    (setq ChainAndLevels (subst (append found (list crossingString))
						found ChainAndLevels))
		    )
		  )
	 )
       (foreach	itm ChainAndLevels
	  (print itm)
		    )
       )
     )
  (princ)
  )

Now I need to find a wicked lisp code that writes to existing Excel file 🙂

HTH

[bit_Cad2018]

@hak_vz  and @pbejse  Gave us your precious time and wholeheartedly thank you for your solution.

[hak_vz]

@bit_Cad2018 Throughout the day I'll finish my code. Would output to .CSV file be OK. Excel reads it without any problem.  Do you want csv output to single directory or you will select it for each sheet file separately?

[pbejse]

=== FOR COMPLETENESS ===

What you need

Excel application : --> Microsoft Office

Giles code :------------>  Help with export list to excel [ Not included as attachement, You need to do this your self]

Excel template file:----> REQUIRED LIST Template.xlsx [ refer to attached xlsx file ]

pBe's code : -----------> LevelsChainageAndCrossing.lsp [ refer to attached lsp file ]

Command: PSS
Select LEVELS and CHAINAGE
Select objects: Specify opposite corner: 22 found, 22 total

Select objects:
Select Crossings[Enter for none]
Select objects: Specify opposite corner: 2 found

< user is then prompted to save the collected values to an excel file >

("192+500.00" 222.17)
("192+540.23" 223.31)
("192+633.63" 227.11)
("192+702.03" 230.19)
("192+794.65" 232.9)
("192+848.27" 232.21)
("192+919.94" 228.53)
("192+994.92" 229.67)
("193+068.40" 230.77 "CART TRACK  CH. 193+068.40")
("193+161.98" 229.42)
("193+225.75" 229.25 "440V POWER LINE 5.2M HEIGHT CH. 193+225.75")

Data saved : D:\Downloads\ALG Profile Data.xlsx

---------------------------------------------------------------------------------------

Notes:

Change xlsx template location according to your File and folder naming convention:

;;;		Excel file Template location		;;;
  (setq XcelTemplate "D:\\Downloads\\REQUIRED LIST Template.xlsx")

Comments: [ Layer filter can be added on the code by user ]

LAYERS : I cannot reitereate this enough.  To identify which is what on a drawin, assign the proper layer. Autocad layers are there for a reason. Create a separate layer names for Chainage, Levels and Crossing TEXT objects. 

HTH

Kudos to @_gile for the wicked gc:WriteExcel function

[pbejse]

---

@bit_Cad2018 wrote:

Gave us your precious time and wholeheartedly thank you for your solution.

---

You are welcome @bit_Cad2018 , Glad I could help

The code wiill save the data in Excel format:

Cheers

[hak_vz]

@pbejseYou have done good work. I would just add option to select exit file through dialog.

@bit_Cad2018  Here you have almost complete code, I'll update mine later.

[pbejse]

---

@hak_vz wrote:

@pbejseYou have done good work. I would just add option to select exit file through dialog.

---

What do you mean by exit file?

Are you referring to excel template file? If you are,its a good idea then 👍

@bit_Cad2018 

Change 

	  (findfile XcelTemplate)

To

(or
	  (findfile XcelTemplate)
	  (setq	XcelTemplate
		 (getfiled
		   "Select Template file"
		   (getvar 'dwgprefix)
		   "xlsx;xls"
		   16
		 )
	  )
	  )

HTH

[hak_vz]

@pbejse  Yes that's it. 

[bit_Cad2018]

I Applied all step but after command active not select (Level and Chainage)

[bit_Cad2018]

May be i ll downloaded wrong file. or i applied wrong steps

@hak_vz  Please attach all required file

[pbejse]

---

@bit_Cad2018 wrote:

I Applied all step but after command active not select (Level and Chainage)

---

This from post # 12 

What you need
Excel application : ----> Microsoft Office
Giles code :------------>[ Not included as attachement, You need to do this your self]
Excel template file:---->[ refer to attached xlsx file ]
pBe's code : ----------->[ refer to attached lsp file ]

Anyways

Refer to attached lisp files

• WriteExcel_Gile.LSP
• LevelsChainageAndCrossing.lsp

Appload both files

pBe

[bit_Cad2018]

@pbejse  Thanks you for Help, This Program Extremely Good.

I  Downloaded Wrong WriteCSV.lsp File  

🙏