*&---------------------------------------------------------------------*
*& Report ZLOAD
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZLOAD.
TYPE-POOLS:SLIS,ICON.
DATA:GT_FIELDCAT TYPE LVC_T_FCAT,
GS_FIELDCAT TYPE LVC_S_FCAT,
IS_LAYOUT TYPE LVC_S_LAYO.
"内表结构定义
TYPES:BEGIN OF TYP_OUT,
VBELN TYPE VBAK-VBELN,
MENGE TYPE STRING,
AUDAT TYPE STRING,
END OF TYP_OUT.
"内表定义
DATA: GT_OUT TYPE TABLE OF TYP_OUT,
GS_OUT TYPE TYP_OUT.
DEFINE FIELDCAT.
gs_fieldcat-key = &1."主键
gs_fieldcat-fieldname = &2."字段名
gs_fieldcat-scrtext_l = &3."字段描述
gs_fieldcat-edit = &4."可编辑
gs_fieldcat-no_zero = &5."不显示前导0
gs_fieldcat-ref_table = &6."参考表
gs_fieldcat-ref_field = &7."参考字段
APPEND gs_fieldcat TO gt_fieldcat.
END-OF-DEFINITION.
FIELDCAT '' 'VBELN' '采购订单' '' '' '' '' .
FIELDCAT '' 'MENGE' '数量' '' '' '' '' .
FIELDCAT '' 'AUDAT' '日期' '' '' '' '' .
IS_LAYOUT-CWIDTH_OPT = 'X'.
IS_LAYOUT-ZEBRA = 'X' .
GT_OUT = VALUE #(
( VBELN = '100001' MENGE = '2' AUDAT = '2024/01/01' )
( VBELN = '100002' MENGE = '3' AUDAT = '2024/01/02' ) ).
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
EXPORTING
I_CALLBACK_PROGRAM = SY-REPID
I_CALLBACK_PF_STATUS_SET = 'FRM_PF_STATUS'
I_CALLBACK_USER_COMMAND = 'FRM_USER_COMMAND'
IS_LAYOUT_LVC = IS_LAYOUT
IT_FIELDCAT_LVC = GT_FIELDCAT
I_DEFAULT = 'X'
I_SAVE = 'A'
TABLES
T_OUTTAB = GT_OUT[]
EXCEPTIONS
PROGRAM_ERROR = 1
OTHERS = 2.
FORM FRM_PF_STATUS USING IT_EXTAB TYPE SLIS_T_EXTAB.
SET PF-STATUS 'STATUS'.
ENDFORM.
FORM FRM_USER_COMMAND USING UCOMM TYPE SY-UCOMM
CHANGING SELFIELD TYPE SLIS_SELFIELD.
CASE UCOMM.
WHEN '&EXCEL'."EXCE导出
*EXCEL点击下载弹窗选择路径
PERFORM FRM_SELECT_FILE.
PERFORM FRM_EXCEL_LOAD1."*方法1 GUI_DOWNLOAD 只支持.XLS
PERFORM FRM_EXCEL_LOAD2."方法2 DOI方式 第一行深色 .XLSX
PERFORM FRM_EXCEL_LOAD3."方法3 支持.XLSX
WHEN '&OLE'. "OLE导出
PERFORM FRM_OLE_LOAD.
WHEN '&DOI'. "DOI导出
PERFORM FRM_DOI_LOAD.
WHEN '&TXT'. "TXT导出
PERFORM FRM_TXT_LOAD.
WHEN OTHERS.
ENDCASE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_LOAD
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_EXCEL_LOAD1 .
DATA: BEGIN OF LT_FIELDNAMES OCCURS 0 ,
FIELD(100),
END OF LT_FIELDNAMES.
APPEND '单号' TO LT_FIELDNAMES.
APPEND '数量' TO LT_FIELDNAMES.
APPEND '日期' TO LT_FIELDNAMES.
*ASC 负数1000-不会显示为-1000 DAT会显示为-1000，
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
FILENAME = 'C:\TEST\EXCEL.xls'
FILETYPE = 'DAT'
CODEPAGE = '8404'
TABLES
DATA_TAB = GT_OUT
FIELDNAMES = LT_FIELDNAMES.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_OLE_LOAD
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_OLE_LOAD .
TYPE-POOLS OLE2. " 引用ole2类型池
DATA: EXCEL TYPE OLE2_OBJECT, "EXCEL
WORKBOOK TYPE OLE2_OBJECT, "workworkbook(工作簿)
WORKBOOKS TYPE OLE2_OBJECT,
SHEET TYPE OLE2_OBJECT, "sheet页
SHEETS TYPE OLE2_OBJECT,
CELL TYPE OLE2_OBJECT, "单元格
CELL2 TYPE OLE2_OBJECT,
FONT TYPE OLE2_OBJECT,
RANGE TYPE OLE2_OBJECT,
COLUMN TYPE OLE2_OBJECT,
SAVE TYPE OLE2_OBJECT.
DATA: LV_FILE TYPE STRING,
LV_RC TYPE I.
*定义数据拷贝到剪切板上内表
DATA: BEGIN OF LS_DATA,
LINE(3400) TYPE C,
END OF LS_DATA.
DATA LT_DATA LIKE TABLE OF LS_DATA.
*定义通过宏来设置指定单元格的内容和字体
DEFINE FILL_CELL.
CALL METHOD OF excel 'CELLS' = cell " 单元格位置
EXPORTING
#1 = &1
#2 = &2.
SET PROPERTY OF cell 'VALUE' = &3. " 单元格内容
CALL METHOD OF cell 'FONT' = font.
SET PROPERTY OF font 'BOLD' = &4. " 设置是否为粗体
SET PROPERTY OF font 'SIZE' = &5. " 设置字体大小
FREE OBJECT font.
FREE OBJECT cell.
END-OF-DEFINITION.
"创建Excel对象
CREATE OBJECT EXCEL 'Excel.Application'.
IF SY-SUBRC <> 0.
MESSAGE '调用EXCEL失败' TYPE 'E'.
ENDIF.
SET PROPERTY OF EXCEL 'VISIBLE' = 0."使excel可见（1：可见 0：后台运行不可见）
SET PROPERTY OF EXCEL 'DisplayAlerts' = 0.
SET PROPERTY OF EXCEL 'SHEETSINNEWWORKBOOK' = 1."设置Excel打开时插入一个sheet1、Sheet2.....）
CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOKS.
CALL METHOD OF WORKBOOKS 'ADD' = WORKBOOK.
GET PROPERTY OF EXCEL 'Sheets' = SHEETS.
GET PROPERTY OF EXCEL 'ACTIVESHEET' = SHEET.
CALL METHOD OF SHEET 'Select'.
SET PROPERTY OF SHEET 'NAME' = 'Format'. "设定sheet名称
CONCATENATE
'单号'
'数量'
'日期'
INTO LS_DATA SEPARATED BY CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB .
APPEND LS_DATA TO LT_DATA.
CLEAR LS_DATA.
LOOP AT GT_OUT INTO GS_OUT.
CONCATENATE
GS_OUT-VBELN
GS_OUT-MENGE
GS_OUT-AUDAT
INTO LS_DATA SEPARATED BY CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB .
APPEND LS_DATA TO LT_DATA.
CLEAR LS_DATA.
ENDLOOP.
*将内表放在剪切板
CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT
IMPORTING
DATA = LT_DATA "剪切板字符串
CHANGING
RC = LV_RC " Return Code
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
* no_authority = 4
OTHERS = 5.
CALL METHOD OF EXCEL 'CELLS' = CELL
EXPORTING
#1 = 1
#2 = 1 .
CALL METHOD OF EXCEL 'CELLS' = CELL2
EXPORTING
#1 = 1
#2 = 1 .
*放在EXCEL 第一个行 第一个列也就是A1上
CALL METHOD OF EXCEL'RANGE' = RANGE
EXPORTING
#1 = CELL
#2 = CELL2 .
***指定第2列数字格式---------------------------------------
CALL METHOD OF EXCEL 'COLUMNS' = COLUMN
EXPORTING
#1 = 2.
SET PROPERTY OF COLUMN 'NumberFormat' = '@'.
***指定第2列数字格式--------------------------------------
***指定日期格式-------------------------------------------
* CALL METHOD OF EXCEL 'COLUMNS' = COLUMN
*
* EXPORTING
* #1 = 3.
*
* SET PROPERTY OF COLUMN 'NumberFormat' = '[$-14409]yyyy/mm/dd;@'.
***指定日期格式--------------------------------------------
* 将剪贴板放在EXCEL上 注意要先设置格式再放在剪切板
CALL METHOD OF RANGE 'SELECT'.
CALL METHOD OF SHEET 'PASTE'.
" 将EXCEL单元格宽度按实际文本长度来设置 优化列宽
CALL METHOD OF EXCEL'COLUMNS' = COLUMN.
CALL METHOD OF COLUMN 'AUTOFIT'.
"激活保存
GET PROPERTY OF EXCEL 'ACTIVESHEET' = SHEET."激活工作簿
GET PROPERTY OF EXCEL 'ACTIVEWORKBOOK' = WORKBOOKS."激活工作区
***路径
* LV_FILE = 'C:\TEST\EXCEL.xls'.
*
*
*
* CALL METHOD OF SHEET 'SAVEAS'= SAVE
* EXPORTING
* #1 = LV_FILE "将excel文件保存为路径
* #2 = 1.
*如果是设置.XLSX 要用51
LV_FILE = 'C:\TEST\EXCEL.xlsx'.
CALL METHOD OF SHEET 'SAVEAS'= SAVE
EXPORTING
#1 = LV_FILE "将excel文件保存为路径
#2 = 51.
CALL METHOD OF WORKBOOKS 'CLOSE'."关闭工作区
CALL METHOD OF EXCEL 'QUIT'. "退出excel
FREE OBJECT COLUMN.
FREE OBJECT CELL.
FREE OBJECT CELL2.
FREE OBJECT RANGE.
FREE OBJECT SHEET.
FREE OBJECT SHEETS.
FREE OBJECT WORKBOOK.
FREE OBJECT WORKBOOKS.
FREE OBJECT SAVE.
FREE OBJECT EXCEL.
***结束EXCEL进程
* CALL FUNCTION 'GUI_EXEC'
* EXPORTING
* COMMAND = 'cmd /c taskkill /f /t /im EXCEL.EXE'.
* WAIT UP TO 1 SECONDS.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DOI_LOAD
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_DOI_LOAD .
DATA:
GR_CONTAINER TYPE REF TO CL_GUI_CONTAINER,
GR_CONTROL TYPE REF TO I_OI_CONTAINER_CONTROL,
GR_DOCUMENT TYPE REF TO I_OI_DOCUMENT_PROXY,
GR_SPREADSHEET TYPE REF TO I_OI_SPREADSHEET,
GR_SPLITTER TYPE REF TO CL_GUI_SPLITTER_CONTAINER,
ERRORS TYPE REF TO I_OI_ERROR OCCURS 0 WITH HEADER LINE
.
* business document system
DATA:
GR_BDS_DOCUMENTS TYPE REF TO CL_BDS_DOCUMENT_SET,
GV_CLASSNAME TYPE SBDST_CLASSNAME,
GV_CLASSTYPE TYPE SBDST_CLASSTYPE,
GV_OBJECTKEY TYPE SBDST_OBJECT_KEY,
GV_DOC_COMPONENTS TYPE SBDST_COMPONENTS,
GV_DOC_SIGNATURE TYPE SBDST_SIGNATURE.
* template url
DATA:
GT_BDS_URIS TYPE SBDST_URI,
GW_BDS_URL LIKE LINE OF GT_BDS_URIS,
GV_TEMPLATE_URL(256) TYPE C.
* Required for writing data to Excel
DATA:
GT_RANGES TYPE SOI_RANGE_LIST,
GW_RANGE TYPE SOI_RANGE_ITEM,
GT_CONTENTS TYPE SOI_GENERIC_TABLE,
GW_CONTENT TYPE SOI_GENERIC_ITEM.
CONSTANTS DOCUMENT_NAME(30) VALUE 'ZSDR042.XLSX'. "模板名字
CREATE OBJECT GR_SPLITTER
EXPORTING
PARENT = CL_GUI_CONTAINER=>SCREEN0
ROWS = 1
COLUMNS = 1.
CALL METHOD GR_SPLITTER->SET_BORDER
EXPORTING
BORDER = CL_GUI_CFW=>FALSE.
GR_CONTAINER = GR_SPLITTER->GET_CONTAINER( ROW = 1 COLUMN = 1 ).
* create container control
CALL METHOD C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL
IMPORTING
CONTROL = GR_CONTROL
ERROR = ERRORS.
APPEND ERRORS.
* initialize control
CALL METHOD GR_CONTROL->INIT_CONTROL
EXPORTING
INPLACE_ENABLED = 'X '
INPLACE_SCROLL_DOCUMENTS = 'X'
REGISTER_ON_CLOSE_EVENT = 'X'
REGISTER_ON_CUSTOM_EVENT = 'X'
R3_APPLICATION_NAME = 'DOI demo'
PARENT = GR_CONTAINER
IMPORTING
ERROR = ERRORS.
APPEND ERRORS.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_TXT_LOAD
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_TXT_LOAD .
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
CONFIRM_OVERWRITE = 'X' "如果文件存在 弹出是否覆盖文件的对话框
WRITE_FIELD_SEPARATOR = 'X' "加入字段分隔符 TAB
FILENAME = 'C:\TEST\TXT.txt' "文件名 必须为 STRING 类型
TABLES
DATA_TAB = GT_OUT "内表
EXCEPTIONS
FILE_WRITE_ERROR = 1
FILE_NOT_FOUND = 2.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_LOAD2
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_EXCEL_LOAD2 .
DATA : LT_EXCEL_STRUCTURE TYPE TABLE OF TYP_OUT,
LR_EXCEL_STRUCTURE TYPE REF TO DATA,
LV_CONTENT TYPE XSTRING.
"file download
DATA : LT_BINARY TYPE TABLE OF SDOKCNTASC,
LV_LENGTH TYPE I.
DATA LV_FILE TYPE STRING.
LT_EXCEL_STRUCTURE[] = GT_OUT[].
GET REFERENCE OF LT_EXCEL_STRUCTURE INTO LR_EXCEL_STRUCTURE.
"excel instantiate
DATA(LO_TOOL_XLS) = CL_SALV_EXPORT_TOOL_ATS_XLS=>CREATE_FOR_EXCEL(
EXPORTING R_DATA = LR_EXCEL_STRUCTURE ) .
"Add columns to sheet
DATA(LO_CONFIG) = LO_TOOL_XLS->CONFIGURATION( ).
LO_CONFIG->ADD_COLUMN(
EXPORTING
HEADER_TEXT = CONV STRING( '单号' )
FIELD_NAME = CONV STRING( 'VBELN' )
DISPLAY_TYPE = IF_SALV_BS_MODEL_COLUMN=>UIE_TEXT_VIEW ).
LO_CONFIG->ADD_COLUMN(
EXPORTING
HEADER_TEXT = CONV STRING( '数量' )
FIELD_NAME = CONV STRING( 'MENGE' )
DISPLAY_TYPE = IF_SALV_BS_MODEL_COLUMN=>UIE_TEXT_VIEW ).
LO_CONFIG->ADD_COLUMN(
EXPORTING
HEADER_TEXT = CONV STRING( '日期' )
FIELD_NAME = CONV STRING( 'AUDAT' )
DISPLAY_TYPE = IF_SALV_BS_MODEL_COLUMN=>UIE_TEXT_VIEW ).
"get excel in xstring
LO_TOOL_XLS->READ_RESULT( IMPORTING CONTENT = LV_CONTENT ).
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
BUFFER = LV_CONTENT
IMPORTING
OUTPUT_LENGTH = LV_LENGTH
TABLES
BINARY_TAB = LT_BINARY.
LV_FILE = 'C:\TEST\EXCEL.xlsx'.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
BIN_FILESIZE = LV_LENGTH
FILENAME = CONV STRING( LV_FILE ) "'C:\TEST\EXCEL.XLSX'
FILETYPE = 'BIN'
TABLES
DATA_TAB = LT_BINARY.
IF SY-SUBRC <> 0.
MESSAGE: '导出错误' && SY-SUBRC TYPE 'E'.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_LOAD3
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_EXCEL_LOAD3 .
TYPES:BEGIN OF TY_OUT,
VBELN(10) TYPE C,
MENGE(10) TYPE C,
AUDAT(10) TYPE C,
END OF TY_OUT.
DATA LT_OUT TYPE TABLE OF TY_OUT.
LT_OUT = VALUE #(
( VBELN = '单号 ' MENGE = '数量' AUDAT = '日期' )
( VBELN = '100001' MENGE = '2' AUDAT = '2024/01/01' )
( VBELN = '100002' MENGE = '3' AUDAT = '2024/01/02' ) ).
CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
EXPORTING
I_FILENAME = 'C:\TEST\EXCEL.xlsx'
TABLES
I_TAB_SAP_DATA = LT_OUT
EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_SELECT_FILE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_SELECT_FILE .
DATA P_FILE TYPE RLGRAP-FILENAME.
CONSTANTS DOCUMENT_NAME(30) VALUE 'EXCEL.XLSX'. "默认EXCEL名
**&---保存文件
CALL FUNCTION 'TB_LIMIT_WS_FILENAME_GET'
EXPORTING
DEF_FILENAME = DOCUMENT_NAME
MASK = '*.XLSX'
IMPORTING
FILENAME = P_FILE
EXCEPTIONS
SELECTION_CANCEL = 1
SELECTION_ERROR = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
LEAVE TO SCREEN 0.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.
标签：DOI,OLE,EXCEL,CALL,EXPORTING,TYPE,METHOD,&---------------------------------- From： https://www.cnblogs.com/Anyohh233/p/18023631