难以逾越，变量取得excel单元格数据，请教高手

yxh1202 发表于 2013-12-10 22:42:24

本帖最后由 yxh1202 于 2013-12-26 08:15 编辑

从网上看到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)

渠辉发表于 2022-7-26 07:26:59

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

yxh1202 发表于 2022-7-27 22:41:40

渠辉发表于 2022-7-26 07:26

谢谢你时隔几年还回帖指导

snddd2000 发表于 2013-12-11 09:26:10

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

yxh1202 发表于 2013-12-11 14:14:53

snddd2000 发表于 2013-12-11 09:26 static/image/common/back.gif
(vlax-get-object "Excel.Application")返回nil表示excel没有打开。

呵呵，还是师傅出手了。我想对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
)

snddd2000 发表于 2013-12-12 10:10:23

yxh1202 发表于 2013-12-11 14:14 static/image/common/back.gif
呵呵，还是师傅出手了。我想对cell（row，col）设定为变量来查找数据，但是不成功，请教一下给把个脉
...

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

yxh1202 发表于 2013-12-13 17:36:27

snddd2000 发表于 2013-12-12 10:10 static/image/common/back.gif

谢谢你的帮忙，但是运行出现 ty ; 错误: 参数类型错误: VLA-OBJECT nil

混沌初开 发表于 2013-12-13 18:42:34

支持楼主!

snddd2000 发表于 2013-12-16 18:43:22

yxh1202 发表于 2013-12-13 17:36 static/image/common/back.gif
谢谢你的帮忙，但是运行出现 ty ; 错误: 参数类型错误: VLA-OBJECT nil

我可以取到值，你自己调试下，看看是哪个变量没有得到值。

yxh1202 发表于 2013-12-17 13:24:30

本帖最后由 yxh1202 于 2013-12-17 13:40 编辑

snddd2000 发表于 2013-12-16 18:43 static/image/common/back.gif
我可以取到值，你自己调试下，看看是哪个变量没有得到值。

我是excel2010 cad2010，请指教。

snddd2000 发表于 2013-12-17 14:48:08

我是excel2007 cad2007，调试只能靠自己。

清风明月名字 发表于 2013-12-17 15:10:45

谢谢众位坛友代码分享！

页: [1] 2

明经CAD社区's Archiver

难以逾越，变量取得excel单元格数据，请教高手