Skip to end of metadata
Go to start of metadata
Description Example below reates a Pivot in Excel direct from a SAP internal table.   
The pivot interface object should be created soon after creating the document and spreadsheet interfaces object. Otherwise often it is not successful and returns a null pivot handle. The Internal table should have a header line otherwise DYN_ANALYSE_SINGLE will fail.

This example should make it very easy to do Excel Integration.
----------------------------------------------------------------------------------------
*  Author Saurabh Siwach

Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
Report ZExcelTest Message-id ym.
DATA spreadsheetintf TYPE REF TO i_oi_spreadsheet.
DATA documentintf    TYPE REF TO i_oi_document_proxy.
DATA pivotintf       TYPE REF TO i_oi_pivot.
DATA zjncerror       TYPE REF TO i_oi_error.
DATA zjncretcode     TYPE SOI_RET_STRING.
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.
Data: Begin of WA_PIVOT,
        region(60),
        date   TYPE d,
        amount TYPE p DECIMALS 3,
      End of   WA_PIVOT.
Qty Internal Table for Date-wise Plant-Wise
Data: IT_PIVOT Like Sorted Table Of WA_PIVOT With Header Line
                  With Unique Key region date.Data: DT_PIVOT     Like Table Of WA_PIVOT With Header Line.
Data: IT_ITEMS     type  SOI_NAMETYPE_TABLE.
Data: WA_ITEMS     like line of IT_ITEMS.
DATA: WA_NAMECOL   TYPE SOI_NAMECOL_ITEM.
DATA: NAMECOL      TYPE SOI_NAMECOL_TABLE.
DATA: WA_FIELD     LIKE RFC_FIELDS.
DATA: FIELDS_TABLE TYPE TABLE OF RFC_FIELDS.
DATA: OFFSET    LIKE RFC_FIELDS-OFFSET VALUE 0.
DATA: N         TYPE I.
DATA: POS       TYPE I VALUE 1.
DATA: ALIGNMENT TYPE I.
  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.
  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.
  LOOP AT sales_table.
    Move sales_table-region  to WA_PIVOT-region.
    Move sales_table-date    to WA_PIVOT-date.
    Move sales_table-amount  to WA_PIVOT-amount.
    Collect WA_PIVOT Into IT_PIVOT.
  ENDLOOP.
  DT_PIVOT[] = IT_PIVOT[].
  CALL FUNCTION 'ZJNC_START_EXCEL'
    IMPORTING
      SPREADSHEETINTF       =  SPREADSHEETINTF
      DOCUMENTINTF          =  DOCUMENTINTF.
  If documentintf    Is Initial
  Or spreadsheetintf Is Initial.
     message e999 with 'NULL document/spreadsheet interfaces'.
  EndIf.
  CALL METHOD documentintf->get_pivot_interface
       EXPORTING    no_flush        = ' '
       IMPORTING    pivot_interface = pivotintf
                    error           = zjncerror
                    retcode         = zjncretcode.
  If zjncretcode <> c_oi_errors=>ret_ok.
       CALL METHOD c_oi_errors=>raise_message
           EXPORTING type   = 'E'.
  EndIf.
  If pivotintf Is Initial.
     message e999 with 'NULL pivot interface'.
  EndIf.
  PERFORM DYN_ANALYSE_SINGLE TABLES    FIELDS_TABLE
                             USING     DT_PIVOT
                             CHANGING  POS N OFFSET ALIGNMENT.
  WA_NAMECOL-NAME = 'REGION'.
  WA_NAMECOL-COLUMN = '1'.
  APPEND WA_NAMECOL TO NAMECOL.
  WA_NAMECOL-NAME = 'DATE'.
  WA_NAMECOL-COLUMN = '2'.
  APPEND WA_NAMECOL TO NAMECOL.
  WA_NAMECOL-NAME = 'AMOUNT'.
  WA_NAMECOL-COLUMN = '3'.
  APPEND WA_NAMECOL TO NAMECOL.
  LOOP AT NAMECOL INTO WA_NAMECOL.
    READ TABLE FIELDS_TABLE INTO WA_FIELD INDEX WA_NAMECOL-COLUMN.
    IF SY-SUBRC EQ 0.
      WA_FIELD-FIELDNAME = WA_NAMECOL-NAME.
      MODIFY FIELDS_TABLE INDEX WA_NAMECOL-COLUMN FROM WA_FIELD.
    ENDIF.
  ENDLOOP.
  Move 'REGION' to WA_ITEMS-name.
  Move i_oi_pivot=>rowfield  to  WA_ITEMS-type.
  Append WA_ITEMS to IT_ITEMS.
  Move 'DATE' to WA_ITEMS-name.
  Move i_oi_pivot=>columnfield   to  WA_ITEMS-type.
  Append WA_ITEMS to IT_ITEMS.
  Move 'AMOUNT' to WA_ITEMS-name.
  Move i_oi_pivot=>datafield  to  WA_ITEMS-type.
  Append WA_ITEMS to IT_ITEMS.
  CALL METHOD pivotintf->set_source_table
       EXPORTING data_table   = DT_PIVOT[]
                 fields_table = FIELDS_TABLE[]
                 name         = 'PlanningPivot'
                 items        = IT_ITEMS[]
       IMPORTING error        = zjncerror
                 retcode      = zjncretcode.
  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_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.
  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.
  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 METHOD documentintf->save_as
     EXPORTING file_name   = 'c:\jnc.xls'.
  CALL FUNCTION 'POPUP_TO_INFORM'
   EXPORTING
        titel = sy-repid
        txt2  = 'See EXCEL & SAVE if Needed'
        txt1  = 'Jai Hind ....'.
&---------------------------------------------------------------------
*&      Form  DYN_ANALYSE_SINGLE
&---------------------------------------------------------------------
FORM DYN_ANALYSE_SINGLE TABLES   P_FIELDS STRUCTURE RFC_FIELDS
                        USING    P_DATA
                        CHANGING POS TYPE I
                                 LEN TYPE I
                                 OFFSET LIKE RFC_FIELDS-OFFSET
                                 ALIGNMENT_STRUCT.
  DATA : TYP(1) TYPE C, DECS TYPE I.
  DATA : N_COMPS TYPE I.
  DATA : NN_COMPS TYPE I.
  DATA : ALIGNMENT TYPE I.
  DATA : MAX_ALIGNMENT TYPE I VALUE 1.
  DATA : THISFIELDS TYPE RFC_FIELDS OCCURS 0 WITH HEADER LINE.
  DATA : DELTA TYPE I.
  DATA : OLDOFFSET TYPE I.
  DATA : DATATYPE TYPE C.
  DATA : DATALEN TYPE I.
  DATA : DATADEC TYPE I.
  FIELD-SYMBOLS : <F>.
First check whether we have a structure
  DESCRIBE FIELD P_DATA TYPE DATATYPE COMPONENTS N_COMPS
                        LENGTH DATALEN IN BYTE MODE DECIMALS DATADEC.
  IF N_COMPS > 0.    DO.
      ASSIGN COMPONENT SY-INDEX OF STRUCTURE P_DATA TO <F>.
      IF SY-SUBRC <> 0.
        ALIGNMENT_STRUCT = MAX_ALIGNMENT.
CORRECT OFFSET FOR INNER STRUCTURES
        IF MAX_ALIGNMENT > 1.loop at all fields an correct offset according to alignment ofthe field type and the alignment of entire structure
          LOOP AT THISFIELDS.
            OLDOFFSET = THISFIELDS-OFFSET.DELTA always contains the current shift already done.
            THISFIELDS-OFFSET = THISFIELDS-OFFSET + DELTA.
            IF SY-TABIX = 1.First element is aligned according to max alignment of any elementin structure.
              PERFORM OFFSET_CORRECTION USING
                                            ' '
                                            MAX_ALIGNMENT
                                        CHANGING
                                            THISFIELDS-OFFSET.
            ELSE.All subsequent elements are aligned according to their typ.
              PERFORM OFFSET_CORRECTION USING
                                            THISFIELDS-EXID
                                            0
                                        CHANGING
                                             THISFIELDS-OFFSET.
            ENDIF.remember current shift in DELTA
            DELTA = THISFIELDS-OFFSET - OLDOFFSET.
            MODIFY THISFIELDS.
          ENDLOOP.new global offset for next fields must be at least latest offset + 1
          OFFSET =  THISFIELDS-OFFSET + 1.
        ENDIF.ABAP aligns inner strcutures also at the end according to thestructure alignment. That means the global offset OFFSET has to bealigned to that value.
        PERFORM OFFSET_CORRECTION USING
                                     ' '
                                     MAX_ALIGNMENT
                                  CHANGING
                                     OFFSET.Append fields for this structure to entire fields table ...
        APPEND LINES OF THISFIELDS TO P_FIELDS.... and leave
        EXIT.
      ENDIF.
      DESCRIBE FIELD <F> TYPE TYP
                         COMPONENTS NN_COMPS
                         LENGTH LEN IN BYTE MODE
                         DECIMALS DECS.Do recursive calls for nested structures.
      IF NN_COMPS > 0.
        PERFORM DYN_ANALYSE_SINGLE TABLES THISFIELDS
                                   USING <F>
                                   CHANGING POS LEN OFFSET ALIGNMENT.remember maximum alignment requirement, since inner structures arealigned according to the maximum required by inner elements.
        IF ALIGNMENT > MAX_ALIGNMENT.
          MAX_ALIGNMENT = ALIGNMENT.
        ENDIF.
      ELSE.get the required alignment for this type of element and remebermaximum.
        PERFORM GET_ALIGNMENT USING TYP CHANGING ALIGNMENT.
        IF ALIGNMENT > MAX_ALIGNMENT.
          MAX_ALIGNMENT = ALIGNMENT.
        ENDIF.store all significant information in THISFIELDS, which is appendedto P_FIELDS later.
        THISFIELDS-EXID = TYP.
        THISFIELDS-INTLENGTH = LEN.
        THISFIELDS-POSITION = POS.
        THISFIELDS-OFFSET = OFFSET.
        THISFIELDS-FIELDNAME = POS.
        THISFIELDS-TABNAME = CNDP_SAP_TAB_UNKNOWN.
        THISFIELDS-DECIMALS = DECS.Adding the length to offset is fine here, since the correct offsetaccording to the required alignment is done later.
        OFFSET = THISFIELDS-OFFSET + LEN.
        POS = POS + 1.
        APPEND THISFIELDS.
      ENDIF.
    ENDDO.
  ELSE.                                " if n_Comps > 0We only have a simple field.Be straight forward, just put the elements into p_fields.
    REFRESH P_FIELDS.
    P_FIELDS-EXID = DATATYPE.
    P_FIELDS-INTLENGTH = DATALEN.
    P_FIELDS-POSITION = 1.             " Position is always
    P_FIELDS-OFFSET = 0.               " Offset at beginning
    P_FIELDS-FIELDNAME = '1'.          "#EC_TEXT Name as above
    P_FIELDS-TABNAME = CNDP_SAP_TAB_UNKNOWN.
    P_FIELDS-DECIMALS = DATADEC.
    APPEND P_FIELDS.
  ENDIF.ENDFORM.
&---------------------------------------------------------------------
*&      Form  GET_ALIGNMENT
&---------------------------------------------------------------------
FORM GET_ALIGNMENT USING    P_TYP
                   CHANGING ALIGNBASE.
  ALIGNBASE = 1.
  CASE P_TYP.
*FLOAT VALUE NEEDS 8 BYTE ALIGNMENT
    WHEN 'F'.
      ALIGNBASE = 8.
*SHORT INTEGER NEEDS 2 BYTE ALIGNMENT
    WHEN 's'.
      ALIGNBASE = 2.
*LONG INTEGER NEEDS 4 BYTE ALIGNMENT
    WHEN 'I'.
      ALIGNBASE = 4.
*ALL OTHER TYPES ARE BYTE ALIGNED
  ENDCASE.
ENDFORM.
&---------------------------------------------------------------------
*&      Form  OFFSET_CORRECTION
&---------------------------------------------------------------------
FORM OFFSET_CORRECTION USING    P_TYP ALIGNBASE
                       CHANGING P_FIELDS_OFFSET.
  DATA : MODRESULT TYPE I.
  DATA : ADDOFFSET TYPE I.
  DATA : THISALIGNBASE TYPE I.
  IF P_TYP NE ' '.
    PERFORM GET_ALIGNMENT USING P_TYP CHANGING THISALIGNBASE.
  ELSE.
    THISALIGNBASE = ALIGNBASE.
  ENDIF.
  IF THISALIGNBASE NE 1.
If the reminder of mod operation is not equal 0 we have to adjustthe offset
    MODRESULT = P_FIELDS_OFFSET MOD THISALIGNBASE.
    IF MODRESULT NE 0.
      ADDOFFSET = THISALIGNBASE - MODRESULT.
      P_FIELDS_OFFSET = P_FIELDS_OFFSET + ADDOFFSET.
    ENDIF.
  ENDIF.
ENDFORM.

Thanks

  • No labels

1 Comment

  1. why do not see the code here ???