前期文章中，有两三种导入方法。有的需要EXCEL处于打开状态，有的需要转成CSV。本例是最常用方法，如你想象中的样子。
。。。
FUNCTION t110_impexcel()
DEFINE ls_str STRING,
ls_file STRING,
ls_loction STRING,
gs_location STRING
LET ls_str =cl_getmsg("lib-201",g_lang)
WHILE TRUE
PROMPT ls_str CLIPPED FOR gs_location
ATTRIBUTE(WITHOUT defaults)
ON action accept
EXIT WHILE
ON action cancel
LET gs_location=NULL
EXIT WHILE
ON action browse_document
LET ls_file = cl_browse_file()
IF ls_file IS NOT NULL THEN
LET gs_location = ls_file
END IF
ON idle g_idle_seconds
CALL cl_on_idle()
RETURN
END PROMPT
END WHILE
IF NOT cl_null(gs_location) THEN
CALL t110_impexcel_p(gs_location)
END IF
END FUNCTION
#17011201
FUNCTION t110_impexcel_p(p_fileloc)
DEFINE l_flag LIKE type_file.chr1
DEFINE p_fileloc string
DEFINE xlApp,iRes,iRow,i,j INTEGER
DEFINE li_k,l_cnt LIKE type_file.num5
DEFINE li_i_r LIKE type_file.num5
DEFINE lr_err DYNAMIC ARRAY OF RECORD
line string,
key1 string,
err string
END RECORD
DEFINE l_abb RECORD LIKE abb_file.*
CALL s_showmsg_init()
LET l_flag='Y'
BEGIN WORK
CALL ui.interface.frontCall('WinCOM','CreateInstance',['Excel.Application'],[xlApp])
IF xlApp <> -1 THEN
CALL ui.interface.frontCall('WinCOM','CallMethod',[xlApp,'WorkBooks.Open',p_fileloc],[iRes])
IF iRes <> -1 THEN
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.UsedRange.Rows.Count'],[iRow])
IF iRow > 1 THEN
LET li_k = 1
FOR i=2 TO iRow
INITIALIZE l_abb.* TO NULL
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',1).Value'],[l_abb.abb03])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',2).Value'],[l_abb.abb04])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',3).Value'],[l_abb.abb05])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',4).Value'],[l_abb.abb06])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',5).Value'],[l_abb.abb24])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',6).Value'],[l_abb.abb25])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',7).Value'],[l_abb.abb07f])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',8).Value'],[l_abb.abb07])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',9).Value'],[l_abb.abb08])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',10).Value'],[l_abb.abb11])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',11).Value'],[l_abb.abb12])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',12).Value'],[l_abb.abb13])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',13).Value'],[l_abb.abb14])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',14).Value'],[l_abb.abb31])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',15).Value'],[l_abb.abb35])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',16).Value'],[l_abb.abb36])
CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',17).Value'],[l_abb.abb37])
LET l_abb.abb00=g_aba.aba00
LET l_abb.abb01=g_aba.aba01
LET l_abb.abb02=i-1
LET l_abb.abblegal=g_aba.abalegal
LET l_cnt=0
SELECT COUNT(0) INTO l_cnt FROM aag_file WHERE aag01=l_abb.abb03 AND aagacti='Y'
IF l_cnt=0 THEN
LET l_flag='N'
CALL s_errmsg('',i-1,l_abb.abb03,'cgl-005',1)
END IF
LET l_cnt=0
IF NOT cl_null(l_abb.abb05) AND l_abb.abb05<>' ' THEN #部门不为空时，才进行此项
SELECT count(0) INTO l_cnt FROM gem_file WHERE gem01=l_abb.abb05 AND gemacti='Y'
IF l_cnt=0 THEN
LET l_flag='N'
CALL s_errmsg('',i-1,l_abb.abb05,'cgl-006',1)
END IF
END IF
LET l_cnt=0
SELECT count(0) INTO l_cnt FROM azi_file WHERE azi01=l_abb.abb24 AND aziacti='Y'
IF l_cnt=0 THEN
LET l_flag='N'
CALL s_errmsg('',i-1,l_abb.abb24,'cgl-008',1)
END IF
INSERT INTO abb_file VALUES(l_abb.*)
IF SQLCA.sqlcode OR SQLCA.sqlerrd[3]=0 THEN
LET l_flag='N'
CALL s_errmsg('',i-1,SQLCA.sqlcode,'cgl-007',1)
CONTINUE FOR
END IF
END FOR
END IF
ELSE
MESSAGE 'NO file'
END IF
ELSE
MESSAGE 'NO EXCEl'
END IF
IF l_flag='Y' THEN
SELECT sum(abb07) INTO g_aba.aba08 FROM abb_file WHERE abb01=g_aba.aba01 AND abb06='1'
SELECT sum(abb07) INTO g_aba.aba09 FROM abb_file WHERE abb01=g_aba.aba01 AND abb06='2'
IF cl_null(g_aba.aba08) THEN LET g_aba.aba08=0 END IF
IF cl_null(g_aba.aba09) THEN LET g_aba.aba09=0 END IF
UPDATE aba_file SET aba08=g_aba.aba08,
aba09=g_aba.aba09
WHERE aba01=g_aba.aba01
IF SQLCA.sqlcode THEN
LET l_flag='N'
CALL s_errmsg('',0,SQLCA.sqlcode,'cgl-009',1)
END IF
END IF
CALL ui.interface.frontCall('WinCOM','CallMethod',[xlApp,'Quit'],[iRes])
CALL ui.interface.frontCall('WinCOM','ReleaseInstance',[xlApp],[iRes])
IF l_flag='N' THEN
ROLLBACK WORK
CALL s_showmsg()
ELSE
COMMIT WORK
DISPLAY BY NAME g_aba.aba08,g_aba.aba09
CALL t110_show()
END IF
END FUNCTION
转载请注明：赫非域 » TIPTOP EXCEL导入正常方法