Author Email: sss@cal.vsnl.net.in
Author Website: http://www.geocities.com/ojnc
Refer to previous tip
ABAP Object Oriented SpreadSheet with "Unlimited" Power
http://www.erpgreat.com/abap/abap-object-oriented-spreadsheet-with-unlimited-power.htm
"SAPRDEMO_PIVOTINTERFACE" is SAP's official example uses the pivot interface.
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 Jayanta Narayan Choudhuri * Flat 302 * 395 Jodhpur Park * Kolkata 700 068 * Email sss@cal.vsnl.net.in * URL: http://www.geocities.com/ojnc 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 of * the 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 element * in 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 the * structure alignment. That means the global offset OFFSET has to be * aligned 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 are * aligned 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 remeber * maximum. PERFORM GET_ALIGNMENT USING TYP CHANGING ALIGNMENT. IF ALIGNMENT > MAX_ALIGNMENT. MAX_ALIGNMENT = ALIGNMENT. ENDIF. * store all significant information in THISFIELDS, which is appended * to 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 offset * according to the required alignment is done later. OFFSET = THISFIELDS-OFFSET + LEN. POS = POS + 1. APPEND THISFIELDS. ENDIF. ENDDO. ELSE. " if n_Comps > 0 * We 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 adjust * the offset MODRESULT = P_FIELDS_OFFSET MOD THISALIGNBASE. IF MODRESULT NE 0. ADDOFFSET = THISALIGNBASE - MODRESULT. P_FIELDS_OFFSET = P_FIELDS_OFFSET + ADDOFFSET. ENDIF. ENDIF. ENDFORM.
