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

yxh1202 · 发表于 2013-12-10 22:42:00

从网上看到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单元格对象
(setq  a1-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-property  xlapp 'workbooks)
                           xlwbk (vlax-invoke-method xlwbs 'open xls)
                           xlsht (vlax-get-property  xlapp 'activesheet)
                           xlrng (vlax-get-property  xlsht 'usedrange)
                           xlcls (vlax-get-property  xlrng 'cells)
                           xlrow (vlax-get-property  xlrng 'rows)
                           xlcol (vlax-get-property  xlrng '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:00

copy


;; (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:00

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

snddd2000 · 发表于 2013-12-11 09:26:00

(vlax-get-object "Excel.Application")返回nil表示excel没有打开。

copy

(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:00

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

copy

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