弹出File Browser窗口
1 PRIVATE FUNCTION cxrt020_open_file()
2 DEFINE l_dir LIKE type_t.chr500
3 DEFINE r_success LIKE type_t.num5
4 #
5 LET r_success = FALSE
6 CALL cl_client_browse_file() RETURNING l_dir
7 IF NOT cl_null(l_dir) THEN
8 CALL cxrt020_ins_excel(l_dir) RETURNING r_success
9 END IF
10 RETURN r_success
11
12 END FUNCTION
######按路径，把xls的数据按格式顺序ins到数据表，注意xls的文件名不能有中文或特殊符号，最好全英文文件名；
1 PRIVATE FUNCTION cxrt020_ins_excel(p_excelname)
2 DEFINE p_excelname LIKE type_t.chr1000 #excel档名
3 DEFINE r_success LIKE type_t.num5
4 DEFINE l_excelname STRING #excel档名
5 DEFINE l_count LIKE type_t.num10
6 DEFINE li_i LIKE type_t.num10
7 DEFINE li_j LIKE type_t.num10
8 DEFINE xlapp,iRes,iRow LIKE type_t.num5
9 DEFINE l_xrsguc RECORD LIKE xrsguc_t.*
10 DEFINE l_today LIKE type_t.dat
11 DEFINE l_n LIKE type_t.num5
12
13 WHENEVER ERROR CONTINUE
14 LET r_success = TRUE
15
16 LET l_today= cl_get_current()
17 LET l_count = LENGTH(p_excelname CLIPPED)
18 #转换路径分隔符
19 FOR li_i = 1 TO l_count
20 IF p_excelname[li_i,li_i] ="/" THEN
21 LET l_excelname = l_excelname CLIPPED,'\'
22 ELSE
23 LET l_excelname = l_excelname CLIPPED,p_excelname[li_i,li_i]
24 END IF
25 END FOR
26
27 CALL ui.interface.frontCall('WinCOM','CreateInstance',
28 ['Excel.Application'],[xlApp])
29 IF xlApp <> -1 THEN
30 CALL ui.interface.frontCall('WinCOM','CallMethod',
31 [xlApp,'WorkBooks.Open',l_excelname],[iRes])
32 IF iRes <> -1 THEN
33 CALL ui.interface.frontCall('WinCOM','GetProperty',
34 [xlApp,'ActiveSheet.UsedRange.Rows.Count'],[iRow])
35 IF iRow > 1 THEN
36 FOR li_i = 2 TO iRow
37 INITIALIZE l_xrsguc.* TO NULL
38 LET l_xrsguc.xrsgucent = g_enterprise
39 LET l_xrsguc.xrsgucsite = g_site
40 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',1).Value'],[l_xrsguc.xrsgucdocno])
41 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',2).Value'],[l_xrsguc.xrsguc001])
42 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',3).Value'],[l_xrsguc.xrsguc002])
43 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',4).Value'],[l_xrsguc.xrsguc003])
44 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',5).Value'],[l_xrsguc.xrsguc004])
45 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',6).Value'],[l_xrsguc.xrsguc005])
46 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',7).Value'],[l_xrsguc.xrsguc006])
47 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',8).Value'],[l_xrsguc.xrsguc007])
48 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',9).Value'],[l_xrsguc.xrsguc008])
49 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',10).Value'],[l_xrsguc.xrsguc009])
50 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',11).Value'],[l_xrsguc.xrsguc010])
51 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',12).Value'],[l_xrsguc.xrsguc011])
52 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',13).Value'],[l_xrsguc.xrsguc012])
53 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',14).Value'],[l_xrsguc.xrsguc013])
54 #
55 INSERT INTO xrsguc_t VALUES l_xrsguc.*
56 IF SQLCA.sqlcode THEN
57 INITIALIZE g_errparam TO NULL
58 LET g_errparam.code = SQLCA.sqlcode
59 LET g_errparam.extend = 'INSERT INTO xrsguc_t'
60 LET g_errparam.popup = FALSE
61 CALL cl_err()
62 LET r_success = FALSE
63 EXIT FOR
64 END IF
65 END FOR
66 END IF
67 ELSE
68 INITIALIZE g_errparam TO NULL
69 LET g_errparam.code = 'axc-00387'
70 LET g_errparam.extend = '' #NO FILE
71 LET g_errparam.popup = TRUE
72 CALL cl_err()
73 LET r_success = FALSE
74 END IF
75 ELSE
76 INITIALIZE g_errparam TO NULL
77 LET g_errparam.code = 'axc-00387'
78 LET g_errparam.extend = '' #NO EXCEL
79 LET g_errparam.popup = TRUE
80 CALL cl_err()
81 LET r_success = FALSE
82 END IF
83
84 CALL ui.interface.frontCall('WinCOM','CallMethod',[xlApp,'Quit'],[iRes])
85 CALL ui.interface.frontCall('WinCOM','ReleaseInstance',[xlApp],[iRes])
86
87 RETURN r_success
88
89 END FUNCTION