温馨提示:
本文所述内容具有依赖性,可能因软硬条件不同而与预期有所差异,故请以实际为准,仅供参考。
应用场景
- 批量新增数据;
- 批量更新数据;
应用分析
在实际操作过程中,比如开帐时,有成千上万比资料需要新增,如果一笔笔人工新增操作,十分耗费人力效率,如果由后台批量更新,维护人员会很辛苦。因此考虑在前端作业新增 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