Excel Sheet Creation from ABAP

TYPE-POOLS ole2.
DATA: wf_cell_from  TYPE ole2_object,
      wf_cell_from1 TYPE ole2_object,
      wf_cell_to    TYPE ole2_object,
      wf_cell_to1   TYPE ole2_object,
      wf_excel      TYPE ole2_object,   " Excel object
      wf_mapl       TYPE ole2_object,   " list of workbooks
      wf_map        TYPE ole2_object,   " workbook
      wf_worksheet  TYPE ole2_object,   " Worksheet
      wf_cell       TYPE ole2_object,   " Cell Range
      wf_cell1      TYPE ole2_object,
      wf_range      TYPE ole2_object,   " Range of cells to be formatted
      wf_range2     TYPE ole2_object,
      wf_column1    TYPE ole2_object.   " Column to be Autofit
 
 
DATA: BEGIN OF t_hex,
      l_tab TYPE x,
      END OF t_hex.
 
DATA: wf_deli(1) TYPE c,            "delimiter
      wf_action TYPE i,
      wf_file TYPE string,
      wf_path TYPE string,
      wf_fullpath TYPE string.
 
 
 
TYPES: t_data1(1500) TYPE c,
       int_ty TYPE TABLE OF t_data1. "line type internal table
 
*All the data was prepared as line type internal tables for faster
*download
 
DATA: int_matl  TYPE int_ty ,
      int_matl1 TYPE int_ty ,
      wa_matl   TYPE t_data1.
 
TYPES: BEGIN OF ty_mara,
       matnr TYPE matnr,
       mtart TYPE mtart,
       matkl TYPE matkl,
       meins TYPE meins,
       END OF ty_mara.
 
DATA: int_mara TYPE STANDARD TABLE OF ty_mara,
      wa_mara TYPE ty_mara.
 
FIELD-SYMBOLS: <fs> .
 
DATA: wc_sheets LIKE sy-index.  "no.of sheets
DATA: it_tabemp TYPE filetable,
      gd_subrcemp TYPE i.
 
CONSTANTS wl_c09(2) TYPE n VALUE 09.
 
CLEAR wc_sheets.
 
 
DEFINE ole_check_error.
  if &1 ne 0.
    message e001(zz) with &1.
    exit.
  endif.
END-OF-DEFINITION.
 
SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file   LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.
 
 
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
 
  REFRESH: it_tabemp.
 
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = 'Select File'
*      default_extension    = 'xls'
      default_file_name    = 'Material Details'
*      with_encoding        =
      file_filter          = '*.xls'
      initial_directory    = 'C:\'
      prompt_on_overwrite  = ' '
    CHANGING
      filename             = wf_file
      path                 = wf_path
      fullpath             = wf_fullpath
      user_action          = wf_action
*      file_encoding        =
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4
          .
  IF sy-subrc <> 0.
*   MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*              WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.
 
  IF wf_action EQ 9.
    MESSAGE 'No File have been Selected' TYPE 'S'.
  ELSE.
    p_file = wf_fullpath.
    PERFORM create_excel.
  ENDIF.
 
*&---------------------------------------------------------------------*
*&      Form  create_excel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM create_excel.
  LOOP AT it_tabemp INTO p_file.
  ENDLOOP.
* START THE EXCEL APPLICATION
  CREATE OBJECT wf_excel 'EXCEL.APPLICATION'.
  PERFORM err_hdl.
 
* PUT EXCEL IN FRONT
  SET PROPERTY OF wf_excel  'VISIBLE' = 1.
  PERFORM err_hdl.
 
* CREATE AN EXCEL WORKBOOK OBJECT
  CALL METHOD OF wf_excel 'WORKBOOKS' = wf_mapl.
  PERFORM err_hdl.
 
  SET PROPERTY OF wf_excel 'SheetsInNewWorkbook' = 3. "no of sheets
  PERFORM err_hdl.
 
  CALL METHOD OF wf_mapl 'ADD' = wf_map.
  PERFORM err_hdl.
 
 
*Assign the Delimiter to field  symbol.
  ASSIGN wf_deli TO <fs> TYPE 'X'.
  t_hex-l_tab = wl_c09.
  <fs> = t_hex-l_tab.
 
  CLEAR int_matl.
  REFRESH int_matl.
  SELECT matnr
       mtart
       matkl
       meins
      FROM mara
      INTO CORRESPONDING FIELDS OF TABLE int_mara.
*first the headings will be displayed  in the excel sheet
  CONCATENATE 'Material Number'
  'Material type'
  'Material Group'
  'Base Unit of Measure'
  INTO wa_matl
  SEPARATED BY wf_deli.
  APPEND wa_matl TO int_matl.
 
 
  LOOP AT int_mara INTO wa_mara.
    CONCATENATE wa_mara-matnr
                wa_mara-mtart
                wa_mara-matkl
                wa_mara-meins
                INTO wa_matl
                SEPARATED BY wf_deli.
    APPEND wa_matl TO int_matl.
    CLEAR wa_matl.
  ENDLOOP.
 
*Copyng thae same contents to another table to display in
*new sheet
  MOVE int_matl TO int_matl1.
  PERFORM f_material_details
  TABLES int_matl
  USING  1.
 
  PERFORM f_material_details
  TABLES int_matl
  USING  2.
 
 
  GET PROPERTY OF wf_excel 'ActiveSheet' = wf_map.
  GET PROPERTY OF wf_excel 'ActiveWorkbook' = wf_mapl.
 
  CALL FUNCTION 'FLUSH'
    EXCEPTIONS
      cntl_system_error = 1
      cntl_error        = 2
      OTHERS            = 3.
  IF sy-subrc = 0.
 
    CALL METHOD OF wf_map 'SAVEAS'
      EXPORTING #1 = p_file.
  ENDIF.
 
  CALL METHOD OF wf_mapl 'CLOSE'.
  CALL METHOD OF wf_excel 'QUIT'.
 
  FREE OBJECT wf_mapl.
  FREE OBJECT wf_map.
  FREE OBJECT wf_excel.
 
ENDFORM.                    "create_excel
*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM err_hdl.
 
  IF sy-subrc <> 0.
    WRITE: / 'OLE ERROR: RETURN CODE ='(i10), sy-subrc.
    STOP.
  ENDIF.
 
ENDFORM.                    "ERR_HDL
 
*-- End of Program
*&---------------------------------------------------------------------*
*&      Form  f_material_details
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM f_material_details
   TABLES lint_matl
  USING l_sheet_no TYPE i.
 
  DATA: lv_lines TYPE i,
        lv_sheet_name(50) TYPE c.
 
  wc_sheets = l_sheet_no.
  CASE l_sheet_no.
    WHEN 1.
      lv_sheet_name = 'Material_sheet1'.
    WHEN 2.
      lv_sheet_name = 'Material_sheet2'.
  ENDCASE.
 
 
*-- activating the worksheet and giving a  name to it
  CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet
    EXPORTING
    #1 = wc_sheets.
  CALL METHOD OF wf_worksheet 'ACTIVATE'.
 
 
 
  SET PROPERTY OF wf_worksheet 'NAME' = lv_sheet_name.
 
*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to
    EXPORTING
    #1 = lv_lines
    #2 = 4.
*--range of cells to be formatted (in this case 1 to 4)
  CALL METHOD OF wf_excel 'Range' = wf_cell
    EXPORTING
    #1 = wf_cell_from
    #2 = wf_cell_to.
 
*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from1
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to1
    EXPORTING
    #1 = lv_lines
    #2 = 1.
  CALL METHOD OF wf_excel 'Range' = wf_cell1  " Cell range for first
 
                                              " column(Material)
    EXPORTING
    #1 = wf_cell_from1
    #2 = wf_cell_to1.
 
  SET PROPERTY OF wf_cell1 'NumberFormat' = '@' . "To disply zeros
  "in Material number
 
 
  DATA l_rc TYPE i.
*DATA download into excel first sheet
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data         = lint_matl[]
    CHANGING
      rc           = l_rc
    EXCEPTIONS
      cntl_error   = 1
      error_no_gui = 2
      OTHERS       = 4.
  CALL METHOD OF wf_worksheet 'Paste'.
 
  CALL METHOD OF wf_excel 'Columns' = wf_column1.
  CALL METHOD OF wf_column1 'Autofit'.
  FREE OBJECT wf_column1.
 
ENDFORM.                    " f_material_details

Reference link:

https://archive.sap.com/discussions/thread/1260102

https://blogs.sap.com/2012/03/29/using-ole2-objects-for-create-an-excel-file/

Parallel Processing using SPTA framework

Using SPTA framework, the parallel processing can be achieved in a more effective & efficient way as everything is handled by the standards. Performance wise also it is more optimized.

CONSTANTS: lc_e TYPE symsgty VALUE 'E'.

  IF gt_op_file[] IS INITIAL.
    RETURN.
  ENDIF.

  "Fetch the server group
  SELECT SINGLE classname
  INTO @DATA(lv_server_grp)
  FROM rzllitab
  WHERE grouptype = 'S'.
  IF sy-subrc NE 0.
    RETURN.
  ENDIF.


*---------------------------------------------------------------------*
*                P A R A L L E L   P R O C E S S I N G
*---------------------------------------------------------------------*
  "to invoke parallel processing framwork
  IF gv_parallel_process EQ abap_true.
    CALL FUNCTION 'SPTA_PARA_PROCESS_START_2'
      EXPORTING
        server_group             = lv_server_grp
        before_rfc_callback_form = 'BEFORE_RFC'
        in_rfc_callback_form     = 'IN_RFC'
        after_rfc_callback_form  = 'AFTER_RFC'
        callback_prog            = sy-repid
      CHANGING
        user_param               = gt_op_file
      EXCEPTIONS
        invalid_server_group     = 1
        no_resources_available   = 2
        OTHERS                   = 3.
    IF sy-subrc NE 0.
      "Populate the log
      PERFORM fill_msg_log USING lc_e
                                 TEXT-e01
                                 space space space
                        CHANGING gt_msg_log.
    ENDIF.
  ELSE.
    "Post the data
    PERFORM post_data CHANGING gt_op_file.
  ENDIF.

*&---------------------------------------------------------------------*
*& Form BEFORE_RFC
*&---------------------------------------------------------------------*
*& Packets are prepared for parallel processing
*&---------------------------------------------------------------------*
FORM before_rfc USING ps_before_rfc_imp     TYPE spta_t_before_rfc_imp
             CHANGING cs_before_rfc_exp     TYPE spta_t_before_rfc_exp
                      ct_rfcdata            TYPE spta_t_indxtab
                      ct_failed_objects     TYPE spta_t_failed_objects
                      ct_objects_in_process TYPE spta_t_objects_in_process
                      ct_file_data          TYPE gtt_op_file.

  TYPES: lty_op_file TYPE TABLE OF gty_op_file WITH EMPTY KEY.
  DATA: lv_counter        TYPE i,
        lv_pck_size       TYPE i VALUE 5000, "One work packet size
        lt_final          TYPE gtt_op_file,
        lv_current_tranno TYPE string.

  IF ct_file_data[] IS INITIAL.
    CLEAR: cs_before_rfc_exp-start_rfc.
    RETURN.
  ENDIF.

** Create small work packets for parallel processing
  SORT ct_file_data BY tranno.
  LOOP AT ct_file_data ASSIGNING FIELD-SYMBOL(<lfs_file_data>).
    IF lv_current_tranno NE <lfs_file_data>-tranno.
      lv_current_tranno = <lfs_file_data>-tranno.
      "count the total records against particular transactions
      DATA(lv_rec_count) = REDUCE i( INIT x = 0 FOR ls_file_data IN ct_file_data
                                  WHERE ( tranno EQ <lfs_file_data>-tranno )
                                  NEXT x = x + 1 ).

      "if the total number of records can be accomodated in the current batch then accomodate it
      IF ( lv_rec_count + lines( lt_final ) ) LE lv_pck_size.
        APPEND <lfs_file_data> TO lt_final.

        "If the record count for particular transactions is more than the pack size
        "then assign the whole lot in a single batch
      ELSEIF lv_rec_count GT lv_pck_size.
        lt_final = VALUE lty_op_file( FOR ls_file_data2 IN ct_file_data
                                      WHERE ( tranno EQ <lfs_file_data>-tranno )
                                          ( ls_file_data2 ) ).
      ENDIF.
    ELSE.
      APPEND <lfs_file_data> TO lt_final.
    ENDIF.
  ENDLOOP.

  "Delete the records which has been grouped
  DELETE ct_file_data FROM 1 TO lv_pck_size.

* Convert the input data into the INDX structure that is needed for the RFC
  CALL FUNCTION 'SPTA_INDX_PACKAGE_ENCODE'
    EXPORTING
      data    = lt_final
    IMPORTING
      indxtab = ct_rfcdata.

* informing task manager that an rfc can be started from the data compiled
  IF lt_final IS NOT INITIAL.
    cs_before_rfc_exp-start_rfc = abap_true.
  ELSE.
    CLEAR cs_before_rfc_exp-start_rfc.
    RETURN.
  ENDIF.

ENDFORM.                    "BEFORE_RFC

*&---------------------------------------------------------------------*
*&      Form  IN_RFC
*&---------------------------------------------------------------------*
*  create own RFC enabled FM or write logic in this routine
*----------------------------------------------------------------------*
FORM in_rfc USING ps_in_rfc_imp TYPE spta_t_in_rfc_imp
         CHANGING cs_in_rfc_exp TYPE spta_t_in_rfc_exp
                  ct_rfcdata    TYPE spta_t_indxtab.


  DATA: lt_final TYPE gtt_op_file.

* Decode the data from the INDX Structure into the process work list
  CALL FUNCTION 'SPTA_INDX_PACKAGE_DECODE'
    EXPORTING
      indxtab = ct_rfcdata
    IMPORTING
      data    = lt_final.

* Simple logic to sort data
  SORT lt_final BY tranno.
  PERFORM post_data CHANGING lt_final.

* Again Encode output data for AFTER_RFC form
  CALL FUNCTION 'SPTA_INDX_PACKAGE_ENCODE'
    EXPORTING
      data    = lt_final
    IMPORTING
      indxtab = ct_rfcdata.

ENDFORM.                    "in_rfc

*&---------------------------------------------------------------------*
*&      Form  AFTER_RFC
*&---------------------------------------------------------------------*
* To collect date after parallel processing
*----------------------------------------------------------------------*
FORM after_rfc USING pt_rfcdata            TYPE spta_t_indxtab
                     pv_rfcsubrc           TYPE sy-subrc
                     pv_rfcmsg             TYPE spta_t_rfcmsg
                     pt_objects_in_process TYPE spta_t_objects_in_process
                     ps_after_rfc_imp      TYPE spta_t_after_rfc_imp
            CHANGING cs_after_rfc_exp      TYPE spta_t_after_rfc_exp
                     cs_user_param         TYPE gtt_op_file.

  DATA: lt_final TYPE gtt_op_file.

* Decode RFC output data and add RFC-results to global data
  IF gv_parallel_process EQ abap_true.
    CALL FUNCTION 'SPTA_INDX_PACKAGE_DECODE'
      EXPORTING
        indxtab = pt_rfcdata
      IMPORTING
        data    = lt_final.
  ENDIF.

* After parallel processing write the records in AL11
  IF lt_final[] IS NOT INITIAL.

  ENDIF.
ENDFORM.                    "AFTER_RFC

SAP Automatic Login

1. Create a folder where you want to store all login files (batch file).
2. In that folder, create a text file and put this code into it

start sapshcut -sysname=xxx -client=xxx -user=xxxxxx -pw=xxxxxxxx

sysname = system ID in logon pad
pw = password

(more info on parameters can be found on run –> sapshcut.exe /? )

3. Once done, save this file and change file name to <SYSID_CLIENT>.BAT and change save as type to ‘all files(*)’.

4. We are done !!!.. just double click on the batch file and no need to remember any system details from now on

Reference