Excel TAble via Lisp and Excel formatting

[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
)

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

[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

[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

[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!

[Arni]

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

[BIGAL]

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

[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 

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