topic Re: Alignment sheet Profile Data to excel in Visual LISP, AutoLISP and General Customization Forum

Alignment sheet Profile Data to excel

bit_Cad2018 — Fri, 16 Oct 2020 18:54:05 GMT

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

Re: Alignment sheet Profile Data to excel

hak_vz — Fri, 16 Oct 2020 19:29:45 GMT

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

Re: Alignment sheet Profile Data to excel

bit_Cad2018 — Fri, 16 Oct 2020 19:35:42 GMT

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....

Re: Alignment sheet Profile Data to excel

CADmgrMike — Fri, 16 Oct 2020 19:48:08 GMT

What version of C3D?

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

Re: Alignment sheet Profile Data to excel

hak_vz — Fri, 16 Oct 2020 19:49:29 GMT

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.

Re: Alignment sheet Profile Data to excel

bit_Cad2018 — Fri, 16 Oct 2020 19:53:09 GMT

@CADmgrMike Sorry I don't have C3D..

Sorry Mistakenly Accept Solution Clicked.

Re: Alignment sheet Profile Data to excel

bit_Cad2018 — Fri, 16 Oct 2020 19:57:27 GMT

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

Re: Alignment sheet Profile Data to excel

hak_vz — Fri, 16 Oct 2020 21:56:26 GMT

@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

Re: Alignment sheet Profile Data to excel

pbejse — Sat, 17 Oct 2020 05:35:35 GMT

@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

Re: Alignment sheet Profile Data to excel

bit_Cad2018 — Sat, 17 Oct 2020 07:26:16 GMT

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

Re: Alignment sheet Profile Data to excel

hak_vz — Sat, 17 Oct 2020 07:45:03 GMT

@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?

Re: Alignment sheet Profile Data to excel

pbejse — Sat, 17 Oct 2020 07:45:48 GMT

=== 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

Re: Alignment sheet Profile Data to excel

pbejse — Sat, 17 Oct 2020 07:53:04 GMT

@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

Re: Alignment sheet Profile Data to excel

hak_vz — Sat, 17 Oct 2020 07:54:20 GMT

@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.

Re: Alignment sheet Profile Data to excel

pbejse — Sat, 17 Oct 2020 08:11:29 GMT

@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)

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

HTH

Re: Alignment sheet Profile Data to excel

hak_vz — Sat, 17 Oct 2020 08:16:45 GMT

@pbejse Yes that's it.

Re: Alignment sheet Profile Data to excel

bit_Cad2018 — Sat, 17 Oct 2020 08:20:27 GMT

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

Re: Alignment sheet Profile Data to excel

bit_Cad2018 — Sat, 17 Oct 2020 08:31:57 GMT

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

@hak_vz Please attach all required file

Re: Alignment sheet Profile Data to excel

pbejse — Sat, 17 Oct 2020 09:15:48 GMT

@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

Re: Alignment sheet Profile Data to excel

bit_Cad2018 — Sat, 17 Oct 2020 09:31:13 GMT

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

I Downloaded Wrong WriteCSV.lsp File

🙏