ABAP Object Oriented SpreadSheet with "Unlimited" Power

Content Author:  Jayanta Narayan Choudhuri
Author Email:      sss@cal.vsnl.net.in
Author Website:  http://www.geocities.com/ojnc

Objective: SAP On-Line HELP has a section - "Controls and Control Framework (BC-CI)".
Under this refer "Desktop Office Integration (BC-CI)"
In that section read "The Spreadsheet Interface" thoroughly. 

The ides is that once a programmer gets hold of a SpreadSheetInterface Object he/she can use the powerful methods to populate Excel in any way setting sheets, ranges, colours, fonts and ofcourse content.

Create a Function Group ZUTIL

Paste TOP Level code into LZUTILTOP

Create 4 Functionn Modules
ZJNC_START_EXCEL.
ZJNC_ADD_SHEET.
ZJNC_ADD_RANGE.
ZJNC_ADD_TABLE.

ZJNC_START_EXCEL - uses the "secret" screen 2307 which a user does not even see to get hold of a Spreadsheet Interface handle. With this alone a user has virtually unlimited power as he she can call all the methods.

But to make life easier I created 4 simple functions:

ZJNC_ADD_SHEET adds a sheet to a work book

ZJNC_ADD_RANGE adds a range to a sheet

ZJNC_ADD_TABLE adds a internal table to a range with specification of all properties like font colour size bold italic etc. In ABAP Objects, you can only declare tables without headers.  Hence TABLE[] syntax ensures Header is Stripped.

It is best to have full geometry in mind and fill in the following sequence

For each SHEET Create 1 RANGE  & Populate Data immediately
For each SHEET Reapeat for all Ranges

Before creating a range you will need to consider size based on table.
The no. of Rows & Columns will decide size. 
The cumulative rows will gixe the corner co-ordinates.

------------------------------------------------------------------------------------------

Attached Files:

ZJNCEXCEL_Test.ab4 is the Test Program

ZJNCEXCEL_FUNC.ab4 is the Function Group

ZEXCEL_WRITEUP.txt is this write-up

*  Author Jayanta Narayan Choudhuri
*         Flat 302
*         395 Jodhpur Park
*         Kolkata 700 068
*       Email sss@cal.vsnl.net.in
*       URL:  http://www.geocities.com/ojnc

*------------------------------------------------------------------------------------------                   
* Screen 2307 has only 1 Custom Control MYCONTROL 
* Screen 2307 Flow Logic

PROCESS BEFORE OUTPUT.
  MODULE ZJNCPBO.
*
PROCESS AFTER INPUT.
* MODULE ZJNCPAI.

1 Custom Control MYCONTROL
OK ZJNC_OK_CODE

*------------------------------------------------------------------------------------------                 
FUNCTION ZJNC_START_EXCEL.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  EXPORTING
*"     REFERENCE(SPREADSHEETINTF) TYPE REF TO  I_OI_SPREADSHEET
*"----------------------------------------------------------------------

Move SY-REPID to ZJNC_REPID.
CALL SCREEN 2307.

spreadsheetintf = zjncspreadsheet.

ENDFUNCTION.

FUNCTION ZJNC_ADD_SHEET.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     REFERENCE(PSHEET) TYPE  C
*"     REFERENCE(SPREADSHEETINTF) TYPE REF TO  I_OI_SPREADSHEET
*"----------------------------------------------------------------------

    Move SY-REPID to ZJNC_REPID.

    CALL METHOD SPREADSHEETINTF->add_sheet
           EXPORTING name     = psheet
                     no_flush  = ' '
           IMPORTING error     = zjncerror
                     retcode   = zjncretcode.

ENDFUNCTION.

FUNCTION ZJNC_ADD_RANGE.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     REFERENCE(PRANGE) TYPE  C
*"     REFERENCE(STARTROW) TYPE  I
*"     REFERENCE(STARTCOL) TYPE  I
*"     REFERENCE(NUMROWS) TYPE  I
*"     REFERENCE(NUMCOLS) TYPE  I
*"     REFERENCE(PSHEET) TYPE  C
*"     REFERENCE(SPREADSHEETINTF) TYPE REF TO  I_OI_SPREADSHEET
*"----------------------------------------------------------------------


    Move SY-REPID to zjnc_repid.

    CALL METHOD SPREADSHEETINTF->select_sheet
           EXPORTING name      = psheet
           no_flush  = ' '
           IMPORTING error     = zjncerror
                     retcode   = zjncretcode.

    CALL METHOD SPREADSHEETINTF->set_selection
           EXPORTING top       = StartRow
                     left      = StartCol
                     rows      = 1
                     columns   = 1
                     no_flush  = ' '
           IMPORTING error     = zjncerror
                     retcode   = zjncretcode.

    CALL METHOD SPREADSHEETINTF->insert_range
           EXPORTING name      = prange
                     rows      = numRows
                     columns   = numCols
                     no_flush  = ' '
           IMPORTING error     = zjncerror
                     retcode   = zjncretcode.


ENDFUNCTION.

FUNCTION ZJNC_ADD_TABLE.
*"----------------------------------------------------------------------
*"*"Local interface:
*"  IMPORTING
*"     REFERENCE(PTABLE) TYPE  TABLE
*"     REFERENCE(PRANGE) TYPE  C
*"     REFERENCE(PSIZE) TYPE  I DEFAULT -1
*"     REFERENCE(PBOLD) TYPE  I DEFAULT -1
*"     REFERENCE(PITALIC) TYPE  I DEFAULT -1
*"     REFERENCE(PALIGN) TYPE  I DEFAULT -1
*"     REFERENCE(PFRONT) TYPE  I DEFAULT -1
*"     REFERENCE(PBACK) TYPE  I DEFAULT -1
*"     REFERENCE(PFORMAT) TYPE  C DEFAULT 'NA'
*"     REFERENCE(SPREADSHEETINTF) TYPE REF TO  I_OI_SPREADSHEET
*"----------------------------------------------------------------------


** TYPES: SOI_zjnc_fields_table TYPE STANDARD TABLE OF RFC_FIELDS.
DATA: zjnc_fields_table   Type TABLE OF rfc_fields.
DATA: zjncwa_zjnc_fields_table TYPE     rfc_fields.

    Move SY-REPID to zjnc_repid.

    CALL FUNCTION 'DP_GET_FIELDS_FROM_TABLE'
         TABLES
              data   = ptable
              fields = zjnc_fields_table.

    CALL METHOD SPREADSHEETINTF->insert_one_table
           EXPORTING
*                    ddic_name    = ddic_name
                     data_table   = ptable
                     fields_table = zjnc_fields_table
                     rangename    = prange
                     wholetable   = 'X'
                     no_flush     = ' '
           IMPORTING error        = zjncerror
                     retcode      = zjncretcode.

    CALL METHOD SPREADSHEETINTF->set_font
           EXPORTING rangename = prange
                     family    = 'Arial'
                     size      = psize
                     bold      = pbold
                     italic    = pitalic
                     align     = palign
                     no_flush  = ' '
           IMPORTING error     = zjncerror
                     retcode   = zjncretcode.

    CALL METHOD SPREADSHEETINTF->set_color
           EXPORTING rangename = prange
                     front     = pfront
                     back      = pback
                     no_flush  = ' '
           IMPORTING error     = zjncerror
                     retcode   = zjncretcode.

    If pFormat <> 'NA'.
        CALL METHOD SPREADSHEETINTF->set_format_string
               EXPORTING rangename    = prange
                         formatstring = pformat
                         no_flush     = ' '
               IMPORTING error     = zjncerror
                         retcode   = zjncretcode.
    EndIf.

ENDFUNCTION.


*------------------------------------------------------------------------------------------
* TOP level Include of Function Group ZUTIL


FUNCTION-POOL ZUTIL.                        "MESSAGE-ID ..

* Global ZUTIL Data for ZJNCEXCEL
DATA zjnccontainer   TYPE REF TO cl_gui_custom_container.

DATA zjnccontrol     TYPE REF TO i_oi_container_control.

DATA zjncdocument    TYPE REF TO i_oi_document_proxy.

DATA zjncspreadsheet TYPE REF TO i_oi_spreadsheet.

DATA zjncerror       TYPE REF TO i_oi_error.
DATA zjncretcode     TYPE SOI_RET_STRING.

DATA zjncexcelsheet  TYPE soi_document_type VALUE 
SOI_DOCTYPE_EXCEL_SHEET.

DATA:   zjnc_ok_code LIKE sy-ucomm,         " return code from screen
        zjnc_repid   LIKE sy-repid.


************************************************************************
*   P B O
************************************************************************
MODULE zjncpbo OUTPUT.

*    SET PF-STATUS 'ZJNCSTATUS'.
*    SET TITLEBAR  'ZJNCTITLE'.

    IF zjncdocument IS NOT INITIAL.
       RETURN.
    EndIf.

    Perform ZJNC_INIT_EXCEL.

    Leave to Screen 0.

ENDMODULE.                             " PBO


*&---------------------------------------------------------------------*
*&      Form  ZJNC_INIT_EXCEL
*&---------------------------------------------------------------------*
Form ZJNC_INIT_EXCEL.

    CALL METHOD c_oi_container_control_creator=>get_container_control
         IMPORTING control = zjnccontrol
                   error   = zjncerror.

    IF sy-subrc NE 0.
      CALL FUNCTION 'POPUP_TO_INFORM'
           EXPORTING
                titel = zjnc_repid
                txt2  = 'Create OLE zjnccontrol Failed'
                txt1  = 'to make Excel zjnccontrol'.
      Leave Program.
    ENDIF.

    CREATE OBJECT zjnccontainer
      EXPORTING
         CONTAINER_NAME = 'MYCONTROL'
      EXCEPTIONS
         CNTL_ERROR = 1
         CNTL_SYSTEM_ERROR = 2
         CREATE_ERROR = 3
         LIFETIME_ERROR = 4
         LIFETIME_DYNPRO_DYNPRO_LINK = 5.

    IF sy-subrc NE 0.
*      add your handling
    ENDIF.

    CALL METHOD zjnccontrol->init_control
         EXPORTING  r3_application_name      = 'R/3 Basis' "#EC NOTEXT
                    inplace_enabled          = 'X'
                    inplace_scroll_documents = 'X'
                    parent                   =  zjnccontainer
                    register_on_close_event  = 'X'
                    register_on_custom_event = 'X'
                    no_flush                 = 'X'
          IMPORTING error = zjncerror.

    IF sy-subrc NE 0.
      CALL FUNCTION 'POPUP_TO_INFORM'
           EXPORTING
                titel = zjnc_repid
                txt2  = 'INIT OLE zjnccontrol Failed'
                txt1  = 'to init Excel zjnccontrol'.
      Leave Program.
    ENDIF.

    CALL METHOD zjnccontrol->get_document_proxy
       EXPORTING document_type       = zjncexcelsheet
*                document_format     = document_format
*                register_container  = register_container
                 no_flush            = ' '
       IMPORTING document_proxy       = zjncdocument
                 retcode              = zjncretcode
                 error                = zjncerror.

    IF sy-subrc NE 0.
      CALL FUNCTION 'POPUP_TO_INFORM'
           EXPORTING
                titel = zjnc_repid
                txt2  = 'Create zjncdocument PROXY Failed'
                txt1  = 'to make Excel zjncdocument'.
      Leave Program.
    ENDIF.

    CALL METHOD zjncdocument->create_document
      EXPORTING open_inplace     = ' '
*               create_view_data = create_view_data
*               onsave_macro     = onsave_macro
*               startup_macro    = startup_macro
                document_title   = 'JNC'
                no_flush         = ' '
     IMPORTING error            = zjncerror
*               retcode          = retcode
                .

    IF sy-subrc NE 0.
      CALL FUNCTION 'POPUP_TO_INFORM'
           EXPORTING
                titel = zjnc_repid
                txt2  = 'Create zjncdocument Failed'
                txt1  = 'to make Excel zjncdocument'.
      Leave Program.
    ENDIF.

   CALL METHOD zjncdocument->get_spreadsheet_interface
     EXPORTING no_flush        = ' '
     IMPORTING sheet_interface = zjncspreadsheet
               error           = zjncerror
               retcode         = zjncretcode.

    IF sy-subrc NE 0.
      CALL FUNCTION 'POPUP_TO_INFORM'
           EXPORTING
                titel = zjnc_repid
                txt2  = 'Create zjncspreadsheet INTERFACE Failed'
                txt1  = 'to make Excel zjncspreadsheet'.
      Leave Program.
    ENDIF.

ENDFORM.                               " ZJNC_INIT_EXCEL


Report ZExcelTest.

DATA spreadsheetintf TYPE REF TO i_oi_spreadsheet.

DATA: numRows    type I,
      maxRows    type I.

DATA: usa_sales TYPE i VALUE 1000,
      europe_sales TYPE i VALUE 2000,
      japan_sales TYPE i VALUE 1000,
      asia_sales TYPE i VALUE 100,
      america_sales TYPE i VALUE 100,
      africa_sales TYPE i VALUE 100.

  DATA: BEGIN OF head_table Occurs 0,
             hd_region(10),
             hd_sales(10),
             hd_date(10),
             hd_time(10),
             hd_weight(10),
             hd_amount(10),
             hd_id(10),
        END OF head_table.

  DATA: BEGIN OF sales_table Occurs 0,
             region(60),
             sales TYPE i,
             date  TYPE d,
             time  TYPE t,
             weight TYPE f,
             amount TYPE p DECIMALS 3,
             id(10) TYPE n,
        END OF sales_table.


  DATA: ind TYPE i.


  CLEAR: head_table.

  Head_Table-hd_region = 'Region'.
  Head_Table-hd_sales = 'Sales'.
  Head_Table-hd_date = 'Date'.
  Head_Table-hd_time = 'Time'.
  Head_Table-hd_weight = 'Weight in MT'.
  Head_Table-hd_amount = 'Value in Rupees'.
  Head_Table-hd_id = 'Sytem ID'.

  Append Head_Table.

  CALL FUNCTION 'ZJNC_START_EXCEL'
    IMPORTING
      SPREADSHEETINTF       =  SPREADSHEETINTF.

  CALL FUNCTION 'ZJNC_ADD_SHEET'
    EXPORTING
      PSHEET                = 'Sheet ONE'
      SPREADSHEETINTF       = spreadsheetintf.

  maxRows = 1.

  CALL FUNCTION 'ZJNC_ADD_RANGE'
    EXPORTING
      PRANGE                = 'HeadRange1'
      STARTROW              = maxRows
      STARTCOL              = 1
      NUMROWS               = 1
      NUMCOLS               = 7
      PSHEET                = 'Sheet ONE'
      SPREADSHEETINTF       = spreadsheetintf.

* In ABAP Objects, you can only declare tables without headers.
* Hence sales_table[] ensures Header is Stripped

  CALL FUNCTION 'ZJNC_ADD_TABLE'
    EXPORTING
      PTABLE                = head_table[]
      PRANGE                = 'HeadRange1'
*     PSIZE                 = -1
      PBOLD                 = 1
*     PITALIC               = -1
*     PALIGN                = -1
*     PFRONT                = -1
*     PBACK                 = -1
*     PFORMAT               = 'NA'
      SPREADSHEETINTF       = spreadsheetintf.

  Add 1 to maxrows.

  CLEAR: sales_table.

  sales_table-region = 'USA'(usa).
  sales_table-sales = usa_sales.
  APPEND sales_table.

  sales_table-region = 'Europe'(eur).
  sales_table-sales = europe_sales.
  APPEND sales_table.

  sales_table-region = 'Japan'(jap).
  sales_table-sales = japan_sales.
  APPEND sales_table.

  sales_table-region = 'Asia'(asi).
  sales_table-sales = asia_sales.
  APPEND sales_table.

  LOOP AT sales_table.
    ind = sy-tabix.
    sales_table-date = sy-datum + ind.
    sales_table-time = sy-uzeit + ind.
    sales_table-weight = 100000 * ind.
    sales_table-amount = 11111 * ind.
    sales_table-id = ind.
    MODIFY sales_table.
  ENDLOOP.

  Describe Table  sales_table Lines numRows.

  CALL FUNCTION 'ZJNC_ADD_RANGE'
    EXPORTING
      PRANGE                = 'DataRange1'
      STARTROW              = maxRows
      STARTCOL              = 1
      NUMROWS               = numRows
      NUMCOLS               = 7
      PSHEET                = 'Sheet ONE'
      SPREADSHEETINTF       = spreadsheetintf.

  CALL FUNCTION 'ZJNC_ADD_TABLE'
    EXPORTING
      PTABLE                = sales_table[]
      PRANGE                = 'DataRange1'
*     PSIZE                 = -1
      PBOLD                 = 0
*     PITALIC               = -1
*     PALIGN                = -1
      PFRONT                = 3
*     PBACK                 = -1
*     PFORMAT               = 'NA'
      SPREADSHEETINTF       = spreadsheetintf.

* Start NewSheet on TOP
  Move 1 to maxRows.

  CALL FUNCTION 'ZJNC_ADD_SHEET'
    EXPORTING
      PSHEET                = 'Sheet TWO'
      SPREADSHEETINTF       = spreadsheetintf.

  CALL FUNCTION 'ZJNC_ADD_RANGE'
    EXPORTING
      PRANGE                = 'HeadRange2'
      STARTROW              = maxRows
      STARTCOL              = 1
      NUMROWS               = 1
      NUMCOLS               = 7
      PSHEET                = 'Sheet TWO'
      SPREADSHEETINTF       = spreadsheetintf.

* In ABAP Objects, you can only declare tables without headers.
* Hence sales_table[] ensures Header is Stripped

  CALL FUNCTION 'ZJNC_ADD_TABLE'
    EXPORTING
      PTABLE                = head_table[]
      PRANGE                = 'HeadRange2'
*     PSIZE                 = -1
      PBOLD                 = 1
*     PITALIC               = -1
*     PALIGN                = -1
*     PFRONT                = -1
*     PBACK                 = -1
*     PFORMAT               = 'NA'
      SPREADSHEETINTF       = spreadsheetintf.

  Add 1 to maxrows.

  CLEAR: sales_table.

  sales_table-region = 'America'(ame).
  sales_table-sales = america_sales.
  APPEND sales_table.

  sales_table-region = 'Africa'(afr).
  sales_table-sales = africa_sales.
  APPEND sales_table.

  LOOP AT sales_table.
    ind = sy-tabix.
    sales_table-date = sy-datum + ind.
    sales_table-time = sy-uzeit + ind.
    sales_table-weight = 700000 * ind.
    sales_table-amount = 123456 * ind.
    sales_table-id = ind.
    MODIFY sales_table.
  ENDLOOP.

  Describe Table  sales_table Lines numRows.

  CALL FUNCTION 'ZJNC_ADD_RANGE'
    EXPORTING
      PRANGE                = 'DataRange2'
      STARTROW              = maxRows
      STARTCOL              = 1
      NUMROWS               = numRows
      NUMCOLS               = 7
      PSHEET                = 'Sheet TWO'
      SPREADSHEETINTF       = spreadsheetintf.

  CALL FUNCTION 'ZJNC_ADD_TABLE'
    EXPORTING
      PTABLE                = sales_table[]
      PRANGE                = 'DataRange2'
*     PSIZE                 = -1
      PBOLD                 = 0
*     PITALIC               = -1
*     PALIGN                = -1
      PFRONT                = 55
      PBACK                 = 6
*     PFORMAT               = 'NA'
      SPREADSHEETINTF       = spreadsheetintf.


  CALL FUNCTION 'POPUP_TO_INFORM'
   EXPORTING
        titel = sy-repid
        txt2  = 'See EXCEL & SAVE if Needed'
        txt1  = 'Jai Hind ....'.

ABAP Tips

Related ABAP Topics:
Table CDHDR and CDPOS Usage

Get help for your ABAP problems
Do you have a ABAP Question?

SAP Books
SAP Certification, Interview Questions, Functional, Basis Administration and ABAP Programming Reference Books

More ABAP Tips

Main Index
SAP ERP Modules, Basis, ABAP and Other IMG Stuff

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.  The site www.erpgreat.com is in no way affiliated with SAP AG.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.