难以逾越，变量取得excel单元格数据，请教高手
从网上看到snnnod大侠的帖子，解决了读取指定单元格的数据（比较实用，没有类库连接、ADO这些高深的要求），但是有一个遗憾就是，单元格不能是（row col)这样的变量。经过修改代码如下，后面的是原来的，都可以用。但是有以下问题，请高手指点迷津1、如何判断excel未打开，如果未打开，通过lisp打开excel文件
2、如何判断当前表格中最后一行或最后一列的数据
(defun c:ty ()
(vl-load-com)
;;简单示例lisp读取excel里的数据（没有判断容错功能）by snddd2000 2011-11-17
(setq excelapp0 (vlax-get-object "Excel.Application"))
;;excel程序对象，没开excel下边的都是白搭
(setq activeworkbook0 (vlax-get-property excelapp0 'ActiveWorkbook))
;;excel工作簿对象
(setq activesheet0 (vlax-get-property activeworkbook0 'ActiveSheet))
;;excel工作表对象
(setq cells0 (vlax-get-property activesheet0 'cells))
;;excel单元格对象
(get-cell 4 6)
）
(defun get-cell (row col)
(setq a1 (vlax-get-property cells0 'item row col))
;;A1单元格对象
(setqa1-value
(vlax-variant-value
(vlax-get-property (vlax-variant-value a1) 'value)
;;或者'value
)
)
;;单元格里面的值里的值
a1-value
)
原来的代码，如下：
(defun c:ty ()
(vl-load-com)
;;简单示例lisp读取excel里的数据（没有判断容错功能）by snddd2000 2011-11-17
(setq excelapp0 (vlax-get-object "Excel.Application"))
;;excel程序对象，没开excel下边的都是白搭
(setq activeworkbook0 (vlax-get-property excelapp0 'ActiveWorkbook))
;;excel工作簿对象
(setq activesheet0 (vlax-get-property activeworkbook0 'ActiveSheet))
;;excel工作表对象
(setq cells0 (vlax-get-property activesheet0 'cells))
;;excel单元格对象
(setq a1 (vlax-get-property cells0 'item 4 6 ))
;;A1单元格对象
(setqa1-value
(vlax-variant-value
(vlax-get-property (vlax-variant-value a1) 'value)
;;或者'value
)
)
;;单元格里面的值里的值
a1-value
)
)
昨天晚上从一个国外的网站上看到这个，有所提示，但还是解决不了问题，求帮助：
;; Example demonstrating how to read the plain text content of all cells within
;; the UsedRange of the active worksheet of a supplied Excel file.
;; - Lee Mac-2013-10-29
(defun readexcel ( xls / rtn xlapp xlcls xlcol xlrng xlrow xlsht xlwbk xlwbs )
(if
(and
(setq xls (findfile xls))
(setq xlapp (vlax-get-or-create-object "excel.application"))
)
(progn
(setq rtn
(vl-catch-all-apply
'(lambda ( / col lst row )
(setq xlwbs (vlax-get-propertyxlapp 'workbooks)
xlwbk (vlax-invoke-method xlwbs 'open xls)
xlsht (vlax-get-propertyxlapp 'activesheet)
xlrng (vlax-get-propertyxlsht 'usedrange)
xlcls (vlax-get-propertyxlrng 'cells)
xlrow (vlax-get-propertyxlrng 'rows)
xlcol (vlax-get-propertyxlrng 'columns)
)
(repeat (setq row (vlax-get-property xlrow 'count))
(repeat (setq col (vlax-get-property xlcol 'count))
(setq lst
(cons
(vlax-variant-value
(vlax-get-property
(vlax-variant-value
(vlax-get-property xlcls 'item row col)
)
'value
)
)
lst
)
)
(setq col (1- col))
)
(setq row (1- row))
)
lst
)
)
)
(if (= 'vla-object (type xlwbk))
(vl-catch-all-apply 'vlax-invoke-method (list xlwbk 'close :vlax-false))
)
(vl-catch-all-apply 'vlax-invoke-method (list xlapp 'quit))
(foreach obj (list xlcol xlrow xlcls xlrng xlsht xlwbk xlwbs xlapp)
(if (= 'vla-object (type obj))
(vlax-release-object obj)
)
)
(gc)
(if (vl-catch-all-error-p rtn)
(prompt (strcat "\nError: " (vl-catch-all-error-message rtn)))
rtn
)
)
(prompt "\nUnable to interface with Excel Application.")
)
)
(vl-load-com) (princ)
;; (excel-getcell 1 2)
(defun excel-getcell (row col)
(setq excel (cond ((vlax-get-or-create-object "ket.application"))
((vlax-get-or-create-object "execl.application"))
)
sht (vlax-get excel 'activesheet)
)
(variant-value
(vlax-get-property
(vlax-variant-value
(vlax-get-property
(vlax-get-property sht 'cells)
'item
row
col
)
)
'value2
)
)
)
谢谢你时隔几年还回帖指导 (vlax-get-object "Excel.Application")返回nil表示excel没有打开。
(if (null (setq excelapp0 (vlax-get-object "Excel.Application"))
)
(progn
(setq excelapp0 (vlax-create-object "Excel.Application"))
(vlax-put-property excelapp0 'Visible 1)
)
)
(setq excelworkbooks0 (vlax-get-property excelapp0 'Workbooks))
(setq activeworkbook0 (vlax-invoke-method excelworkbooks0 'add))
(setq activesheet0 (vlax-get-property activeworkbook0 'ActiveSheet))
(setq cells0 (vlax-get-property activesheet0 'cells))
(setq aLastcell (vlax-get-property
cells0
'item
(vlax-get-property
(vlax-get-property
(vlax-get-property cells0 'cells)
'rows
)
'count
)
1
)
)
(setq endxluprow0 ;_A列有数据的最后一行的行号
(vlax-get-property
(vlax-get-property
(vlax-variant-value aLastcell)
'end
-4162
)
'row
)
)
呵呵，还是师傅出手了。我想对cell（row，col）设定为变量来查找数据，但是不成功，请教一下给把个脉
;;通过变量查找符合条件的单元格数据
defun c:ty ()
(vl-load-com)
;;简单示例lisp读取excel里的数据（没有判断容错功能）by snddd2000 2011-11-17
(setq excelapp0 (vlax-get-object "Excel.Application"))
;;excel程序对象，没开excel下边的都是白搭
(setq activeworkbook0 (vlax-get-property excelapp0 'ActiveWorkbook))
;;excel工作簿对象
(setq activesheet0 (vlax-get-property activeworkbook0 'ActiveSheet))
;;excel工作表对象
(setq cells0 (vlax-get-property activesheet0 'cells))
;;excel单元格对象
(setq k 1)
(while (< 50 k)
(if (<= 300 (get-cell (vlax-variant-value k) 2))
(setq k (+ k 1))
)
)
(get-cell (vlax-variant-value k) 2)
)
(defun get-cell (row col)
(setq dy (vlax-get-property cells0 'item row col))
;;单元格对象
(setq
a1-value (vlax-variant-value
(vlax-get-property (vlax-variant-value dy) 'value)
;;或者'value
)
)
;;单元格里面的值里的值
a1-value
)
(defun c:ty
(/ excelapp0 activeworkbook0 activesheet0 cells0 k get-cell)
(vl-load-com)
(defun get-cell (row col / dy a1-value a1-value)
(setq dy (vlax-get-property cells0 'item row col))
;;单元格对象
(setq
a1-value (vlax-variant-value
(vlax-get-property (vlax-variant-value dy) 'value)
;;或者'value
)
)
;;单元格里面的值里的值
a1-value
)
;;简单示例lisp读取excel里的数据（没有判断容错功能）by snddd2000 2011-11-17
(setq excelapp0 (vlax-get-object "Excel.Application"))
;;excel程序对象，没开excel下边的都是白搭
(setq activeworkbook0 (vlax-get-property excelapp0 'ActiveWorkbook))
;;excel工作簿对象
(setq activesheet0 (vlax-get-property activeworkbook0 'ActiveSheet))
;;excel工作表对象
(setq cells0 (vlax-get-property activesheet0 'cells))
;;excel单元格对象
(setq k 1)
(while (< 50 k)
(if (<= 300 (get-cell k 2))
(setq k (1+ k))
)
)
(get-cell k 2)
(mapcar 'vlax-release-object
(list cells0 activesheet0 activeworkbook0 excelapp0)
)
(gc)
(princ)
)
谢谢你的帮忙，但是运行出现 ty ; 错误: 参数类型错误: VLA-OBJECT nil 支持楼主!
我可以取到值，你自己调试下，看看是哪个变量没有得到值。
我是excel2010 cad2010，请指教。
页:
[1]
2