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 (874 Views)

reading .xlsx files and modifying a block

874 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 (857 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 (841 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 (824 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 (802 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 (784 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 (746 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. 

You are not logged in.

Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register

Announcements
Are you familiar with the Autodesk Expert Elites? The Expert Elite program is made up of customers that help other customers by sharing knowledge and exemplifying an engaging style of collaboration. To learn more, please visit our Expert Elite website.

Need installation help?

Start with some of our most frequented solutions to get help installing your software.

Ask the Community