我们经常需要通过上载excel或者是txt文本然后进行处理。。处理excel和txt文本有多种方法,一般都是通过调用function module或者是method来完成:
1、导入excel并处理:
1)一般这些上载文件的都要先在 AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. 做处理,excel的处理一般是:
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
mask = ' '
static = 'X'
CHANGING
file_name = p_file.
2)使用上载的FM,把excel的数据上载到对应的内表:
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = l_file
i_begin_col = 1
i_begin_row = 3
i_end_col = 37
i_end_row = 10000
TABLES
intern = lt_temp
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
其中it_temp这个内表可以定义为都是字符字段的集合,然后再把这个内表的各个字段赋值到要处理的内表。当然也可以指定excel的格式,第几个输入什么字段,同时it_temp也要按照相对于的字段顺序。
2、导入excel的多个sheet处理:
1)首先做一个CASE处理:分别去读取excel里面的数据放到不同的内表里面
DO 3 TIMES.
CASE SY-INDEX.
WHEN 1.
SHEET_NAME = 'Header'.
ASSIGN PO_HEADER TO <DYN_WA>.
WHEN 2.
SHEET_NAME = 'PO List'.
ASSIGN PO_ITEMS2 TO <DYN_WA>.
WHEN 3.
SHEET_NAME = 'GL account'.
ASSIGN GL_DATA TO <DYN_WA>.
ENDCASE.
PERFORM GET_EXCEL_DATA.
ENDDO.
2)调用一个function module(自己定义的,有需要可以问博主要。。不便公布)
CALL FUNCTION ''自己定义 " call function get file data
EXPORTING
FILENAME = P_TABLE 文件路径
SHEET_NAME = SHEET_NAME sheet name
I_BEGIN_COL = I_BEGIN_COL 1
I_BEGIN_ROW = I_BEGIN_ROW 2
I_END_COL = I_END_COL 256
I_END_ROW = I_END_ROW 65536
TABLES
INTERN = I_INTERN 输出内表
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
MESSAGE I003(ZSOH) WITH TEXT-001.
STOP.
ENDIF.
3、导出excel的处理:
1)直接调用导出excel的FM处理:
SET LOCALE LANGUAGE SY-LANGU.
TRANSLATE P_TABLE TO LOWER CASE.
REPLACE '.xlsx' WITH '' INTO P_TABLE.
REPLACE '.xls' WITH '' INTO P_TABLE.
CONCATENATE P_TABLE '_log_' SY-DATUM SY-UZEIT '.xls' INTO L_FILE.
PERFORM INSER_TEXT.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
FILENAME = L_FILE
WRITE_FIELD_SEPARATOR = 'X'
CONFIRM_OVERWRITE = 'X'
FILETYPE = 'DAT'
CODEPAGE = '8404'
TABLES
DATA_TAB = MIRO_LOG
EXCEPTIONS
FILE_WRITE_ERROR = 1
NO_BATCH = 2
GUI_REFUSE_FILETRANSFER = 3
INVALID_TYPE = 4
NO_AUTHORITY = 5
UNKNOWN_ERROR = 6
HEADER_NOT_ALLOWED = 7
SEPARATOR_NOT_ALLOWED = 8
FILESIZE_NOT_ALLOWED = 9
HEADER_TOO_LONG = 10
DP_ERROR_CREATE = 11
DP_ERROR_SEND = 12
DP_ERROR_WRITE = 13
UNKNOWN_DP_ERROR = 14
ACCESS_DENIED = 15
DP_OUT_OF_MEMORY = 16
DISK_FULL = 17
DP_TIMEOUT = 18
FILE_NOT_FOUND = 19
DATAPROVIDER_EXCEPTION = 20
CONTROL_FLUSH_ERROR = 21
OTHERS = 22.
4、导入txt文本并处理:
1)一样的,要对上传的文件进行检查,AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. :
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
mask = ',*.*,*.*.'
mode = 'O'
IMPORTING
filename = filename
EXCEPTIONS
OTHERS = 0.
2)导入txt文本的时候有两个方法,不过一般现在比较多用的是后面那个method。
第一个是: CALL FUNCTION 'WS_UPLOAD'
DATA: %type LIKE sy-msgty.
CALL FUNCTION 'WS_UPLOAD'
EXPORTING
* CODEPAGE = ' '
filename = a_file
filetype = 'DAT'
TABLES
data_tab = itab
EXCEPTIONS
conversion_error = 1
file_open_error = 2
file_read_error = 3
invalid_type = 4
no_batch = 5
unknown_error = 6
invalid_table_width = 7
gui_refuse_filetransfer = 8
customer_error = 9
OTHERS = 10.
%type = sy-msgty.
IF sy-subrc = '1'.
%type = 'I'.
ENDIF.
第二个是: CALL METHOD cl_gui_frontend_services=>gui_upload
CALL METHOD cl_gui_frontend_services=>gui_upload
EXPORTING
filename = i_file
* FILETYPE = 'DAT' "MCS deleted by LIUYI 20060825
codepage = codepage
dat_mode = 'X' "MCS added by LIUYI 20060825
has_field_separator = tab_key "MCS added by LIUYI 20060825
CHANGING
data_tab = itab[]
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
not_supported_by_gui = 17
error_no_gui = 18
OTHERS = 19.
5、导出txt处理:
1)直接使用标准的method:
DATA: codepage TYPE abap_encod VALUE '8400'.
DATA: o_file TYPE string.
o_file = logname.
CALL METHOD cl_gui_frontend_services=>gui_download
EXPORTING
filename = o_file
filetype = 'DAT'
codepage = codepage
CHANGING
data_tab = it_log[]
EXCEPTIONS
file_write_error = 1
no_batch = 2
gui_refuse_filetransfer = 3
invalid_type = 4
no_authority = 5
unknown_error = 6
header_not_allowed = 7
separator_not_allowed = 8
filesize_not_allowed = 9
header_too_long = 10
dp_error_create = 11
dp_error_send = 12
dp_error_write = 13
unknown_dp_error = 14
access_denied = 15
dp_out_of_memory = 16
disk_full = 17
dp_timeout = 18
file_not_found = 19
dataprovider_exception = 20
control_flush_error = 21
not_supported_by_gui = 22
error_no_gui = 23
OTHERS = 24.