REPORT ZTEST_SOURAV_EXCEL
NO
STANDARD
PAGE HEADING
.
DATA :
OREF_CONTAINER TYPE REF TO CL_GUI_CUSTOM_CONTAINER ,
IREF_CONTROL TYPE REF TO I_OI_CONTAINER_CONTROL ,
IREF_DOCUMENT TYPE REF TO I_OI_DOCUMENT_PROXY ,
IREF_SPREADSHEET TYPE REF TO I_OI_SPREADSHEET ,
IREF_ERROR TYPE REF TO I_OI_ERROR .
DATA :
V_DOCUMENT_URL TYPE C LENGTH 256 ,
I_SHEETS TYPE SOI_SHEETS_TABLE ,
WA_SHEETS TYPE SOI_SHEETS ,
I_DATA TYPE SOI_GENERIC_TABLE ,
WA_DATA TYPE SOI_GENERIC_ITEM ,
I_RANGES TYPE SOI_RANGE_LIST .
PARAMETERS :
P_FILE TYPE LOCALFILE OBLIGATORY ,
P_ROWS TYPE I DEFAULT 100 OBLIGATORY , "Rows (Maximum 65536)
P_COLS TYPE I DEFAULT 10 OBLIGATORY . "Columns (Maximum 256)
INITIALIZATION .
CALL METHOD C_OI_CONTAINER_CONTROL_CREATOR =>GET_CONTAINER_CONTROL
IMPORTING
CONTROL = IREF_CONTROL
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'E' .
ENDIF .
CREATE OBJECT OREF_CONTAINER
EXPORTING
* parent =
CONTAINER_NAME = 'CONT'
* style =
* lifetime = lifetime_default
* repid =
* dynnr =
* no_autodef_progid_dynnr =
EXCEPTIONS
CNTL_ERROR = 1
CNTL_SYSTEM_ERROR = 2
CREATE_ERROR = 3
LIFETIME_ERROR = 4
LIFETIME_DYNPRO_DYNPRO_LINK = 5
OTHERS = 6 .
IF SY -SUBRC <> 0 .
MESSAGE E001 ( 00 ) WITH 'Error while creating container' .
ENDIF .
CALL METHOD IREF_CONTROL ->INIT_CONTROL
EXPORTING
* dynpro_nr = SY-DYNNR
* gui_container = ' '
INPLACE_ENABLED = 'X'
* inplace_mode = 0
* inplace_resize_documents = ' '
* inplace_scroll_documents = ' '
* inplace_show_toolbars = 'X'
* no_flush = ' '
* parent_id = cl_gui_cfw=>dynpro_0
R3_APPLICATION_NAME = 'EXCEL CONTAINER'
* register_on_close_event = ' '
* register_on_custom_event = ' '
* rep_id = SY-REPID
* shell_style = 1384185856
PARENT = OREF_CONTAINER
* name =
* autoalign = 'x'
IMPORTING
ERROR = IREF_ERROR
* retcode =
EXCEPTIONS
JAVABEANNOTSUPPORTED = 1
OTHERS = 2 .
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'E' .
ENDIF .
CALL METHOD IREF_CONTROL ->GET_DOCUMENT_PROXY
EXPORTING
* document_format = 'NATIVE'
DOCUMENT_TYPE = SOI_DOCTYPE_EXCEL_SHEET
* no_flush = ' '
* register_container = ' '
IMPORTING
DOCUMENT_PROXY = IREF_DOCUMENT
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'E' .
ENDIF .
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE .
* To provide F4 help for the file
PERFORM SUB_FILE_F4 .
START-OF-SELECTION .
CONCATENATE 'FILE://' P_FILE INTO V_DOCUMENT_URL .
CALL METHOD IREF_DOCUMENT ->OPEN_DOCUMENT
EXPORTING
DOCUMENT_TITLE = 'Excel'
DOCUMENT_URL = V_DOCUMENT_URL
* no_flush = ' '
OPEN_INPLACE = 'X'
* open_readonly = ' '
* protect_document = ' '
* onsave_macro = ' '
* startup_macro = ''
* user_info =
IMPORTING
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'I' .
LEAVE LIST-PROCESSING .
ENDIF .
CALL METHOD IREF_DOCUMENT ->GET_SPREADSHEET_INTERFACE
EXPORTING
NO_FLUSH = ' '
IMPORTING
ERROR = IREF_ERROR
SHEET_INTERFACE = IREF_SPREADSHEET
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'I' .
LEAVE LIST-PROCESSING .
ENDIF .
CALL METHOD IREF_SPREADSHEET ->GET_SHEETS
EXPORTING
NO_FLUSH = ' '
* updating = -1
IMPORTING
SHEETS = I_SHEETS
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'I' .
LEAVE LIST-PROCESSING .
ENDIF .
LOOP AT I_SHEETS INTO WA_SHEETS .
CALL METHOD IREF_SPREADSHEET ->SELECT_SHEET
EXPORTING
NAME = WA_SHEETS -SHEET_NAME
* no_flush = ' '
IMPORTING
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
EXIT .
* call method iref_error->raise_message
* exporting
* type = 'E'.
ENDIF .
CALL METHOD IREF_SPREADSHEET ->SET_SELECTION
EXPORTING
TOP = 1
LEFT = 1
ROWS = P_ROWS
COLUMNS = P_COLS .
CALL METHOD IREF_SPREADSHEET ->INSERT_RANGE
EXPORTING
NAME = 'Test'
ROWS = P_ROWS
COLUMNS = P_COLS
NO_FLUSH = ''
IMPORTING
ERROR = IREF_ERROR .
IF IREF_ERROR ->HAS_FAILED = 'X' .
EXIT .
* call method iref_error->raise_message
* exporting
* type = 'E'.
ENDIF .
REFRESH I_DATA .
CALL METHOD IREF_SPREADSHEET ->GET_RANGES_DATA
EXPORTING
* no_flush = ' '
ALL = 'X'
* updating = -1
* rangesdef =
IMPORTING
CONTENTS = I_DATA
ERROR = IREF_ERROR
* retcode =
CHANGING
RANGES = I_RANGES .
* Remove ranges not to be processed else the data keeps on adding up
CALL METHOD IREF_SPREADSHEET ->DELETE_RANGES
EXPORTING
RANGES = I_RANGES .
DELETE I_DATA WHERE VALUE IS INITIAL OR VALUE = SPACE .
ULINE .
WRITE :/1 WA_SHEETS -SHEET_NAME COLOR 3 .
ULINE .
LOOP AT I_DATA INTO WA_DATA .
WRITE :(50 ) WA_DATA - VALUE .
AT END OF ROW .
NEW-LINE .
ENDAT .
ENDLOOP .
ENDLOOP .
CALL METHOD IREF_DOCUMENT ->CLOSE_DOCUMENT
* EXPORTING
* do_save = ' '
* no_flush = ' '
IMPORTING
ERROR = IREF_ERROR
* has_changed =
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'I' .
LEAVE LIST-PROCESSING .
ENDIF .
CALL METHOD IREF_DOCUMENT ->RELEASE_DOCUMENT
* EXPORTING
* no_flush = ' '
IMPORTING
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'I' .
LEAVE LIST-PROCESSING .
ENDIF .
*&---------------------------------------------------------------------*
*& Form SUB_FILE_F4
*&---------------------------------------------------------------------*
* F4 help for file path
*----------------------------------------------------------------------*
FORM SUB_FILE_F4 .
DATA :
L_DESKTOP TYPE STRING ,
L_I_FILES TYPE FILETABLE ,
L_WA_FILES TYPE FILE_TABLE ,
L_RCODE TYPE INT4 ,
L_ACTION TYPE I .
* Finding desktop
CALL METHOD CL_GUI_FRONTEND_SERVICES =>GET_DESKTOP_DIRECTORY
CHANGING
DESKTOP_DIRECTORY = L_DESKTOP
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4 .
IF SY -SUBRC <> 0 .
L_DESKTOP = 'C:\' .
ENDIF .
* Update View
CALL METHOD CL_GUI_CFW =>UPDATE_VIEW
EXCEPTIONS
CNTL_SYSTEM_ERROR = 1
CNTL_ERROR = 2
OTHERS = 3 .
CALL METHOD CL_GUI_FRONTEND_SERVICES =>FILE_OPEN_DIALOG
EXPORTING
WINDOW_TITLE = '文件选择'
DEFAULT_EXTENSION = '.xls'
* default_filename =
FILE_FILTER = 'Excel(*.xls;*.xlsx)|*.XLSX;*.XLS;'
* with_encoding =
INITIAL_DIRECTORY = L_DESKTOP
* multiselection =
CHANGING
FILE_TABLE = L_I_FILES
RC = L_RCODE
USER_ACTION = L_ACTION
* file_encoding =
EXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR = 2
ERROR_NO_GUI = 3
NOT_SUPPORTED_BY_GUI = 4
OTHERS = 5 .
IF L_ACTION = 0 AND SY -SUBRC = 0 .
READ TABLE L_I_FILES INDEX 1 INTO L_WA_FILES .
IF SY -SUBRC = 0 .
P_FILE = L_WA_FILES -FILENAME .
ENDIF .
ENDIF .
ENDFORM . " SUB_FILE_F4
DATA :
OREF_CONTAINER TYPE REF TO CL_GUI_CUSTOM_CONTAINER ,
IREF_CONTROL TYPE REF TO I_OI_CONTAINER_CONTROL ,
IREF_DOCUMENT TYPE REF TO I_OI_DOCUMENT_PROXY ,
IREF_SPREADSHEET TYPE REF TO I_OI_SPREADSHEET ,
IREF_ERROR TYPE REF TO I_OI_ERROR .
DATA :
V_DOCUMENT_URL TYPE C LENGTH 256 ,
I_SHEETS TYPE SOI_SHEETS_TABLE ,
WA_SHEETS TYPE SOI_SHEETS ,
I_DATA TYPE SOI_GENERIC_TABLE ,
WA_DATA TYPE SOI_GENERIC_ITEM ,
I_RANGES TYPE SOI_RANGE_LIST .
PARAMETERS :
P_FILE TYPE LOCALFILE OBLIGATORY ,
P_ROWS TYPE I DEFAULT 100 OBLIGATORY , "Rows (Maximum 65536)
P_COLS TYPE I DEFAULT 10 OBLIGATORY . "Columns (Maximum 256)
INITIALIZATION .
CALL METHOD C_OI_CONTAINER_CONTROL_CREATOR =>GET_CONTAINER_CONTROL
IMPORTING
CONTROL = IREF_CONTROL
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'E' .
ENDIF .
CREATE OBJECT OREF_CONTAINER
EXPORTING
* parent =
CONTAINER_NAME = 'CONT'
* style =
* lifetime = lifetime_default
* repid =
* dynnr =
* no_autodef_progid_dynnr =
EXCEPTIONS
CNTL_ERROR = 1
CNTL_SYSTEM_ERROR = 2
CREATE_ERROR = 3
LIFETIME_ERROR = 4
LIFETIME_DYNPRO_DYNPRO_LINK = 5
OTHERS = 6 .
IF SY -SUBRC <> 0 .
MESSAGE E001 ( 00 ) WITH 'Error while creating container' .
ENDIF .
CALL METHOD IREF_CONTROL ->INIT_CONTROL
EXPORTING
* dynpro_nr = SY-DYNNR
* gui_container = ' '
INPLACE_ENABLED = 'X'
* inplace_mode = 0
* inplace_resize_documents = ' '
* inplace_scroll_documents = ' '
* inplace_show_toolbars = 'X'
* no_flush = ' '
* parent_id = cl_gui_cfw=>dynpro_0
R3_APPLICATION_NAME = 'EXCEL CONTAINER'
* register_on_close_event = ' '
* register_on_custom_event = ' '
* rep_id = SY-REPID
* shell_style = 1384185856
PARENT = OREF_CONTAINER
* name =
* autoalign = 'x'
IMPORTING
ERROR = IREF_ERROR
* retcode =
EXCEPTIONS
JAVABEANNOTSUPPORTED = 1
OTHERS = 2 .
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'E' .
ENDIF .
CALL METHOD IREF_CONTROL ->GET_DOCUMENT_PROXY
EXPORTING
* document_format = 'NATIVE'
DOCUMENT_TYPE = SOI_DOCTYPE_EXCEL_SHEET
* no_flush = ' '
* register_container = ' '
IMPORTING
DOCUMENT_PROXY = IREF_DOCUMENT
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'E' .
ENDIF .
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE .
* To provide F4 help for the file
PERFORM SUB_FILE_F4 .
START-OF-SELECTION .
CONCATENATE 'FILE://' P_FILE INTO V_DOCUMENT_URL .
CALL METHOD IREF_DOCUMENT ->OPEN_DOCUMENT
EXPORTING
DOCUMENT_TITLE = 'Excel'
DOCUMENT_URL = V_DOCUMENT_URL
* no_flush = ' '
OPEN_INPLACE = 'X'
* open_readonly = ' '
* protect_document = ' '
* onsave_macro = ' '
* startup_macro = ''
* user_info =
IMPORTING
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'I' .
LEAVE LIST-PROCESSING .
ENDIF .
CALL METHOD IREF_DOCUMENT ->GET_SPREADSHEET_INTERFACE
EXPORTING
NO_FLUSH = ' '
IMPORTING
ERROR = IREF_ERROR
SHEET_INTERFACE = IREF_SPREADSHEET
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'I' .
LEAVE LIST-PROCESSING .
ENDIF .
CALL METHOD IREF_SPREADSHEET ->GET_SHEETS
EXPORTING
NO_FLUSH = ' '
* updating = -1
IMPORTING
SHEETS = I_SHEETS
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'I' .
LEAVE LIST-PROCESSING .
ENDIF .
LOOP AT I_SHEETS INTO WA_SHEETS .
CALL METHOD IREF_SPREADSHEET ->SELECT_SHEET
EXPORTING
NAME = WA_SHEETS -SHEET_NAME
* no_flush = ' '
IMPORTING
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
EXIT .
* call method iref_error->raise_message
* exporting
* type = 'E'.
ENDIF .
CALL METHOD IREF_SPREADSHEET ->SET_SELECTION
EXPORTING
TOP = 1
LEFT = 1
ROWS = P_ROWS
COLUMNS = P_COLS .
CALL METHOD IREF_SPREADSHEET ->INSERT_RANGE
EXPORTING
NAME = 'Test'
ROWS = P_ROWS
COLUMNS = P_COLS
NO_FLUSH = ''
IMPORTING
ERROR = IREF_ERROR .
IF IREF_ERROR ->HAS_FAILED = 'X' .
EXIT .
* call method iref_error->raise_message
* exporting
* type = 'E'.
ENDIF .
REFRESH I_DATA .
CALL METHOD IREF_SPREADSHEET ->GET_RANGES_DATA
EXPORTING
* no_flush = ' '
ALL = 'X'
* updating = -1
* rangesdef =
IMPORTING
CONTENTS = I_DATA
ERROR = IREF_ERROR
* retcode =
CHANGING
RANGES = I_RANGES .
* Remove ranges not to be processed else the data keeps on adding up
CALL METHOD IREF_SPREADSHEET ->DELETE_RANGES
EXPORTING
RANGES = I_RANGES .
DELETE I_DATA WHERE VALUE IS INITIAL OR VALUE = SPACE .
ULINE .
WRITE :/1 WA_SHEETS -SHEET_NAME COLOR 3 .
ULINE .
LOOP AT I_DATA INTO WA_DATA .
WRITE :(50 ) WA_DATA - VALUE .
AT END OF ROW .
NEW-LINE .
ENDAT .
ENDLOOP .
ENDLOOP .
CALL METHOD IREF_DOCUMENT ->CLOSE_DOCUMENT
* EXPORTING
* do_save = ' '
* no_flush = ' '
IMPORTING
ERROR = IREF_ERROR
* has_changed =
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'I' .
LEAVE LIST-PROCESSING .
ENDIF .
CALL METHOD IREF_DOCUMENT ->RELEASE_DOCUMENT
* EXPORTING
* no_flush = ' '
IMPORTING
ERROR = IREF_ERROR
* retcode =
.
IF IREF_ERROR ->HAS_FAILED = 'X' .
CALL METHOD IREF_ERROR ->RAISE_MESSAGE
EXPORTING
TYPE = 'I' .
LEAVE LIST-PROCESSING .
ENDIF .
*&---------------------------------------------------------------------*
*& Form SUB_FILE_F4
*&---------------------------------------------------------------------*
* F4 help for file path
*----------------------------------------------------------------------*
FORM SUB_FILE_F4 .
DATA :
L_DESKTOP TYPE STRING ,
L_I_FILES TYPE FILETABLE ,
L_WA_FILES TYPE FILE_TABLE ,
L_RCODE TYPE INT4 ,
L_ACTION TYPE I .
* Finding desktop
CALL METHOD CL_GUI_FRONTEND_SERVICES =>GET_DESKTOP_DIRECTORY
CHANGING
DESKTOP_DIRECTORY = L_DESKTOP
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4 .
IF SY -SUBRC <> 0 .
L_DESKTOP = 'C:\' .
ENDIF .
* Update View
CALL METHOD CL_GUI_CFW =>UPDATE_VIEW
EXCEPTIONS
CNTL_SYSTEM_ERROR = 1
CNTL_ERROR = 2
OTHERS = 3 .
CALL METHOD CL_GUI_FRONTEND_SERVICES =>FILE_OPEN_DIALOG
EXPORTING
WINDOW_TITLE = '文件选择'
DEFAULT_EXTENSION = '.xls'
* default_filename =
FILE_FILTER = 'Excel(*.xls;*.xlsx)|*.XLSX;*.XLS;'
* with_encoding =
INITIAL_DIRECTORY = L_DESKTOP
* multiselection =
CHANGING
FILE_TABLE = L_I_FILES
RC = L_RCODE
USER_ACTION = L_ACTION
* file_encoding =
EXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR = 2
ERROR_NO_GUI = 3
NOT_SUPPORTED_BY_GUI = 4
OTHERS = 5 .
IF L_ACTION = 0 AND SY -SUBRC = 0 .
READ TABLE L_I_FILES INDEX 1 INTO L_WA_FILES .
IF SY -SUBRC = 0 .
P_FILE = L_WA_FILES -FILENAME .
ENDIF .
ENDIF .
ENDFORM . " SUB_FILE_F4