+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17
  1. #1
    Forum Newbie
    Using
    AutoCAD 2014
    Join Date
    May 2014
    Posts
    7

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

    Registered forum members do not see this ad.

    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

  2. #2
    Luminous Being
    Using
    Civil 3D 2016
    Join Date
    Dec 2005
    Location
    GEELONG AUSTRALIA
    Posts
    9,721

    Default

    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.
    A man who never made mistakes never made anything

  3. #3
    Forum Deity steven-g's Avatar
    Computer Details
    steven-g's Computer Details
    Operating System:
    Win 7
    Discipline
    Construction
    steven-g's Discipline Details
    Occupation
    Draughtsman
    Discipline
    Construction
    Details
    I work in the estimating department of a large building contractor.
    Using
    AutoCAD 2017
    Join Date
    May 2013
    Location
    An Englishman in Belgium
    Posts
    2,038

    Default

    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
    Attached Files

  4. #4
    Forum Newbie
    Using
    AutoCAD 2014
    Join Date
    May 2014
    Posts
    7

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

    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
    Attached Files
    Last edited by mjm; 21st May 2014 at 07:37 pm. Reason: edit text

  5. #5
    Senior Member LibertyOne's Avatar
    Using
    AutoCAD 2010
    Join Date
    Apr 2011
    Location
    Germany
    Posts
    194

    Default

    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.

  6. #6
    Forum Newbie
    Using
    AutoCAD 2014
    Join Date
    May 2014
    Posts
    7

    Default

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

  7. #7
    Luminous Being
    Using
    Civil 3D 2016
    Join Date
    Dec 2005
    Location
    GEELONG AUSTRALIA
    Posts
    9,721

    Default

    As each lot has a number it should be able to be done by just changing the hatch layer. Will try to find time.
    A man who never made mistakes never made anything

  8. #8
    Forum Newbie
    Using
    AutoCAD 2014
    Join Date
    May 2014
    Posts
    7

    Default

    I look forward to your imput

  9. #9
    Luminous Being
    Using
    Civil 3D 2016
    Join Date
    Dec 2005
    Location
    GEELONG AUSTRALIA
    Posts
    9,721

    Default

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

    Code:
    ; 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)
    Last edited by BIGAL; 25th May 2014 at 01:20 am.
    A man who never made mistakes never made anything

  10. #10
    Forum Newbie
    Using
    AutoCAD 2014
    Join Date
    May 2014
    Posts
    7

    Default

    Registered forum members do not see this ad.

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


    htnaks

Similar Threads

  1. Data Extraction to Excel turns data into dates.
    By arkitectin in forum AutoCAD Drawing Management & Output
    Replies: 2
    Last Post: 8th May 2014, 05:31 pm
  2. Extracting Data from Excel to CAD or CAD to Excel
    By shivakumar in forum AutoCAD LT
    Replies: 20
    Last Post: 3rd Sep 2013, 07:40 pm
  3. Can't edit attribute when inserting excel data that has 3 lines of data
    By Findermb in forum The CUI, Hatches, Linetypes, Scripts & Macros
    Replies: 2
    Last Post: 12th Jun 2013, 02:34 pm
  4. Replies: 2
    Last Post: 5th Jan 2011, 09:40 pm
  5. Data Link Auto Update
    By guitarguy1685 in forum AutoCAD Drawing Management & Output
    Replies: 0
    Last Post: 17th Aug 2010, 07:57 pm

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts