设为首页收藏本站

晓东CAD家园-论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

查看: 346|回复: 3

[原创] 通过Ado发送Sql语句和Excel通信

[复制链接]
发表于 2020-1-5 23:56:34 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
请点击此处下载

查看状态:需购买或无权限

您的用户组是:游客

文件名称:接线表.rar 
下载次数:18  文件大小:2.32 MB 
下载权限: 不限 以上  [免费赚D豆]



以前我们都是先通过excel来读取指定路径下的xls文件,然后筛选需要的数据,但是,如果遇到客户用xls做数据库,里面有65536行数据,255列,lisap读取后筛选其中的数据就会速度很慢了,遇到这种情况我们一般都会想到让用户将xls按照指定模板整理后倒入到mysql或者是sqlserver的数据库里面,然后通过http发送sql指令过滤自己需要的数据,这种办法是可以的,问题是很多人不懂mysql和sqlerver数据库的部署,所以,我就研究了一天时间发现可以通过ADO方法访问xls,将xls当做数据库,发送sql语句就能快速从65536条数据里面快速搜索自己需要的数据,比如:我们需要搜索来自excel里面“名称”的值为“电线”,同时“供货商”的值为“秦始皇”,那么,我们的sql语句就可以这样写了:"SELECT 序号,线种 FROM [接线表$] WHERE (`名称` = '电线' and `供货商` = '秦始皇')"
接下来我将ado访问excel的方法分享出来,水平有限,不足之处,大家自己琢磨
  1. (defun $Ado_get_excel_v$ (xls-path     sqlstr
  2.           lst       /
  3.           $Ado_open_excel$
  4.           $get-data$     ado_getfieldvaluesfromrs
  5.           Adodb     ADO-SQL
  6.           data     hdr
  7.           obj
  8.          )
  9.           ;xls-path excel的路径
  10.           ;sqlstr sql语句
  11.           ;lst 很多参数可以放这里面
  12.     (defun ADO_getFieldValuesFromRS (rs         /   flds
  13.              fldsCnt   fldsIdx   Lst
  14.              Row       thisField thisFld
  15.              thisValue str-jd
  16.             )
  17.       (if (not (vl-catch-all-error-p rs))
  18.   (progn
  19.     (vl-catch-all-apply
  20.       'vlax-invoke-method
  21.       (list rs "MoveFirst")
  22.     )
  23.     (setq Lst '())
  24.     (while
  25.       (not
  26.         (equal
  27.     :vlax-true
  28.     (vl-catch-all-apply 'vlax-get-property (list rs "EOF"))
  29.         )
  30.       )
  31.        (SETQ
  32.          flds
  33.     (vl-catch-all-apply 'vlax-get-property (list rs "Fields"))
  34.        )
  35.        (SETQ fldsCnt (1- (vl-catch-all-apply
  36.          'vlax-get-property
  37.          (list flds "Count")
  38.              )
  39.          )
  40.        )
  41.        (SETQ fldsIdx 0)
  42.        (setq str-jd "█")
  43.        (SETQ Row '())
  44.        (while (<= fldsIdx fldsCnt)
  45.          (if (= (rem fldsIdx 100) 0)
  46.      (setq str-jd (strcat str-jd "█"))
  47.          )
  48.          (setvar 'modemacro str-jd)
  49.          (setq
  50.      thisFld (vl-catch-all-apply
  51.          'vlax-get-property
  52.          (list flds "item" fldsIdx)
  53.        )
  54.          )
  55.          (OR (SETQ thisValue (vl-catch-all-apply
  56.              'vlax-variant-value
  57.              (list (vl-catch-all-apply
  58.                'vlax-get-property
  59.                (list thisFld "Value")
  60.              )
  61.              )
  62.            )
  63.        )
  64.        (SETQ thisValue "")
  65.          )
  66.          (SETQ Row (cons thisValue Row))
  67.          (SETQ fldsIdx (1+ fldsIdx))
  68.          (if (>= (strlen str-jd) 30)
  69.      (setq str-jd "█")
  70.          )
  71.          (princ)
  72.        )
  73.        (setq
  74.          Row (reverse Row)
  75.        )
  76.        (SETQ Lst (cons Row Lst))
  77.        (vl-catch-all-apply
  78.          'vlax-invoke-method
  79.          (list rs "MoveNext")
  80.        )
  81.     )
  82.     (setq Lst (reverse Lst))
  83.   )
  84.       )
  85.       (setvar 'modemacro "中线CAD")
  86.       lst
  87.     )
  88.     (defun $Ado_open_excel$ (Adodb xls-path HDR / JET ACE)
  89.       (if (and Adodb xls-path HDR (WCMATCH HDR "[,YES,NO,]"))
  90.   (progn (setq JET
  91.           (vl-catch-all-apply
  92.       (function
  93.         (lambda ()
  94.           (vlax-invoke
  95.             Adodb
  96.             'Open
  97.             (strcat
  98.         "Provider=Microsoft.jet.OLEDB.4.0; Data Source="
  99.         xls-path
  100.         "; Extended Properties='Excel 8.0; HDR="
  101.         HDR "; IMEX=1'")
  102.           )
  103.         )
  104.       )
  105.           )
  106.          )
  107.          (if (or (and JET (vl-catch-all-error-p JET))
  108.            (vl-catch-all-error-p Adodb)
  109.        )
  110.      (progn  (setq ACE
  111.              (vl-catch-all-apply
  112.          (function
  113.            (lambda ()
  114.              (vlax-invoke
  115.                Adodb
  116.                'Open
  117.                (strcat
  118.            "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= "
  119.            xls-path
  120.            "; Extended Properties='Excel 12.0; HDR="
  121.            HDR "; IMEX=1'")
  122.              )
  123.            )
  124.          )
  125.              )
  126.       )
  127.      )
  128.          )
  129.   )
  130.       )
  131.       (if (or (and ACE (vl-catch-all-error-p ACE))
  132.         (vl-catch-all-error-p Adodb)
  133.     )
  134.   (PROGN (if (vl-catch-all-error-p JET)
  135.      (print (vl-catch-all-error-message JET))
  136.          )
  137.          (print (vl-catch-all-error-message ACE))
  138.   )
  139.   Adodb
  140.       )
  141.     )
  142.     (defun $get-data$ (xls-path sqlstr HDR / adodb ado-sql data)
  143.       (defun setClipText (str / html result)
  144.           ;往剪切板上丢数据
  145.   (if (and str (= 'STR (type str)))
  146.     (progn
  147.       (setq html   (vlax-create-object "htmlfile")
  148.       result (vlax-invoke
  149.          (vlax-get (vlax-get html 'ParentWindow)
  150.              'ClipBoardData
  151.          )
  152.          'setData
  153.          "Text"
  154.          str
  155.        )
  156.       )
  157.       (vlax-release-object html)
  158.       str
  159.     )
  160.   )
  161.       )
  162.       (if (not ADO_getFieldValuesFromRS)
  163.   (print "缺少函数  ADO_getFieldValuesFromRS")
  164.       )
  165.       (if (not $Ado_open_excel$)
  166.   (print "缺少函数  $Ado_open_excel$")
  167.       )
  168.       (setq Adodb (vl-catch-all-apply
  169.         'vlax-create-object
  170.         (list "Adodb.Connection")
  171.       )
  172.       )
  173.       (if (vl-catch-all-error-p Adodb)
  174.   (print "创建 Adodb.Connection 对象失败")
  175.       )
  176.       (if (vl-catch-all-error-p Adodb)
  177.   (print (vl-catch-all-error-message Adodb))
  178.   (progn
  179.     (setq Adodb ($Ado_open_excel$ Adodb xls-path HDR))
  180.     (if Adodb
  181.       ()
  182.       (progn
  183.         (alert
  184.     "
  185. 当前电脑缺少AccessDatabaseEngine

  186. 请到微软官网地址下载:




  187. 64位系统

  188. ;由于晓东瞧不起权限低的用户,这里无法粘贴ado的url地址,请见谅

  189. 32位系统

  190. ;由于晓东瞧不起权限低的用户,这里无法粘贴ado的url地址,请见谅





  191. 64系统下载地址已经放到剪切板了,您粘贴到浏览器下载即可
  192. 32系统下载地址放在Cad命令行,您复制后粘贴到浏览器下载即可
  193.     "
  194.         )
  195.         (setClipText
  196.     ";由于晓东瞧不起权限低的用户,这里无法粘贴ado的url地址,请见谅"
  197.         )
  198.         (print
  199.     ";由于晓东瞧不起权限低的用户,这里无法粘贴ado的url地址,请见谅"
  200.         )
  201.         (and Adodb
  202.        (vl-catch-all-apply
  203.          'vlax-release-object
  204.          (list Adodb)
  205.        )
  206.         )
  207.         (exit)
  208.       )
  209.     )
  210.     (setq  ADO-SQL
  211.      (vl-catch-all-apply
  212.        (function (lambda ()
  213.              (vlax-invoke Adodb 'Execute SQLStr)
  214.            )
  215.        )
  216.      )
  217.     )
  218.     (if (not (vl-catch-all-error-p ADO-SQL))
  219.       (SETQ DATA (ADO_getFieldValuesFromRS ADO-SQL))
  220.       (print (vl-catch-all-error-message ADO-SQL))
  221.     )
  222.     (and Adodb
  223.          (vl-catch-all-apply 'vlax-release-object (list Adodb))
  224.     )
  225.   )
  226.       )
  227.       data
  228.     )
  229.     (if  (and xls-path
  230.        (findfile xls-path)
  231.        sqlstr
  232.   )
  233.       (if
  234.   (and
  235.     sqlstr
  236.     (wcmatch (strcase sqlstr)
  237.        "[,SELECT * FROM `[*`$`]*,SELECT * FROM `[*`$*`]*,]"
  238.     )
  239.   )
  240.    (progn
  241.      (OR (and lst
  242.         (assoc "首行为标题" lst)
  243.         (wcmatch (STRCASE (CDR (assoc "首行为标题" lst)))
  244.            "[,YES,NO,]"
  245.         )
  246.         (SETQ HDR (STRCASE (CDR (assoc "首行为标题" lst))))
  247.          )
  248.          (SETQ HDR "YES")
  249.      )
  250.      (setq DATA ($get-data$ xls-path sqlstr HDR))
  251.    )
  252.    (mapcar 'princ
  253.      '("通"   "过"  "A"   "d"   "o"   "方"  "式"  "读"
  254.        "取"   "e"   "x"   "c"   "e"   "l"   "的"  "s"
  255.        "q"   "l"   "语"  "句"  "不"   "正"  "确"  ","
  256.        "正"   "确"  "的"  "语"  "句"   "必"  "须"  "是"
  257.        "包"   "含"  "S"   "e"   "l"   "e"   "c"   "t"
  258.        "、"   "F"   "r"   "o"   "m"   "、"  "w"   "h"
  259.        "e"   "r"   "e"   ","  "建"   "议"  "使"  "用"
  260.        "另"   "一"  "个"  "函"  "数"   "$"   "g"   "e"
  261.        "t"   "-"   "e"   "x"   "c"   "e"   "l"   "-"
  262.        "s"   "h"   "e"   "e"   "t"   "-"   "v"   "$"
  263.        "可"   "以"  "在"  "5"   "秒"   "内"  "返"  "回"
  264.        "一"   "万"  "行"  "数"  "据"
  265.       )
  266.    )
  267.       )
  268.     )
  269.     DATA
  270.   )

示例1:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq
  4.      SQLStr
  5.       "SELECT TOP 10 * FROM [接线表$] WHERE 1"
  6.    )        ;查询前十行数据(含标题行)(因为要返回标题行,所以,下面的“首行为标题”必须设定为no),有助于其他调用程序判断字段类型,ado好像也是这么判断的,通过前十行判断数据是int型还是string型  
  7.    (LIST (CONS "首行为标题" "NO"))
  8.        )


示例2:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq
  4.      SQLStr
  5.       "SELECT 线径 FROM [接线表$] where 1"
  6.    )        ;返回线径列的所有数据,后面程序可以根据这一个返回判断sql是否需要加单引号  
  7.    (LIST (CONS "首行为标题" "YES"))
  8.        )


示例3:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq
  4.      SQLStr
  5.       "SELECT * FROM [接线表$A:Z] where (`线径` is not null)"
  6.    )        ;限定列(在A:Z里面)查找数据(下面的“首行为标题”必须设定为yes)  
  7.    (LIST (CONS "首行为标题" "YES"))
  8.        )


示例4:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq
  4.      SQLStr "SELECT * FROM [接线表$] WHERE (`线号` is not null)"
  5.    )        ;线号不为空(这个速度会很慢,因为返回了整个表,还不如用另一个函数$get-excel-sheet-v$)
  6.    (LIST (CONS "首行为标题" "YES"))
  7.        )


示例5:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq SQLStr "SELECT * FROM [接线表$] WHERE `线号` = 'AL06'")
  4.           ;查询线号(下面的“首行为标题”必须设定为yes)  
  5.    (LIST (CONS "首行为标题" "YES"))
  6.        )


示例6:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq
  4.      SQLStr
  5.       "SELECT * FROM [接线表$] WHERE (`线种` = 'FLRY-B')"
  6.    )        ;查询线种(下面的“首行为标题”必须设定为yes)  
  7.    (LIST (CONS "首行为标题" "YES"))
  8.        )


示例7:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq
  4.      SQLStr
  5.       "SELECT * FROM [接线表$] WHERE (`线径` = 0.5)"
  6.    )        ;查询线径(下面的“首行为标题”必须设定为yes)
  7.    (LIST (CONS "首行为标题" "YES"))
  8. )


示例8:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq
  4.      SQLStr
  5.       "SELECT 序号,线种 FROM [接线表$] WHERE (`线种` = 'FLRY-B' and `线径` = 0.35)"
  6.    )        ;带有返回字段限制(下面的“首行为标题”必须设定为yes)
  7.    (LIST (CONS "首行为标题" "YES"))
  8.        )


示例9:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq
  4.      SQLStr
  5.       "SELECT * FROM [接线表$] WHERE (`线种` = 'FLRY-B' and `线径` = 0.35)"
  6.    )        ;无返回字段限制(下面的“首行为标题”必须设定为yes)
  7.    (LIST (CONS "首行为标题" "YES"))
  8.        )


示例10:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq
  4.      SQLStr
  5.       "SELECT * FROM [接线表$] WHERE (F4 = '0.5')"
  6.           ;这里用F4作为标题行,那么下面的lst里面的“首行为标题”必须设定为no
  7.    )        ;查询第四列的值为0.5的数据
  8.    (LIST (CONS "首行为标题" "NO"))
  9.        )


示例11:
  1. ($Ado_get_excel_v$
  2.    (setq xls-path "C:\\Users\\Administrator\\Desktop\\接线表.xls")
  3.    (setq
  4.      SQLStr
  5.       "SELECT * FROM [接线表$] WHERE (F6 > '1000') and (F6 < '2000')"
  6.           ;这里用F4作为标题行,那么下面的lst里面的“首行为标题”必须设定为no
  7.    )        ;查询第六列的值在1000至2000之间的数据
  8.    (LIST (CONS "首行为标题" "NO"))
  9.        )

评分

参与人数 1D豆 +15 贡献 +9 收起 理由
e2002 + 15 + 9 很给力!经验;技术要点;资料分享奖!

查看全部评分

论坛插件加载方法
发帖求助前要善用【论坛搜索】功能,那里可能会有你要找的答案;
如果你在论坛求助问题,并且已经从坛友或者管理的回复中解决了问题,请把帖子标题加上【已解决】;
如何回报帮助你解决问题的坛友,一个好办法就是给对方加【D豆】,加分不会扣除自己的积分,做一个热心并受欢迎的人!
 楼主| 发表于 2020-1-6 10:00:55 | 显示全部楼层
关于lisp调用Go语言和sqlite通信部分,改天会分享出来
论坛插件加载方法
发帖求助前要善用【论坛搜索】功能,那里可能会有你要找的答案;
如果你在论坛求助问题,并且已经从坛友或者管理的回复中解决了问题,请把帖子标题加上【已解决】;
如何回报帮助你解决问题的坛友,一个好办法就是给对方加【D豆】,加分不会扣除自己的积分,做一个热心并受欢迎的人!
回复 支持 反对

使用道具 举报

已领礼包: 86个

财富等级: 招财进宝

发表于 2020-1-6 11:36:48 | 显示全部楼层
留名,虽然不知道有什么用,先留爪再说。。。。
论坛插件加载方法
发帖求助前要善用【论坛搜索】功能,那里可能会有你要找的答案;
如果你在论坛求助问题,并且已经从坛友或者管理的回复中解决了问题,请把帖子标题加上【已解决】;
如何回报帮助你解决问题的坛友,一个好办法就是给对方加【D豆】,加分不会扣除自己的积分,做一个热心并受欢迎的人!
回复 支持 反对

使用道具 举报

发表于 2020-1-10 09:35:02 | 显示全部楼层
太赞了太赞了太赞了很实用
论坛插件加载方法
发帖求助前要善用【论坛搜索】功能,那里可能会有你要找的答案;
如果你在论坛求助问题,并且已经从坛友或者管理的回复中解决了问题,请把帖子标题加上【已解决】;
如何回报帮助你解决问题的坛友,一个好办法就是给对方加【D豆】,加分不会扣除自己的积分,做一个热心并受欢迎的人!
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|申请友链|小黑屋|手机版|Archiver|辽公网安备|晓东CAD家园 ( 辽ICP备15016793号 )  

GMT+8, 2020-9-22 22:19 , Processed in 0.151581 second(s), 43 queries , Gzip On, WinCache On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表