SAP 内表数据导出到EXCEL的几种方法示例
SAP 内表数据导出到EXCEL的几种方法
方法一:函数GUI_DOWNLOAD
1. 代码
DATA: lt_itab TYPE TABLE OF sflight.
TYPES: BEGIN OF ty_fieldname,
name TYPE char20,
END OF ty_fieldname.
DATA: lt_fieldname TYPE TABLE OF ty_fieldname WITH HEADER LINE.
SELECT * FROM sflight INTO TABLE lt_itab UP TO 30 ROWS.
PERFORM frm_set_fieldname USING'Client'.
PERFORM frm_set_fieldname USING'航线代码'.
PERFORM frm_set_fieldname USING'航班连接编号'.
PERFORM frm_set_fieldname USING'航班日期'.
PERFORM frm_set_fieldname USING'航空运费'.
PERFORM frm_set_fieldname USING'航班的本地货币 '.
PERFORM frm_set_fieldname USING'飞机类型'.
PERFORM frm_set_fieldname USING'经济舱的最大容量 '.
PERFORM frm_set_fieldname USING'占据的经济舱座位'.
PERFORM frm_set_fieldname USING'当前预定总数'.
PERFORM frm_set_fieldname USING'商务舱的最大容量 '.
PERFORM frm_set_fieldname USING'占据的商务舱座位'.
PERFORM frm_set_fieldname USING'头等舱的最大容量 '.
PERFORM frm_set_fieldname USING'占据的头等舱座位'.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
* BIN_FILESIZE =
FILENAME = 'C:\Users\Administrator\Desktop\123.xls'
FILETYPE = 'DAT' "ASC格式 1000- 不会显示为 -1000 DBF格式 字符前空格 前导0不会显示
* APPEND = ' '
* WRITE_FIELD_SEPARATOR = ' '
* HEADER = '00'
* TRUNC_TRAILING_BLANKS = ' '
* WRITE_LF = 'X'
* COL_SELECT = ' '
* COL_SELECT_MASK = ' '
* DAT_MODE = ' '
* CONFIRM_OVERWRITE = ' '
* NO_AUTH_CHECK = ' '
CODEPAGE = '8404' "四位字符集代码 可通过表TCP00A,查询对应字符集代码
* IGNORE_CERR = ABAP_TRUE
* REPLACEMENT = '#'
* WRITE_BOM = ' '
* TRUNC_TRAILING_BLANKS_EOL = 'X'
* WK1_N_FORMAT = ' '
* WK1_N_SIZE = ' '
* WK1_T_FORMAT = ' '
* WK1_T_SIZE = ' '
* WRITE_LF_AFTER_LAST_LINE = ABAP_TRUE
* SHOW_TRANSFER_STATUS = ABAP_TRUE
* VIRUS_SCAN_PROFILE = '/SCET/GUI_DOWNLOAD'
* IMPORTING
* FILELENGTH =
TABLES
DATA_TAB = lt_itab
FIELDNAMES = lt_fieldname
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.
*sub
FORM FRM_SET_FIELDNAME USING VALUE(P_FIELDNAME).
lt_fieldname = P_FIELDNAME.
APPEND lt_fieldname.
ENDFORM.
2. 由于导出的格式为DAT,打开EXCEL会提示文件损坏,确认打开即可
3. 输出结果
方法二:函数SAP_CONVERT_TO_XLS_FORMAT
1. 代码
TABLES: SFLIGHT.
*定义C类型的表,防止输出错误
TYPES: BEGIN OF ty_excel,
MANDT TYPE char40,
CARRID TYPE char40,
CONNID TYPE char40,
FLDATE TYPE char40,
PRICE TYPE char40,
CURRENCY TYPE char40,
PLANETYPE TYPE char40,
SEATSMAX TYPE char40,
SEATSOCC TYPE char40,
PAYMENTSUM TYPE char40,
SEATSMAX_B TYPE char40,
SEATSOCC_B TYPE char40,
SEATSMAX_F TYPE char40,
SEATSOCC_F TYPE char40,
END OF ty_excel.
DATA: ls_structure TYPE REF TO cl_abap_structdescr.
DATA: lt_itab TYPE TABLE OF sflight,
lt_excel TYPE TABLE OF ty_excel,
lv_filename TYPE rlgrap-filename.
lv_filename = 'C:\Users\Administrator\Desktop\123.xlsx'.
SELECT * FROM sflight INTO TABLE lt_itab UP TO 30 ROWS.
MOVE-CORRESPONDING lt_itab TO lt_excel. "数据移动到输出的CHAR内表中
*---------------------加入Title行-----------------------*
INSERT INITIAL LINE INTO lt_excel ASSIGNING FIELD-SYMBOL(<fs1>) INDEX 1. "向输出内表插入一行Title 并Assigning给<fs1>
ls_structure ?= cl_abap_typedescr=>describe_by_data( sflight ). "获取SFLIHGHT的属性
LOOP AT ls_structure->components INTO DATA(ls_comps). "LOOP SFLIGHT的组件,并依次将<fs1>的组件Assigning给<fs2>,给<fs2>赋值,即给Title赋值
ASSIGN COMPONENT sy-tabix OF STRUCTURE <fs1> TO FIELD-SYMBOL(<fs2>).
IF sy-subrc EQ 0 .
<fs2> = ls_comps-name.
ENDIF.
ENDLOOP.
*-------------------------------------------------------*
CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
EXPORTING
* I_FIELD_SEPERATOR =
I_LINE_HEADER = 'X'
I_FILENAME = lv_filename
* I_APPL_KEEP = ' '
TABLES
I_TAB_SAP_DATA = lt_excel
* CHANGING
* I_TAB_CONVERTED_DATA =
EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2.
2. 上面的方法输出内容可能出现以下错误;可按照后面步骤进行修正(和SE16N输出对比)
- 金额输出不保留小数位数
- 日期类型显示为ABAP内部日期值
- 数字输出为科学计数法
3. 设置金额字段单元格类型为数值类型 2位小数,设置日期类型为文本类型,然后清空EXCEL文档内容并保存
4. SMW0上传EXCEL模板
5. 更新代码
TABLES: SFLIGHT.
*定义C类型的表,防止输出错误
TYPES: BEGIN OF ty_excel,
MANDT TYPE char40,
CARRID TYPE char40,
CONNID TYPE char40,
FLDATE TYPE char40,
PRICE TYPE char40,
CURRENCY TYPE char40,
PLANETYPE TYPE char40,
SEATSMAX TYPE char40,
SEATSOCC TYPE char40,
PAYMENTSUM TYPE char40,
SEATSMAX_B TYPE char40,
SEATSOCC_B TYPE char40,
SEATSMAX_F TYPE char40,
SEATSOCC_F TYPE char40,
END OF ty_excel.
DATA: ls_structure TYPE REF TO cl_abap_structdescr.
DATA: lt_itab TYPE TABLE OF sflight,
lt_excel TYPE TABLE OF ty_excel,
lv_filename TYPE rlgrap-filename.
*Definition Structural
DATA: wa_wwwdata LIKE wwwdatatab.
lv_filename = 'C:\Users\Administrator\Desktop\123.xlsx'.
*Download Template File
SELECT SINGLE * FROM wwwdata
INNER JOIN tadir ON wwwdata~objid = tadir~obj_name
INTO CORRESPONDING FIELDS OF wa_wwwdata
WHERE wwwdata~srtf2 = 0
AND wwwdata~relid = 'MI' "MI二进制对象
AND tadir~pgmid = 'R3TR'
AND tadir~object = 'W3MI'
AND tadir~obj_name = 'Z_SFLIGHT'. "SMW0上传的模板对象名
IF sy-subrc = 0.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
KEY = wa_wwwdata
DESTINATION = lv_filename.
ENDIF.
SELECT * FROM sflight INTO TABLE lt_itab UP TO 30 ROWS.
MOVE-CORRESPONDING lt_itab TO lt_excel. "数据移动到输出的CHAR内表中
*Covert Date
LOOP AT lt_excel INTO DATA(wa_excel).
wa_excel-FLDATE = |{ wa_excel-FLDATE+0(4) }.{ wa_excel-FLDATE+4(2) }.{ wa_excel-FLDATE+6(2) }|. "ABAP新语法 旧语法可用CONCATENATE
MODIFY lt_excel FROM wa_excel.
ENDLOOP.
*---------------------加入Title行-----------------------*
INSERT INITIAL LINE INTO lt_excel ASSIGNING FIELD-SYMBOL(<fs1>) INDEX 1. "向输出内表插入一行Title 并Assigning给<fs1>
ls_structure ?= cl_abap_typedescr=>describe_by_data( sflight ). "获取SFLIHGHT的属性
LOOP AT ls_structure->components INTO DATA(ls_comps). "LOOP SFLIGHT的组件,并依次将<fs1>的组件Assigning给<fs2>,给<fs2>赋值,即给Title赋值
ASSIGN COMPONENT sy-tabix OF STRUCTURE <fs1> TO FIELD-SYMBOL(<fs2>).
IF sy-subrc EQ 0 .
<fs2> = ls_comps-name.
ENDIF.
ENDLOOP.
*-------------------------------------------------------*
CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
EXPORTING
* I_FIELD_SEPERATOR =
I_LINE_HEADER = 'X'
I_FILENAME = lv_filename
* I_APPL_KEEP = ''
TABLES
I_TAB_SAP_DATA = lt_excel
* CHANGING
* I_TAB_CONVERTED_DATA =
EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2.
6. 修改后运行结果
方法三:OLE
1. OLE方法此处不再赘述,可自行百度用法
方法四:abap2xlsx
1. 安装SAPlink
2. 安装ABAPXLSX
3. ABAP2XLSX输出EXCEL方法,后续在博客中其他文章更新
post SAP-Garson
原文链接:https://blog.csdn.net/qq_30797051/article/details/108951816文章来自于网络,如果侵犯了您的权益,请联系站长删除!