handasa 发表于 2022-7-5 15:34:12

从“打开的”excel生成表格

大家好。。
 
我找到了Juan Villarreal写的代码
它要求用户浏览目标excel，然后打开它并将其使用范围作为表格复制到autocad。。。
 
现在我已经打开了我的excel。。。有人可以修改这段代码的哪一部分来读取这个打开的Excel工作簿中的数据，而不是打开一个新的工作簿吗。。。
 
感谢您的阅读和宝贵时间
 
;; © 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-getexcel-app "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 发表于 2022-7-5 15:43:08

为什么不使用AutoCAD中已有的数据链接功能？

handasa 发表于 2022-7-5 15:45:38

 
因为从excel中获取表格是大型lisp代码的步骤。。。其中有步骤之前和步骤之后

rlx 发表于 2022-7-5 15:49:59

只需获取activesheet属性
 
(setq activesheet (vlax-get-property excel-app"activesheet"))
 
gr.Rlx

BIGAL 发表于 2022-7-5 15:55:53

获取Getexel的副本。lsp它可能对您想要的东西更有用。它更多地是关于在excel中获取/放置单元格。

rlx 发表于 2022-7-5 16:01:12

像这样的
 
 


(defun ExcelData (/ row col lst vertxldata CellProps CellItem activesheet activeworkbook)
(setq excel-app   (vlax-get-or-create-object "excel.application")
activesheet (vlax-get-property excel-app "activesheet")
activeworkbook (vlax-get-property excel-app "activeworkbook")
sheetname   (vlax-get-property activesheet "Name")
FilePath    (vlax-get-property activeworkbook "FullName"))
)


gr.Rlx

handasa 发表于 2022-7-5 16:07:01

 
谢谢，Rlx
 
如果打开的工作表尚未保存。。。“FilePath”变量是否成立？

rlx 发表于 2022-7-5 16:10:38

 
 
不客气。。。
 
 
不，文件必须先保存。。。至少，我试过了，但失败了。程序仍然将“book1”视为名称，但它无法获得乍一看似乎是的数据。
 
 
Gr.Rlx

handasa 发表于 2022-7-5 16:14:42

再次感谢，Rlx
你救了我一天。。。谢谢兄弟

Jagdish 发表于 2022-7-5 16:20:18

亲爱的
 
我们习惯使用Excel格式，所以请帮助！！！
 
;; © 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-getexcel-app "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
)
页: [1] 2
查看完整版本: 从“打开的”excel生成表格