Visual LISP, AutoLISP and General Customization

Visual LISP, AutoLISP and General Customization

Reply
Active Contributor
sethtriad
Posts: 27
Registered: ‎09-27-2011
Message 1 of 7 (1,047 Views)

reading .xlsx files and modifying a block

1047 Views, 6 Replies
02-07-2013 09:40 AM

I was searching the series of tubes for an answer, but I couldn't find one, so I thought I'd ask here:

 

Is it possible to write an AutoLISP script to read an .xlsx file and use the data in the various cells to modify a block within a .dwg?

 

Ex.: We use Flow Control Modules (FCMs) and use AutoCAD Electrical to show electrical contractors where/how to wire up the devices. In AutoCAD, we have blocks for various types of FCMs, and each block has terminals which are tagged DESCA1, DESCA2, DESCA3, etc. Each of these tags will change based on what the particular FCM is used for. 

 

We also have .xls(x) files we call FCM Maps, which come from our engineers and tell the drafters how to lay out the .dwg files. 

 

Is it possible to write a LISP script to take, say, cell A1 from the .xlsx file, and put that data into DESCA2 in the block, cell A2 into DESCA3, and so on?

*Expert Elite*
Hallex
Posts: 1,569
Registered: ‎10-08-2008
Message 2 of 7 (1,030 Views)

Re: reading .xlsx files and modifying a block

02-07-2013 01:53 PM in reply to: sethtriad

I know nothing about your stuffs sorry

To get you started try this code

Note Excel file must be open and activated

on desired worksheet before you run this lisp

_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Active Contributor
sethtriad
Posts: 27
Registered: ‎09-27-2011
Message 3 of 7 (1,014 Views)

Re: reading .xlsx files and modifying a block

02-08-2013 06:39 AM in reply to: Hallex

This is really cool!

Could you explain this section a bit so I can expand upon your work:

 

(cond ((eq (vla-get-tagstring attref) "DESCA1")
			 (vla-put-textstring attref (nth 1 vl)));A2 (if cell A1 is as header)
			((eq (vla-get-tagstring attref) "DESCA2")
			 (vla-put-textstring attref (nth 2 vl)));A3
			((eq (vla-get-tagstring attref) "DESCA3")
			 (vla-put-textstring attref (nth 3 vl)));A4
			;;	---	etc	etc	---	;;

 I'm assuming that DESCA1 of the selected block is filled with information from cell A2, right? How do I modify the "nth 3 vl" to selelect different cells of the spreadsheet? Say I waned information from cells A2, B3, C4, D5, how would I modify this code to include those cells.

*Expert Elite*
Hallex
Posts: 1,569
Registered: ‎10-08-2008
Message 4 of 7 (997 Views)

Re: reading .xlsx files and modifying a block

02-09-2013 12:03 PM in reply to: sethtriad

I changed code to select list or separate cells,

see block attributes , yo may need to change them all

if you need to select block by name then add

check on this name or select using:

 

(setq sset (ssget "_+.:smileyfrustrated::E" (list (cons 0 "insert")(cons 2 blockname)))))

(setq en (ssname sset 0) ... etc, etc

 

hth

_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Active Contributor
sethtriad
Posts: 27
Registered: ‎09-27-2011
Message 5 of 7 (975 Views)

Re: reading .xlsx files and modifying a block

02-11-2013 06:19 AM in reply to: Hallex

Your first script seems like it has more potential for what I need: open a .dwg w/ the block in it, run the script, select the block, then select the range of cells in excel, and the script would fill out the tags in the block. 

 

In your scripts, you have a few lines of code that pick cells from excel and assign them to tags in the block within ACADE: 

(cond ((eq (vla-get-tagstring attref) "DESCA1")
			 (vla-put-textstring attref (nth 1 vl)));A2 (if cell A1 is as header)

 

 How does that select cell A2?

 

Does it base the information on how I select the cells in excel? If I started my selection at B2, for example, would that code put the B2 data into "DESCA1"?

*Expert Elite*
Hallex
Posts: 1,569
Registered: ‎10-08-2008
Message 6 of 7 (957 Views)

Re: reading .xlsx files and modifying a block

02-11-2013 01:28 PM in reply to: sethtriad

Second routine is works good both ways with single cell as well as with list

You have just change ta part to working with your block,

this is another deal, I don't know either your block name and attribute, or

other things you want to pull cell data to, it looks like homework question for me.

Thus you have to finish it by yourself, sorry

_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Active Contributor
sethtriad
Posts: 27
Registered: ‎09-27-2011
Message 7 of 7 (919 Views)

Re: reading .xlsx files and modifying a block

02-12-2013 11:13 AM in reply to: Hallex

I've attached a blank FCM .dwg to show what I/we start with as well as a typical FCM .dwg and an example FCM map which is where the data comes from for the FCM descriptions/tags.

 

in the typical FCM .dwg, every description that has "meter", "pulser", "no solenoid", "nc solenoid", "additive", "control valve" are standard and never change. Every description that has "Product A/B", "Additive A/B", "FCM 1", "LANE 1 FCM PANEL", "LANE 1", all come from cells in a spread sheet. 

 

Looking at the descriptions on the blocks, each LOC description is changed from "FCM PANEL", in the blank FCM, to LANE 1 FCM PANEL, where LANE 1 comes from cell A1 in the example excel spreadsheet. 

 

DESC1 on all these blocks is derived from cell A2 in the spreadsheet. 

Post to the Community

Have questions about Autodesk products? Ask the community.

New Post
Announcements
Are You Going To Be @ AU 2014? Feel free to drop by our AU topic post and share your plans, plug a class that you're teaching, or simply check out who else from the community might be in attendance. Ohh and don't forgot to stop by the Autodesk Help | Learn | Collaborate booths in the Exhibit Hall and meet our community team if you get a chance!