Jump to content
huygens

Excel TAble via Lisp and Excel formatting

Recommended Posts

huygens

Hi all,

i already found a great lisp bij Juan Villareal . With this lisp i can select a range in a specific excelsheet. It is added in Autocad but without excel formatting

could the excelformatting be added?

 

;; © 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
)

Share this post


Link to post
Share on other sites
ReMark

Users can specify a range of cells from Excel (with formatting) to be brought into AutoCAD using the DATALINK option of the TABLE command.

Share this post


Link to post
Share on other sites
huygens

thanks.

but if you wish to do a lot via commandline than the lisp i placed works great.

But. It doesnt automatically keep excel formatting. And i know there is an option to keep this in the Datalink menu.

So this should be placed in the lisp code somehow

cheers

Share this post


Link to post
Share on other sites
EComstock

Belatedly, under (setq DataLinkList :

 

  (cons 1 "AcExcel")
 (cons 300 "")
 (cons 301 (strcat "Data Link\n" DataLinkName "\n" FilePath "\nLink details: Entire sheet: " SheetName))
 (cons 302 (strcat RelPath "!" SheetName))
 (cons 90 2)
 (cons 91 1835009) ; keep Excel formatting

 

Additionally, if you want a relative path instead of a full path, you can insert this into the code as well:

 

(setq miss (vl-string-mismatch (getvar 'dwgprefix) (vl-filename-directory FilePath)))

(setq count 0)
(setq num nil)
(setq pfx (substr (vl-filename-directory FilePath) (1+ miss)))
(while (< 0 (strlen pfx))
 (setq count (1+ count))
 (setq num (vl-string-search "\\" pfx))
 (if (not num) (setq num (- (strlen pfx) 1)))
 (setq pfx (substr pfx (+ 2 num)))
 ) ; end while

(setq path "")
(while (< -1 count)
 (setq path (strcat "..\\" path))
 (setq count (1- count))
 ) ; end while
 (setq RelPath (strcat path (vl-filename-base FilePath) (vl-filename-extension FilePath)))

 

If you need the full code, I'll be happy to post Villareal's code with my edits.

 

Cheers,

E Comstock

Share this post


Link to post
Share on other sites
huygens

just one post yet Comstock? keep up the good work!

 

the only thing that would be great if you added it would be a commandline only version

And a complete code woul be cool

thank you anyway!

Share this post


Link to post
Share on other sites
Arni

Can you give me a hint, please? 
how to set a hard range in relation to the excel and edit if necessary 

Share this post


Link to post
Share on other sites
BIGAL

Arni google getexcel.lsp or any other example will have a method of selecting a range of cells.

Share this post


Link to post
Share on other sites
Arni
1 hour ago, BIGAL said:

Arni google getexcel.lsp or any other example will have a method of selecting a range of cells.

Thank you, getexcel.lsp creates the table and copies the value but does not create a link to Excel 
and Tbl.lsp linking the data to the entire sheet 
I'd appreciate it if you could tell me how to link the data to the range, or tell me where to find 

Share this post


Link to post
Share on other sites
BIGAL

This is the key word in code Entire sheet: but no idea what to replace it with. It may be as simple as A1,G56:

 

A data link object with range should reveal the 301 property. 

Share this post


Link to post
Share on other sites

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
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  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...