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