mjm Posted May 20, 2014 Share Posted May 20, 2014 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 Quote Link to comment Share on other sites More sharing options...
BIGAL Posted May 21, 2014 Share Posted May 21, 2014 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. Quote Link to comment Share on other sites More sharing options...
steven-g Posted May 21, 2014 Share Posted May 21, 2014 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 Quote Link to comment Share on other sites More sharing options...
mjm Posted May 21, 2014 Author Share Posted May 21, 2014 (edited) 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 May 21, 2014 by mjm edit text Quote Link to comment Share on other sites More sharing options...
LibertyOne Posted May 21, 2014 Share Posted May 21, 2014 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. Quote Link to comment Share on other sites More sharing options...
mjm Posted May 21, 2014 Author Share Posted May 21, 2014 thanks, liberytone, i will experiment with these when i get it set up Quote Link to comment Share on other sites More sharing options...
BIGAL Posted May 22, 2014 Share Posted May 22, 2014 As each lot has a number it should be able to be done by just changing the hatch layer. Will try to find time. Quote Link to comment Share on other sites More sharing options...
mjm Posted May 23, 2014 Author Share Posted May 23, 2014 I look forward to your imput Quote Link to comment Share on other sites More sharing options...
BIGAL Posted May 24, 2014 Share Posted May 24, 2014 (edited) 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 May 25, 2014 by BIGAL Quote Link to comment Share on other sites More sharing options...
mjm Posted May 27, 2014 Author Share Posted May 27, 2014 Hi BigAl thank you for your imput, how do I go about implementing this code with cad and excelÉ htnaks Quote Link to comment Share on other sites More sharing options...
BIGAL Posted May 28, 2014 Share Posted May 28, 2014 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 Quote Link to comment Share on other sites More sharing options...
mjm Posted May 30, 2014 Author Share Posted May 30, 2014 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 Quote Link to comment Share on other sites More sharing options...
BIGAL Posted May 31, 2014 Share Posted May 31, 2014 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 Quote Link to comment Share on other sites More sharing options...
mjm Posted June 2, 2014 Author Share Posted June 2, 2014 (edited) 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 June 2, 2014 by mjm Quote Link to comment Share on other sites More sharing options...
dgp Posted February 7, 2017 Share Posted February 7, 2017 Has anyone figured this out if it actually works? This is exactly what I am looking for. Quote Link to comment Share on other sites More sharing options...
BIGAL Posted February 8, 2017 Share Posted February 8, 2017 I wrote it and it works did you try it ? Quote Link to comment Share on other sites More sharing options...
dgp Posted February 8, 2017 Share Posted February 8, 2017 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.