11-11-2018 3:07 PM
I have written a code to divide an internal table to multiple excel sheet in a single excel file, the below code is working fine but its adds the sheet to the first position.
I want newly added sheet to be at last position.
EXPORT z_excel_download.
*** Include objects
INCLUDE: ole2incl.
*** Table definitions
TABLES: mara.
*** Constants
CONSTANTS: max_lines(3) TYPE n VALUE 225.
*** Type declarations
TYPES: BEGIN OF gty_excel1,
matnr TYPE matnr,
ersda TYPE ersda,
ernam TYPE ernam.
TYPES: END OF gty_excel1.
TYPES: data1(1500) TYPE c,
ty_data TYPE TABLE OF data1.
*** Variable Declarations
* Declare cells
DATA: w_cell1 TYPE ole2_object,
w_cell2 TYPE ole2_object.
* Ole data Declarations
DATA: h_excel TYPE ole2_object, " Excel object
h_mapl TYPE ole2_object, " list of workbooks
h_map TYPE ole2_object, " workbook
h_zl TYPE ole2_object, " cell
h_f TYPE ole2_object, " font
h_row TYPE ole2_object, " row
gs_interior TYPE ole2_object, " Pattern
worksheet TYPE ole2_object,
h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object,
range TYPE ole2_object,
h_sheet1 TYPE ole2_object,
gs_font TYPE ole2_object,
flg_stop(1) TYPE c.
*** Work area and internal table definitions
DATA: gt_excel1 TYPE STANDARD TABLE OF gty_excel1,
wa_excel1 TYPE gty_excel1.
*** Variables
DATA: gt_1 TYPE ty_data WITH HEADER LINE,
gt_2 TYPE ty_data WITH HEADER LINE,
deli(1) TYPE c,
gv_sheet_name(20) TYPE c,
lv_sheet(20) TYPE c,
lv_lines TYPE i,
lv_low TYPE i,
lv_high TYPE i,
lv_times TYPE i,
lv_ind TYPE sy-index,
lv_selected_folder TYPE string.
*&---------------------------------------------------------------------*
*& Include Z_EXCEL_DOWNLOAD_SEL
*&---------------------------------------------------------------------*
SELECT-OPTIONS: s_matnr FOR mara-matnr.
START-OF-SELECTION.
PERFORM process_data.
*&---------------------------------------------------------------------*
*& Form PROCESS_DATA
*&---------------------------------------------------------------------*
FORM process_data .
DATA l_rc TYPE i.
deli = cl_abap_char_utilities=>horizontal_tab.
*** Popoluate tables
SELECT matnr ersda ernam INTO TABLE gt_excel1
FROM mara
WHERE matnr IN s_matnr.
LOOP AT gt_excel1 INTO wa_excel1.
CONCATENATE wa_excel1-matnr wa_excel1-ersda wa_excel1-ernam INTO gt_1 SEPARATED BY deli.
APPEND gt_1.
CLEAR gt_1.
ENDLOOP.
CALL METHOD cl_gui_frontend_services=>directory_browse
EXPORTING
window_title = 'Save Data'
initial_folder = `C:\`
CHANGING
selected_folder = lv_selected_folder
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
OTHERS = 3.
CHECK NOT lv_selected_folder IS INITIAL.
* start Excel
IF h_excel-header = space OR h_excel-handle = -1.
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
ENDIF.
*--- get list of workbooks, initially empty
CALL METHOD OF h_excel 'Workbooks' = h_mapl.
SET PROPERTY OF h_excel 'Visible' = 0.
CALL METHOD OF h_mapl 'Add' = h_map.
DESCRIBE TABLE gt_1[] LINES lv_lines.
lv_times = lv_lines / max_lines.
lv_times = lv_times.
lv_low = 1.
lv_high = max_lines.
DO lv_times TIMES.
APPEND LINES OF gt_1 FROM lv_low TO lv_high TO gt_2.
PERFORM f_set_header TABLES gt_2.
lv_ind = sy-index.
MOVE sy-index TO lv_sheet.
CONDENSE lv_sheet.
CONCATENATE 'Sheet' lv_sheet INTO lv_sheet.
CONDENSE lv_sheet.
PERFORM create_sheet TABLES gt_2
USING lv_sheet h_sheet1 lv_ind.
lv_low = lv_high + 1.
lv_high = lv_high + max_lines.
IF lv_high > lv_lines.
lv_high = lv_lines.
ENDIF.
CLEAR gt_2[].
ENDDO.
CONCATENATE lv_selected_folder '\Test' INTO lv_selected_folder.
CALL METHOD OF h_map 'SaveAs'
EXPORTING
#1 = lv_selected_folder.
IF sy-subrc EQ 0.
MESSAGE 'File downloaded successfully' TYPE 'S'.
ELSE.
MESSAGE 'Error downloading the file' TYPE 'E'.
ENDIF.
CALL METHOD OF h_excel 'QUIT'.
* Free Excel objects
FREE OBJECT: h_zl,
h_mapl,
h_map,
h_excel.
ENDFORM. " PROCESS_DATA
*&---------------------------------------------------------------------*
*& Form CREATE_SHEET
*&---------------------------------------------------------------------*
FORM create_sheet TABLES it_sheet TYPE ty_data
USING iv_name
iv_sheet TYPE ole2_object
lv_ind.
DATA l_rc TYPE i.
IF lv_ind EQ 1.
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
SET PROPERTY OF worksheet 'Name' = iv_name .
ELSE.
GET PROPERTY OF h_excel 'Sheets' = iv_sheet .
CALL METHOD OF iv_sheet 'Add' = h_map.
SET PROPERTY OF h_map 'Name' = iv_name .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
ENDIF.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = it_sheet[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
CALL METHOD OF h_excel 'Cells' = w_cell1
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Range' = range
EXPORTING
#1 = w_cell1
#2 = w_cell2.
CALL METHOD OF range 'Select'.
CALL METHOD OF worksheet 'Paste'.
CALL METHOD OF worksheet 'Rows' = h_row
EXPORTING
#1 = 1.
CALL METHOD OF h_row 'FONT' = h_f.
SET PROPERTY OF h_f 'Bold' = 1.
ENDFORM. " CREATE_SHEET
FORM f_set_header TABLES it_tab TYPE ty_data.
DATA: wa_tab TYPE data1.
CONCATENATE 'Col1' 'Col2' 'Col3' INTO wa_tab SEPARATED BY deli.
INSERT wa_tab INTO it_tab INDEX 1.
ENDFORM.