SM30是SAP提供的一个很方便进行表内容维护的工具,也是顾问不需要偷偷摸摸就能使用的一个数据导入工具,SAP自己的配置也大多是利用SM30里面完成,应用极广。
但是!SM30有几个非常不爽的缺陷,在维护数据比较多的时候会造成很大困扰和麻烦,比如没有排序、查找(包含模糊查找)、批量导入、导出、详细且直观的修改日志查看等等。
下面的代码就是给SM30补全这些功能。
先看下功能介绍:
1、查找
如下图,我想找到物料描述里面有“开关”的行
这时候只要把光标放到物料描述这一列里面随便哪一行,然后优雅的按下Ctrl+F,或者点一下搜索按钮,立刻就会弹出一个对话框:”Sir,需要为您找点啥?” “我想找到物料描述包含'开关'的” “No Problem,您直接输入开关俩字,然后再劳驾回个车”
找到的数据就显示在最上面的行了:
如果意犹未尽,还需要找到其他带“开关”的,只需要再按Ctrl+G
2、排序
其实有了查找,排序也可以没有,但是谁能拒绝多一个趁手的功能呢?
排排序而已,这个功能说起来很简单,其实程序实现起来,,比说起来还要简单。用起来就更简单了,只要把光标放到想排序的列,降序、升序,随心所欲。
3、导出功能
虽然我通常基本使用SE11/SE16/SE16N/S416N/S416D/S416H/S4H16D/S4H16H/S4H16N来导出数据,但是用户依然希望在没有令人羡慕的查表权限的时候能导出他们维护的数据,多么淳朴的需求,这个必须满足。
只要点一下这个按钮,数据就都导出到文本文件了哦,而且还带着标题!
甚至点这个也可以直接用ALV来查看哦:
4、批量导入数据
天哪,SM30没有批导入!多么丧心病狂!如果我说这个玩意才是真正限制着使用SM30维护大数据量的罪魁祸首,大概很多人会眼含热泪的说对对对对对对。
事实上,这个功能是如此的重要也是如此的难做,以至于我立志做这个功能的那一天熬夜到鸡叫都没有搞定,以至于随后几天又淋淋啦啦测试了好多场景才逐步完善好。
说了一顿有的没的,先看看导入的例子哈。很简单,只要在随便哪个地方,Excel、WORD、文本文件,复制出需要导入的数据(制表符分隔哦),点一下粘贴按钮,吧唧,妥了!导入多少条给你提示的明明白白的,如果有主键重复的,还能自动给去掉呢。有客官说一次性能导入多少条?万儿八千的没问题,三万五万也凑合,看你机器性能啦。
5、查看更改日志
表更改日志这个东西吧,怎么说呢,可能你很久都用不到一次。一旦用到了,但是你没有,那就只能傻眼(有土豪客户能在PRD开表更改日志的除外)。
(说到日志,我写的ZILOG记录接口日志是最好用的,不接受任何反驳!,但是接受任何挑战。看似简单的一个程序奇妙无穷玩法多样,既能记录飞快不耽误接口时间又能查询如飞让你查起来得心应手,很多公司拿着我第一版开源的改来改去,但总归是比我的后续版本差很多)
说远了,那么这个SM30日志有啥特点呢?
好用,直观,细节拉满,一切为用户着想就是它的特点,看看界面就知道了:
删除的数据:
更改的数据:有更改数据带黄底色显示,并标明了改前和改后的值
(最后图是之前数据,所以看上去字段不一样)
其实最后还有一个批量更改列数据的功能,但是用途不是那么大,纯粹是凑数的功能,不讲了,不值一提。
下面就是群众喜闻乐见的展示源码环节了(暂不包含日志功能),谢谢我就能拿走。
下篇文章讲下如何改造生成的表格维护器,如何把这些代码塞进去。
*&---------------------------------------------------------------------*
*程序描述: 通用程序,给SM30增加排序/查找/导出/导入/批量修改/记录更改日志等功能
*说明:需要把表做一个维护视图,不支持直接对表做SM30
*----------------------------------------------------------------------*
*Baitianzhen 2019
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
* 灰掉定位按钮
* 在排序后,定位按钮会失效
*----------------------------------------------------------------------*
MODULE hide_position_button OUTPUT.
LOOP AT SCREEN.
IF screen-name = 'VIM_POSI_PUSH'.
screen-input = '0'.
MODIFY SCREEN.
ENDIF.
ENDLOOP.
ENDMODULE. "hide_position_button OUTPUT
*&---------------------------------------------------------------------*
*& Module ZSM30ADDON_COMMAND INPUT
*&---------------------------------------------------------------------*
MODULE zsm30addon_command INPUT.
IF function(4) = 'SEAR'.
PERFORM tc_search.
ELSEIF function(4) = 'SORT'.
PERFORM sort_table.
ELSEIF function(4) = 'EXPT'.
PERFORM export.
ELSEIF function = 'UPLD'.
PERFORM upload.
ELSEIF function = 'BATMOD'.
PERFORM batch_change.
ENDIF.
ENDMODULE. " SEARCH_TABLE INPUT
*&---------------------------------------------------------------------*
*& 批量更改功能
*& 选择需要批量更改的行,然后把光标放到需要更改的列,按批量更改按钮
*& 如果没有选择行,则更改所有的行的值
*&---------------------------------------------------------------------*
FORM batch_change.
DATA: lv_colnam TYPE name_feld,
lv_tabnam TYPE char30,
lv_fldnam TYPE char30.
DATA: ls_column TYPE scxtab_column.
DATA: lv_string TYPE string.
FIELD-SYMBOLS: <lv_fldval> TYPE any.
GET CURSOR FIELD lv_colnam.
SPLIT lv_colnam AT '-' INTO lv_tabnam lv_fldnam.
READ TABLE <vim_tctrl>-cols INTO ls_column WITH KEY screen-name = lv_colnam.
IF sy-subrc <> 0 OR ls_column-screen-input = 0.
RETURN.
ENDIF.
PERFORM popup_getvalue USING lv_tabnam lv_colnam ''
CHANGING lv_string sy-subrc.
CHECK sy-subrc = 0.
LOOP AT extract.
IF mark_extract > 0.
CHECK <xmark> = 'M'.
ENDIF.
ASSIGN COMPONENT lv_fldnam OF STRUCTURE extract TO <lv_fldval>.
IF sy-subrc = 0.
<lv_fldval> = lv_string.
ENDIF.
IF <xact> <> 'N'.
<xact> = 'U'.
ENDIF.
<status>-upd_flag = 'X'.
MOVE-CORRESPONDING <vim_extract_struc> TO <table1>.
PERFORM update_tab.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& 批量上载数据
*& 使用方法为在Excel复制数据后,按粘贴按钮即可把数据批量导入
*& 数据格式为SM30"新条目"界面中所看到的列
*& 如果是C类型数据,且没有指定区分大小写,则转为大写
*& 如果已经存在相同主键数据,则本条数据不导入,并有提示
*& 如果存在转换例程,会转为内部格式
*&---------------------------------------------------------------------*
FORM upload.
DATA: ls_column TYPE scxtab_column.
DATA: lv_fmname TYPE rs38l_fnam.
DATA: lv_insrow TYPE i,
lv_delrow TYPE i.
DATA: lt_fldcat TYPE lvc_t_fcat,
ls_fldcat TYPE lvc_s_fcat.
DATA: lr_table TYPE REF TO data,
lr_lines TYPE REF TO data.
* DATA: ls_exfld TYPE vimexclfld.
FIELD-SYMBOLS: <lt_table> TYPE STANDARD TABLE,
<ls_lines> TYPE any,
<lv_field> TYPE any.
"数据格式为SM30"新条目"界面中所看到的列
LOOP AT <vim_tctrl>-cols INTO ls_column.
SPLIT ls_column-screen-name AT '-' INTO ls_fldcat-ref_table ls_fldcat-ref_field.
* READ TABLE excl_rpl_tab INTO ls_exfld WITH KEY fieldname = ls_fldcat-ref_field.
* CHECK sy-subrc <> 0.
ls_fldcat-fieldname = ls_fldcat-ref_field.
APPEND ls_fldcat TO lt_fldcat.
ENDLOOP.
"生成动态内表,以接受剪贴板数据
CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = lt_fldcat
IMPORTING
ep_table = lr_table.
ASSIGN lr_table->* TO <lt_table>.
CREATE DATA lr_lines LIKE LINE OF <lt_table>.
ASSIGN lr_lines->* TO <ls_lines>.
"导入剪贴板数据
PERFORM cliptoitab TABLES <lt_table>.
nextline = 1.
LOOP AT extract INTO extract.
IF <xact> = 'N'.
nextline = nextline + 1.
ENDIF.
ENDLOOP.
LOOP AT <lt_table> INTO <ls_lines>.
LOOP AT x_namtab INTO x_namtab. "#EC CI_NESTED
ASSIGN COMPONENT x_namtab-viewfield OF STRUCTURE <ls_lines> TO <lv_field>.
CHECK sy-subrc = 0.
"如果是C类型数据,且没有指定区分大小写,则转为大写
IF x_namtab-lowercase = '' AND x_namtab-inttype = 'C'.
TRANSLATE <lv_field> TO UPPER CASE.
ENDIF.
IF x_namtab-convexit <> ''.
"如果存在转换例程,会转为内部格式
CONCATENATE 'CONVERSION_EXIT_' x_namtab-convexit '_INPUT' INTO lv_fmname.
CALL FUNCTION lv_fmname
EXPORTING
input = <lv_field>
IMPORTING
output = <lv_field>
EXCEPTIONS
OTHERS = 1.
IF sy-subrc <> 0.
MESSAGE e000(oo) WITH '数据转为内部格式失败' <lv_field>.
ENDIF.
ENDIF.
MODIFY <lt_table> FROM <ls_lines>.
ENDLOOP.
MOVE-CORRESPONDING <ls_lines> TO <table1>.
"如果已经存在相同主键数据,则本条数据不导入,并有提示
"此READ语法在CLASS中不再支持,但是因为是在SM30的基础上增强,无法更改为其他代码
READ TABLE total INTO total WITH KEY <f1_x> BINARY SEARCH ##WARN_OK.
IF sy-subrc = 0.
lv_delrow = lv_delrow + 1.
ELSE.
<xact> = 'L'.
<status>-upd_flag = 'X'.
<table2_x> = <initial_x>.
PERFORM complete_exprofields.
PERFORM update_tab.
nextline = nextline + 1.
lv_insrow = lv_insrow + 1.
ENDIF.
ENDLOOP.
MESSAGE s000(oo) WITH '删除主键重复的' lv_delrow '条,实际插入条数' lv_insrow.
"如果导入的数据超过本屏幕所能显示的行数,
"把最后一行位置定位到屏幕的中间,提升用户体验
IF nextline <= looplines.
nextline = 1.
ELSE.
nextline = nextline - ceil( looplines / 2 ).
ENDIF.
ENDFORM. "upload
*&---------------------------------------------------------------------*
*& 排序功能
*& 把光标放到要排序的列内,然后点击排序按钮
*&---------------------------------------------------------------------*
FORM sort_table.
DATA: lv_colnam TYPE name_feld.
GET CURSOR FIELD lv_colnam.
SPLIT lv_colnam AT '-' INTO lv_colnam lv_colnam.
IF function = 'SORTA'.
SORT extract STABLE BY (lv_colnam).
ELSE.
SORT extract STABLE BY (lv_colnam) DESCENDING.
ENDIF.
ENDFORM. "sort_table
*&---------------------------------------------------------------------*
*& 搜索功能
*& 把光标放到要搜索的列内,然后按Ctrl+F,即可弹出搜索框
*& 如果是字符型数据,会自动变为模糊搜索,如果是其他类型数据,则为精确搜索
*& 按Ctrl+F,搜索下一个
*&---------------------------------------------------------------------*
FORM tc_search.
DATA: lv_subrc TYPE sy-subrc.
DATA: lv_colnam TYPE char80,
lv_tabnam TYPE char30,
lv_fldnam TYPE char30.
"使用静态变量,作用是Ctrl+G的时候能记住需要搜索的值以及已经找到的位置
STATICS: lv_finds TYPE string,
lv_colnm TYPE string,
lv_whstr TYPE string,
lv_itype TYPE char1,
lv_index TYPE i.
FIELD-SYMBOLS: <lv_fldval> TYPE any.
lv_subrc = 1.
IF function = 'SEARF'.
"获取光标所在列,进一步获取要搜索的字段名
"把需要搜的字段名ASSIGN到 <lv_fldval>
GET CURSOR FIELD lv_colnam.
SPLIT lv_colnam AT '-' INTO lv_tabnam lv_fldnam.
CONCATENATE 'EXTRACT-' lv_fldnam INTO lv_whstr.
ASSIGN (lv_whstr) TO <lv_fldval>.
"弹出窗口,输入需要搜索的数据
PERFORM popup_getvalue USING lv_tabnam lv_colnam 'X'
CHANGING lv_finds sy-subrc.
CHECK sy-subrc = 0.
"如果是C类型的数据,则设置为模糊搜索
"如果是其他类型数据,CONDENSE
READ TABLE x_namtab INTO x_namtab WITH KEY viewfield = lv_fldnam.
IF x_namtab-inttype = 'C'.
CONCATENATE '*' lv_finds '*' INTO lv_finds.
ELSE.
CONDENSE lv_finds.
ENDIF.
lv_itype = x_namtab-inttype.
"使用CP关键字匹配C类型数据
"其他类型数据需要严格相等
LOOP AT extract INTO extract.
IF x_namtab-inttype = 'C'.
CHECK <lv_fldval> CP lv_finds.
ELSE.
CHECK <lv_fldval> = lv_finds.
ENDIF.
"如果本条数据可以匹配,则光标定位到此
nextline = sy-tabix.
l = 1.
lv_index = sy-tabix + 1.
lv_colnm = lv_fldnam.
lv_subrc = 0.
EXIT.
ENDLOOP.
ELSE.
"Ctrl+G,查找下一条
"先判定是否有搜索的列名和已经有Ctrl+F过
CHECK lv_colnm IS NOT INITIAL AND lv_finds IS NOT INITIAL.
CONCATENATE 'EXTRACT-' lv_colnm INTO lv_whstr.
ASSIGN (lv_whstr) TO <lv_fldval>.
LOOP AT extract INTO extract FROM lv_index.
IF lv_itype = 'C'.
CHECK <lv_fldval> CP lv_finds.
ELSE.
CHECK <lv_fldval> = lv_finds.
ENDIF.
"如果本条数据可以匹配,则光标定位到此
nextline = sy-tabix.
l = 1.
lv_index = sy-tabix + 1.
lv_subrc = 0.
EXIT.
ENDLOOP.
ENDIF.
IF lv_subrc = 1.
MESSAGE s000(oo) WITH '没有符合条件的记录'.
ENDIF.
ENDFORM. "tc_search
*&---------------------------------------------------------------------*
*& 导出数据,可以把数据导出到本地文件,保存为UTF-8编码的文本文件
*& 也可以使用ALV显示数据
*& 如果选定的了行,只导出选择的行,没有没有选择任何行,则导出所有行
*&---------------------------------------------------------------------*
FORM export.
DATA: lv_file TYPE string,
lv_path TYPE string,
lv_exte TYPE string VALUE 'txt'.
DATA: ls_cols TYPE scxtab_column.
DATA: lt_fldc TYPE lvc_t_fcat,
ls_fldc TYPE lvc_s_fcat.
DATA: lr_tabl TYPE REF TO data,
lr_line TYPE REF TO data.
DATA: lr_grid TYPE REF TO cl_salv_table,
lr_funl TYPE REF TO cl_salv_functions_list.
FIELD-SYMBOLS: <lt_tabl> TYPE STANDARD TABLE,
<lt_line> TYPE any.
"根据实际SM30的列定义内表
LOOP AT <vim_tctrl>-cols INTO ls_cols.
SPLIT ls_cols-screen-name AT '-' INTO ls_fldc-ref_table ls_fldc-ref_field.
ls_fldc-fieldname = ls_fldc-ref_field.
APPEND ls_fldc TO lt_fldc.
ENDLOOP.
"定义动态内表
"内表为<lt_tabl>,其对应工作区域为<lt_line>
CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = lt_fldc
IMPORTING
ep_table = lr_tabl.
ASSIGN lr_tabl->* TO <lt_tabl>.
CREATE DATA lr_line LIKE LINE OF <lt_tabl>.
ASSIGN lr_line->* TO <lt_line>.
CLEAR <lt_tabl>.
LOOP AT extract INTO extract.
"如果选定的了行,只导出选择的行,没有没有选择任何行,则导出所有行
IF mark_extract > 0.
CHECK <xmark> = 'M'.
ENDIF.
"把SM30数据导入到自定义内表
MOVE-CORRESPONDING extract TO <lt_line>.
CHECK <lt_line> IS NOT INITIAL.
APPEND <lt_line> TO <lt_tabl>.
ENDLOOP.
IF <lt_tabl> IS INITIAL.
MESSAGE s000(oo) WITH '无内容'.
RETURN.
ELSE.
IF function = 'EXPTTAB'.
"把数据导出到本地,显示文件保存框
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
default_extension = lv_exte
CHANGING
filename = lv_path
path = lv_path
fullpath = lv_file
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF lv_file IS NOT INITIAL AND sy-subrc = 0.
"如果用户没有取消,则下载文件,编码为UTF-8
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = lv_file
filetype = 'DAT'
write_field_separator = 'X'
codepage = '4110' "UTF-8
TABLES
data_tab = <lt_tabl>
EXCEPTIONS
OTHERS = 22.
IF sy-subrc <> 0.
MESSAGE e000(oo) WITH '下载失败'.
ENDIF.
ENDIF.
ELSEIF function = 'EXPTALV'.
"如果使用ALV显示,则使用SALV的功能,弹出ALV窗口
TRY.
cl_salv_table=>factory(
IMPORTING
r_salv_table = lr_grid
CHANGING
t_table = <lt_tabl> ).
CATCH cx_root.
MESSAGE s000(oo) WITH 'CL_SALV_TABLE Error'.
RETURN.
ENDTRY.
lr_funl = lr_grid->get_functions( ).
lr_funl->set_all( 'X' ).
IF lr_grid IS BOUND.
"弹出ALV窗口
lr_grid->set_screen_popup( start_column = 10
end_column = 110
start_line = 5
end_line = 15 ).
lr_grid->display( ).
ENDIF.
ENDIF.
ENDIF.
ENDFORM. "Export
*&---------------------------------------------------------------------*
*& 在需要输入值的时候弹出窗口供用户输入
*&---------------------------------------------------------------------*
FORM popup_getvalue USING pv_tabnam pv_colnam pv_obl
CHANGING cv_ostr cv_subrc.
DATA: lt_sval TYPE TABLE OF sval,
ls_sval TYPE sval,
lv_code TYPE char1,
ls_dd03 TYPE dd03l.
"拆分出表名和列名
"并在表DD03L获取参照表信息
SPLIT pv_colnam AT '-' INTO ls_sval-tabname ls_sval-fieldname.
SELECT SINGLE * INTO ls_dd03 FROM dd03l "#EC CI_ALL_FIELDS_NEEDED
WHERE tabname = ls_sval-tabname AND
fieldname = ls_sval-fieldname AND
as4local = 'A' ##WARN_OK.
IF sy-subrc <> 0.
MESSAGE e000(oo) WITH 'Get DD03L Error'.
ENDIF.
ls_sval-tabname = pv_tabnam.
ls_sval-fieldtext = '输入'.
ls_sval-field_obl = pv_obl. "是否必输
APPEND ls_sval TO lt_sval.
"如果参照表/字段不为空,则特殊处理,
"此处是为金额/数量等字段使用
IF ls_dd03-reftable IS NOT INITIAL.
ls_sval-tabname = ls_dd03-reftable.
ls_sval-fieldname = ls_dd03-reffield.
ls_sval-field_attr = '04'.
APPEND ls_sval TO lt_sval.
ENDIF.
CALL FUNCTION 'POPUP_GET_VALUES'
EXPORTING
popup_title = '输入'
IMPORTING
returncode = lv_code
TABLES
fields = lt_sval.
IF lv_code = 'A'.
cv_subrc = 1.
RETURN.
ELSE.
READ TABLE lt_sval INTO ls_sval INDEX 1.
cv_ostr = ls_sval-value.
cv_subrc = 0.
ENDIF.
ENDFORM. "popup_getvalue
*&---------------------------------------------------------------------*
*& 把剪贴板内容导入内表
*& 如果是数字类型数据,会去掉逗号作为的科学计数法分隔符
*&---------------------------------------------------------------------*
FORM cliptoitab TABLES t_outtab.
DATA: lt_clip TYPE TABLE OF char2048,
ls_clip TYPE char2048,
lt_fval TYPE TABLE OF char2048,
ls_fval TYPE char2048.
DATA: lv_tabix TYPE sy-tabix,
lv_ftype TYPE char1.
DATA: lv_htab TYPE c VALUE cl_abap_char_utilities=>horizontal_tab.
FIELD-SYMBOLS: <lv_fval> TYPE any,
<ls_line> TYPE any.
"把内表参数ASSIGN到FIELD-SYMBOLS
ASSIGN t_outtab TO <ls_line>.
"读取剪贴板数据到CLIP内表
CALL METHOD cl_gui_frontend_services=>clipboard_import
IMPORTING
data = lt_clip
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
"FLUSH一下,否则可能获取不到数据
CALL METHOD cl_gui_cfw=>flush.
"处理CLIP内表数据
"并把数据放入数据内表
LOOP AT lt_clip INTO ls_clip.
CLEAR: lv_tabix,lt_fval.
SPLIT ls_clip AT lv_htab INTO TABLE lt_fval.
LOOP AT lt_fval INTO ls_fval. "#EC CI_NESTED
lv_tabix = lv_tabix + 1.
ASSIGN COMPONENT lv_tabix OF STRUCTURE <ls_line> TO <lv_fval>.
CHECK sy-subrc = 0.
TRY.
"判定数据类型
DESCRIBE FIELD <lv_fval> TYPE lv_ftype.
CASE lv_ftype.
"去掉科学计数法的逗号
"并去掉空格
WHEN 'I' OR 'P' OR 'F' OR 'a' OR 'e' OR 'b' OR 's'.
TRANSLATE ls_fval USING ', '.
CONDENSE ls_fval NO-GAPS.
WHEN OTHERS.
ENDCASE.
<lv_fval> = ls_fval.
CATCH cx_root.
MESSAGE e000(oo) WITH '数据转换错误'.
ENDTRY.
ENDLOOP.
APPEND <ls_line> TO t_outtab.
CLEAR <ls_line>.
ENDLOOP.
ENDFORM. "cliptoitab