topic Re: Download to Excel using METHODS in Application Development and Automation Discussions

Download to Excel using METHODS

Former Member — Tue, 07 Nov 2006 08:25:53 GMT

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

Re: Download to Excel using METHODS

anversha_s — Tue, 07 Nov 2006 08:27:21 GMT

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