Jump to content

Using LISP to Import From Excel


Bill Tillman

Recommended Posts

I'm not exactly an ace lisp coder but I do understand enough of it to get myself into trouble. I am trying to use Jeffery P. Sanders' lisp routine which claims it will read data from Excel files. The code appears to be a generic module to which the end user needs to add some lines to call the routines. But alas, my feeble attempts at this have proven that I still have a lot to learn about the process. Could anyone offer some assistance here?

Link to comment
Share on other sites

Have you taken a look at Lee Mac's program called Point Manager?

 

Another option available at the CADforum:

 

Csv2Table - import comma-delimited CSV file (Excel) to AutoCAD Table (VLX Lisp)

Link to comment
Share on other sites

Yes I have worked with Point Manager and it's great. But for this task I need to import the data directly from Excel sheets. I'm wondering if these scripts from JP Sanders only work with older versions of Excel. I'm using Excel 2007. My goal is to read data directly from the spreadsheets without creating delimited files and such.

 

And I'm now wondering if Visual Basic will be the tool needed to do this properly.

Link to comment
Share on other sites

Hey Fixo, that's pretty cool....Like the way it worked.

 

I did some additional work with this and found it's really quite easy to link a table to an Excel spreadsheet. In fact, it was so easy I don't know why you'd want to use LISP to do it. That seems like a rather brutal process to go through to end up with the same thing. Plus, if I read the docs right, you can make changes either to the table in AutoCAD or to the Excel spreadsheet and all will be updated. That makes it a no-brainer. Still I have to show some people next week how to access Excel using LISP so I' will keep trying.

Link to comment
Share on other sites

Hey Fixo, that's pretty cool....Like the way it worked.

 

I did some additional work with this and found it's really quite easy to link a table to an Excel spreadsheet. In fact, it was so easy I don't know why you'd want to use LISP to do it. That seems like a rather brutal process to go through to end up with the same thing. Plus, if I read the docs right, you can make changes either to the table in AutoCAD or to the Excel spreadsheet and all will be updated. That makes it a no-brainer. Still I have to show some people next week how to access Excel using LISP so I' will keep trying.

 

 

Here's an example of creating a full sheet datalink through lisp:

(AddDataLink )

-FilePath can be nil

(defun ExcelData ( /  row col lst vertxldata CellProps CellItem)
(or
(and
  FilePath
  (= (type FilePath) 'STR)
  (findfile FilePath)
 )
(setq FilePath (getfiled "Select File:" (getvar 'dwgprefix) "xls;csv" 2))
)
(setq excel-app (vlax-get-or-create-object "excel.application")
     wb-collection (vlax-get excel-app "workbooks")
     arq    (vlax-invoke-method wb-collection  "Open" FilePath)
     sheets (vlax-get arq "sheets")
     sheet1 (vlax-get-property sheets "item" 1)
)
(setq sheetname (vlax-get-property sheet1 "Name"))
(vlax-invoke-method wb-collection 'Close)
(vl-catch-all-apply 'vlax-invoke-method (list excel-app 'Quit))
(mapcar '(lambda (x)(vlax-release-object x))
(list sheet1 sheets arq wb-collection excel-app))
(mapcar '(lambda (x)(setq x nil)(gc))
(list sheet1 sheets arq wb-collection excel-app))
(gc)(gc)
)

(defun dc (str len /)
(atoi (substr (rtos DlDate 2 20) str len))
)

(Defun AddDataLink ( DataLinkName FilePath / ActDoc DatDict DlDate DatDictEname DataLinkList DLEM
                    TempTC SheetName TableContent)
(excelData)
(setq ActDoc (vla-get-activedocument (vlax-get-acad-object)))
(setq DatDict
(vla-add
 (vla-get-dictionaries
  (vla-get-database ActDoc)
 )
 "ACAD_DATALINK"
)
)
(setq datalink (list '(0 . "DATALINK") '(100 . "AcDbDataLink")))
(setq DLEM (entmakex datalink))
(setq EDL (entget DLEM))
(setq TempTC
(entmakex
 (list 
  (cons 0 "TABLECONTENT")
  (cons 100 "AcDbLinkedData")
  (cons 100 "AcDbLinkedTableData")
  (cons 92 0)
  (cons 100 "AcDbFormattedTableData")
  (cons 300 "TABLEFORMAT")
  (CONS 1 "TABLEFORMAT_BEGIN")
  (CONS 90 4)
  (CONS 170 0)
  (CONS 309 "TABLEFORMAT_END")
  (CONS 90 0)
  (CONS 100 "AcDbTableContent")
 )))
(setq DlDate (getvar 'cdate))
(setq DatDictEname (vlax-vla-object->ename DatDict))
(entmod (subst (cons 330 DatDictEname)(assoc 330 (entget temptc)) (entget temptc)))
(setq DataLinkList
(list
 (assoc -1 edl)
 (cons 0 "DATALINK")
 (cons 102 "{ACAD_REACTORS")
 (cons 330 DatDictEname)
 (cons 102 "}")
 (cons 330 DatDictEname)
 (cons 100 "AcDbDataLink")
 (cons 1 "AcExcel")
 (cons 300 "")
 (cons 301 (strcat "Data Link\n" DataLinkName "\n" FilePath "\nLink details: Entire sheet: " SheetName))
 (cons 302 (strcat FilePath "!" SheetName))
 (cons 90 2)
 (cons 91 1179649)
 (cons 92 1)
 (cons 170 (dc 1 4));Year
 (cons 171 (dc 5 2));Month
 (cons 172 (dc 7 2));Day
 (cons 173 (+ (dc 10 2)6));Hour+6
 (cons 174 (dc 12 2));Minutes
 (cons 175 (dc 14 2));Seconds
 (cons 176 (dc 16 2));Milliseconds
 (cons 177 3)
 (cons 93 0)
 (cons 304 "")
 (cons 94 0)
 (cons 360 TempTC)
 (cons 305 "CUSTOMDATA")
 (cons 1 "DATAMAP_BEGIN")
 (cons 90 3)
 (cons 300 "ACEXCEL_UPDATEOPTIONS")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 1)
 (cons 91 1179649)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 300 "ACEXCEL_CONNECTION_STRING")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 4)
 (cons 1 (strcat FilePath "!" SheetName))
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 300 "ACEXCEL_SOURCEDATE")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 
 (cons 92 16)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 309 "DATAMAP_END")
)
)
(entmod DataLinkList)
(entmod (append (entget datdictename)(list (cons 3 DataLinkName) (cons 360 DLEM))))
(princ)
)

Link to comment
Share on other sites

  • 2 years later...

Hello guys, I read these posts, I'm in a similar position but with a bit different goal in mind. I also like XL, it seams very approachable to me since I'm terrible at programing, NOOB, but am good at cad and want to get faster at it.

 

I installed XL, it runs, pulls up the Excel file, and does it's searches but keeps coming back with the command: Processing attributes, Revised (0) attributes!

So here's where I'm at in my excel file:

column A has the “str#” where # is numbers 1 through 100numerically,

Column B has the terms it’slooking for, for example ADA卫生间(典型) which is in the SimSum font, exactly like it appears inCAD, I tested it with English letter, and it also came back with same result. :/

And column C has the Englishwhich I wish to swap it out with.. for example: ADA TOILET (TYP.)

In CAD, I start up the XL command,

I pick the Excel file (which is in the XLS format which XL wants it to be (newer excel saves it to XLSX which XL does not work with))

I select the sheet which myterms are on (sheet 1)

And hit enter. It lookslike it’s going through the process now, but it returns with:

Processing attributes, Revised (0) attributes!

every, single, time. :cry:

I feel like I’m super close togetting this to work.. just need a bit of expert advice.. any ideas what Imight be doing wrong?

Link to comment
Share on other sites

  • 3 weeks later...
Try this code, you can select desired Excel range

using embedded RefEdit Excel's control

( Excel file must be open before you'll run lisp)

http://forums.augi.com/showpost.php?p=1150815&postcount=10

 

 

 

Hey fixo... I have tried using your lisp for exporting Excel range.... but when I run this...

it give me the error "bad argument type: VLA-OBJECT #"

 

 

I'm new to lisp routine & don't know what to do to eliminate this error.. :(

do I need to create table before running this lisp or something else..??

Link to comment
Share on other sites

  • 1 year later...

Hello, one thing id like to add to this thread

the lisp routine from villarreal tbl.lsp is perfect for me.

BUT it needs to keep excel formatting . right now it doensnt.

and im no lisp expert. So can anybody help me?

 

;; © Juan Villarreal 12.06.2011

(defun ExcelData ( /  row col lst vertxldata CellProps CellItem)

(or
(and
  FilePath
  (= (type FilePath) 'STR)
  (findfile FilePath)
 )
(setq FilePath (getfiled "Select File:" (getvar 'dwgprefix) "xls;csv" 2))
)
(setq excel-app (vlax-get-or-create-object "excel.application")
     wb-collection (vlax-get excel-app "workbooks")
     arq    (vlax-invoke-method wb-collection  "Open" FilePath)
     sheets (vlax-get arq "sheets")
     sheet1 (vlax-get-property sheets "item" 1)
)

(setq sheetname (vlax-get-property sheet1 "Name"))
(vlax-invoke-method wb-collection 'Close)
(vl-catch-all-apply 'vlax-invoke-method (list excel-app 'Quit))
(mapcar '(lambda (x)(vlax-release-object x))
(list sheet1 sheets arq wb-collection excel-app))
(mapcar '(lambda (x)(setq x nil)(gc))
(list sheet1 sheets arq wb-collection excel-app))
(gc)(gc)
)


(defun dc (str len /)
(atoi (substr (rtos DlDate 2 20) str len))
)


(Defun AddDataLink ( DataLinkName FilePath / ActDoc DatDict DlDate DatDictEname DataLinkList DLEM
                    TempTC SheetName TableContent)
(excelData)
(setq ActDoc (vla-get-activedocument (vlax-get-acad-object)))
(setq DatDict
(vla-add
 (vla-get-dictionaries
  (vla-get-database ActDoc)
 )
 "ACAD_DATALINK"
)
)
(setq datalink (list '(0 . "DATALINK") '(100 . "AcDbDataLink")))
(setq DLEM (entmakex datalink))
(setq EDL (entget DLEM))
(setq TempTC
(entmakex
 (list 
  (cons 0 "TABLECONTENT")
  (cons 100 "AcDbLinkedData")
  (cons 100 "AcDbLinkedTableData")
  (cons 92 0)
  (cons 100 "AcDbFormattedTableData")
  (cons 300 "TABLEFORMAT")
  (CONS 1 "TABLEFORMAT_BEGIN")
  (CONS 90 4)
  (CONS 170 0)
  (CONS 309 "TABLEFORMAT_END")
  (CONS 90 0)
  (CONS 100 "AcDbTableContent")
 )))
(setq DlDate (getvar 'cdate))
(setq DatDictEname (vlax-vla-object->ename DatDict))
(entmod (subst (cons 330 DatDictEname)(assoc 330 (entget temptc)) (entget temptc)))

(setq DataLinkList
(list
 (assoc -1 edl)
 (cons 0 "DATALINK")
 (cons 102 "{ACAD_REACTORS")
 (cons 330 DatDictEname)
 (cons 102 "}")
 (cons 330 DatDictEname)
 (cons 100 "AcDbDataLink")
 (cons 1 "AcExcel")
 (cons 300 "")
 (cons 301 (strcat "Data Link\n" DataLinkName "\n" FilePath "\nLink details: Entire sheet: " SheetName))
 (cons 302 (strcat FilePath "!" SheetName))
 (cons 90 2)
 (cons 91 1179649)
 (cons 92 1)
 (cons 170 (dc 1 4));Year
 (cons 171 (dc 5 2));Month
 (cons 172 (dc 7 2));Day
 (cons 173 (+ (dc 10 2)6));Hour+6
 (cons 174 (dc 12 2));Minutes
 (cons 175 (dc 14 2));Seconds
 (cons 176 (dc 16 2));Milliseconds
 (cons 177 3)
 (cons 93 0)
 (cons 304 "")
 (cons 94 0)
 (cons 360 TempTC)
 (cons 305 "CUSTOMDATA")
 (cons 1 "DATAMAP_BEGIN")
 (cons 90 3)
 (cons 300 "ACEXCEL_UPDATEOPTIONS")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 1)
 (cons 91 1179649)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 300 "ACEXCEL_CONNECTION_STRING")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 4)
 (cons 1 (strcat FilePath "!" SheetName))
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 300 "ACEXCEL_SOURCEDATE")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 
 (cons 92 16)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 309 "DATAMAP_END")
)
)
(entmod DataLinkList)
(entmod (append (entget datdictename)(list (cons 3 DataLinkName) (cons 360 DLEM))))
(princ)
)

(defun c:Tbl (/ DLName)
(setq DLName (getstring T "Data Link Name: "))
(AddDataLink DLName nil);or use filename in place of nil
(vl-cmdf "-table" "L" DLName);IF YOU DIDN'T ADD IT TO THE FUNCTION
)

Link to comment
Share on other sites

  • 3 years later...

Hello, I came across this post, searching for advise on LISP Coding and how it can be connected to Excel and AutoCAD. I am a Mechanical Engineer, so not too great with Computer Science or Computer Coding, I was wondering if anyone could point me in a right direction to help me fully understand this or if there are any classes that help teach this concept.

 

I am ultimately looking to implement data into Excel (Not in a table or chart), just implement values such as Demand Load or Breaker Size or conduit wire size into excel and have that information populate automatically into AutoCAD in the correct fields after typing in a command into AutoCAD. I have seen this process done but I have not been able to see the coding to get a better understanding of what is happening. I was hoping someone could point me in the right direction, Thank you for your time and help.

 

Cameron 

Link to comment
Share on other sites

It rates as perhaps the top topic Autocad < - > Excel so many posts.

 

It is not that hard with the right lisp tools, you basicaly get a row - column value from excel.

 

There are plenty of examples if you google or just start here you should be able to find what you want.

 

An example of code is Getexcel.lsp. Others will suggest.

 

If you post an xls and a dwg with some notes about what goes where some one here may be able to do something quickly.

Link to comment
Share on other sites

  • 5 months later...

I here is a similar topic, scenario if I have items like blocks I can have attributes and have them update from an excel. But what about property sets, if I use property sets instead of attributes is there a way to export the property sets to excel and than re-import/update using lisp to lisp? or vba?  any ideas on this one.

Link to comment
Share on other sites

Tell me how to make programs automatically create a table with connection to the excel with the range "A1:C50" for example

 

 

 

On 3/20/2019 at 7:29 AM, BIGAL said:

 

Link to comment
Share on other sites

  • 4 years later...

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.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...