Jump to content
mjm

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

Recommended Posts

mjm

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

Share this post


Link to post
Share on other sites
BIGAL

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.

Share this post


Link to post
Share on other sites
steven-g

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.

 

EDIT:

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

ColorPlots.dwg

ColorPlots.xls

Share this post


Link to post
Share on other sites
mjm

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

mac.dwg

Edited by mjm
edit text

Share this post


Link to post
Share on other sites
LibertyOne

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.

Share this post


Link to post
Share on other sites
mjm

thanks, liberytone, i will experiment with these when i get it set up

Share this post


Link to post
Share on other sites
BIGAL

As each lot has a number it should be able to be done by just changing the hatch layer. Will try to find time.

Share this post


Link to post
Share on other sites
mjm

I look forward to your imput

Share this post


Link to post
Share on other sites
BIGAL

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 OLDSNAP (GETVAR "OSMODE"))
(SETVAR "OSMODE" 0)
(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 PT1 (LIST (NTH 1 TPT)(NTH 2 TPT)))
(SETQ FPTS (LIST (POLAR PT1 1.57 2 ) PT1)) ; 2 is a dummy value just past text hole
(SETQ HATOBJ (ENTGET (SSNAME (SSGET "f" FPTS '((0 . "HATCH"))) 0)))
(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)
(princ)

Edited by BIGAL

Share this post


Link to post
Share on other sites
mjm

Hi BigAl thank you for your imput, how do I go about implementing this code with cad and excelÉ

 

 

htnaks

Share this post


Link to post
Share on other sites
BIGAL

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

 

lotnum,layername

lotnum,layername

lotnum,layername

 

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

Share this post


Link to post
Share on other sites
mjm

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

Share this post


Link to post
Share on other sites
BIGAL

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

 

Then

appload hatchlots.lsp

 

dwg should be different now

Share this post


Link to post
Share on other sites
mjm

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.

 

thanks

lots.csv

lisp.lsp

Edited by mjm

Share this post


Link to post
Share on other sites
dgp

Has anyone figured this out if it actually works? This is exactly what I am looking for.

Share this post


Link to post
Share on other sites
BIGAL

I wrote it and it works did you try it ?

Share this post


Link to post
Share on other sites
dgp

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?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×