Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Add Sheet at last in Excel Workbook

former_member266414
Member

‎11-11-2018 3:07 PM

0 Kudos

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.
0 REPLIES 0