Download to Excel using METHODS

Former Member · ‎2006 Nov 07

I want to change the Worksheet name which in default being created as Sheet1/ Sheet2/ Sheet3 on creation of Excel file while downloading using Objects.

How should I change the name of Sheet1/ Sheet2/ Sheet3 to my requirement. Attached the coding part. Please help me out in finding the place where I should rename it using which statement.

FORM excel_download.

gv_step = 'Starting Excel'.

CREATE OBJECT gw_excel 'EXCEL.APPLICATION'.

PERFORM xls_error_hdl.

SET PROPERTY OF gw_excel 'VISIBLE' = 0.

GET PROPERTY OF gw_excel 'WORKBOOKS' = gw_wbooks.

CALL METHOD OF gw_wbooks 'Add'.

*Create Excel Sheet

CALL METHOD OF gw_excel 'Worksheets' = gw_worksheet

EXPORTING

#1 = 1.

CALL METHOD OF gw_wbooks 'Activate'.

SET PROPERTY OF gw_wbooks 'Name' = 'Sheet1'.

CALL METHOD OF gw_wbooks 'OPEN' = gw_wbooks

EXPORTING

#1 = gv_fname.

CALL METHOD OF gw_wbooks 'Activate' .

*Download to Worksheet 1.

GET PROPERTY OF gw_excel 'Worksheets' = gw_worksheet

exporting #1 = 'Sheet1'. "S4M1MR

exporting #1 = 'Appd_Sites'. "S4M1MR

PERFORM xls_error_hdl.

CALL METHOD OF gw_worksheet 'Activate' .

gv_step = 'Adding data to Excel'.

gv_row = 0.

LOOP AT gi_yps_down_tab1 INTO gw_yps_grid2.

CLEAR gw_yps_common-anln1.

gv_row = gv_row + 1.

gv_col = 2.

DO 4 TIMES.

ASSIGN COMPONENT sy-index OF STRUCTURE gw_yps_grid2 TO <comp>.

PERFORM fill_cell USING gv_row gv_col <comp>.

gv_col = gv_col + 1.

ENDDO.

ENDLOOP.

*Download to Worksheet 2.

GET PROPERTY OF gw_excel 'Worksheets' = gw_worksheet

exporting #1 = 'Sheet2'.

exporting #1 = 'Appd Sites'. "S4M1MR

PERFORM xls_error_hdl.

CALL METHOD OF gw_worksheet 'Activate' .

gv_step = 'Adding data to Excel'.

gv_row = 0.

LOOP AT gi_yps_down_tab2 INTO gw_yps_common.

LOOP AT gi_yps_down_tab2 INTO gw_exclusion.

PERFORM conversion.

ENDLOOP.

*Download to Worksheet 3.

GET PROPERTY OF gw_excel 'Worksheets' = gw_worksheet

exporting #1 = 'Sheet3'.

PERFORM xls_error_hdl.

CALL METHOD OF gw_worksheet 'Activate' .

gv_step = 'Adding data to Excel'.

gv_row = 0.

LOOP AT gi_yps_down_tab3 INTO gw_yps_common.

LOOP AT gi_yps_down_tab3 INTO gw_exclusion.

PERFORM conversion.

ENDLOOP.

*Download to Worksheet 4.

CALL METHOD OF gw_excel 'Sheets' = gw_worksheet.

Add new workbook (create a file)

CALL METHOD OF gw_worksheet 'Add'.

FREE OBJECT gw_worksheet.

GET PROPERTY OF gw_excel 'Worksheets' = gw_worksheet

exporting #1 = 'Sheet4'.

PERFORM xls_error_hdl.

CALL METHOD OF gw_worksheet 'Activate' .

gv_step = 'Adding data to Excel'.

gv_row = 0.

LOOP AT gi_yps_down_tab4 INTO gw_yps_common.

LOOP AT gi_yps_down_tab4 INTO gw_exclusion.

PERFORM conversion.

ENDLOOP.

To Save the Book after downloading.

PERFORM save_excel.

ENDFORM. " EXCEL_DOWNLOAD

-

Thanks in advance

anversha_s · ‎2006 Nov 07

hi,

chk a sample pgm.

*&---------------------------------------------------------------------*
*&  Include           YHCON9051INCL                                    *
*&---------------------------------------------------------------------*

*---------------------------------------------------------------------*
*   SUBROUTINES                                                       *
*---------------------------------------------------------------------*

*&---------------------------------------------------------------------*
*&      Form  F_CHECK_LOCAL_FILE_EXIST
*&---------------------------------------------------------------------*
*       CHECK WETHER LOCAL FILE EXIST
*----------------------------------------------------------------------*
*      -->P_FILE LOCAL FILE NAME
*----------------------------------------------------------------------*
FORM f_check_local_file_exist  USING    p_file.

  DATA: loc_filename TYPE string,
        loc_flag     TYPE abap_bool.


  loc_filename = p_file.

  IF loc_filename Eq ' '.
    MESSAGE e053(8i) .
  ENDIF.

  CALL METHOD cl_gui_frontend_services=>file_exist
    EXPORTING
      file                 = loc_filename
    RECEIVING
      result               = loc_flag
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      wrong_parameter      = 3
      not_supported_by_gui = 4
      OTHERS               = 5.

  IF sy-subrc NE wl_yes.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

  IF loc_flag NE wl_true.
    MESSAGE e000(8i) WITH text-020 p_file.
  ENDIF.


ENDFORM.                    " F_CHECK_LOCAL_FILE_EXIST
*&---------------------------------------------------------------------*
*&      Form  F_UPLOAD_DATA_FROM_LOCAL_FILE
*&---------------------------------------------------------------------*
*       GET DATA FROM EXCEL FILE AND CONVERT TO SAP FORMAT
*----------------------------------------------------------------------*
*      -->P_FILE LOCAL FILE NAME
*----------------------------------------------------------------------*
FORM f_upload_data_from_local_file  USING    p_file.

  DATA: loc_filename TYPE rlgrap-filename,
        int_raw      TYPE truxs_t_text_data.

  loc_filename = p_file.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_field_seperator    = 'X'
      i_line_header        = 'X'
      i_tab_raw_data       = int_raw
      i_filename           = loc_filename
    TABLES
      i_tab_converted_data = int_content_lfile
    EXCEPTIONS
      conversion_failed    = 1
      OTHERS               = 2.

  IF sy-subrc NE wl_yes.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
         WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.


  CLEAR : int_record , int_content_lfile ,int_record[].

  LOOP AT int_content_lfile.
* Moving the records to the Internal table for process
    int_record-adm_area = int_content_lfile-adm_area .
    int_record-dsrd_loc = int_content_lfile-dsrd_loc.
    int_record-fl_ind   = int_content_lfile-fl_ind.
    int_record-loc_loc  = int_content_lfile-loc_loc.
    int_record-loc_desc = int_content_lfile-loc_desc .
    APPEND int_record.
    CLEAR  int_record.
  ENDLOOP.

ENDFORM.                    " F_UPLOAD_DATA_FROM_LOCAL_FILE
*&---------------------------------------------------------------------*
*&      Form  GET_FILE_OPEN_DIALOG
*&---------------------------------------------------------------------*
*      F4 TO FETCH THE FILE
*----------------------------------------------------------------------*
FORM get_file_open_dialog .

  DATA: lint_filetable TYPE filetable,
        ls_filetable   TYPE file_table,
        li_count TYPE i.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
       window_title            = 'Please select the file'
       default_extension       = '*.XLS'
*      DEFAULT_FILENAME        =
*      FILE_FILTER             =
*      INITIAL_DIRECTORY       =
       multiselection          = ''
*      WITH_ENCODING           =
     CHANGING
       file_table              = lint_filetable
       rc                      = li_count
*      USER_ACTION             =
*      FILE_ENCODING           =
     EXCEPTIONS
       file_open_dialog_failed = 1
       cntl_error              = 2
       error_no_gui            = 3
       not_supported_by_gui    = 4
       OTHERS                  = 5.
  IF sy-subrc <> 0.

  ELSE.
    READ TABLE lint_filetable INDEX 1 INTO ls_filetable.
    IF sy-subrc EQ wl_yes.
      p_lcl = ls_filetable-filename.
    ENDIF.
  ENDIF.

ENDFORM.                    " GET_FILE_OPEN_DIALOG

*&---------------------------------------------------------------------*
*&      Form  F_UPLOAD_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM f_upload_data .

* To move the data to Internal table fields.
  PERFORM data_transfer.

ENDFORM.                    " F_UPLOAD_DATA
*&---------------------------------------------------------------------*
*&      Form  F_UPDATE_TABLE
*&---------------------------------------------------------------------*
*       ZTABLE UPDATION
*----------------------------------------------------------------------*
FORM f_update_table .

  DATA:   wl_sess          TYPE c,
          loc_wf_loc_desc  TYPE  zhofs_loc_desc.

  REFRESH : int_caltrscc, int_caltrerr .

  LOOP AT int_record.
** Moving the data as desired for the Z -table updation
*  PERFORM MOVE_DATA.
    MOVE :
       int_record-adm_area        TO   loc_wf_loc_desc-adm_area  ,
       int_record-dsrd_loc        TO   loc_wf_loc_desc-dsrd_loc ,
       int_record-fl_ind          TO   loc_wf_loc_desc-fl_ind    ,
       int_record-loc_loc         TO   loc_wf_loc_desc-loc_loc   ,
       int_record-loc_desc        TO   loc_wf_loc_desc-loc_desc .

* Inserting the data to the Table ZHOFS_LOT_TABLE

    INSERT zhofs_loc_desc FROM loc_wf_loc_desc .

    IF sy-subrc EQ wl_yes.
*     Entry Inserted Correctly
      wf_suc = wf_suc + 1.
      int_caltrscc-adm_area   =  int_record-adm_area .
      int_caltrscc-dsrd_loc   =  int_record-dsrd_loc.
      int_caltrscc-fl_ind     =  int_record-fl_ind.
      int_caltrscc-loc_loc    =  int_record-loc_loc.
      int_caltrscc-reason  =  text-007.
      APPEND int_caltrscc.
      CLEAR  int_caltrscc.

    ELSE.
*     Entry Not Inserted
      wf_err = wf_err + 1.
      int_caltrerr-adm_area   =  int_record-adm_area .
      int_caltrerr-dsrd_loc   =  int_record-dsrd_loc.
      int_caltrerr-fl_ind     =  int_record-fl_ind.
      int_caltrerr-loc_loc    =  int_record-loc_loc.
      int_caltrerr-reason  =  text-006.
      APPEND int_caltrerr.
      CLEAR  int_caltrerr.
    ENDIF.

    CLEAR :  loc_wf_loc_desc-loc_desc,int_record .
  ENDLOOP.

ENDFORM.                    " F_UPDATE_TABLE

*&---------------------------------------------------------------------*
*&      Form  DATA_TRANSFER
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM data_transfer .

  DATA : loc_int_loc_desc TYPE TABLE OF zhofs_loc_desc WITH HEADER LINE.
  CLEAR: int_record.

  LOOP AT int_record.

    wf_rec =  wf_rec + 1.
*  To Check Whether an already any records exists
    SELECT *
    FROM zhofs_loc_desc
    INTO TABLE loc_int_loc_desc
    WHERE
      adm_area = int_record-adm_area AND
      dsrd_loc  = int_record-dsrd_loc AND
      loc_loc = int_record-loc_loc.

    IF sy-subrc EQ wl_yes.
*     The entry already exist in the Table
      int_error-adm_area   =  int_record-adm_area.
      int_error-dsrd_loc   =  int_record-dsrd_loc.
      int_error-fl_ind      = int_record-fl_ind .
      int_error-loc_loc  = int_record-loc_loc.
      int_error-message =  text-005.
      APPEND int_error.
      CLEAR : int_error.
      wf_err_rec = wf_err_rec + 1.
      CONTINUE.
    ELSE.
      wf_cor_rec = wf_cor_rec + 1 .
    ENDIF.
    CLEAR: int_record.
  ENDLOOP.

  CLEAR: int_record .

  LOOP AT int_error.

    READ TABLE int_record
    WITH KEY
         adm_area = int_error-adm_area
         dsrd_loc = int_error-dsrd_loc
         loc_loc  = int_error-loc_loc .

    IF sy-subrc EQ wl_yes.
*     Delete the duplicate entries
      DELETE int_record
      WHERE
       adm_area = int_error-adm_area AND
       dsrd_loc    = int_error-dsrd_loc AND
       fl_ind = int_error-fl_ind AND
       loc_loc = int_error-loc_loc .
    ENDIF.

    CLEAR int_error.
  ENDLOOP.

ENDFORM.                    " DATA_TRANSFER
*&---------------------------------------------------------------------*
*&      Form  f_check_data
*&---------------------------------------------------------------------*
*       Check DATA
*----------------------------------------------------------------------*
FORM f_check_data .
*ERROR FULL DATA
  IF wf_err_rec GT 0.
    WRITE:/060(1)   sy-vline, text-000 COLOR 6,sy-vline.
    LOOP AT int_error.
      FORMAT RESET.
      FORMAT COLOR  6 .
      WRITE:/001(1)   sy-vline,
             002(3)   int_error-adm_area ,
             005(1)   sy-vline  CENTERED ,
             006(4)   int_error-dsrd_loc ,
             010(1)   sy-vline  CENTERED ,
             011(1)    int_error-fl_ind  ,
             012(1)   sy-vline  CENTERED,
             013(4)   int_error-loc_loc  ,
             017(1)   sy-vline  CENTERED ,
             018(25)  int_error-message  ,
             043(1)   sy-vline  CENTERED .
      FORMAT COLOR OFF .
    ENDLOOP.
    WRITE:/001(25)  text-002,wf_err_rec.
    WRITE:/001(25)  text-003,wf_rec .
  ENDIF.
*SUCCESSFULL DATA
  IF wf_cor_rec GT 0.
    WRITE:/060(1)   sy-vline, text-001 COLOR 1 ,sy-vline.
    LOOP AT int_record.
      FORMAT RESET.
      FORMAT COLOR 1 ON .
      WRITE:/001(1)   sy-vline,
             002(3)   int_record-adm_area ,
             005(1)   sy-vline  CENTERED ,
             006(4)   int_record-dsrd_loc ,
             010(1)   sy-vline  CENTERED ,
             011(1)   int_record-fl_ind  ,
             012(1)   sy-vline  CENTERED,
             013(4)   int_record-loc_loc  ,
             017(1)   sy-vline  CENTERED ,
             018(25)  'PROPER ENTRY' ,
            043(1)   sy-vline  CENTERED .
      FORMAT COLOR OFF .
    ENDLOOP.
    WRITE:/001(25)  text-002,wf_cor_rec .
    WRITE:/001(25)  text-003,wf_rec .

  ENDIF.
ENDFORM.                    " f_check_data
*&---------------------------------------------------------------------*
*&      Form  f_check_db_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM f_check_db_data .
*ERROR FULL DATA - WHILE INSERTING TO TABLE
  IF wf_err GT 0.
    WRITE:/060(1)   sy-vline,text-004 COLOR 6,sy-vline.
    LOOP AT  int_caltrerr.
      FORMAT RESET.
      FORMAT COLOR  6 .
      WRITE:/001(1)   sy-vline,
             002(3)    int_caltrerr-adm_area ,
             005(1)   sy-vline  CENTERED ,
             006(4)    int_caltrerr-dsrd_loc ,
             010(1)   sy-vline  CENTERED ,
             011(1)    int_caltrerr-fl_ind  ,
             012(1)   sy-vline  CENTERED,
             013(4)    int_caltrerr-loc_loc  ,
             017(1)   sy-vline  CENTERED ,
             018(25)   int_caltrerr-reason  ,
             043(1)   sy-vline  CENTERED .
      FORMAT COLOR OFF .
    ENDLOOP.
    WRITE:/001(25)  text-002,wf_err.
    WRITE:/001(25)  text-003,wf_rec .
  ENDIF.

*    ERROR BY DUPLICATION
  IF wf_err_rec GT 0.
    WRITE:/060(1)   sy-vline,text-000 COLOR 6,sy-vline.
    LOOP AT int_error.
      FORMAT RESET.
      FORMAT COLOR  6 .
      WRITE:/001(1)   sy-vline,
             002(3)   int_error-adm_area ,
             005(1)   sy-vline  CENTERED ,
             006(4)   int_error-dsrd_loc ,
             010(1)   sy-vline  CENTERED ,
             011(1)    int_error-fl_ind  ,
             012(1)   sy-vline  CENTERED,
             013(4)   int_error-loc_loc  ,
             017(1)   sy-vline  CENTERED ,
             018(25)  int_error-message  ,
             043(1)   sy-vline  CENTERED .
      FORMAT COLOR OFF .
    ENDLOOP.
    WRITE:/001(25)  text-002,wf_err_rec.
    WRITE:/001(25)  text-003,wf_rec .
  ENDIF.

*SUCCESSFULL DATA
  IF wf_suc GT 0.
    WRITE:/060(1)   sy-vline,text-001 COLOR 1 ,sy-vline.
    LOOP AT int_caltrscc.
      FORMAT RESET.
      FORMAT COLOR 1 ON .
      WRITE:/001(1)   sy-vline,
             002(3)   int_caltrscc-adm_area ,
             005(1)   sy-vline  CENTERED ,
             006(4)  int_caltrscc-dsrd_loc ,
             010(1)   sy-vline  CENTERED ,
             011(1)   int_caltrscc-fl_ind  ,
             012(1)   sy-vline  CENTERED,
             013(4)   int_caltrscc-loc_loc  ,
             017(1)   sy-vline  CENTERED ,
             018(25)  int_caltrscc-reason ,
            043(1)   sy-vline  CENTERED .
      FORMAT COLOR OFF .
    ENDLOOP.
    WRITE:/001(25)  text-002,wf_cor_rec .
    WRITE:/001(25)  text-003,wf_rec .
  ENDIF.

ENDFORM.                    " f_check_db_data

rgds

Anver

if hlped pls mark points

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My Account

My Account

Download to Excel using METHODS