Topic: Reading a chart

[ronjonp]

How would I begin to read values from a selected Excel column file based on 2 user inputs in lisp?

Enter Pipesize: 1

Enter Flow: 3

And this would extract 0.87 and 0.14.....




Thanks,

Ron

[whdjr]

Check out David Stein's website DSXCad and another link on his site leads to some excellent excel and visual lisp Topics.

[CarlB]

You might also consider doing the calculations for velocity and psi loss in the lisp routine.  The equation for velocity is simply flowrate divided by the cross-sectional area.  The pressure loss has a little more to the calculation but is not too lengthy.  You would need some data stored in the routine, such as actual pipe diameter given the nominal pipe size.

[StarGazer]

But... is there a way of doing a lookup like this between Autocad and Excel? I can think of a few tables that I have in Excel that I would like to automate with lisp or vba.

Thomas

[SPDCad]

You could use a multi-level condition;

Code: [Select]

(cond ((= PIPE .75)
(cond (= FLW 1)(etc...) );
     (= FLW 2)(etc...) );
     (= FLW 3)(etc...) );
     etc.
        );
      );end cond pipe=.75
      ((= PIPE 1)
(cond (= FLW 1)(etc...) );
     (= FLW 2)(etc...) );
     (= FLW 3)(etc...) );
     etc.
        );
      );end cond pipe=1
      ((= PIPE 1.25)
(cond (= FLW 1)(etc...) );
     (= FLW 2)(etc...) );
     (= FLW 3)(etc...) );
     etc.
        );
      );end cond pipe=1.25
);end cond


You could right a lisp if you were daring that would read a ‘csv’ which you generate with excel and have the lisp retrieve the data file (‘csv’) and look up the data you need based on the input you receive. If you are familiar with the formulas in excel, then you have the basic principals to setup a similar lisp programme. {ie. @vlookup(Cell,Data range, column)}
I have just  completed a lisp that looks up the equivalent window manufactures windows based on a generic size I have already specified. The lisp allows me to specify a specific manufacture for my plans based on the client I have.

[Mark]

You could right a lisp if you were daring that would read a ‘csv’ ...

I was thinking the same thing, only I'd use Python.

All in all, good idea if you ask me.

[CAB]

Because the data is fixed I would just hard code it like this:

Code: [Select]

(defun flow (gpm size / data)
  ;;  returns a list of Velocity & Loss
  ;;  gpm must be an interger
  ;;  size must be a real number
  ;;  returns nil if not found
  (setq data
         '((0.75 ; pipe size
            (0.47 0.06) ; 1 GPM
            (0.94 0.22) ; 2 GPM
            (1.42 0.46) ; 3 GPM
            (1.89 0.79)
            (2.36 1.19)
           )
           (1
            (0.29 0.02)
            (0.58 0.07)
            (0.87 0.14)
            (1.15 0.24)
            (1.14 0.36)
           )
           (1.25
            (0.18 0.01)
            (0.36 0.02)
            (0.54 0.04)
            (0.72 0.08)
            (0.90 0.12)
            )
          )
  )
  (nth gpm (assoc size data))
)

(defun c:test(/ lst)
  (setq lst (flow 3 1)) ; 3 gpm 1" pipe
  (prompt
    (strcat "\nFor a 1 inch pipe flowing 3 Gpm the Velocity is "
            (rtos (car lst) 2 2)
            " and the loss is "
            (rtos (cadr lst) 2 2)
          )
    )
  (princ)
)

Although it would be easy to read the data from a text file as well.
I think th problem with that chart in excel is the header info you will have to weed
through to get the pipe size. Well if you new which column belonged to which size you
could overcome that too.
Just a matter of how you want to solve the problem.
Send me the excel file if you want.

[whdjr]

But... is there a way of doing a lookup like this between Autocad and Excel? I can think of a few tables that I have in Excel that I would like to automate with lisp or vba.

Thomas

Yes!  I have a lisp file at my office that reads the layers from AutoCAD and then creates an Excel file, writes the data directly to Excel, and does different cell formats as well.  All from Visual Lisp with in AutoCAD 2000.  You can read from Excel as well.

You could right a lisp if you were daring that would read a ‘csv’ which you generate with excel and have the lisp retrieve the data file (‘csv’) and look up the data you need based on the input you receive.

No need to create the csv file just access Excel directly.  See previous post for links to DSXCAD.

[Jimmy D]

Hi Ronjonp,

I wouldn't recommend reading/writing to/from Excel.
The programming itself isn't so difficult, I've done it with lisp and VBA.
The problem I have is that most of the time I'm not able to close Excel (see also David M. Stein's bible on this matter).
I believe it is best to:
or calculate the necessary data,
or put the data directly in the lisp,
or extract a .txt file from the .xls file that you can then easily read in lsp.

(using ACAD2002 + XP)