TIPTOP ERP GP3.0导入excel文件
随着ERP系统单据逐渐增多，尤其是单身数据笔数超过几百条甚至上千条时，复制功能也显得十分脆弱，我们的用户提出了这样的需求：能否在ERP系统上加上一个汇入excel的功能，来减轻他们的工作量。接到这个需求后，走访各个技术群，得到一些答案，增加了一个上传excel的功能，但是这个功能在我们GP3.0下使用貌似有两处不好使：
1.在导入的时候需要打开excel文件，否则导入不成功；
2.在导入的excel文件内容之中不能包含中文字眼，否则导入之后因为编码问题中文就会变成乱码。
这两个问题困扰了我很久，但是最终没有得到解决。后来技术群朋友给了一些导入txt文件的功能，得到一些启发，我何不将excel文件另存为txt文件之后然后再读取出来，这样从文件类型转变解决了上面的两个问题。说了这么多废话现在开始进入正题：
客制一个采购料件核价维护作业cpmi255，这是一个简单的双档作业，录入好单头数据之后点击确定跳入单身之前弹出对话框是否导入txt文本文件，点击是弹出选择本地txt的对话框；反之按照正常方式录入单身。
【注意：①.在我们将excel另存为txt文件的时候，为了保证数据格式，我们最好是先从cpmi255作业中导出一份excel文件，复制到另外的一个excel工作表中编辑，编辑完成之后，将其另存，类型一定选择为“文本文件（制表符分割）（*.txt）”，文件名最好更改为数字或英文的txt文件。
②.今天遇到一个很郁闷的问题,一直导入失败,报错提示-1213字符转换错误,后来折腾了大半天之后,原来是一个misc的料件的品名规格里面输入了逗号, 我擦.请大家一定要注意,文件另存的时候注意查看一定更不要有逗号,因为逗号会造成4gl代码里面load 以逗号作为分隔 from txt文件字段值读取错误,导致字段对应不上, 所以提示了错误!!!当然你也可以修改load 以其他分隔符来读取字段值.】
--------------------华--------------------丽--------------------分--------------------割--------------------线--------------------
另存注意之处截图：
--------------------华--------------------丽--------------------分--------------------割--------------------线--------------------
另存之后的txt文件截图：
--------------------华--------------------丽--------------------分--------------------割--------------------线--------------------
导入txt文件的主要代码：
#===excel上传脚本1 #add by condor 2012-08-31
FUNCTION i255_imp_excel()
DEFINE ls_str STRING,
ls_file STRING,
ls_location STRING
LET ls_str = cl_getmsg('lib-201',g_lang)
WHILE TRUE
PROMPT ls_str CLIPPED FOR ls_location
ATTRIBUTE(WITHOUT DEFAULTS)
ON ACTION accept
EXIT WHILE
ON ACTION cancel
EXIT WHILE
ON ACTION browse_document
LET ls_file = cl_browse_file()
IF ls_file IS NOT NULL THEN
LET ls_location = ls_file
END IF
END PROMPT
END WHILE
IF NOT cl_null(ls_location) THEN
CALL i255_upload_file(ls_location)
END IF
END FUNCTION
FUNCTION i255_upload_file(p_file)
DEFINE p_file LIKEze_file.ze03
DEFINE l_download_file LIKEze_file.ze03
DEFINE l_download_file2 LIKEze_file.ze03
DEFINE l_upload_file LIKEze_file.ze03
DEFINE l_status INTEGER
DEFINE l_tempdir LIKE ze_file.ze03
DEFINE l_n INTEGER
DEFINE lst_token base.StringTokenizer
DEFINE ls_token STRING
DEFINE l_i INTEGER
DEFINE l_j INTEGER
DEFINE ss VARCHAR(1000)
DEFINE sss VARCHAR(1000)
DEFINE l_part VARCHAR(100)
DEFINE b_tc_pmj RECORD LIKE tc_pmj_file.*
DEFINE l_tc_pmj23 LIKE tc_pmj_file.tc_pmj23
DEFINE l_cmd VARCHAR(1000)
DEFINE l_mm INTEGER,
l_yy INTEGER,
l_dd INTEGER,
l_bd INTEGER,
l_cnt INTEGER,
l_sql STRING
DEFINE sr RECORD
tc_pmj01 LIKE tc_pmj_file.tc_pmj01,
tc_pmj02 LIKE tc_pmj_file.tc_pmj02,
tc_pmj03 LIKE tc_pmj_file.tc_pmj03,
tc_pmj04 LIKE tc_pmj_file.tc_pmj04,
tc_pmj05 LIKE tc_pmj_file.tc_pmj05,
tc_pmj06 LIKE tc_pmj_file.tc_pmj06,
tc_pmj07 LIKE tc_pmj_file.tc_pmj07,
tc_pmj08 LIKE tc_pmj_file.tc_pmj08,
tc_pmj09 LIKE tc_pmj_file.tc_pmj09,
tc_pmj19 LIKE tc_pmj_file.tc_pmj19,
tc_pmj10 LIKE tc_pmj_file.tc_pmj10,
tc_pmj11 LIKE tc_pmj_file.tc_pmj11,
tc_pmj12 LIKE tc_pmj_file.tc_pmj12,
tc_pmj13 LIKE tc_pmj_file.tc_pmj13,
tc_pmj14 LIKE tc_pmj_file.tc_pmj14,
tc_pmj15 LIKEtc_pmj_file.tc_pmj15,
tc_pmj16 LIKE tc_pmj_file.tc_pmj16,
tc_pmj17 LIKE tc_pmj_file.tc_pmj17,
tc_pmj18 LIKE tc_pmj_file.tc_pmj18,
tc_pmj20 LIKE tc_pmj_file.tc_pmj20,
tc_pmj21 LIKE tc_pmj_file.tc_pmj21,
tc_pmj22 LIKE tc_pmj_file.tc_pmj22,
tc_pmj23 LIKE tc_pmj_file.tc_pmj23
END RECORD
DROP TABLE tmp1_file
CREATE TEMP TABLE tmp1_file(tc_pmj01 INTEGER,
tc_pmj02 VARCHAR(10),
tc_pmj03 VARCHAR(20),
tc_pmj04 VARCHAR(30),
tc_pmj05 VARCHAR(30),
tc_pmj06 VARCHAR(30),
tc_pmj07 VARCHAR(30),
tc_pmj08 VARCHAR(30),
tc_pmj09 VARCHAR(10),
tc_pmj19 VARCHAR(20),
tc_pmj10 VARCHAR(10),
tc_pmj11 VARCHAR(10),
tc_pmj12 DECIMAL(20,6),
tc_pmj13 DECIMAL(20,6),
tc_pmj14 DECIMAL(20,6),
tc_pmj15 DECIMAL(20,6),
tc_pmj16 VARCHAR(20),
tc_pmj17 VARCHAR(20),
tc_pmj18 VARCHAR(20),
tc_pmj20 VARCHAR(20),
tc_pmj21 VARCHAR(10),
tc_pmj22 VARCHAR(10),
tc_pmj23 VARCHAR(200)
);
DELETE FROM tmp1_file
WHERE 1=1
LET l_tempdir=FGL_GETENV("TEMPDIR")
LET l_n=LENGTH(l_tempdir)
IF l_n>0 THEN
IF l_tempdir[l_n,l_n]='/' THEN
LET l_tempdir[l_n,l_n]=' '
END IF
END IF
LET lst_token = base.StringTokenizer.create(p_file,'/')
WHILE lst_token.hasMoreTokens()
LET ls_token = lst_token.nextToken()
END WHILE
IF cl_null(l_tempdir) THEN
LET l_download_file = ls_token.trim()
ELSE
LET l_download_file = l_tempdirCLIPPED,'/',ls_token.trim()
END IF
LET l_upload_file = p_file
IF NOT cl_upload_file(l_upload_file,l_download_file) THEN
CALL cl_err(l_upload_file,STATUS,1)
END IF
LET ss="test -s ",l_download_file CLIPPED
RUN ss RETURNING l_n
IF l_n THEN
IF l_tempdir IS NULL THEN
LET l_tempdir='.'
END IF
DISPLAY "* NOTICE * No such text file '",l_download_fileCLIPPED,"'"
DISPLAY "PLEASE make sure that the text file download fromLEADER"
DISPLAY "has been put in the directory:"
DISPLAY '--> ',l_tempdir
CALL cl_used(g_prog,g_time,2) RETURNING g_time #No.FUN-690120
EXIT PROGRAM
END IF
LET ss = "sed 's/\t/,/g' ",l_download_file," -i" #替换掉因为从excel另存到txt文本里面的table键或者空格键
RUN ss
LOAD FROM l_download_file DELIMITER ',' INSERT INTO tmp1_file #从u1/out下面读取文本文件插入临时表
IF STATUS THEN
CALL cl_err('',status,1)
LET ss = " rm ",l_download_file #为了避免文件暂用过大的空间,提示任何错误时都删除文件
RUN ss
RETURN
END IF
LET l_sql =
" SELECT tc_pmj01,tc_pmj02,tc_pmj03,tc_pmj04,tc_pmj05,tc_pmj06,tc_pmj07,tc_pmj08,tc_pmj09,tc_pmj19,tc_pmj10,",
"tc_pmj11,tc_pmj12,tc_pmj13,tc_pmj14,tc_pmj15,tc_pmj16,TO_DATE(tc_pmj17,'YYYY-MM-DD'),TO_DATE(tc_pmj18,'YYYY-MM-DD'),tc_pmj20,",
" tc_pmj21,tc_pmj22,tc_pmj23 FROMtmp1_file ORDER BY tc_pmj01 "
DECLARE tmp_cur1 CURSOR FROM l_sql
IF STATUS THEN
CALL cl_err('dec tmp_cur1',STATUS,1)
LET ss = " rm ",l_download_file #为了避免文件暂用过大的空间,提示任何错误时都删除文件
RUN ss
RETURN
END IF
FOREACH tmp_cur1 INTO sr.*
INITIALIZE b_tc_pmj.* TO NULL
LET b_tc_pmj.tc_pmj00 = g_tc_pmi.tc_pmi00
LET b_tc_pmj.tc_pmj01 = sr.tc_pmj01
LET b_tc_pmj.tc_pmj02 = sr.tc_pmj02
LET b_tc_pmj.tc_pmj03 = sr.tc_pmj03
LET b_tc_pmj.tc_pmj04 = sr.tc_pmj04
LET b_tc_pmj.tc_pmj05 = sr.tc_pmj05
LET b_tc_pmj.tc_pmj06 = sr.tc_pmj06
LET b_tc_pmj.tc_pmj07 =sr.tc_pmj07
LET b_tc_pmj.tc_pmj08 = sr.tc_pmj08
LET b_tc_pmj.tc_pmj09 = sr.tc_pmj09
LET b_tc_pmj.tc_pmj10 = sr.tc_pmj10
LET b_tc_pmj.tc_pmj11 = sr.tc_pmj11
LET b_tc_pmj.tc_pmj12 = sr.tc_pmj12
LET b_tc_pmj.tc_pmj13 = sr.tc_pmj13
LET b_tc_pmj.tc_pmj14 = sr.tc_pmj14
LET b_tc_pmj.tc_pmj15 = sr.tc_pmj15
LET b_tc_pmj.tc_pmj16 = sr.tc_pmj16
LET b_tc_pmj.tc_pmj17 = sr.tc_pmj17
LET b_tc_pmj.tc_pmj18 = sr.tc_pmj18
LET b_tc_pmj.tc_pmj19 = sr.tc_pmj19
LET b_tc_pmj.tc_pmj20 = sr.tc_pmj20
LET b_tc_pmj.tc_pmj21 = sr.tc_pmj21
LET b_tc_pmj.tc_pmj22 = sr.tc_pmj22
LET b_tc_pmj.tc_pmj23 = sr.tc_pmj23
IF NOT cl_null(sr.tc_pmj23) THEN
LET l_tc_pmj23 = sr.tc_pmj23
CALLcl_replace_str(l_tc_pmj23,'\r','') RETURNING l_tc_pmj23 #处理记事本里面最后一列数据值后面的'\r'
LET l_tc_pmj23 = l_tc_pmj23 CLIPPED
LET b_tc_pmj.tc_pmj23 =l_tc_pmj23
END IF
INSERT INTO tc_pmj_file VALUES(b_tc_pmj.*)
IF SQLCA.SQLCODE THEN
CALLcl_err('excel_ins_inb',SQLCA.sqlcode,1)
LET ss = " rm",l_download_file #为了避免文件暂用过大的空间,提示任何错误时都删除文件
RUN ss
RETURN
END IF
END FOREACH
LET ss = " rm ",l_download_file #用完之后删除文件
RUN ss
CALL i255_b_fill("1=1")
END FUNCTION
#===excel上传脚本1 #end bycondor 2012-08-31
--------------------华--------------------丽--------------------分--------------------割--------------------线--------------------
上传完成之后的效果图：
cpmi255作业代码下载地址：http://pan.baidu.com/share/link?shareid=88129&uk=4060653789
Edit By Condor
2012年10月24日11:44:12
1795
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
