在工作中,我们可能会经常遇到从xls中导数据到系统中的情况,SAP给我们提供了非常方便的方法,只需要调用一个'TEXT_CONVERT_XLS_TO_SAP'这个Function module就可以将xls中的内容导到内表中,用这种方法需要注意的是:首先需要定义一个内表,这个内表的字段顺序要和xls中数据的顺序是一一对应的,即xls中第一列会对应内表的第一个字段,第二列会对应内表中的第二个字段,以此类推。下面是导入xls的完整代码。
先定义一个内表,用来存放Xls中的内容和一些相关的变量。
data:g_filefilter TYPE string VALUE 'Excel模版文件|*.xls|*.xlsx',
g_filetitle TYPE string VALUE '选择文件',
g_initdir TYPE string VALUE 'C:\'.
* g_total TYPE i,
* g_success TYPE i,
* g_pos TYPE i,
* g_rowIndex type i.
g_filetitle TYPE string VALUE '选择文件',
g_initdir TYPE string VALUE 'C:\'.
* g_total TYPE i,
* g_success TYPE i,
* g_pos TYPE i,
* g_rowIndex type i.
data:begin of student OCCURS 0,
number(4) type n,
name(20) type c,
sex(1) type c,
addr(30) type c,
end of student.
number(4) type n,
name(20) type c,
sex(1) type c,
addr(30) type c,
end of student.
在屏幕上画一个文本输入框。
selection-screen begin of block blk1 with frame. title text-001.
parameters p_file type rlgrap
-filename .
selection-screen end of block blk1.
给文本输入框加上一个选择框,单击选择框的时候,弹出打开文件对话框。
AT SELECTION-SCREEN on VALUE-REQUEST FOR p_file.
perform. file_up.
perform. file_up.
form. file_up.
DATA:lt_file TYPE filetable WITH HEADER LINE,
rec TYPE i.
DATA: l_filter TYPE string.
rec TYPE i.
DATA: l_filter TYPE string.
* 选择上传文件类型
CONCATENATE cl_gui_frontend_services=>filetype_excel
cl_gui_frontend_services=>filetype_all
INTO l_filter.
CONCATENATE cl_gui_frontend_services=>filetype_excel
cl_gui_frontend_services=>filetype_all
INTO l_filter.
"弹出打开文件对话框
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = 'Upload Excel'
* default_extension = '.xls'
* default_filename =
file_filter = l_filter
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = 'Upload Excel'
* default_extension = '.xls'
* default_filename =
file_filter = l_filter
"打开文件对话框默认的路径
initial_directory = 'C:\'
CHANGING
file_table = lt_file[]
rc = rec
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
IF rec >= 1.
"获取上传文件的文件名
READ TABLE lt_file INDEX 1.
p_file = lt_file-filename.
ELSEIF rec = 0.
CLEAR p_file.
ELSE.
"MESSAGE e000 WITH text-e01.
ENDIF.
initial_directory = 'C:\'
CHANGING
file_table = lt_file[]
rc = rec
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
IF rec >= 1.
"获取上传文件的文件名
READ TABLE lt_file INDEX 1.
p_file = lt_file-filename.
ELSEIF rec = 0.
CLEAR p_file.
ELSE.
"MESSAGE e000 WITH text-e01.
ENDIF.
endform.
按F8或点闹钟之后开始进行数据导入。
start-of-selection.
perform. frm_upload_file using 'STUDENT'. "内表的名字作为参数。
form. frm_upload_file using tabName type c.
DATA:li_tab_raw_data TYPE truxs_t_text_data.
DATA:l_name TYPE string.
DATA:l_name TYPE string.
FIELD-SYMBOLS: TYPE STANDARD TABLE.
CONCATENATE p_name '[]' INTO l_name.
ASSIGN (l_name) TO .
"Xls导入内表 最关键的Function
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_line_header = 'X'
i_tab_raw_data = li_tab_raw_data
i_filename = p_file "本地文件的路径
TABLES
i_tab_converted_data =
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
"MESSAGE s000 WITH text-e02.
STOP.
ENDIF.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_line_header = 'X'
i_tab_raw_data = li_tab_raw_data
i_filename = p_file "本地文件的路径
TABLES
i_tab_converted_data =
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
"MESSAGE s000 WITH text-e02.
STOP.
ENDIF.
** 删除抬头信息
* DO 1 TIMES.
* DELETE INDEX 1.
* ENDDO.
* DO 1 TIMES.
* DELETE INDEX 1.
* ENDDO.
IF IS INITIAL.
"MESSAGE s000 WITH text-e03 DISPLAY LIKE 'E'.
STOP.
ENDIF.
"MESSAGE s000 WITH text-e03 DISPLAY LIKE 'E'.
STOP.
ENDIF.
endform.
最后将导入的结果显示出来。
end-of-selection.
loop at student.
write:student-number,student-name,student-sex,/.
endloop.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23353800/viewspace-731684/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23353800/viewspace-731684/