背景:
在做自建表批量导入工具时做的一个函数,没有使用常见的TEXT_CONVERT_XLS_TO_SAP和ALSM_EXCEL_TO_INTERNAL_TABLE,当然以上两个函数都可以满足需求,但都有一些小问题。
TEXT_CONVERT_XLS_TO_SAP的一些小问题:
- 在上传.xlsx后缀的EXCEL时,会有一个弹窗,点了才可以导入,并且表头最多只能有一行;
- 上传时只能填内码,否则一些外码是字符型,内码是数字类型的字段会导致导入失败;
- 单元格最多只能填写256个字符,超过后会变成?????;
- 导入效率慢;
- 只能读取保存时当前Sheet页的数据;
- Excel列的顺序要和接受内表的字段顺序完全一致;
- 不是所有系统都有该函数;
ALSM_EXCEL_TO_INTERNAL_TABLE的一些小问题:
- 单元格最多只能有不超过50位的字符;
- 最多只能导入9999行,受函数内部定义字段长度影响;
- 只能读取保存时当前Sheet页的数据;
- 需要指定处理数据的范围,当有效数据比较少的时候会浪费很多额外的时间;
- 上传后需要手动循环处理数据匹配到内表中;
- 不是所有系统都有该函数;
其实上面大部分的问题都是可以copy出来修修改改解决掉的,只是看需要投入多少而已,前两年调查一个问题的时候发现了一个工具类也可以处理EXCEL导入,并且可以规避掉上面绝大部分问题,目前看来唯一的缺点就是效率跟TEXT_CONVERT_XLS_TO_SAP差不多,有点慢,ABAP2XLSX当然是最好的选择,这个就当是记录一种不同的解决方式吧。
正文:
使用工具类cl_fdt_xl_spreadsheet进行xlsx处理
主要步骤如下:先使用GUI_UPLOAD以二进制形式上传EXCEL,再转换为XString形式,然后使用cl_fdt_xl_spreadsheet中的相关方法进行处理,大概看过一眼里面的代码,是转换成XML再进行处理的,主要好处是可以一次性处理所有页签的数据,在任何一个sheet页保存都可以,不再有单元格字符数限制,任何长度都可以。
* 二进制上传文件内容
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = lv_filename
filetype = 'BIN'
IMPORTING
filelength = lv_filelength
header = lv_headerxstring
TABLES
data_tab = lt_records
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17.
* 二进制转换为XString
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
EXPORTING
input_length = lv_filelength
IMPORTING
buffer = lv_headerxstring
TABLES
binary_tab = lt_records
EXCEPTIONS
failed = 1
OTHERS = 2.
TRY.
* 操作多tab页对象创建
CREATE OBJECT lo_excel_ref
EXPORTING
document_name = lv_filename
xdocument = lv_headerxstring.
CATCH cx_fdt_excel_core .
ENDTRY.
IF lo_excel_ref IS BOUND.
* 获取页签列表
lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING
worksheet_names = lt_worksheets ).
IF lt_worksheets IS NOT INITIAL.
************************************************************************
* 获取第一个页签数据
READ TABLE lt_worksheets INTO ls_worksheets INDEX 1.
lo_data_ref = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet( ls_worksheets ).
ASSIGN lo_data_ref->* TO <lt_data>.
* 内表转换
PERFORM frm_data_conv TABLES t_result
USING <lt_data>
i_begin_row.
************************************************************************
* 获取其他页签数据
* ...
************************************************************************
ENDIF.
ENDIF.
基于这种方式封装了一个新的函数,可以基于接收内表的类型(需要参照DDIC类型,临时定义的内表类型无法获取到转换例程信息)对导入数据进行一些自动处理,目前仅做了自动外码转内码处理,如需其他功能可以自行添加,当然内表的顺序还是要和EXCEL列的顺序保持一致的,如果不想保持一致可以自行改造,完整源码如下:
FUNCTION zfm_upload_xlsx_to_tab.
*"----------------------------------------------------------------------
*"*"本地接口:
*" IMPORTING
*" VALUE(I_FILE)
*" VALUE(I_BEGIN_ROW) TYPE NUMC4 DEFAULT 2
*" TABLES
*" T_RESULT
*" EXCEPTIONS
*" HAS_ERROR
*" NO_DATA
*"----------------------------------------------------------------------
DATA:
lv_filename TYPE string,
lt_records TYPE solix_tab,
lv_headerxstring TYPE xstring,
lv_filelength TYPE i.
DATA:
lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet,
lo_data_ref TYPE REF TO data.
DATA:
lt_worksheets TYPE STANDARD TABLE OF string,
ls_worksheets TYPE string.
FIELD-SYMBOLS:
<lt_data> TYPE STANDARD TABLE.
lv_filename = i_file.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = '正在导入...'(000).
* 二进制上传文件内容
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = lv_filename
filetype = 'BIN'
IMPORTING
filelength = lv_filelength
header = lv_headerxstring
TABLES
data_tab = lt_records
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17.
IF sy-subrc <> 0.
MESSAGE '请关闭导入文件后再次尝试'(001) TYPE 'S' DISPLAY LIKE 'E' RAISING has_error.
ENDIF.
* 二进制转换为XString
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
EXPORTING
input_length = lv_filelength
IMPORTING
buffer = lv_headerxstring
TABLES
binary_tab = lt_records
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid
TYPE sy-msgty
NUMBER sy-msgno
WITH sy-msgv1
sy-msgv2
sy-msgv3
sy-msgv4
RAISING has_error.
ENDIF.
TRY.
* 操作多tab页对象创建
CREATE OBJECT lo_excel_ref
EXPORTING
document_name = lv_filename
xdocument = lv_headerxstring.
CATCH cx_fdt_excel_core .
ENDTRY.
IF lo_excel_ref IS BOUND.
* 获取页签列表
lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING
worksheet_names = lt_worksheets ).
IF lt_worksheets IS NOT INITIAL.
************************************************************************
* 获取第一个页签数据
READ TABLE lt_worksheets INTO ls_worksheets INDEX 1.
lo_data_ref = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet( ls_worksheets ).
ASSIGN lo_data_ref->* TO <lt_data>.
* 内表转换
PERFORM frm_data_conv TABLES t_result
USING <lt_data>
i_begin_row.
************************************************************************
* 获取其他页签数据
* ...
************************************************************************
ENDIF.
ENDIF.
ENDFUNCTION.
*&---------------------------------------------------------------------*
*& Form FRM_DATA_CONV
*&---------------------------------------------------------------------*
* 内表转换
*----------------------------------------------------------------------*
* <--CT_TABLE 导入文件内表
* -->UT_DATA 标准表
* -->UV_BEGIN_ROW 起始行
*----------------------------------------------------------------------*
FORM frm_data_conv TABLES ct_table
USING ut_data TYPE STANDARD TABLE
uv_begin_row TYPE numc4.
DATA:lo_typedescr TYPE REF TO cl_abap_typedescr,
lo_structdescr TYPE REF TO cl_abap_structdescr,
lo_tabledescr TYPE REF TO cl_abap_tabledescr,
lo_result_ref TYPE REF TO data,
lo_newline_ref TYPE REF TO data,
lt_ddic_list TYPE ddfields,
ls_ddic_list TYPE dfies.
DATA:lv_index TYPE i.
FIELD-SYMBOLS:
<ls_itab> TYPE data,
<ls_newline> TYPE data,
<ls_source_value> TYPE data,
<ls_target_value> TYPE data.
* 获取返回结果表类型
CALL METHOD cl_abap_structdescr=>describe_by_data
EXPORTING
p_data = ct_table
RECEIVING
p_descr_ref = lo_typedescr.
lo_structdescr ?= lo_typedescr.
* 获取DDIC信息
CALL METHOD lo_structdescr->get_ddic_field_list
RECEIVING
p_field_list = lt_ddic_list
EXCEPTIONS
not_found = 1
no_ddic_type = 2
OTHERS = 3.
* 创建工作区引用
CREATE DATA lo_newline_ref TYPE HANDLE lo_structdescr.
ASSIGN lo_newline_ref->* TO <ls_newline>.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = '正在自动转码...'(002).
* Mapping赋值
LOOP AT ut_data ASSIGNING <ls_itab> FROM uv_begin_row.
DO.
ADD 1 TO lv_index.
ASSIGN COMPONENT lv_index OF STRUCTURE <ls_itab> TO <ls_source_value>.
ASSIGN COMPONENT lv_index OF STRUCTURE <ls_newline> TO <ls_target_value>.
IF <ls_source_value> IS ASSIGNED AND <ls_target_value> IS ASSIGNED.
IF lt_ddic_list IS NOT INITIAL.
READ TABLE lt_ddic_list INTO ls_ddic_list INDEX lv_index.
IF ls_ddic_list-convexit IS NOT INITIAL.
* 自动转换内码
PERFORM frm_convert_value_in USING ls_ddic_list-convexit
<ls_source_value>
CHANGING <ls_target_value>.
ENDIF.
ENDIF.
IF <ls_target_value> is INITIAL.
<ls_target_value> = <ls_source_value>.
ENDIF.
ELSE.
EXIT.
ENDIF.
UNASSIGN:
<ls_source_value>,
<ls_target_value>.
ENDDO.
IF <ls_newline> IS NOT INITIAL.
APPEND <ls_newline> TO ct_table.
ENDIF.
CLEAR:
<ls_newline>,
lv_index.
ENDLOOP.
IF ct_table[] IS INITIAL.
MESSAGE '不包含有效数据!'(003) TYPE 'S' DISPLAY LIKE 'E' RAISING no_data.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_CONVERT_VALUE_IN
*&---------------------------------------------------------------------*
* 自动外码转内码
*----------------------------------------------------------------------*
* -->uv_convexit 转换码
* -->uv_source 转换前value
* <--cv_target 转换后value
*----------------------------------------------------------------------*
FORM frm_convert_value_in USING uv_convexit TYPE convexit
uv_source
CHANGING cv_target.
CASE uv_convexit.
* 前导零
WHEN 'ALPHA'.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = uv_source
IMPORTING
output = cv_target.
* 单位
WHEN 'CUNIT'.
CALL FUNCTION 'CONVERSION_EXIT_CUNIT_INPUT'
EXPORTING
input = uv_source
language = sy-langu
IMPORTING
output = cv_target
EXCEPTIONS
unit_not_found = 1
OTHERS = 2.
* 物料号
WHEN 'MATN1'.
CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT'
EXPORTING
input = uv_source
IMPORTING
output = cv_target
EXCEPTIONS
length_error = 1
OTHERS = 2.
* WBS元素
WHEN 'ABPSP'.
CALL FUNCTION 'CONVERSION_EXIT_ABPSP_INPUT'
EXPORTING
input = uv_source
IMPORTING
output = cv_target
EXCEPTIONS
not_found = 1
OTHERS = 2.
* 特性值
WHEN 'ATINN'.
CALL FUNCTION 'CONVERSION_EXIT_ATINN_INPUT'
EXPORTING
input = uv_source
IMPORTING
output = cv_target.
* 按需添加
WHEN OTHERS.
ENDCASE.
ENDFORM.
基于此函数的一个实际用例如下: