report abap alv xls

8/18/2019 Report Abap Alv Xls

1/10

REPORT Y_LALP_OLE_XLS Message-id ym. .

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

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

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: 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-WiseData: IT_PIVOT Like Sorted Table Of WA_PIVOT With Header Line


2/10

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.

call screen 0100.

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.


3/10

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.

CALL METHOD zjncdocument->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.


4/10

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.

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.

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 METHOD zjncspreadsheet->add_sheet EXPORTING name = 'Sheet ONE'

no_flush = ' ' IMPORTING error = zjncerror retcode = zjncretcode.maxRows = 1.

* CALL FUNCTION 'ZJNC_ADD_RANGE'* EXPORTING* PRANGE = 'HeadRange1'* STARTROW = maxRows* STARTCOL = 1


5/10

* NUMROWS = 1* NUMCOLS = 7* PSHEET = 'Sheet ONE'* SPREADSHEETINTF = spreadsheetintf.

Move SY-REPID to zjnc_repid.

CALL METHOD zjncspreadsheet->select_sheet EXPORTING name = 'Sheet ONE' no_flush = ' ' IMPORTING error = zjncerror retcode = zjncretcode.

CALL METHOD zjncspreadsheet->set_selection EXPORTING top = maxRows left = 7 rows = 1 columns = 1 no_flush = ' ' IMPORTING error = zjncerror retcode = zjncretcode.

CALL METHOD zjncspreadsheet->insert_range EXPORTING name = 'HeadRange1'

rows = maxRows columns = 7 no_flush = ' ' IMPORTING error = zjncerror retcode = zjncretcode.

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

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 = head_table[] fields = zjnc_fields_table.

CALL METHOD zjncspreadsheet->insert_one_table EXPORTING* ddic_name = ddic_name


6/10

data_table = head_table[] fields_table = zjnc_fields_table rangename = 'HeadRange1' wholetable = 'X' no_flush = ' ' IMPORTING error = zjncerror retcode = zjncretcode.

CALL METHOD zjncspreadsheet->set_font EXPORTING rangename = 'HeadRange1' family = 'Arial' size = -1 bold = 1 italic = -1 align = -1 no_flush = ' ' IMPORTING error = zjncerror retcode = zjncretcode.

CALL METHOD zjncspreadsheet->set_color EXPORTING rangename = 'HeadRange1' front = -1 back = -1 no_flush = ' '

IMPORTING error = zjncerror retcode = zjncretcode.

* 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


7/10

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

*break-point.CALL METHOD zjncdocument->save_as EXPORTING file_name = 'C:\@ALombardi\ejm.xls'.

* CALL FUNCTION 'POPUP_TO_INFORM'


8/10

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

*&---------------------------------------------------------------------**& 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.*&---------------------------------------------------------------------**& Module STATUS_0100 OUTPUT*&---------------------------------------------------------------------** text*----------------------------------------------------------------------*module STATUS_0100 output.* SET PF-STATUS 'xxxxxxxx'.* SET TITLEBAR 'xxx'.


9/10

** IF zjncdocument IS NOT INITIAL.* RETURN.* EndIf.

Perform ZJNC_INIT_EXCEL.

Leave to Screen 0.

endmodule." STATUS_0100 OUTPUT*&---------------------------------------------------------------------**& 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.


10/10

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 abap alv xls

Documents