; ** 01-Dec-2008 - sample program based on example created by Autodesk's Pat Murnen ; ----------- P I D _ P I C K . L S P ------------ ; ; This function called from within Circuit Builder when a motor symbol has just been inserted. ; It opens an Excel file containing data extracted from a drawing such as a P&ID. This data is ; displayed in a small dialog. User picks an entry in this dialog and hit's OK. Selected entry's ; data is then pushed out to specific TAG1 and RATINGx attributes on the just-inserted motor ; symbol. (defun c:pid_pick ( xlsfnam / cancel dcl_id dclnam dsplst ff fields fldnamstr fnam lst recs rtrn xlshdl en tabnam xlsfnam ben newlst data x) ; NOTE: do NOT declare #hdl_list as a local variable here. It is a global Lisp variable that ; is set by Circuit Builder's "insert component" API call. (setq rtrn nil) (setq recs nil) (setq ben nil) (setq fnam nil) ; First, test to see if Circuit Builder has just inserted a component. The global "#hdl_list" ; should be non-nil and should contain a valid handle as the first element of the list. (if (OR (not #hdl_list) (not (car #hdl_list)) ; #hdl_lst does not exist (not (setq ben (handent (car #hdl_list))))) ; big assumption that first block is our target (progn ; problem, no global exists or doen't carry valid handle (princ "\nProblem: could not find handle of recently inserted component\n") ) ; ELSE (progn ; Have what appears to be the recently inserted component. Okay to continue. ; Step #1 - open up the spreadsheet containing the P&ID data. Read in the data and ; display in a pick list. ; read in the spreadsheet with the extracted P&ID data (if (not xlsfnam)(setq xlsfnam (getfiled "P & ID Extracted Data Spreadsheet" "pid_extract.xls" "XLS" 0))) ) ) (if xlsfnam (progn ; A spreadsheet filename identified. Try to open it and read it in. (if (/= (strcase (substr xlsfnam (- (strlen xlsfnam) 2))) "XLS") (setq xlsfnam (strcat xlsfnam ".xls")) ) (if (not (setq fnam (findfile xlsfnam))) ; look for the XLS file in normal Acad path list ; Not found. Keep looking. (setq fnam (findfile (strcat GBL_wd_usr xlsfnam))) ; look for it in AcadE "User" folder ) (if (not fnam) (progn ; could not find the target spreadsheet file (princ "\nCannot find ") (princ xlsfnam) ) ; ELSE (progn ; open it and read it in (if (AND (setq xlshdl (wd_mdb_gethandle 2)) ; get next file handle (setq ff (wd_mdb_Open xlshdl fnam "Excel 5.0;HDR=YES")) ; open as Excel file (setq tabnam (wd_mdb_GetTabNames xlshdl)) ; list of sheet names (setq tabnam (car tabnam))) ; use first or only sheet defined in xls file (progn ; Excel file exists and was able to be opened. Read in the list of ; column field names pulled from the target sheet. (setq fields (wd_mdb_GetFieldSpecs xlshdl tabnam)) ; Assuming columns in the sheet are in the expected order ( ! ) (if fields (progn ; only pulling out 2nd (Tag) and 4th (Description) fields and 6th (Rating1) (setq fields (car fields)) (setq fldnamstr (strcat (car (nth 1 fields)) "," (car (nth 3 fields)) "," (car (nth 5 fields)))) ) ; else (setq fldnamstr "Tag,Description,Equipment Spec") ) ; Read in all the data from the first sheet of the spreadsheet (setq recs (wd_mdb_GetRecs xlshdl tabnam fldnamstr "" "")) (wd_mdb_Close xlshdl) ; close the spreadsheet file (setq xlshdl nil) ; Now process the returned spreadsheet data dump (setq dsplst '()) (if recs (progn ; some spreadsheet data returned ; clean up the returned data. It consists of dotted pairs with first part ; being a copy of the column name. Strip off the column names leaving just ; a list of sublists of row data. (setq data recs) (setq recs nil) (foreach lst data (setq newlst '()) (foreach x lst (setq x (cdr x)) (if (= x " ") (setq x "")) (setq newlst (cons x newlst)) ) (setq recs (cons (reverse newlst) recs)) ) (setq recs (reverse recs)) ; put back into original order ; Format the cleaned spreadsheet data into a display list of data (foreach lst recs ; display format is different order than actual data. It needs to be a ; list of concatenated text strings with TAB characters appropriately ; positioned. (setq dsplst (cons (strcat (car lst) "\t" (nth 2 lst) "\t" (cadr lst)) dsplst)) ) (setq dsplst (reverse dsplst)) ; restore original order ) ) ; Look for dcl file of same name, open if found. (setq cancel nil) (if (AND dsplst (setq dclnam (c:ace_find_file "pid_pick.dcl" 16))) (progn ; 16 bit set =display error dialog if file not found (setq dcl_id (load_dialog dclnam)) (if (new_dialog "main_select" dcl_id) (progn ; successfully found and opened the DCL file and dialog definition (set_tile "main_title" "P & ID Equipment List") ; Push the spreadsheet's formatted data list out to the display (start_list "dsplst") (mapcar 'add_list dsplst) (end_list) ; Set up to allow user to select a row of data from the display list (action_tile "dsplst" "(setq rtrn (nth (atoi $value) recs))") (action_tile "cancel" "(setq cancel 1)") ; "Cancel" button (start_dialog) (unload_dialog dcl_id) ; user exits dialog with OK or cancel ) ) ) ) (if (AND (not cancel) rtrn) (progn ; user didn't cancel out of dialog, okay to continue (if (AND (car rtrn) (/= (car rtrn) "")) (progn ; returned non-blank tag-ID value (c:ace_mod_tag_attrval ben (car rtrn) nil) ; push tag-ID out to TAG1/TAG2 ; Mark the tag as "fixed" (c:ace_tag_fixed en 1 nil) ) ) ; Push data out to DESC1 and RATING1 attributes (if (AND (cadr rtrn) (/= (cadr rtrn) "")) (c:wd_modattrval ben "DESC1" (cadr rtrn) nil)) (if (AND (nth 2 rtrn) (/= (nth 2 rtrn) "")) (c:wd_modattrval ben "RATING1" (nth 2 rtrn) nil)) ) ) ) ) ) ) ) ) (princ) )