Bhull1985 Posted July 28, 2014 Share Posted July 28, 2014 (edited) Hey everyone, I've had to add a ton of data into this cad-to-excel program I've been working on for the last long while... Here's example of the data list that is now hardcoded into the routine (setq x1tr-t1 (list "Valve, Gate, NPT, 800#, A352-LCB/LCC or A350-LF2 Body, SS Wedge, HF-SS Seat, API Trim 10, 12 or 16, HW Op., Reduced Port, Bolted Bonnet- OS&Y, Graphite Packing, Solid Wedge, API-600 & 598, ASME B16.34 & B16.10, 500ppm VOC" "150" "1100" "quoting 1/2\" Newco 18T- LF24/3NCRP")) (SETQ X1TR-T1-2 (LIST "Valve, Gate, NPT, 800#, A-182 F316/A-351Gr.CF8M Body, SS Stem/Wedge/Seat/Bonnet, API Trim 10, 12 or 16, HW Op., Red. Port, Sealed SS Bolted Extended Bonnet, Graphite/TFE Packing, Flex Wedge, API-600 & 598, ASME B16.34 & B16.10, 500ppm VOC" "250" "550" "DSI 4121F12-18")) (SETQ X1TR-T1-3 (LIST "Valve, Floating Ball, NPT, 2000#, A-216Gr.WCB Body, SS Stem/Ball, PTFE Seats, -20 to 400 F, Lever Op., Red. Port, Hydrocarbon Trim, Anti-blowout Stem, API-607, ASME B16.34, 500ppm VOC, Bubble Tight Seal" "250" "550" "DSI 4121F12-18")) (SETQ X1TR-T1-4 (LIST "Valve, Trunnion Ball, 300# RF, A-216Gr.WCB/A-105 Body, SS Stem/Ball, Hydrocarbon Compatible Seat/O-Rings/Packing, -20 to 400 F, Gear Op., Red. Port, Anti-blowout Stem, API-607, ASME B16.34 & B16.10, 500ppm VOC, Bubble Tight Seal" "250" "550" "DSI 4121F12-18")) (SETQ X1TR-T1-5 (LIST "Valve, Globe, SW, 800#, A352-LCB/LCC or A350-LF2 Body, SS Disc, HF- SS Seat, MS Stem, API Trim 10, 12 or 16, 800#, -50 to 800 F, HW Op., Bolted Bonnet-OS&Y, Graphite Packing, API-602 & 598, ASME B16.34 & B16.10, 500ppm VOC" "250" "550" "Newco 18TLF24/3NCRP")) (SETQ X1TR-S2L (LIST "Valve, Piston Check, NPT, A-182 F316/A-351Gr.CF8M Body, SS Piston/Disc, SS Spring, API Trim 10, 12 or 16, 800#, -20 to 800 F, Bolted Bonnet, Std. Port, Renew. Seats, API-602 & 598, ASME B16.34" "150" "1100" "OIC 8GT6LUSEG")) (SETQ X1TR-2SL-2 (LIST "Valve, Floating Ball, 300# RF, A-216Gr.WCB/A-105 Body, SS Stem/Ball, Hydrocarbon Compatible Seat/O-Rings/Packing, -20 to 400 F, Lever Op. \(Gear Op>8\" 300#\), Full Port, Anti-blowout Stem, API-607, ASME B16.34 & B16.10, 500ppm VOC, Bubble Tight Seal" "150" "1100" "OIC 8GT6LUSEG")) (SETQ X1TR-S2 (LIST "Valve, Gate, NPT, 800#, A-182 F316/A-351Gr.CF8M Body, SS Stem/Wedge/Seat/Bonnet, API Trim 10, 12 or 16, HW Op., Red. Port, Sealed SS Bolted Extended Bonnet, Graphite/TFE Packing, Flex Wedge, API-600 & 598, ASME B16.34 & B16.10, 500ppm VOC" "150" "1100" "OIC 8GT6LUSEG")) (SETQ X1TR-S2-2 (LIST "Valve, Piston Check, NPT, A-182 F316/A-351Gr.CF8M Body, SS Piston/Disc, SS Spring, API Trim 10, 12 or 16, 800#, -20 to 800 F, Bolted Bonnet, Std. Port, Renew. Seats, API-602 & 598, ASME B16.34" "150" "1100" "OIC 8GT6LUSEG")) (SETQ X4S-C1 (LIST "Valve, Gate, NPT, 800#, A-182 F316/A-351Gr.CF8M Body, SS Stem/Wedge/Seat/Bonnet, API Trim 10, 12 or 16, HW Op., Red. Port, Sealed SS Bolted Extended Bonnet, Graphite/TFE Packing, Flex Wedge, API-600 & 598, ASME B16.34 & B16.10, 500ppm VOC" "175" "1100" "OIC 8GL6LUSWG-EB")) (SETQ X4S-C1-2 (LIST "Valve, Butterfly, Insert Type, 300# RF, A-182 F316/A-351Gr.CF8M Body Extended Stem, SS, 17-4 PH or Inconel Shaft/Disc/Pins/Retainers/Rings, Cryogenic Trim to -320 F or Specified, PTFE or Kel-F Packing, ASME B16.34, API-609, Offset to be Specified by Engineer" "175" "1100" "OIC 8GL6LUSWG-EB")) (SETQ X4S-C1-3 (LIST "Valve, Swing Check, SW, 800#, A-182 F316/A-351Gr.CF8M Body, SS Disc/Pins, API Trim 10, 12 or 16, Cryogenic Trim to -320F unless otherwise indicated, SS Bolted Bonnet, Std. Port, Renew. Seats, API-600 & 598, ASME B16.34 & B16.10" "175" "1100" "OIC 8GL6LUSWG-EB")) (setq X4S-H1 (list "Valve, Globe, SW, 800#, A-105 Body, CS/13Cr Disc, HF-CS Seat, MS Stem, API Trim 5 or 8, -20 to 800 F, HW Op., Bolted Bonnet-OS&Y, Graphite Packing, Renew, Seats, API-602 & 598, ASME B16.34, 500ppm VOC" "175" "1100" "DSI 4211A8-18")) (setq X4S-H2 (list "Valve, Globe, SW, 800#, A-105 Body, CS/13Cr Disc, HF-CS Seat, MS Stem, API Trim 5 or 8, -20 to 800 F, HW Op., Bolted Bonnet-OS&Y, Graphite Packing, Renew, Seats, API-602 & 598, ASME B16.34, 500ppm VOC" "175" "1100" "DSI 4211A8-18")) (SETQ X4S-H2-3 (LIST "Valve, Trunnion Ball, 300# RF, A-216Gr.WCB/A-105 Body, SS Stem/Ball, Hydrocarbon Compatible Seat/O-Rings/Packing, -20 to 400 F, Gear Op., Full Port, Anti-blowout Stem, API-607, ASME B16.34 & B16.10, 500ppm VOC, Bubble Tight Seal" "175" "1100" "DSI 4211A8-18")) and i'm currently obtaining the data using (car) (cadr) (caddr) and (cadddr). These data are all structured in the same fashion. Here is the part of the routine that uses the data, that I would like to know how I can incorporate a mapcar statement so that I don't have to do four expressions for each piece of data (there are in between 50-100), that's in a repeat loop. That's alot of processing, that I'm sure will slow down the routine and I know mapcar is the tool i'd use to shorten it up, but I'm just out of practice and need some help applying it. here's the subfunction, though, with two of the longer method to (what I thought would) accomplish the task, as you can imagine adding the same lines to all of my data entries would create about 500 new lines of code that I think the same task can be accomplished using only a few lines and mapcar: (defun doTYPE ( vallist / c cell namedata) (if (setq cell (strcat "K" (itoa (vl-bb-ref 'excelrow)))) (acet-ui-progress-init "Writing valve TYPES to Excel." (length vallist)) );if (setq c 0) (setq len (length vallist)) (setq cell (string_ cell)) (repeat len (setq data (nth c vallist)) (setq typedata (cadr data)) (putcell cell typedata) (if (= typedata "U2TR-H2") (progn (setq desccell (strcat "I" (itoa (vl-bb-ref 'excelrow)))) (setq descdata (car u2tr-h2)) (PUTCELL desccell descdata) (setq tempcell (strcat "F" (itoa (vl-bb-ref 'excelrow)))) (setq tempdata (cadr u2tr-h2)) (PUTCELL tempcell tempdata) (setq presscell (strcat "E" (itoa (vl-bb-ref 'excelrow)))) (setq pressdata (caddr u2tr-h2)) (PUTCELL presscell pressdata) (setq mfrcell (strcat "A" (itoa (vl-bb-ref 'excelrow)))) (setq mfrdata (cadddr u2tr-h2)) (putcell mrfcell mrfdata) );PROGN );IF U2TR-H2 (setq X1TR-H2 (list "Valve, Gate, NPT, 800#, A-105 Body, CS/13Cr Wedge, HF-CS Seat, MS Stem, API Trim 5 or 8, -20 to 800 F, HW Op., Red. Port, Bltd. Bonnet-OS&Y, Graphite Packing, Solid Wedge, Renew. Seats, API-602 & 598, ASME B16.34, 500ppm VOC" "595" "1100" "DSI 4121A8-18")) (if (= typedata "X1TR-H2") (progn (setq desccell (strcat "I" (itoa (vl-bb-ref 'excelrow)))) (setq descdata (car X1TR-H2)) (PUTCELL desccell descdata) (setq tempcell (strcat "F" (itoa (vl-bb-ref 'excelrow)))) (setq tempdata (cadr X1TR-H2)) (PUTCELL tempcell tempdata) (setq presscell (strcat "E" (itoa (vl-bb-ref 'excelrow)))) (setq pressdata (caddr X1TR-H2)) (PUTCELL presscell pressdata) (setq mfrcell (strcat "A" (itoa (vl-bb-ref 'excelrow)))) (setq mfrdata (cadddr X1TR-H2)) (PUTCELL mrfcell mrfdata) );PROGN );IF X1TR-H2 (setq cell (string_ cell)) (acet-ui-progress-safe c) (setq c (1+ c)) );repeat (acet-ui-progress-done) (if (= c len)(dotag vallist));;goto do-tag sub );defun doname currently it's acting strange...take a look: Command: (setq mfrcell (strcat "A" (itoa (vl-bb-ref 'excelrow)))) "A6" Command: (setq mfrdata (cadddr X1TR-H2)) "DSI 4121A8-18" Command: (PUTCELL mrfcell mrfdata) ; error: bad argument type: stringp nil ;;not sure why routine did not put "DSI 4121A8-18" because... Command: !MFRDATA "DSI 4121A8-18" Command: !MFRCELL "A6" Even though these are showing as strings to me the routine gave a stringp nil error. So, I tried it manually, and it worked. The correct string was placed into the correct excel field, using: Command: (PUTCELL "A6" "DSI 4121A8-18") This one worked! Why, why why :/ So two tasks, really, that I could use some assistance with surely. Anyone? Edited July 28, 2014 by Bhull1985 tags Quote Link to comment Share on other sites More sharing options...
7o7 Posted July 29, 2014 Share Posted July 29, 2014 Maybe you "putcell" value to a cell which has data, and excel don't let you overwrite it. Quote Link to comment Share on other sites More sharing options...
MSasu Posted July 29, 2014 Share Posted July 29, 2014 Please pay attention that you inverted some letters in your variables names ("fr" vs. "rf"): (setq m[color=red]fr[/color]cell (strcat "A" (itoa (vl-bb-ref 'excelrow)))) (setq m[color=red]fr[/color]data (cadddr X1TR-H2)) (PUTCELL m[color=red]rf[/color]cell m[color=red]rf[/color]data) Quote Link to comment Share on other sites More sharing options...
Bhull1985 Posted July 29, 2014 Author Share Posted July 29, 2014 Thank you Msasu! 7o7- Excel lets you overwrite existing cell contents, but thank you anyhow! Quote Link to comment Share on other sites More sharing options...
Bhull1985 Posted July 31, 2014 Author Share Posted July 31, 2014 Okay, good deal! Didn't use mapcar, but an alternative...an auxiliary function or four. Here, check it out. I tell ya, it was pure joy when I figured the last line out and the program ran as intended. Here was the method, using the same data list that I posted above (if vallist (acet-ui-progress-init "Writing valve TYPES to Excel." (length vallist)) );if (setq c 0) (setq len (length vallist)) (repeat len (setq cella (CELL-ADDR "K")) (setq data (nth c vallist)) (setq cell (itoa (vl-bb-ref 'excelrow))) (setq typedata (cadr data)) (putcell cella typedata) (setq typekey (read typedata)) (putcell (cell-addr "I") (desc-data typekey)) (PUTCELL (cell-addr "F") (temp-data typekey)) (PUTCELL (cell-addr "E") (press-data typekey)) (PUTCELL (cell-addr "A") (mfr-data typekey)) (setq cell (string_ cell)) (setq cellb (atoi cell)) (vl-bb-set 'excelrow cellb) (acet-ui-progress-safe c) (setq c (1+ c)) );repeat (acet-ui-progress-done) (setvar "cmdecho" 0) (princ "\n\t\t*************************************************************")(princ) (princ "\n\t\t***\tAutoExcel Report \t*******************************")(princ) (princ (strcat "\n\t\t***\t" (itoa (length @dupeslist)) " unique items duplicated in excel file \t\t***"))(princ) (princ "\n\t\t***\t") (princ lst) (princ "\n\t\t***\tThese items have had their cells colored Grey\t***")(princ) (princ "\n\t\t***\tFinished!\t\t\t\t\t*****************************")(princ) (princ "\n\t\t*************************************************************")(princ) (if vallist (setq svflg 1)) (setq vallist nil) (vl-bb-set 'dupeslist nil) (setq @dupeslist nil) (setq @excel_row "6") (setq *excelApp% nil) (princ "\n") ;;end of excel input, routine will alert to user that it is complete (princ) (setq *error* nil) (gc) );defun dotype Just wanted to say thanks, and that I got past it. WHOO! 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.