求各部门名册人数 - 编程论坛

#2

sdta2021-08-22 19:58

程序代码：

CLOSE DATABASES
lcPath = "C:\Users\Administrator\Desktop\表文件名\表文件名"
SET DEFAULT TO (lcPath)
oExcel = NEWOBJECT("Excel.Application")
USE filenam
BLANK FIELDS rs ALL
SCAN
    WITH oExcel
        .WorkBooks.Open(lcPath + "\" + Fname)
        FOR lnj = 3 TO .ActiveSheet.UsedRange.Rows.Count
            IF ISNULL(.ActiveSheet.Cells(lnj, 2).value)
                EXIT
            ENDIF
        ENDFOR
        .ActiveWorkbook.Close(.F.)
    ENDWITH
    REPLACE rs WITH lnj - 3
ENDSCAN
oExcel.Quit
BROWSE

#3

吹水佬2021-08-22 22:04

只有本站会员才能查看附件，请登录

程序代码：

cDefPath = ADDBS(JUSTPATH(SYS(16)))
SET DEFAULT TO (cDefPath)
USE filenam
REPLACE ALL rs WITH fun()
BROWSE
USE
RETURN

FUNCTION fun()
    xlsx = cDefPath + ALLTRIM(fname)
    oExcel = CREATEOBJECT('Excel.Application')
    oExcel.DisplayAlerts = 0
    oExcel.WorkBooks.Open(xlsx)
    oAS = oExcel.ActiveSheet
    ret = oExcel.CountA(oAS.Range("B:B")) - 1
    oExcel.WorkBooks.Close
    oExcel.Quit
    RETURN ret
ENDFUNC

#4

杂七杂八2021-08-22 22:12

回复 2楼 sdta

谢谢，我要的结果是

只有本站会员才能查看附件，请登录

，能做到吗？

#5

杂七杂八2021-08-22 22:16

回复 3楼吹水佬

谢谢，太谢谢了，

#6

杂七杂八2021-08-22 22:18

文件名的后面XLSX能不显示出来吗？

#7

sdta2021-08-22 22:18

以下是引用杂七杂八在2021-8-22 22:12:36的发言：

谢谢，我要的结果是，能做到吗？

结果不对吗

#8

杂七杂八2021-08-22 22:30

回复 7楼 sdta

给你添麻烦了，没有填写姓名的行不计入。

#9

sdta2021-08-22 23:04

以下是引用杂七杂八在2021-8-22 22:30:28的发言：

给你添麻烦了，没有填写姓名的行不计入。

只有本站会员才能查看附件，请登录

是这个结果吗

#10

杂七杂八2021-08-22 23:29

回复 9楼 sdta

是的，谢谢！

#11

杂七杂八2021-08-22 23:29

考虑到excel 表格文件数目多少不确定，还不能把filenam里fname字段内容固定。

#12

杂七杂八2021-08-23 05:52

程序代码：

set safety OFF
cDefPath = ADDBS(JUSTPATH(SYS(16)))
SET DEFAULT TO (cDefPath)

CREATE tabl filenam(fname c(30),rs n(4))

aa=ADIR(mm,'*.xls')

FOR nn=1 to aa
      M_File=juststem(cDefPath+mm(nn,1))
     INSERT INTO filenam (fname) VALUES (M_File)
  ENDFOR
REPLACE ALL rs WITH fun()
BROWSE
USE
RETURN

FUNCTION fun()
    xlsx = cDefPath + ALLTRIM(fname)
    oExcel = CREATEOBJECT('Excel.Application')
    oExcel.DisplayAlerts = 0
    oExcel.WorkBooks.Open(xlsx)
    oAS = oExcel.ActiveSheet
    ret = oExcel.CountA(oAS.Range("B:B")) - 1
    oExcel.WorkBooks.Close
    oExcel.Quit
    RETURN ret
ENDFUNC

#13

杂七杂八2021-08-23 05:55

在老师的代码上，自行修改了一下。
想在最后一行加上：合计人数。没有成功。

#14

杂七杂八2021-08-23 06:19

程序代码：

set safety OFF
cDefPath = ADDBS(JUSTPATH(SYS(16)))
SET DEFAULT TO (cDefPath)

CREATE tabl filenam(fname c(30),rs n(4))

aa=ADIR(mm,'*.xls')

FOR nn=1 to aa
      M_File=juststem(cDefPath+mm(nn,1))
     INSERT INTO filenam (fname) VALUES (M_File)
  ENDFOR
REPLACE ALL rs WITH fun()

insert into filenam (fname) values ("合计")
     sele sum(rs) from filenam into array kk
     replace rs  with KK  for fname='合计'
BROWSE

USE
RETURN

FUNCTION fun()
    xlsx = cDefPath + ALLTRIM(fname)
    oExcel = CREATEOBJECT('Excel.Application')
    oExcel.DisplayAlerts = 0
    oExcel.WorkBooks.Open(xlsx)
    oAS = oExcel.ActiveSheet
    ret = oExcel.CountA(oAS.Range("B:B")) - 1
    oExcel.WorkBooks.Close
    oExcel.Quit
    RETURN ret
ENDFUNC

代码还能优化吗？谢谢

#15

杂七杂八2021-08-23 06:21

用VFP6.0的

#16

吹水佬2021-08-23 09:16

回复 14楼杂七杂八

只有本站会员才能查看附件，请登录

程序代码：

set safety OFF
cDefPath = ADDBS(JUSTPATH(SYS(16)))
SET DEFAULT TO (cDefPath)

aa=ADIR(mm,'*.xls')
CREATE CURSOR filenam (fname c(30))
INSERT INTO filenam FROM ARRAY mm
SELECT fname, 0000 as rs FROM filenam INTO CURSOR rstj READWRITE
oExcel = CREATEOBJECT("Excel.Application")
oExcel.DisplayAlerts = 0
nCount = 0
REPLACE ALL rs WITH fun() IN "rstj"
INSERT INTO rstj VALUES ("合计",nCount)
oExcel.Quit
SELECT * FROM rstj
RETURN

FUNCTION fun()
    oExcel.WorkBooks.Open(cDefPath + ALLTRIM(rstj.fname))
    oAS = oExcel.ActiveSheet
    ret = oExcel.CountA(oAS.Range("B:B")) - 1
    oExcel.WorkBooks.Close
    nCount = nCount + ret
    RETURN ret
ENDFUNC

[此贴子已经被作者于2021-8-23 09:20编辑过]

#17

杂七杂八2021-08-23 17:09

SELECT fname, 0000 as rs FROM filenam INTO CURSOR rstj READWRITE &&&&出现程序错误“命令中有不能识别的短语或关键字”对话框

#18

吹水佬2021-08-23 21:07

回复 17楼杂七杂八

可能是VFP6兼容问题
改几句
**    CREATE CURSOR filenam (fname c(30))
**    INSERT INTO filenam FROM ARRAY mm
**    SELECT fname, 0000 as rs FROM filenam INTO CURSOR rstj READWRITE

CREATE CURSOR rstj (fname c(30))
INSERT INTO rstj FROM ARRAY mm
ALTER TABLE rstj ADD rs I

#19

xuminxz2021-08-24 10:45

回复 17楼杂七杂八

SELECT fname, 0000 as rs FROM filenam INTO CURSOR rstj READWRITE &&&&出现程序错误“命令中有不能识别的短语或关键字”对话框
VFP6.0中，select 建立的临时表，不支持ReadWrite,不能更新。