Jump to content

Progress Report - Looking for idea on how to have excel data update colors in cad


Recommended Posts

Hello.. Not familiar with autolisp or scripts



I work for a earthworks company, and we are looking for a way to have an excel worksheet to update progress completion by coloring a property another colour in autocad. Example - lot 1 10% complete, and lot 10 is 30% complete as per excel data. I would like lot 1 (10%) to be light green and lot 10(30%) to be a darker green and darker shades for higher % completion and for the autocad drawing to update as the excel data sheet is updated. I have aproximatly 400 lots to program in this way if possible. Each lot will have 10 conditions (ie 10%, 20%, 30%... to 100%)



Thank you

Link to comment
Share on other sites

Can be done pretty easy, simple way is export excel data to csv. Now hard bit your hatched areas I take it you are hatching need to be found some how, if you have a lot lable then lot10 can be found the insertion pt returned and a ssget used to search a little area at this location for a hatch pattern hopefully also on a individual layer then change hatch pattern colour.


I did say simple to csv a direct read from excel is a lot more code.


Need a sample dwg and say the excel data only a few. Then I am sure an answer will follow. This question has been asked before not sure how to search for it.

Link to comment
Share on other sites

You can do it manually with this script created in excel. In the drawing you need to have each plot hatch on its own layer, with the color set to by layer.

It may be possible to have the excel sheet, data linked to a table in autocad, and a lisp routine to automate the layer update (but I use LT), someone might be able to help with that.



I found the color codes on http://sub-atomic.com/~moses/acadcolors.html



Link to comment
Share on other sites

HI, thanks for your reply. Here is the drawing in question. Each property has a tag identifying the lot #. I have not assigned layer or hatch colours yet as im not sure which would be the most efficient. I have to admit, I used CAD often in the last

ten years, but honestly never had a need to link and lisp programs together until now. Makes me feel old fashioned, haha



I HAVE ATTACHED the cad file and example excel sheet. The controllers would update their own sheet they have created, but shouldn't be a problem to link to this simplified sheet. As you can see there are many properties. And as an excel sheet is updated (by controllers/administration) I would like for a colour on each property to change as data changes in excel sheet, therefore identifying through a legend how much progress is competed on each property. Note, for property blocks (this is like a town house, - many units in one building) they will have 3 to 10 lots per block (tag identifying example block 201-1, 201,2 201-3) All lots in each block will update together



Very much appreciate your insight on this matter

test sheet prgress.xlsx


Edited by mjm
edit text
Link to comment
Share on other sites

I'd personally use colors 10, 20, 30, 40, 50, 60, 70, 80, 90 and 100. It gives you a nice gradient from red to green over yellow.

Link to comment
Share on other sites

This is version 1 it will read file bit it would be lotnum,layername file name eg c:/temp/lisp/lots.csv


; reads a csv file lotnum,layername and changes hatch located a text point
;by Alan H May 2014 thanks also to Lee-mac

;; String to List  -  Lee Mac
;; Separates a string using a given delimiter
;; str - [str] String to process
;; del - [str] Delimiter by which to separate the string
;; Returns: [lst] List of strings
(defun LM:str->lst ( str del / pos )
   (if (setq pos (vl-string-search del str))
       (cons (substr str 1 pos) (LM:str->lst (substr str (+ pos 1 (strlen del))) del))
       (list str)

(defun HATLOT (lotnum col / HATOBJ FPTS PT1 TPT )
(setq lotfound (ssget "X" (LIST (CONS 0 "MTEXT")(CONS 1 lotnum)(CONS 8 "C-BLDG-NMBR"))))
(SETQ TPT (ASSOC 10 (ENTGET (ssname lotfound 0))))
(SETQ FPTS (LIST (POLAR PT1 1.57 2 ) PT1)) ; 2 is a dummy value just past text hole
(ENTMOD (subst col (assoc 8 HATOBJ) HATOBJ))
; this is where you would loop through the lots csv from excel number - color
; next version
(setq fopen (open (getstring "\nEnter file name") "R"))
(while (setq strline (read-line fopen))
(setq retstr (LM:str->lst strline ","))
(setq lotnum (nth 0 retstr)) ; read from file
(SETQ COL (nth 1 retstr)) ; read from file
(HATLOT lotnum col)
) ; end while

(setvar "osmode" oldsnap)

Edited by BIGAL
Link to comment
Share on other sites

Copy and paste the code into notepad save as say Lothatch.lsp the data file is just as per example below, then Appload choose Lothatch.lsp






106,10% built


It works on your dwg do you know how to create this simple file from excel ?


I could have done more re finding file but thats what learning is about search for FINDFILE examples

Link to comment
Share on other sites

Hello CAD wizards. Thank you all for you insight and help. I am presently researching and slowly teaching myself LISP. As for the code kindly submitted above by "BIGAL" with help from "lee-mac", im not sure how to implement this. Would this code be for cad itself or for excel, or perhaps both. Perhaps in time I will understand this further, but with time constraints I'm hoping someone may have the time to review what has been suggested and give me some direction as to implementing such a code.



Look forward to your imput

Link to comment
Share on other sites

Ok save code to notepad change the 1 line to this (setq fopen (open "c:/users/test.csv" "R")) and save as a lisp file hatchlots.lsp


Copy this to notepad and save as c:\users\test.csv

106,10% complete
107,20% complete
108,30% complete
109,40% complete



appload hatchlots.lsp


dwg should be different now

Link to comment
Share on other sites

Hello bigal, I created a csv file as instructed with one column lotnum and one column as layer name. I then created a mm.lsp using the code you provided but have been unable to get this to work. after appload I chose the lisp file, then cad ask for file name Am I missing a step or perhaps misunderstand some of your direction?

I have attached the csv file (created in excel) and the lisp file for your review.





Edited by mjm
Link to comment
Share on other sites

  • 2 years later...

I followed the steps from your posts above, however I'm having the same issues as mjm. I appload the lisp file, then it says "Unable to recognize command "hatchlots.lsp" Please Try again" It Then asks for a file name, and when i click on that it says bad argument type. Not sure whether I'm missing a step or something? should the tagged lots in the drawing change colors for the specific ones used in the test csv file?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...