EXCEL数据导入TIPTOP ERP 系统

测试系统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*******************************************