T100excel导入写法模板

PUBLIC FUNCTION cxmt500_01_excel_imp(p_excelname)
DEFINE p_excelname      LIKE type_t.chr1000  #excel档名
DEFINE r_success        LIKE type_t.num5
DEFINE L_success        LIKE type_t.num5
DEFINE l_excelname      STRING               #excel档名
DEFINE l_count          LIKE type_t.num10
DEFINE li_i             LIKE type_t.num10
DEFINE li_j             LIKE type_t.num10
DEFINE xlapp,iRes,iRow  LIKE type_t.num5
DEFINE l_sql            STRING
DEFINE l_xmdc            RECORD
       seq LIKE type_t.num5, 
   l_xmda004 LIKE type_t.chr10, 
   l_xmdc027 LIKE type_t.chr500, 
   l_xmdc027_desc LIKE type_t.chr500, 
   l_xmdc027_desc_1 LIKE type_t.chr500,    
   imaa001 LIKE imaa_t.imaa001, 
   l_xmdc007 LIKE type_t.num20_6, 
   l_xmdc012 LIKE type_t.dat, 
   l_oofg001 LIKE type_t.chr10, 
   imaa003 LIKE imaa_t.imaa003, 
   imaa009 LIKE imaa_t.imaa009, 
   l_xmdc050 LIKE type_t.chr500
END RECORD   
DEFINE l_cnt      LIKE type_t.num5

LET l_sql="INSERT INTO cxmt500_01_tmp VALUES
            (?,?,?,?,?,
             ?,?,?,?,?,
             ?,?)"
PREPARE cxmt500_01_ins_p FROM l_sql             


WHENEVER ERROR CONTINUE
   LET r_success = TRUE


   LET l_count = LENGTH(p_excelname CLIPPED)
   LET l_excelname=p_excelname CLIPPED
   ###创建EXCEL实例
   CALL ui.interface.frontCall('WinCOM','CreateInstance',
                               ['Excel.Application'],[xlApp])
   IF xlApp <> -1 THEN####没有这个文件
      CALL ui.interface.frontCall('WinCOM','CallMethod',####新建excel文件
                                  [xlApp,'WorkBooks.Open',l_excelname],[iRes])
      IF iRes <> -1 THEN####excel表格为空
         CALL ui.interface.frontCall('WinCOM','GetProperty',
              [xlApp,'ActiveSheet.UsedRange.Rows.Count'],[iRow])
          IF iRow > 1 THEN######获取excel数据
            FOR li_i = 2 TO iRow
                 INITIALIZE l_xmdc.* TO NULL
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',1).Value'],[l_xmdc.seq])                              
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',2).Value'],[l_xmdc.l_xmda004])        
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',3).Value'],[l_xmdc.l_xmdc027])        
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',4).Value'],[l_xmdc.l_xmdc027_desc])   
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',5).Value'],[l_xmdc.l_xmdc027_desc_1])     
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',6).Value'],[l_xmdc.imaa001])        
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',7).Value'],[l_xmdc.l_xmdc007])      
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',8).Value'],[l_xmdc.l_xmdc012])            
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',9).Value'],[l_xmdc.l_xmdc050])     
      

                  #栏位管控
                   #1、序号栏位不允许重复；
                   IF cl_null(l_xmdc.seq) THEN 
                   #检查客户代号是否存在
                      INITIALIZE g_errparam TO NULL
                      LET g_errparam.code = 'aoo-00146'
                      LET g_errparam.extend = " 项次：",l_xmdc.seq,"不能为空"
                      LET g_errparam.popup = TRUE
                      CALL cl_err()                            
                      LET r_success = FALSE                         
                   END IF                                           
                   SELECT COUNT(*) INTO l_cnt
                     FROM cxmt500_01_tmp
                    WHERE seq=l_xmdc.seq
                   IF l_cnt>0 THEN 
                     INITIALIZE g_errparam TO NULL
                     LET g_errparam.code = 'aoo-00146'
                     LET g_errparam.extend = "客户编号：",l_xmdc.l_xmda004," 项次：",l_xmdc.seq,"重复"
                     LET g_errparam.popup = TRUE
                     CALL cl_err()
                     LET r_success = FALSE          
#                     EXIT FOR
                   END IF 
                   IF NOT cl_null(l_xmdc.l_xmda004) THEN 
                   #检查客户代号是否存在
                      INITIALIZE g_chkparam.* TO NULL
                      
                      #設定g_chkparam.*的參數
                      LET g_chkparam.arg1 = l_xmdc.l_xmda004               
                      IF NOT  cl_chk_exist("v_pmaa001_10") THEN
                         #檢查成功時後續處理
                         INITIALIZE g_errparam TO NULL
                         LET g_errparam.code = 'aoo-00146'
                         LET g_errparam.extend = " 项次：",l_xmdc.seq,""
                         LET g_errparam.popup = TRUE
                         CALL cl_err()                            
                         LET r_success = FALSE          
#                        EXIT FOR
                      END IF                
                   END IF   
                   
#                   IF NOT cl_null(l_xmdc.imaa001) THEN 
                   #检查料件编码直接赋为空值
                   LET l_xmdc.imaa001 = ''
#                      INITIALIZE g_chkparam.* TO NULL
#                      
#                      #設定g_chkparam.*的參數
#                      LET g_chkparam.arg1 = l_xmdc.imaa001             
#                      IF NOT  cl_chk_exist("v_imaa001") THEN
#                         #檢查成功時後續處理
#                         INITIALIZE g_errparam TO NULL
#                         LET g_errparam.code = 'aoo-00146'
#                         LET g_errparam.extend = " 项次：",l_xmdc.seq,""
#                         LET g_errparam.popup = TRUE
#                         CALL cl_err()                               
#                        LET r_success = FALSE          
##                        EXIT FOR
#                      END IF                
#                   END IF       
                   IF  cl_null(l_xmdc.l_xmdc007) THEN
                     LET l_xmdc.l_xmdc007=0                     
                   END IF 
                   IF  cl_null(l_xmdc.l_xmdc012) THEN
                     LET l_xmdc.l_xmdc012=g_today                   
                   END IF                    
                   #按客户代码+客户料号在aimm213程序中匹配成品编码
                   CALL cxmt500_01_match_imaa001(l_xmdc.l_xmda004,l_xmdc.l_xmdc027) 
                     RETURNING l_xmdc.imaa001,l_xmdc.imaa003,l_xmdc.imaa009
                   EXECUTE cxmt500_01_ins_p USING l_xmdc.*
            END FOR
         END IF                   

      ELSE
         INITIALIZE g_errparam TO NULL
         LET g_errparam.code = 'aoo-00146'
         LET g_errparam.extend = 'NO FILE'
         LET g_errparam.popup = TRUE
         CALL cl_err()

         LET r_success = FALSE
      END IF
   ELSE
      INITIALIZE g_errparam TO NULL
      LET g_errparam.code = 'aoo-00146'
      LET g_errparam.extend = 'NO EXCEL'
      LET g_errparam.popup = TRUE
      CALL cl_err()

      LET r_success = FALSE
   END IF

   CALL ui.interface.frontCall('WinCOM','CallMethod',[xlApp,'Quit'],[iRes])
   CALL ui.interface.frontCall('WinCOM','ReleaseInstance',[xlApp],[iRes])




   RETURN r_success
END FUNCTION