前排提示，导入时EXCEL可能需要处于打开状态，并且EXCEL内容不能包括中文汉字，否则会出现乱码。后面我会再举两种方法，可以导中文的和适合大批量数据的方法。
测试系统TIPTOP GP 3.0
主要用到DDE API
| 函数 | 描述 |
| DDEConnect | This function opens a DDE connection |
| DDEExecute | This function executes a command in the specified program |
| DDEFinish | This function closes a DDE connection |
| DDEFinishAll | This function closes all DDE connections, as well as the DDE server program |
| DDEError | This function returns DDE error information about the last DDE operation |
| DDEPeek | This function retrieves data from the specified program and document using the DDE channel |
| DDEPoke | This function sends data to the specified program and document using the DDE channel |
首先看EXCEL文档内容如下图：
4gl程序如下图：
4gl程序代码如下：
###20120406 BY FMX------LOAD FROM EXCEL-----------
FUNCTION load_from_excel()
DEFINE program VARCHAR(128), #Name of the DDE server program
document VARCHAR(128), #Name of the DDE document
ls_file_path string,
row SMALLINT,
cel SMALLINT,
row_cel STRING,
l_msg VARCHAR(100),
l_len SMALLINT,
l_n SMALLINT,
l_excelseq SMALLINT,
Returns SMALLINT,
l_ac SMALLINT,
l_sl SMALLINT
DEFINE l_tc_sfb RECORD LIKE tc_sfb_file.*
DEFINE cmd VARCHAR(500)
DEFINE res SMALLINT
DEFINE val STRING
DEFINE ret SMALLINT
DEFINE var STRING
DEFINE var2 VARCHAR(40)
DEFINE l_flag VARCHAR(100) #判断必要栏位是否有输入 #No.FUN-690028 CHAR(1)
DEFINE l_sql STRING
LET l_ac=1
LET l_sl=1
LET program="EXCEL"
#开窗选择档案
OPEN WINDOW csfi002_load_w WITH FORM "csf/42f/csfi002_load"
CALL cl_ui_locale("csfi002_load")
INPUT ls_file_path WITHOUT DEFAULTS FROM FORMONLY.doc_path
ON ACTION open_file
CALL cl_browse_file() RETURNING ls_file_path
DISPLAY ls_file_path TO FORMONLY.doc_path
ON ACTION exit
EXIT INPUT
END INPUT
IF INT_FLAG THEN
LET INT_FLAG = FALSE
CLOSE WINDOW csfi002_load_w
RETURN
END IF
###判断路径是否为空
IF ls_file_path IS NULL THEN
CLOSE WINDOW csfi002_load_w
RETURN
ELSE
LET document = ls_file_path
DISPLAY 'ls_file_path = ',document
END IF
LET l_len = LENGTH(document)
IF l_len<=4 THEN
LET document= document CLIPPED,'.xls'
ELSE
IF DOWNSHIFT(document[l_len-3,l_len])!='.xls' THEN
LET document= document CLIPPED,'.xls'
END IF
END IF
CALL ui.Interface.frontCall("WINDDE","DDEConnect",[ program, document ], Returns )
LET row=2 ###从第二行开始
WHILE TRUE
INITIALIZE l_tc_sfb.* TO NULL
#读取工单单号 [tc_sfb01]
LET cel=1 ##第一列
LET row_cel='R',(row USING '<<<<<'),'C',(cel USING '<<<<<')
CALL ui.Interface.frontCall("WINDDE","DDEPeek", [program,document,row_cel], [Returns,val])
LET var2=val CLIPPED
IF (var2 IS NULL OR var2=' ') THEN
ERROR '单号不可为空'
EXIT WHILE
END IF
LET l_tc_sfb.tc_sfb01 = var2
#读取投产期 [tc_sfb022]
LET cel=2 ##第二列
LET row_cel='R',(row USING '<<<<<'),'C',(cel USING '<<<<<')
CALL ui.Interface.frontCall("WINDDE","DDEPeek", [program,document,row_cel], [Returns,val])
LET var2=val CLIPPED
IF (var2 IS NULL OR var2=' ') THEN
ERROR '投产期不可为空'
EXIT WHILE
END IF
LET l_tc_sfb.tc_sfb022 = var2
#读取完成期 [tc_sfb023]
LET cel=3 ##第3列
LET row_cel='R',(row USING '<<<<<'),'C',(cel USING '<<<<<')
CALL ui.Interface.frontCall("WINDDE","DDEPeek", [program,document,row_cel], [Returns,val])
LET var2=val CLIPPED
IF (var2 IS NULL OR var2=' ') THEN
ERROR '完成期不可为空'
EXIT WHILE
END IF
LET l_tc_sfb.tc_sfb023 = var2
#读取机台生产线 [tc_sfb03]
LET cel=4 ##第4列
LET row_cel='R',(row USING '<<<<<'),'C',(cel USING '<<<<<')
CALL ui.Interface.frontCall("WINDDE","DDEPeek", [program,document,row_cel], [Returns,val])
LET var2=val CLIPPED
# IF (var2 IS NULL OR var2=' ') THEN
# ERROR '机台生产线不可为空'
# EXIT WHILE
# END IF
LET l_tc_sfb.tc_sfb03 = var2
#读取是否排班 [tc_sfb05]
LET cel=5 ##第5列
LET row_cel='R',(row USING '<<<<<'),'C',(cel USING '<<<<<')
CALL ui.Interface.frontCall("WINDDE","DDEPeek", [program,document,row_cel], [Returns,val])
LET var2=val CLIPPED
LET l_tc_sfb.tc_sfb05 = var2
#读取白班 [tc_sfb07]
LET cel=6 ##第6列
LET row_cel='R',(row USING '<<<<<'),'C',(cel USING '<<<<<')
CALL ui.Interface.frontCall("WINDDE","DDEPeek", [program,document,row_cel], [Returns,val])
LET var2=val CLIPPED
LET l_tc_sfb.tc_sfb07 = var2
#读取夜班 [tc_sfb08]
LET cel=7 ##第7列
LET row_cel='R',(row USING '<<<<<'),'C',(cel USING '<<<<<')
CALL ui.Interface.frontCall("WINDDE","DDEPeek", [program,document,row_cel], [Returns,val])
LET var2=val CLIPPED
LET l_tc_sfb.tc_sfb08 = var2
LET row=row+1
SELECT COUNT(*) into l_n FROM tc_sfb_file
WHERE tc_sfb01 =l_tc_sfb.tc_sfb01
# AND tc_sfb022 IS NULL
# AND tc_sfb023 IS NULL
IF l_n>0 THEN
UPDATE tc_sfb_file SET tc_sfb022=l_tc_sfb.tc_sfb022,
tc_sfb023=l_tc_sfb.tc_sfb023,
tc_sfb03=l_tc_sfb.tc_sfb03,
tc_sfb05=l_tc_sfb.tc_sfb05,
tc_sfb07=l_tc_sfb.tc_sfb07,
tc_sfb08=l_tc_sfb.tc_sfb08
WHERE tc_sfb01=l_tc_sfb.tc_sfb01
IF STATUS OR SQLCA.SQLERRD[3] = 0 THEN
CALL cl_err3("upd","tc_sfb_file",l_tc_sfb.tc_sfb01,"",STATUS,"","upd tc_sfb01",1) #TQC-660045
LET g_success = 'N' RETURN
END IF
###将导入的开工日和完工日更新到工单上------
UPDATE sfb_file SET sfb13 = l_tc_sfb.tc_sfb022,
sfb15 = l_tc_sfb.tc_sfb023
WHERE sfb01 = l_tc_sfb.tc_sfb01
IF STATUS OR SQLCA.SQLERRD[3] = 0 THEN
CALL cl_err3("upd","sfb_file",l_tc_sfb.tc_sfb01,"",STATUS,"","upd sfb01",1) #TQC-660045
LET g_success = 'N' RETURN
END IF
END IF
###END------------------------
CONTINUE WHILE
IF row>20000 THEN
ERROR '只能显示20000行!现只产生前20000行资料!'
EXIT WHILE
END IF
LET l_ac=l_ac+1
LET l_sl=l_sl+1
END WHILE
-- CALL DDEFinishAll()
CALL cl_end2(1) RETURNING l_flag
CALL i002_b_fill('1=1') #单身
CLOSE WINDOW csfi002_load_w
END FUNCTION
###END*******************************************
转载请注明：赫非域 » 纯英文EXCEL数据导入TIPTOP ERP 系统