求助，lisp查找指定excel单元格数据
本帖最后由 yxh1202 于 2013-12-10 11:20 编辑;;;取出Excel文件中第一个工作表中指定单元格的数据
(defun c:ttt ()
(vl-load-com)
(setq appxls (vlax-get-or-create-object "excel.application"))
(Excel-Get-data)
(vlxls-sheet-put-active (appxls "xl"))
(setq sheet-name vlxls-Sheet-Get-Active)
;;(setq Sheet-name (vlax-variant-value (car (caddr lst))))
;;确定"第一个"工作表的名称
(setq cell-value (Excel-Get-CellValue sheet-name "C16"))
;;H16单元格
(vlax-invoke-method ADORecordset "Close")
(vlax-invoke-method ADOConnect "Close")
(progn
(princ "\n打开Excel数据文件出错")
(vl-catch-all-apply
'vlax-invoke-method
(list ADOConnect "Close")
)
(setq cell-value nil)
)
(vlax-release-object ADORecordset)
(vlax-release-object ADOConnect)
cell-value
)
(defun Excel-Get-data (/ xfile cell
ADOCONNECT ADORECORDSETConnectionString
lst Sheet-name source
cell-tmp cell-value
)
(setq xfile (getfiled "打开纵断面文件" "" "xls" 8))
(setq ADOConnect (vlax-get-or-create-object "ADODB.Connection"))
(setq ADORecordset (vlax-get-or-create-object "ADODB.Recordset"))
(setq ConnectionString
(strcat
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
xfile
";Extended Properties=;Excel 8.0;HDR=No"
)
)
(if (not
(vl-catch-all-error-p
(vl-catch-all-apply
(function vlax-invoke-method)
(list ADOConnect "Open" ConnectionString "admin" "" nil)
)
)
)
(progn
(setq lst
(vlax-safearray->list
(vlax-variant-value
(vlax-invoke-method
(vlax-invoke-method ADOConnect "OpenSchema" 4)
"GetRows"
1
)
)
)
)
)
;;end progn
)
;;endif
)
;;end excel-get-data
;;----获得所有表格名称-----
(Defun vlxls-sheet-get-all (xlapp / SH Rtn)
(vlax-for SH (vlax-get-property Xlapp "sheets")
(setq Rtn (cons (vlax-get-property sh "Name") Rtn))
)
(reverse Rtn)
)
;;函数--指定单元格的数据----------------------------------------
(defun Excel-Get-CellValue (Sheet-name cell)
(setq
source
(strcat "SELECT * FROM [" Sheet-name cell ":" cell "]")
)
(vlax-invoke-method
ADORecordset "Open" source ADOConnect 1 3 nil)
(setq
cell-tmp (vlax-safearray->list
(vlax-variant-value
(vlax-invoke-method ADORecordset "GetRows" 1)
)
)
)
(vlax-variant-value (car (car cell-tmp)))
)
;;--------返回激活的表格名称---------
(Defun vlxls-Sheet-Get-Active (/ xlapp name)
(vlax-get-property
(vlxls-sheet-put-active xlapp name)
)
)
;;-------设定激活的工作表名称--------
(Defun vlxls-sheet-put-active (xlapp Name / sh)
(if (null (vlxls-sheet-add xlapp name))
(vlax-for sh (vlax-get-property Xlapp "sheets")
(if (= (vlax-get-property sh "Name") Name)
(vlax-invoke-method sh "Activate")
)
)
)
(equal (vlxls-sheet-get-active) name)
)
;;------添加新工作表sheet-----------------
(Defun vlxls-sheet-add (xlapp Name / Rtn)
(if (member name (vlxls-sheet-get-all xlapp))
(setq Rtn nil)
(progn
(vlax-put-property
(vlax-invoke-method
(vlax-get-property Xlapp "sheets")
"Add"
)
"name"
Name
)
(setq Rtn (equal (vlxls-sheet-get-active xlapp) name))
)
)
Rtn
)
加载运行后出现 错误: 函数错误: #<VLA-OBJECT _Application 175d7514>
请高人指点 难道说调用excel2010类库无法 (vl-registry-read "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\App Paths\\Excel.EXE" "Path") 试试这个 1993063 发表于 2013-12-7 01:52 static/image/common/back.gif
(vl-registry-read "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\App Paths\\Exce ...
谢谢你，请问有完整的lisp与excel2010通讯的例子吗。 1993063 发表于 2013-12-7 01:52 static/image/common/back.gif
(vl-registry-read "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\App Paths\\Exce ...
1楼的是不是用ado方法。
页:
[1]