T100 EXCEL导入 关键代码

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  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                    
                   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

转载请注明：赫非域 » T100 EXCEL导入 关键代码