给鼎捷易拓 TIPTOP 作业新增 EXCEL 批量导入更新功能(4gl)

应用场景

  • 批量新增数据;
  • 批量更新数据;

应用分析

在实际操作过程中,比如开帐时,有成千上万比资料需要新增,如果一笔笔人工新增操作,十分耗费人力效率,如果由后台批量更新,维护人员会很辛苦。因此考虑在前端作业新增 EXCEL 导入功能,通过固定的模板,由前端作业自动分析 EXCEL 内容,来实现批量新增、更新动作,甚至于批量删除(很危险的操作)。

应用实例

现有作业 amri600,由于采购资料和生产管理经常发生变动,人工修改耗费时间长,效率低下,因此新增批量更新 action,逐行逐列读取出数据,判断是否要更新。

4gl 代码如下:

......
FUNCTION i600_menu()
......
        ON ACTION import_update
           LET g_action_choice="import_update"
           IF cl_chk_act_auth() THEN
              CALL i600_e()
           END IF
......
FUNCTION i600_e()
   OPEN WINDOW i600_e AT 2,4 WITH FORM "cmr/42f/amri600_e" 
      ATTRIBUTE (STYLE = g_win_style CLIPPED)
   CALL cl_ui_init()
   CALL i600_ex()
   CLOSE WINDOW i600_e
   LET g_wc = " 1=1"
   CALL i600_show()
END FUNCTION
FUNCTION i600_ex()
 DEFINE l_file    STRING
 DEFINE l_flag    LIKE type_file.chr1
 DEFINE li_result LIKE type_file.chr20
 DEFINE l_t1      LIKE oay_file.oayslip
 DEFINE l_msg     STRING
   WHILE TRUE
      INPUT BY NAME l_file WITHOUT DEFAULTS
         BEFORE INPUT
         AFTER FIELD l_file
            IF cl_null(l_file) THEN
               CALL cl_err('','cmr-004',1)
            END IF
         ON ACTION update
            LET l_file = cl_browse_file()
            DISPLAY l_file TO FORMONLY.l_file
         ON ACTION locale
            CALL cl_show_fld_cont()
            LET g_action_choice = "locale"
         ON IDLE g_idle_seconds
            CALL cl_on_idle()
            CONTINUE INPUT
         ON ACTION controlg
            CALL cl_cmdask()
         ON ACTION exit
            EXIT WHILE
         ON ACTION cancel
            EXIT WHILE
         ON ACTION accept
            IF NOT cl_null(l_file) THEN
               IF cl_sure(18,20) THEN
                  CALL s_showmsg_init() #初始化错误列表
                  LET g_success = 'Y'
                  BEGIN WORK  
                  CALL i600_upload_file(l_file)
                  LET l_msg = "共导入:" CLIPPED,g_importcount CLIPPED," 笔,成功:" CLIPPED,g_successcount CLIPPED," 笔,失败:" CLIPPED,g_failcount CLIPPED," 笔,不更新:",g_undocount CLIPPED
                  IF g_success = 'Y' THEN
                     COMMIT WORK
                     IF g_totsuccess = 'N' THEN 
                        CALL s_showmsg()
                     END IF 
                     CALL cl_msgany(0,0,l_msg)
                     CALL cl_end2(1) RETURNING l_flag
                  ELSE
                     CALL s_showmsg()
                     ROLLBACK WORK
                     CALL cl_msgany(0,0,l_msg)
                     CALL cl_end2(2) RETURNING l_flag
                  END IF
               END IF
               IF l_flag THEN
                  CONTINUE WHILE
               ELSE
                  EXIT WHILE
               END IF
            END IF
      END INPUT
      IF INT_FLAG THEN
         LET INT_FLAG = 0 
         EXIT WHILE
      END IF
   END WHILE
END FUNCTION
FUNCTION i600_upload_file(p_fname)
   DEFINE p_file          STRING           #表名
   DEFINE p_fname         STRING      #本次汇入档名
   DEFINE l_fname1        STRING      #本次汇入档名
   DEFINE l_fname         STRING       #本次汇入档名
   DEFINE l_string        LIKE type_file.chr1000
   DEFINE l_cmd           LIKE type_file.chr1000
   DEFINE l_count         LIKE type_file.num10
   DEFINE l_num           LIKE type_file.num10
   DEFINE xlApp,iRes, iAct,iRow       INT
   DEFINE i,j,k,m         INT
   DEFINE l_cnt           LIKE type_file.num10
   DEFINE l_flag          LIKE type_file.chr1
   DEFINE l_sql           string
   DEFINE l_cntt          LIKE type_file.num10
   DEFINE l_ima01         LIKE ima_file.ima01
   DEFINE l_ztb03         LIKE ztb_file.ztb03,
          l_value         LIKE ztb_file.ztb03,
          l_value_o       LIKE ztb_file.ztb03, 
          ll_sql          STRING,
          l_azp01         LIKE azp_file.azp01,
          l_msg           STRING
   WHENEVER ERROR CALL cl_err_msg_log
   LET l_cmd =  p_fname CLIPPED
   LET l_count = LENGTH(l_cmd)
   IF l_count = 0  THEN 
      RETURN 
   END IF
   LET l_azp01 = g_plant
   #取导入文件名称
   LET l_num = 0
   FOR i = 1 TO l_count 
      LET l_string = l_cmd[i,i]
      IF l_string ="/" THEN 
         LET l_fname1 = l_cmd[l_num+1,i-1]
         LET l_num = i 
         IF l_fname IS NULL THEN 
            LET l_fname = l_fname1
         ELSE 
            LET l_fname = l_fname CLIPPED,"\\",l_fname1
         END IF 
      END IF 
   END FOR 
   IF l_fname IS NOT NULL THEN 
      LET l_fname1 = l_cmd[l_num+1,l_count]          #文件名
      LET l_fname = l_fname CLIPPED,"\\",l_fname1
   END IF 
   CALL ui.interface.frontCall('WinCOM','CreateInstance',['Excel.Application'],[xlApp])
   IF xlApp <> -1 THEN
      CALL ui.interface.frontCall('WinCOM','CallMethod',[xlApp,'WorkBooks.Open',l_fname],[iRes])
      IF iRes <> -1 THEN
         CALL ui.interface.frontCall('WinCOM','CallMethod',[xlApp,'ActiveSheet.UsedRange.Rows.Count'],[iRow])
         IF iRow <= 2 THEN
            LET l_msg = "文件内容必须大于 3 行才能进行导入!"
            CALL s_errmsg('',l_fname1,l_msg,'',1)
            LET g_success = 'N'
         ELSE
            MESSAGE l_fname1," 资料导入中..."
            FOR i = 3 TO iRow
#                IF g_success = 'N' THEN #允许继续执行
#                  EXIT FOR 
#                END IF 
                CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||', 1).Value'],[l_ima01])    #取料号
                LET l_cnt = 0                 
                IF NOT cl_null(l_ima01) THEN
                  SELECT COUNT(*) INTO l_cnt FROM ima_file WHERE ima01 = l_ima01 AND imaacti = 'Y' #校验料号
                  IF l_cnt < 1 THEN
                    CALL s_errmsg(l_ima01,g_showmsg,'','-0811',1)
#                    LET g_success = 'N'
                  ELSE
                   LET l_cnt = 0
                   FOR j = 1 TO 20
                       LET k = j + 1
                       LET l_value = NULL
                       LET l_value_o = NULL
                       LET l_msg = NULL
                       LET l_ztb03 = NULL
                       CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||2||','||k||').Value'],[l_ztb03]) #字段
                       CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||i||','||k||').Value'],[l_value]) #字段值
                       MESSAGE '读取行:',i USING '<<<<<<',' 列:',k USING '<<<<<<<'
                       CALL ui.Interface.refresh()
                       LET g_importcount = g_importcount + 1
                       IF l_value < 0 OR l_value IS NULL THEN
                          LET l_msg = l_ztb03," / ",l_value
                          CALL s_errmsg(l_ima01,g_showmsg,l_msg,'cmr-005',1)
#                          LET g_success = 'N'
                          LET g_failcount = g_failcount + 1
                       ELSE
                       IF l_ztb03 IS NULL THEN
                          LET l_msg = l_ztb03," / ",l_value
                          CALL s_errmsg(l_ima01,g_showmsg,l_msg,'cmr-006',1)
#                          LET g_success = 'N'
                          LET g_failcount = g_failcount + 1
                          EXIT FOR
                       ELSE
                          LET l_sql = "SELECT ", l_ztb03,
                                     "  FROM ",cl_get_target_table(l_azp01,'ima_file'),
                                     " WHERE ima01 = '",l_ima01,"'"
                          CALL cl_replace_sqldb(l_sql) RETURNING l_sql
                          CALL cl_parse_qry_sql(l_sql,l_azp01) RETURNING l_sql
                          PREPARE i600_ima_p FROM l_sql
                          EXECUTE i600_ima_p INTO l_value_o
                          IF l_value <> l_value_o THEN
                            LET ll_sql = "UPDATE ",cl_get_target_table(l_azp01,'ima_file'),
                                        " SET ", 
                                        l_ztb03, " = '", l_value,"'",
                                        " WHERE ima01 = '",l_ima01,"'"
                            CALL cl_replace_sqldb(ll_sql) RETURNING ll_sql
                            CALL cl_parse_qry_sql(ll_sql,l_azp01) RETURNING ll_sql
                            MESSAGE '更新行:',i USING '<<<<<<',' 列:',k USING '<<<<<<<'
                            PREPARE i600_ima_u FROM ll_sql
                            EXECUTE i600_ima_u
                            IF SQLCA.sqlcode THEN
                              LET l_msg = "update",l_ztb03
                              CALL s_errmsg(l_ima01,g_showmsg,l_msg,SQLCA.sqlcode,1)
                              LET g_success = 'N'
                              LET g_failcount = g_failcount + 1
                            ELSE
                              LET g_successcount = g_successcount + 1
                            END IF
                          ELSE
                            LET g_undocount = g_undocount + 1
                          END IF
                        END IF
                       END IF
                     END FOR
                END IF
               ELSE
                EXIT FOR
               END IF
            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])
   MESSAGE ""
END FUNCTION 

4fd 代码如下:

<?xml version="1.0" encoding="UTF-8" ?>
<ManagedForm databaseName="ds" gstVersion="22800" name="Root" uid="{2c98c061-37dd-4f1b-af74-46e30084bbb0}">
  <AGSettings/>
  <Record additionalTables="" joinLeft="" joinOperator="" joinRight="" name="Undefined" order="" uid="{b98cf067-4de9-479e-ae58-fd0e0f48a410}" where="">
    <RecordField colName="" fieldIdRef="1" fieldType="NON_DATABASE" name="l_file" sqlTabName="" sqlType="CHAR" table_alias_name="" uid="{a579ee7f-35f2-485b-a6dc-61153fba0d08}"/>
  </Record>
  <Form gridHeight="8" gridWidth="64" name="amri600_e" text="amri600_e">
    <Grid gridHeight="5" gridWidth="54" lstrcomment="false" name="gr2" posX="4" posY="1">
      <Label posX="5" posY="3" text="l_file"/>
      <Edit aggregateColName="" aggregateName="" aggregateTableAliasName="" aggregateTableName="" colName="" colorCondition="black" columnCount="" fieldId="1" fieldType="NON_DATABASE" gridHeight="1" gridWidth="23" lstrcomment="false" name="l_file" posX="19" posY="3" rowCount="" sqlTabName="" sqlType="CHAR" stepX="" stepY="" tabIndex="1" table_alias_name="" widget="Edit"/>
      <Button gridHeight="1" gridWidth="4" lstrcomment="false" lstrtext="false" name="update" posX="44" posY="3" tabIndex="2" text="update"/>
    </Grid>
  </Form>
  <DiagramLayout>
    <![CDATA[AAAAAgAAAEwAewBhADUANwA5AGUAZQA3AGYALQAzADUAZgAyAC0ANAA4ADUAYgAtAGEANgBkAGMALQA2ADEAMQA1ADMAZgBiAGEAMABkADAAOAB9QCQAAAAAAABAQoAAAAAAAAAAAAAAAAAAAQAAAEwAewBiADkAOABjAGYAMAA2ADcALQA0AGQAZQA5AC0ANAA3ADkAZQAtAGEAZQA1ADgALQBmAGQAMABlADAAZgA0ADgAYQA0ADEAMAB9AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQ==]]>
  </DiagramLayout>
</ManagedForm>

导入的 EXCEL 模板如下:

料件编号    安全存量    安全库存期间    补货策略    MPS计算    采购单位批量    最少采购数量    采购时损耗率    交货前置期    到厂前置期    入库前置期    文件作业时间    发料单位批量    最少发料数量    生产单位批量    最少生产数量    生产时损耗率    固定前置时间    变动前置时间    变动前置时间批量    QC前置时间
ima01    ima27    ima28    ima37    ima139    ima45    ima46    ima47    ima48    ima49    ima491    ima50    ima64    ima641    ima56    ima561    ima562    ima59    ima60    ima601    ima61
M101100005    0.000    0.000    0    N    5000.000    5000.000    0.0000    0.000    0.000    0.000    0.000    0.000    0.000    1.000    0.000    3.0000    60.000    0.000    1.000    0.000

ArmxMod for Typecho
个性化、自适应、功能强大的响应式主题

推广

 继续浏览关于 tiptop易拓鼎捷教程excel批量作业新增 的文章

 本文最后更新于 2019/09/23 12:00:00,可能因经年累月而与现状有所差异

 引用转载请注明:VirCloud's Blog > 运维 > 给鼎捷易拓 TIPTOP 作业新增 EXCEL 批量导入更新功能(4gl)