Refer to previous tip
ABAP Object Oriented SpreadSheet with "Unlimited" Power
http://www.sap-img.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 :.
* 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.
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 FIELDTYPE 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
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.
No comments:
Post a Comment