导入下载excel（还有excel多个sheet）和txt文本的方法

来源：互联网发布：英国约克大学商科知乎编辑：程序博客网时间：2024/05/01 06:37

我们经常需要通过上载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.