*!* 表a(序号,查找,返回,区域1,区域2,区域3,区域4)
*!* int(rand()*100)+1 &&产生1到100的随机整数
create cursor 表a(序号 i,查找 c(10),返回 c(20),区域1 c(20),区域2 c(20),区域3 c(20),区域4 c(20),区域5 c(20),区域6 c(20),区域7 c(20))
local ii
for ii=1 to 10
insert into 表a(序号,查找,返回,区域1,区域2,区域3,区域4,区域5,区域6,区域7) ;
values(ii,tran(int(rand()*100)+1),'',tran(int(rand()*100)+1),tran(int(rand()*100)+1),tran(int(rand()*100)+1),tran(int(rand()*100)+1),tran(int(rand()*100)+1),tran(int(rand()*100)+1),tran(int(rand()*100)+1))
endfor
LOCAL lookup_value,start_row_num,start_col_num,end_row_num,end_col_num,col_index_num,range_lookup,table_name
=vlookup(2,1,4,100,9,1,0,'表a')
BROWSE
*!* 表a(序号,查找,返回,区域1,区域2,区域3,区域4)，“序号,查找,返回”等3个字段不能少，其他的字段“区域1,区域2,区域3,区域4”可以随便增加、修改
*!* 如果 查找的值含有 ' 就会报错。不知道怎么处理。。
FUNCTION vlookup(lookup_value,start_row_num,start_col_num,end_row_num,end_col_num,col_index_num,range_lookup,table_name)
LOCAL nReccount,cFcount,lcAlias
nReccount = 0
cFcount = 0
lcAlias=ALIAS() &&保存现场
IF TYPE("table_name") = "C" OR !EMPTY(table_name)
IF !USED(table_name)
MESSAGEBOX("表没有打开" + table_name,16,"提示")
RETURN .f.
ELSE
nReccount = RECCOUNT(table_name)
nFcount = FCOUNT(table_name)
ENDIF
ELSE
MESSAGEBOX("表名参数不正确" + TRANSFORM(table_name),16,"提示")
RETURN .f.
ENDIF
IF TYPE("start_row_num") # "N" OR !BETWEEN(start_row_num,1,nReccount)
start_row_num = 1
ENDIF
IF TYPE("start_col_num") # "N" OR !BETWEEN(start_col_num,1,nFcount)
start_col_num = 1
ENDIF
IF TYPE("end_row_num") # "N" OR !BETWEEN(end_row_num,1,nReccount)
end_row_num = nReccount &&记录数
ENDIF
IF TYPE("start_col_num") # "N" OR !BETWEEN(end_col_num,1,nFcount)
end_col_num = nFcount &&字段数
ENDIF
IF TYPE("col_index_num") # "N"
col_index_num = 1
ENDIF
IF TYPE("range_lookup") = "N" AND (range_lookup = 0 OR range_lookup = 1)
ELSE
range_lookup = 0
ENDIF
LOCAL c查找,c返回,c查找1,a字段[nFcount],tmp_table_name
tmp_table_name = 'tmp_' + table_name
SELECT (table_name)
FOR ii = 1 TO nFcount
a字段[ii] = Field(ii)
ENDFOR
SELECT 序号,查找,返回 FROM (table_name) INTO CURSOR (tmp_table_name) READWRITE
SELECT (tmp_table_name)
GO TOP
SCAN
c查找1 = "'" + ALLTRIM(TRANSFORM(查找)) + "'"
c查找=""
FOR ii = start_col_num TO end_col_num
IF range_lookup = 0
c查找 = IIF(EMPTY(c查找), "allt(" + a字段[ii] + ") == tran(" + c查找1 + ")",c查找 + " or " + "allt(" + a字段[ii] + ") == tran(" + c查找1 + ")")
ELSE
c查找 = IIF(EMPTY(c查找), "tran(" + c查找1 + ") $ allt(" + a字段[ii] + ")" ,c查找 + " or " + "tran(" + c查找1 + ") $ allt(" + a字段[ii] + ")")
ENDIF
ENDFOR
c查找 = "LOCATE FOR (" + c查找 + ") and betw(序号,start_row_num,end_row_num)"
SELECT (table_name)
&c查找 &&宏查找
IF FOUND()
c返回 = TRANSFORM(&a字段[col_index_num])
ELSE
c返回 =""
ENDIF
SELECT (tmp_table_name)
REPLACE 返回 WITH c返回
ENDSCAN
UPDATE t1 SET t1.返回=t2.返回 from (table_name) as t1,(tmp_table_name) as t2 WHERE t1.序号 = t2.序号
*!* 恢复现场
IF !EMPTY(lcAlias)
SELECT (lcAlias)
ENDIF
RETURN .t.
ENDFUNC
2016-12-08 22:59
