Author: Matt Hall
Submitted: 5/6/2015
Related Links:
https://scn.sap.com/docs/DOC-33578 - XLSX Upload - A Unified Approach
- Reading Excel file from Application Server into ABAP Internal Table
Many ABAP programs are designed to load data from an Excel template. An issue with using Excel templates is that you never know if the user of the program is using the latest version of the Excel template. The following steps allow you to upload an Excel template to SAP and migrate using a CTS. Only a couple of simple statements need to be added to your program to provide options for the user of the program to download or open the most recent Excel template.
Upload an Excel template to SAP
Run transaction SMW0. Click on the Binary data for WebRFC application radio button and press ENTER.
Search for templates that begin with Z
Create new Template
Save new template in a CTS and migrate as needed.
Example of selection-screen with buttons to download Excel template and open an Excel template
Download Excel template button
Afterwards, open excel template and add data as needed by application.
Open Excel template button
How to add buttons to selection-screen of program to provide user options to save
Excel template and open Excel template
Run transaction SE38 to Edit program. Insert code as shown below.
Function Z_EXPORT_TEMPLATE
FUNCTION Z_EXPORT_TEMPLATE.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" REFERENCE(P_OBJECT_NAME) TYPE W3OBJID DEFAULT 'ZMWRKCTR01'
*" EXPORTING
*" REFERENCE(LS_RETURN) TYPE BAPIRET2
*"----------------------------------------------------------------------
DATA: LS_WWWDATA_TAB LIKE WWWDATATAB.
CLEAR: LS_RETURN.
*Make sure template exists in SMw0 as binary object
SELECT * FROM WWWDATA INNER JOIN TADIR
ON WWWDATA~OBJID = TADIR~OBJ_NAME
INTO CORRESPONDING FIELDS OF LS_WWWDATA_TAB UP TO 1 ROWS
WHERE WWWDATA~SRTF2 = 0
AND WWWDATA~RELID = 'MI'
AND TADIR~PGMID = 'R3TR'
AND TADIR~OBJECT = 'W3MI'
AND TADIR~OBJ_NAME = P_OBJECT_NAME.
ENDSELECT.
IF SY-SUBRC <> 0.
PERFORM FILL_RETURN USING
'E'
'OO'
'000'
'Template'
P_OBJECT_NAME
'does not exist in'
'transaction SMW0'
CHANGING LS_RETURN.
EXIT.
ENDIF.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
KEY = LS_WWWDATA_TAB.
ENDFUNCTION.
Function Z_OPEN_TEMPLATE
FUNCTION Z_OPEN_TEMPLATE.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" REFERENCE(P_OBJECT_NAME) TYPE W3OBJID DEFAULT 'ZMWRKCTR01'
*" REFERENCE(P_MTYPE) TYPE W3CONTTYPE DEFAULT 'Excel'
*" REFERENCE(P_APPLI) TYPE W3FILENAME DEFAULT 'Excel.exe'
*" EXPORTING
*" REFERENCE(LS_RETURN) TYPE BAPIRET2
*"----------------------------------------------------------------------
DATA: LS_WWWDATA_TAB LIKE WWWDATATAB.
DATA: L_OBJID LIKE WWWDATAID,
MIMEAPPS LIKE W3MIMEAPPL OCCURS 10 WITH HEADER LINE.
CLEAR: LS_RETURN.
*Make sure template exists in SMw0 as binary object
SELECT * FROM WWWDATA INNER JOIN TADIR
ON WWWDATA~OBJID = TADIR~OBJ_NAME
INTO CORRESPONDING FIELDS OF LS_WWWDATA_TAB UP TO 1 ROWS
WHERE WWWDATA~SRTF2 = 0
AND WWWDATA~RELID = 'MI'
AND TADIR~PGMID = 'R3TR'
AND TADIR~OBJECT = 'W3MI'
AND TADIR~OBJ_NAME = P_OBJECT_NAME.
ENDSELECT.
IF SY-SUBRC <> 0.
PERFORM FILL_RETURN USING
'E'
'OO'
'000'
'Template'
P_OBJECT_NAME
'does not exist in'
'transaction SMW0'
CHANGING LS_RETURN.
EXIT.
ENDIF.
CONCATENATE 'mimeappl' SY-UNAME INTO L_OBJID-OBJID.
IMPORT MIMEAPPS FROM DATABASE WWWDATA(ST) ID L_OBJID.
IF SY-SUBRC = 0.
READ TABLE MIMEAPPS WITH KEY MTYPE = P_MTYPE.
IF SY-SUBRC <> 0.
MIMEAPPS-MTYPE = P_MTYPE.
MIMEAPPS-APPLI = P_APPLI.
APPEND MIMEAPPS.
EXPORT MIMEAPPS TO DATABASE WWWDATA(ST) ID L_OBJID.
ENDIF.
ELSE.
MIMEAPPS-MTYPE = P_MTYPE.
MIMEAPPS-APPLI = P_APPLI.
APPEND MIMEAPPS.
EXPORT MIMEAPPS TO DATABASE WWWDATA(ST) ID L_OBJID.
ENDIF.
CALL FUNCTION 'SHOW_WEB_OBJECT'
EXPORTING
KEY = LS_WWWDATA_TAB
EXCEPTIONS
CANCELED_BY_USER = 2
PROGRAM_NOT_FOUND = 3.
IF SY-SUBRC <> 0.
PERFORM FILL_RETURN USING
'E'
'OO'
'000'
'Error opening template'
P_OBJECT_NAME
''
''
CHANGING LS_RETURN.
EXIT.
ENDIF.
ENDFUNCTION.
Include LZUTILF01
FORM FILL_RETURN USING P_TYPE
P_ID
P_NUMBER
P_MESSAGE_V1
P_MESSAGE_V2
P_MESSAGE_V3
P_MESSAGE_V4
CHANGING P_RETURN LIKE BAPIRET2.
CLEAR: P_RETURN.
P_RETURN-TYPE = P_TYPE.
P_RETURN-ID = P_ID.
P_RETURN-NUMBER = P_NUMBER.
P_RETURN-MESSAGE_V1 = P_MESSAGE_V1.
P_RETURN-MESSAGE_V2 = P_MESSAGE_V2.
P_RETURN-MESSAGE_V3 = P_MESSAGE_V3.
P_RETURN-MESSAGE_V4 = P_MESSAGE_V4.
MESSAGE ID P_ID TYPE P_TYPE NUMBER P_NUMBER
WITH P_MESSAGE_V1 P_MESSAGE_V2 P_MESSAGE_V3 P_MESSAGE_V4
INTO P_RETURN-MESSAGE.
ENDFORM. " FILL_RETURN