TIPTOP ERP 与 Excel交互的编程应用

TIPTOP ERP 与 Excel交互的编程应用

1、TIPTOP ERP 汇出资料到Excel

2、Excel导入资料到TIPTOP ERP系统

      A.Excel导入资料到TIPTOP ERP的原理

          调用客户端GDC目录/bin下面的Windde.dll或Wincom.dll组件,然后打开本机安装的Excel软件处理资料

          注意：Windde.dll或Wincom.dll都是后台偷偷打开了Excel，前台是看不到的

                    你可以在任务管理器里看到每导入一个Excel就会开启一个EXCEL.EXE的进程

         

      B.关键代码编写

          B-1.根据程序处理Excel资料的规则，coder先将excel模板放到服务器上，这里代码写下载功能,供用户下载excel资料模板

          #demo 

FUNCTION p413_download_excel()   #服务器下载模板
    DEFINE l_linuxPath   STRING
    DEFINE l_winPath     STRING
    DEFINE l_status      LIKE type_file.num5

    LET l_linuxPath = "$CUST/cxm/4gl/cxmp413.xls"
    LET l_winPath   = "c:\\tiptop\\cxmp413.xls"
    LET l_status = cl_download_file(l_linuxPath,l_winPath)
    IF l_status THEN
       CALL cl_err(l_linuxPath,"amd-020",1)
    ELSE
       CALL cl_err(l_linuxPath,"amd-021",1)    
    END IF
END FUNCTION

          B-2.处理excel资料，将excel资料导入TIPTOP ERP系统

          #demo

FUNCTION p413_upload_excel()   #导入Excel
    DEFINE l_sql      STRING
    DEFINE l_prostr   LIKE ze_file.ze03
    DEFINE l_fileLocation   STRING
    DEFINE xlApp,iRes,iRow  INTEGER
    DEFINE i          LIKE type_file.num5
    DEFINE l_cnt      LIKE type_file.num5
    DEFINE l_oeb16    LIKE oeb_file.oeb16
    DEFINE l_errcnt   LIKE type_file.num5
    DEFINE l_msg      LIKE type_file.chr1000
    DEFINE g_oeb      DYNAMIC ARRAY OF RECORD
                      seq   LIKE    type_file.num5,
                      oeb01 LIKE    oeb_file.oeb01,
                      oeb03 LIKE    oeb_file.oeb03,
                      oeb16 LIKE    oeb_file.oeb16
                      END RECORD
    
    CALL g_oeb.clear()
    
    LET l_prostr = cl_getmsg("lib-201", g_lang)
    WHILE TRUE   #选择要导入资料的Excel文件
        PROMPT l_prostr CLIPPED FOR l_fileLocation ATTRIBUTE(WITHOUT DEFAULTS)
           ON ACTION browse_document
              LET l_fileLocation = cl_browse_file()

           ON ACTION ACCEPT
              EXIT WHILE

           ON ACTION CANCEL
              EXIT WHILE

           ON IDLE g_idle_seconds
              CALL cl_on_idle()
              RETURN
        END PROMPT
    END WHILE

    LET l_errcnt = 0
    CALL ui.interface.frontCall('WinCOM','CreateInstance',['Excel.Application'],[xlApp])
    IF xlApp <> -1 THEN
        CALL ui.interface.frontCall('WinCOM','CallMethod',[xlApp,'WorkBooks.Open',l_fileLocation],[iRes])
        IF iRes <> -1 THEN
           CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.UsedRange.Rows.Count'],[iRow])
           IF iRow > 1 THEN
              FOR i = 2 TO iRow  #第2行开始，第1行为Excel抬头
                 INITIALIZE l_oeb16 TO NULL
                 #Excel 1 列
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',1).Value'],[g_oeb[i-1].seq])
                 #Excel 2 列
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',2).Value'],[g_oeb[i-1].oeb01])
                 #Excel 3 列
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',3).Value'],[g_oeb[i-1].oeb03])
                 #Excel 4 列
                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||',4).Value'],[g_oeb[i-1].oeb16])
              END FOR
           END IF
        END IF
    END IF
    CALL ui.interface.frontCall('WinCOM','CallMethod',[xlApp,'Quit'],[iRes])
    CALL ui.interface.frontCall('WinCOM','ReleaseInstance',[xlApp],[iRes])

    IF g_oeb.getLength() <= 0 THEN
       CALL cl_err('','anm-259',1)
    ELSE
       LET g_rec_b = g_oeb.getLength()
       LET l_msg = '共导入',g_rec_b,'笔！'
       CALL cl_msgany(0,0,l_msg)
       CALL p413_b()
    END IF 
END FUNCTION

http://blog.csdn.net/yihuiworld