Best way to desing parts from excel

[djanev]

Hello,

 

I have read a few posts on this message board about importing dimensions from excel into Auto CAD and using those dimensions to draw part. I understand that there are a few methods to achieve this task such as Auto-lisp, Auto CAD Script or Macro's.

 

Our parts are laser cut sheet metal.

We use 2 layers on our dwg's. One is used for the cut layer which is layer 0 and the other layer is called SCRIBE, which we use to laser etch information on the parts such as part numbers, bend lines, and routing in the manufacturing plant. We also have geometries that are laser etched to show to markings for welding locations.

 

I have attached an example of our part that contains geometries on the cut and scribe etch layer. Any help would be appreciated.

 

part example.dwg an example of our part.

[BIGAL]

I had a look and pretty simple

 

eg excel

Border offset1 offset2 L W

rndholes offset1 offset2 R

rndholes offset1 offset2 R

rndholes offset1 offset2 R

slots offset1 offset2 L W

slots offset1 offset2 L W

slots offset1 offset2 L W

slots offset1 offset2 L W

 

There are plenty of free excel to autocad stuff google "Getexel.lsp" you need to do some home work though you need say for a start 3 lisp defuns (mini programs in one bigger one) Slot, border, hole as you read the xecel row by row it runs one of the mini defuns and draws the object pretty easy so far.

 

Now the complex how is your excel created does it have lots of "work it outs" within it ? If not why not just use lisp to work it all out by creating more little defuns.

 

I suggest next step is post a copy of your excel or explain more what it does and how it looks even images will be Ok.

[Bill Tillman]

I've been doing this very type of programming. In my case at least I found GetExcel to be very helpful but it was rather slow. So I opted for some vba code within the Excel file which created a simple text file with the needed information and dimensions. You can then use this same vba code to launch AutoCAD and run a LISP program which will read the text file and prepare your drawing.

[djanev]

The excel file will have a dimension for length, height and the wx and wy dimensions of the cutout locations. The excel file will also have some text for job and part number to be placed on an etching layer.

 

 

[ReMark]

Sounds like a topic just begging for a tutorial.

[djanev]

A tutorial would be a life savoir. Lol. Thanks for the posts, now I have some direction.

[fixo]

Upload your Excel file

Cheers

[BIGAL]

Hi Bill a lot of people are not aware about VBA in excel, rather than write a txt file maybe write a script then dont need a lisp. The lisp is still handy if the txt file comes from some one else.

[irneb]

Hi Bill a lot of people are not aware about VBA in excel, rather than write a txt file maybe write a script then dont need a lisp. The lisp is still handy if the txt file comes from some one else.

VBA in Ecxel 2007+ is not always available - similar to ACad you need to install it separately. Do you perhaps mean VSTA using VB.Net / C#? Or do you mean ActiveX from AutoLisp?

[irneb]

Sounds like a topic just begging for a tutorial.

Challenge accepted

 

I'm going through the "simplest" way to do this - i.e. CSV. Save your XLS file as a CSV (Comma Separated file). Now in Lisp it's rather trivial to convert a CSV file into a list of values:

;;; Replace all instances of a string part in a source string with a new string part
(defun str-subst-all  (new old source / pos)
 (setq pos 0)
 (while (setq pos (vl-string-search old source pos))
   (setq source (vl-string-subst new old source pos)
         pos    (+ pos (strlen new))))
 source)

;;; Convert a delimeted string into a list of strings / numbers
(defun Delim->Lst  (source delim)
 (mapcar '(lambda (str)
            (if (wcmatch str "*[~0-9.`-]*")
              str
              (read str)))
         (read (strcat "(\"" (str-subst-all "\" \"" "," source) "\")"))))

;;; Read a delimeted text file into a list of sublists of strings
(defun Read-Delim->Lst  (filename delim / file line result)
 (if (setq file (open filename "r")) ;Open file for reading
   (progn (while (setq line (read-line file)) ;Read each line from file
            (setq result (cons (Delim->Lst line delim) result))) ;Add converted list into result
          (close file))) ;Close the file
 (reverse result))

Also you need some way of ensuring that the layers exist and are not frozen or locked:

;;; Ensure a Layer exists and is thawed & unlocked
(defun MakeLayer  (Name / Data)
 (if (not (setq Data (tblsearch "LAYER" Name)))
   (entmake (list '(0 . "LAYER")
                  '(100 . "AcDbSymbolTableRecord")
                  '(100 . "AcDbLayerTableRecord")
                  (cons 2 Name)
                  '(62 . 7)
                  '(70 . 0)))
   (entmod (subst '(70 . 0) (assoc 70 Data) Data))))

Now for the main part of actually drawing the cutsheet - I've assumed some sizes as constants. If incorrect these need to be specified inside the XLS or otherwise simply adjust in this defun as needed.

;;; Draw a cut sheet from values
(defun DrawCutSheet  (Part# L H WX WY / CutLayer EtchLayer HoleWidth HoleHeight TextHeight)
 (setq CutLayer "0"
       EtchLayer "1"
       HoleWidth 50.0
       HoleHeight 100.0
       TextHeight 50.0)
 (or *Draw:Offset* (setq *Draw:Offset* 0.0))
 (mapcar 'MakeLayer (list CutLayer EtchLayer)) ;Ensure required layers exist and is thawed & unlocked
 ;; Make outline of sheet
 (entmake
   (append '((0 . "LWPOLYLINE") (100 . "AcDbEntity") (67 . 0))
           (list (cons 8 CutLayer))
           '((100 . "AcDbPolyline") (90 . 4) (70 . 1))
           (mapcar '(lambda (x y) (list 10 x y))
                   (list (+ 0.0 *Draw:Offset*) (+ L *Draw:Offset*) (+ L *Draw:Offset*) (+ 0.0 *Draw:Offset*))
                   (list 0.0 0.0 H H))
           '((210 0.0 0.0 1.0))))
 ;; Make hole in sheet
 (entmake (append '((0 . "LWPOLYLINE") (100 . "AcDbEntity") (67 . 0))
                  (list (cons 8 CutLayer))
                  '((100 . "AcDbPolyline") (90 . 4) (70 . 1))
                  (mapcar '(lambda (x y) (list 10 x y))
                          (list (+ WX *Draw:Offset*)
                                (+ WX *Draw:Offset* HoleWidth)
                                (+ WX *Draw:Offset* HoleWidth)
                                (+ WX *Draw:Offset*))
                          (list WY WY (+ WY HoleHeight) (+ WY HoleHeight)))
                  '((210 0.0 0.0 1.0))))
 ;; Make etched text
 (entmake
   (append '((0 . "TEXT") (100 . "AcDbEntity") (67 . 0))
           (list (cons 8 EtchLayer) '(100 . "AcDbText") (list 10 *Draw:Offset* 0.0 0.0))
           (list (cons 40 TextHeight) (cons 1 Part#) '(50 . 0.0) (cons 7 (getvar "TextStyle")))
           '((72 . 0) (210 0.0 0.0 1.0) (100 . "AcDbText") (73 . 0))))
 (setq *Draw:Offset* (+ *Draw:Offset* L 100.0)))

Notice the *Draw:Offset* global variable? I'm using that to draw each consecutive sheet 100 units to the right of the previous one - just so they're not drawn directly on top of each other.

 

The first portion [ (setq ... ] is simply where I set the assumed values, initialize the *Draw:Offset* to start at X=0.0, as well as ensuring the Cut and Etch layers are available for editing.

 

The Make outline of sheet portion draws a rectangle polyline from the data, the Make hole in sheet does similar but for the hole placed in the WX/WY position and using the assumed sizes of HoleWidth & holeHeight. Then I create a dtext at the lower left corner of the sheet - you don't specify where you want it so I assumed the easiest position using an assumed text height and using the current text style. The last line sets the *Draw:Offset* global variable to the start position of the next sheet - ready for the next call of this function.

 

Now to combine all of these together into one command:

(defun c:DrawCutSheet  (/ file data)
 (if (and (setq file (getfiled "Select CSV file" (getvar "DwgPrefix") "csv" (+ 4 8 16)))
          (setq data (Read-Delim->Lst (findfile file) ",")))
   (progn (setq *Draw:Offset* nil) (foreach line (cdr data) (apply 'DrawCutSheet line))))
 (princ))

Here I check if the file is selected by the user. The dialog defaults to the same folder as the DWG. If the file is selected I check if the data can be read from the file.

 

I clear the global variable so it starts at 0,0,0. Then I step through each line in turn, omitting the 1st (which is the header). For each of these lines I send the data to the DrawCutSheet function.

 

Attached is the LSP with all these defuns included as well as the testing CSV I created. If your XLS's format is different, you'd need to account for that.

DesignParts.LSP

DesignParts.csv

[BIGAL]

My $0.05 worth I would approach it slightly differently read csv line make a single "list" of it

eg Hole L W Offx Offy v's CIRC Rad offx offy V's Border L W offx offy & Text "text" Offx offy Rot

This is a little defun that keeps going to end of line inside while EOF loop.

 

Look at 1st variable in list this decides say using a Cond which of small multiple defuns you run and knows how many variables to expect. some only have 3 others may have 5+

 

I would also go for using VL-addcircle addPolyline etc save a few lines of code compared to entmake these could sit in a master autoloaded lisp so available across all lisps.

 

As the little defuns are known objects the layer required could be hard coded into the defun or even read from the excel as well and like you make sure layer exists.

 

The advantage in using defuns is you can add more pretty easy like "round slot"

 

We still need Janev to post excel

[djanev]

Wow, thanks for all the help. The example was exactly what I needed.

[djanev]

Is there there a way I can have the text exploded by the *.lsp file, we use autocad express tools to explode text. I believe the command is TXTEXP.

 

thanks