TA的每日心情 | 开心 2024-8-31 15:58 |
---|
签到天数: 89 天 [LV.6]常住居民II
管理员
- 积分
- 3366
|
(defun $dosql-csv$
(xls-f sqlstr LST / ACE ACE-F
ACE-F-64 ACE-F-86 adodb ado-sql
dao data hdr JET stop $ado-excel-getfields$
)
;(setq sql "select * from [接线表.csv] where `线号` = 'Q948fB'")
;(setq csv-f "C:\\Users\\Administrator\\Desktop\\csvdata\\")
;(setq d($dosql-csv$ csv-f sql "db"))
(defun $ado-excel-getfields$ (RecordSet / f i n ns)
;获取字段
(if (and RecordSet
(setq
f
(vl-catch-all-apply
(function (lambda () (vlax-get-property RecordSet "fields"))
)
)
)
(not (vl-catch-all-error-p f))
)
(progn
(setq i 0)
(setq ns nil)
(setq n (vla-get-count f))
(repeat n
(setq n
(vl-catch-all-apply
'vla-get-name
(list
(vl-catch-all-apply 'vlax-get-property (list f "item" i))
)
)
)
(set 'ns (cons n ns))
(setq i (1+ i))
)
(setq ns (reverse ns))
)
)
ns
)
(cond ((and LST
(= (type LST) 'list)
(= (type (car LST)) 'list)
(assoc "首行为标题" lst)
)
(setq HDR (cdr (assoc "首行为标题" lst)))
)
(t
(setq HDR "YES")
)
)
(setq dao (vlax-get-or-create-object
"{367E582C-F71C-4BF9-AA1B-9F62B793E9C5}"
)
)
(if (or (not dao) (vl-catch-all-error-p dao))
(progn (vl-catch-all-apply
(function (lambda () ($install-excel-dao$)))
)
(setq stop (+ (getvar "DATE") (/ 5 86400.0)))
(while (> stop (getvar "DATE")) (princ ""))
)
)
(vl-catch-all-apply 'vlax-release-object (list dao))
(setq Adodb (vl-catch-all-apply
'vlax-create-object
(list "Adodb.Connection")
)
)
(if (vl-catch-all-error-p Adodb)
(print "创建 Adodb.Connection 对象失败")
)
(AND xls-f
(SETQ xls-f (vl-string-right-trim "\\" xls-f))
(SETQ xls-f (STRCAT xls-f "\\")) ;尾巴上必须有\\
)
(if (vl-catch-all-error-p Adodb)
(print (vl-catch-all-error-message Adodb))
(progn
(if (and Adodb xls-f HDR (WCMATCH HDR "[,YES,NO,]"))
(progn (setq JET
(vl-catch-all-apply
(function
(lambda ()
(vlax-invoke
Adodb
'Open
(strcat
"Provider=Microsoft.jet.OLEDB.4.0;Data Source="
xls-f
";Extended Properties='text;HDR=YES;FMT=Delimited()';Persist Security Info=False"
)
)
)
)
)
)
)
)
(IF
(and (vl-catch-all-error-p JET) (vl-catch-all-error-p ACE))
(repeat 10 (print "请提前安装excel的dao组件"))
)
(setq ADO-SQL
(vl-catch-all-apply
(function (lambda ()
(vlax-invoke Adodb 'Execute SQLStr)
)
)
)
)
(IF (VL-CATCH-ALL-ERROR-P ADO-SQL)
(PRINT (vl-catch-all-error-message ADO-SQL))
)
(if (not (vl-catch-all-error-p ADO-SQL))
(progn
(setq data ($ado-excel-getrows$ ADO-SQL))
)
(print (vl-catch-all-error-message ADO-SQL))
)
(vl-catch-all-apply
(function (lambda ()
(vlax-invoke ADO-SQL "CLOSE")
)
)
) ;关闭
(vl-catch-all-apply
(function (lambda ()
(vlax-invoke Adodb "CLOSE")
)
)
) ;关闭
(vl-catch-all-apply
(function (lambda () (vlax-release-object Adodb)))
) ;释放
(setq ACE nil
ACE-F nil
ACE-F-64 nil
ACE-F-86 nil
adodb nil
ado-sql nil
dao nil
JET nil
stop nil
)
)
)
(cond
((and data
lst
(or (member lst (list "点表" "db" "DB"))
(member (cdr (assoc "返回格式" lst))
(list "点表" "db" "DB")
)
)
)
(SETQ H (CAR DATA))
(SETQ DATA (MAPCAR (FUNCTION (LAMBDA (A) (MAPCAR 'CONS H A)))
(CDR DATA)
)
)
)
((and data
lst
(or (member lst (list "矩阵" "jz" "JZ"))
(member (cdr (assoc "返回格式" lst))
(list "矩阵" "jz" "JZ")
)
)
)
t
)
)
DATA
) |
|