Topic: Generate Index Text from Excel Spreadsheet.

[Willie]

http://cadtips.cadalyst.com/notestext/text-alignment
 
Maybe this will be op help.  If you explode an Mtext, you can align the text.

[danallen]

Again, speaking to tables... our client doesn't tolerate Mtext.   When you explode a table, all the text becomes MTEXT.  I then have to explode it... Assuming the Mtext has an alignment of bottom center, when you explode it the text doesn't move, but by default, the anchor point becomes "LEFT" not center.  I have to manually change the text from "LEFT" to "CENTER" and then re-align all the text to their expected positions.  That's a lot of work for 10 columns by 300 dwgs.  Rea-positioning 3000 pieces of text is not my idea of a fun time.

Is anyone editing the column after it is created since the source is an excel file? If not, does the justification really matter? If it does, express tools has a command to change justification without moving text, TJUST

[ronjonp]

Here's something to pull the text into the drawing from a CSV file matching the properties of a selected piece of text. You still have to cleanup your data in the spreadsheet, but it's a start .

Code - Auto/Visual Lisp: [Select]

1. (defun c:importcsv (/ _d _readcsvfile d file i p text txt x y)
2.   (defun _d (elist / r)
3.     (if (setq r (textbox elist))
4.       (caadr r)
5.     )
6.   )
7.   (defun _readcsvfile (filename / file i line out tmp)
8.     (if (and (= 'str (type filename))
9.              (setq filename (findfile filename))
10.              (setq file (open filename "r"))
11.         )
12.       (progn (while (setq line (read-line file))
13.                (setq tmp nil)
14.                (while (or (setq i (vl-string-search "," line)) (> (strlen line) 0))
15.                  (if i
16.                    (setq tmp  (cons (substr line 1 i) tmp)
17.                          line (substr line (+ 2 i))
18.                    )
19.                    (setq tmp  (cons line tmp)
20.                          line (substr line (1+ (strlen line)))
21.                    )
22.                  )
23.                )
24.                (setq out (cons (reverse tmp) out))
25.              )
26.              (close file)
27.              (reverse out)
28.       )
29.     )
30.   )
31.   (setq i 0.2)
32.   (if (and (setq file (_readcsvfile (getfiled "Select a CSV File" (getvar 'dwgprefix) "csv" 16)))
33.            (setq txt (car (entsel "\nPick text to match properties: ")))
34.            (= "TEXT" (cdr (assoc 0 (setq txt (entget txt)))))
35.            (setq p (getpoint "\nPick a point to place data: "))
36.            (setq y (cadr p))
37.            (setq x (car p))
38.       )
39.     (while file
40.       (setq p (list x y (caddr p)))
41.       (foreach text (mapcar 'car file)
42.         (entmakex (mapcar '(lambda (x)
43.                              (cond ((= (car x) 1)
44.                                     (cons 1
45.                                           (if (= text "")
46.                                             "-"
47.                                             text
48.                                           )
49.                                     )
50.                                    )
51.                                    ((or (= (car x) 10) (= (car x) 11)) (cons (car x) p))
52.                                    (x)
53.                              )
54.                            )
55.                           txt
56.                   )
57.         )
58.         (setq p (list (car p) (- (cadr p) i) (caddr p)))
59.       )
60.       (setq d (apply 'max
61.                      (mapcar '(lambda (x) (_d (subst (cons 1 x) (assoc 1 txt) txt))) (mapcar 'car file))
62.               )
63.             d (+ d
64.                  (apply 'max
65.                         (mapcar '(lambda (x) (_d (subst (cons 1 x) (assoc 1 txt) txt)))
66.                                 (mapcar 'car (setq file (vl-remove 'nil (mapcar 'cdr file))))
67.                         )
68.                  )
69.               )
70.             x (+ (car p) (/ d 1.5))
71.       )
72.     )
73.   )
74.   (princ)
75. )

[DIW_CADtech]

Thank you, I will give this a try Wednessday,  and see what I can do with it...

Many thanks RonJonP

[ronjonp]

Thank you, I will give this a try Wednessday,  and see what I can do with it...

Many thanks RonJonP

Hope it helps you out

[DIW_CADtech]

I just couldn't wait til Wednesday.   

How does the script determine the horizontal location...  I want to modify that to make the gap a bit wider..

I will get some more help with this after, but I will start a new thread.  At first try this looks pretty awesome.

Some things I can likely change... don't need a prompt for the location.. it will always be the same point.  However, wondering if I can set the horizontal locations.  Right now it is pretty darn close, but If I can get that lined up, this may be doable pretty much as is.

[ronjonp]

I just couldn't wait til Wednesday.   

How does the script determine the horizontal location...  I want to modify that to make the gap a bit wider..

I will get some more help with this after, but I will start a new thread.  At first try this looks pretty awesome.

Some things I can likely change... don't need a prompt for the location.. it will always be the same point.  However, wondering if I can set the horizontal locations.  Right now it is pretty darn close, but If I can get that lined up, this may be doable pretty much as is.

The horizontal location is the largest text width of the current column being processed + the largest text width of the next column to be processed divided by 1.5 ( line 70 in the code above ) If you want to make the gaps larger, divide by a smaller number.

Code - Auto/Visual Lisp: [Select]

1. (+ (car p) (/ d 1.25))

[DIW_CADtech]

I will adjust that for now, and start a new thread to see if I can use specific X's.  In this spreadsheet/index for this client the X's will always be the same.

[DIW_CADtech]

Is anyone editing the column after it is created since the source is an excel file? If not, does the justification really matter? If it does, express tools has a command to change justification without moving text, TJUST

Sometimes yes, sometimes No,

This is a constant problem for me.. as the project is supposed to be directed from the log, not the index.

At times, the engineer will take a short cut and update the index, and not the log, or the drawing sheets.  Or, he/she will update the log and not tell any CAD technician.   This is why I want to generate the script.   Per our client the Project log is the bible.  So it doesn't matter to me what anyone has done to the index, I will update it from the project Log(excel) file. 

Manually this can take over an hour to do, but with the script provided by RONJONP this can be accomplished in about 10 min.  It's pretty good.  I would like to cut minutes down to seconds. I will be looking to tweak it a bit further, but the heavy lifting is done.

I would still prefer to go directly to the project EXCEL spreadsheet and skip having to make a CSV file, and skip having to align the text to the proper X values for the columns.  Since the location of the data in the spreadsheet is always exactly the same.

Baby steps. 

[ronjonp]

Is anyone editing the column after it is created since the source is an excel file? If not, does the justification really matter? If it does, express tools has a command to change justification without moving text, TJUST

...
Manually this can take over an hour to do, but with the script provided by RONJONP this can be accomplished in about 10 min.  It's pretty good.  I would like to cut minutes down to seconds. I will be looking to tweak it a bit further, but the heavy lifting is done.
...

Maybe this version will cut your 10 minutes of time down to 2 . I hard coded values based on that example drawing, so all you have to do is select your data.

Code - Auto/Visual Lisp: [Select]

1. (defun c:importcsv (/ _d _maketext _readcsvfile d file i p txt x xlst)
2.   (defun _d (text / r)
3.     (if (setq r (textbox (list '(67 . 0)
4.                                '(8 . "ANNO-TEXT")
5.                                '(100 . "AcDbText")
6.                                '(40 . 0.09375)
7.                                (cons 1 text)
8.                                '(41 . 0.85)
9.                          )
10.                 )
11.         )
12.       (caadr r)
13.     )
14.   )
15.   (defun _maketext (string point)
16.     (entmakex (list '(0 . "TEXT")
17.                     '(100 . "AcDbEntity")
18.                     '(67 . 0)
19.                     '(8 . "ANNO-TEXT")
20.                     '(100 . "AcDbText")
21.                     (cons 10 point)
22.                     '(40 . 0.09375)
23.                     (cons 1 string)
24.                     '(50 . 0.0)
25.                     '(41 . 0.85)
26.                     '(51 . 0.0)
27.                     '(7 . "TEP")
28.                     '(71 . 0)
29.                     '(72 . 1)
30.                     (cons 11 point)
31.                     '(100 . "AcDbText")
32.                     '(73 . 0)
33.               )
34.     )
35.   )
36.   (defun _readcsvfile (filename / file i line out tmp)
37.     (if (and (= 'str (type filename))
38.              (setq filename (findfile filename))
39.              (setq file (open filename "r"))
40.         )
41.       (progn (while (setq line (read-line file))
42.                (setq tmp nil)
43.                (while (or (setq i (vl-string-search "," line)) (> (strlen line) 0))
44.                  (if i
45.                    (setq tmp  (cons (substr line 1 i) tmp)
46.                          line (substr line (+ 2 i))
47.                    )
48.                    (setq tmp  (cons line tmp)
49.                          line (substr line (1+ (strlen line)))
50.                    )
51.                  )
52.                )
53.                (setq out (cons (reverse tmp) out))
54.              )
55.              (close file)
56.              (reverse out)
57.       )
58.     )
59.   )
60.   ;; Create textstyle
61.   (if (not (tblobjname "style" "TEP"))
62.     (entmake '((0 . "STYLE")
63.                (100 . "AcDbSymbolTableRecord")
64.                (100 . "AcDbTextStyleTableRecord")
65.                (2 . "TEP")
66.                (70 . 0)
67.                (40 . 0.09375)
68.                (41 . 0.85)
69.                (50 . 0.0)
70.                (71 . 0)
71.                (42 . 0.125)
72.                (3 . "romans.shx")
73.                (4 . "")
74.               )
75.     )
76.   )
77.   ;; Column spacing
78.   (setq i 0.2)
79.   ;; Starting point
80.   (setq p '(0.45067 9.85 0.0))
81.   ;; Hard coded X values
82.   (setq xlst '(0.45067 1.20067 2.563 4.49174 5.58817 6.20067 7.95067 11.3745 14.4441 15.9507))
83.   (if (setq file (_readcsvfile (getfiled "Select a CSV File" (getvar 'dwgprefix) "csv" 16)))
84.     (while file
85.       (setq p (list (car xlst) 9.85 0.0))
86.       (foreach text (mapcar 'car file)
87.         (setq txt (_maketext (if (= text "")
88.                                "-"
89.                                text
90.                              )
91.                              p
92.                   )
93.         )
94.         (setq p (list (car p) (- (cadr p) i) (caddr p)))
95.       )
96.       (if (setq xlst (cdr xlst))
97.         (setq file (vl-remove 'nil (mapcar 'cdr file)))
98.         (setq d    (apply 'max (mapcar '(lambda (x) (_d x)) (mapcar 'car file)))
99.               d    (+ d
100.                       (apply 'max
101.                              (mapcar '(lambda (x) (_d x))
102.                                      (mapcar 'car (setq file (vl-remove 'nil (mapcar 'cdr file))))
103.                              )
104.                       )
105.                    )
106.               xlst (cons (+ (car p) (/ d 1.25)) xlst)
107.         )
108.       )
109.     )
110.   )
111.   (princ)
112. )

[danallen]

This is a constant problem for me.. as the project is supposed to be directed from the log, not the index.
At times, the engineer will take a short cut and update the index, and not the log, or the drawing sheets.  Or, he/she will update the log and not tell any CAD technician.

(I know this isn't your call, but...) Then why have the log? Seems like a procedural setup ripe for mistake, two sources of data, how do you know you aren't over-writing changes in the CAD file when you import the XLS log? (If the engineer actually does the right thing one day, but not the next.) If the CAD must be the master, perhaps a better system is to have the engineers give you documentation of the specific changes to make in the CAD and not duplicate the CAD in the XLS. I'd recommend talking to the "people in charge", about fixing the procedures so that if you go on vacation, or quit, someone else can easily pick up the changes without knowing custom procedures. (I usually say "if I get hit by a bus" to obfuscate the idea of me quitting). If they understand the hoops that you have to jump through, and the time you spend, perhaps they'll assist with ideas for a solution.

[DIW_CADtech]

Maybe this version will cut your 10 minutes of time down to 2 . I hard coded values based on that example drawing, so all you have to do is select your data.

Testing it out now.

Thanks again RonJonP

[DIW_CADtech]

RonJonP
That is much better...

Each index sheet can only show 50 lines.
I will essentially create each additional sheet from this first sheet.

Is there a way we can make the gap jump 10" instead of .2 on every 51st line?

In that way, I would only have to copy the header down 18" for each sheet. 

I could then do a saveas and just move/adjust  viewports for the additional sheets?

[DIW_CADtech]

One more thing,
How do I add your credentials to the script. 
It needs a proper author.

It should prompt when loaded "Writen by RonJonP from TheSwamp.org 06-21-2016" that would be sweet.

[ronjonp]

...
Is there a way we can make the gap jump 10" instead of .2 on every 51st line?
...

Here you go:

Code - Auto/Visual Lisp: [Select]

1. (defun c:importcsv (/ _d _maketext _readcsvfile d file i n p txt x xlst)
2.   (defun _d (text / r)
3.     (if (setq r (textbox (list '(67 . 0)
4.                                '(8 . "ANNO-TEXT")
5.                                '(100 . "AcDbText")
6.                                '(40 . 0.09375)
7.                                (cons 1 text)
8.                                '(41 . 0.85)
9.                          )
10.                 )
11.         )
12.       (caadr r)
13.       0.
14.     )
15.   )
16.   (defun _maketext (string point)
17.     (entmakex (list '(0 . "TEXT")
18.                     '(100 . "AcDbEntity")
19.                     '(67 . 0)
20.                     '(8 . "ANNO-TEXT")
21.                     '(100 . "AcDbText")
22.                     (cons 10 point)
23.                     '(40 . 0.09375)
24.                     (cons 1 string)
25.                     '(50 . 0.0)
26.                     '(41 . 0.85)
27.                     '(51 . 0.0)
28.                     '(7 . "TEP")
29.                     '(71 . 0)
30.                     '(72 . 1)
31.                     (cons 11 point)
32.                     '(100 . "AcDbText")
33.                     '(73 . 0)
34.               )
35.     )
36.   )
37.   (defun _readcsvfile (filename / file i line out tmp)
38.     (if (and (= 'str (type filename))
39.              (setq filename (findfile filename))
40.              (setq file (open filename "r"))
41.         )
42.       (progn (while (setq line (read-line file))
43.                (setq tmp nil)
44.                (while (or (setq i (vl-string-search "," line)) (> (strlen line) 0))
45.                  (if i
46.                    (setq tmp  (cons (substr line 1 i) tmp)
47.                          line (substr line (+ 2 i))
48.                    )
49.                    (setq tmp  (cons line tmp)
50.                          line (substr line (1+ (strlen line)))
51.                    )
52.                  )
53.                )
54.                (setq out (cons (reverse tmp) out))
55.              )
56.              (close file)
57.              (reverse out)
58.       )
59.     )
60.   )
61.   ;; Create textstyle
62.   (if (not (tblobjname "style" "TEP"))
63.     (entmake '((0 . "STYLE")
64.                (100 . "AcDbSymbolTableRecord")
65.                (100 . "AcDbTextStyleTableRecord")
66.                (2 . "TEP")
67.                (70 . 0)
68.                (40 . 0.09375)
69.                (41 . 0.85)
70.                (50 . 0.0)
71.                (71 . 0)
72.                (42 . 0.125)
73.                (3 . "romans.shx")
74.                (4 . "")
75.               )
76.     )
77.   )
78.   ;; Starting point
79.   (setq p '(0.45067 9.85 0.0))
80.   ;; Hard coded X values
81.   (setq xlst '(0.45067 1.20067 2.563 4.49174 5.58817 6.20067 7.95067 11.3745 14.4441 15.9507))
82.   (if (setq file (_readcsvfile (getfiled "Select a CSV File" (getvar 'dwgprefix) "csv" 16)))
83.     (while file
84.       (setq p (list (car xlst) 9.85 0.0))
85.       (setq n 0)
86.       (foreach text (mapcar 'car file)
87.         (setq n (1+ n))
88.         (setq txt (_maketext (if (= text "")
89.                                "-"
90.                                text
91.                              )
92.                              p
93.                   )
94.         )
95.         (if (= 50 n)
96.           (setq i 10
97.                 n 0
98.           )
99.           (setq i 0.2)
100.         )
101.         (setq p (list (car p) (- (cadr p) i) (caddr p)))
102.       )
103.       (if (setq xlst (cdr xlst))
104.         (setq file (vl-remove 'nil (mapcar 'cdr file)))
105.         (setq d    (apply 'max (mapcar '(lambda (x) (_d x)) (mapcar 'car file)))
106.               d    (+ d
107.                       (apply 'max
108.                              (mapcar '(lambda (x) (_d x))
109.                                      (mapcar 'car (setq file (vl-remove 'nil (mapcar 'cdr file))))
110.                              )
111.                       )
112.                    )
113.               xlst (cons (+ (car p) (/ d 1.25)) xlst)
114.         )
115.       )
116.     )
117.   )
118.   (princ)
119. )