ABAP DOI EXCEL

http://www.sap-img.com/abap/abap-object-oriented-spreadsheet-with-unlimited-power.htm

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 ....'.