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.
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.
Code:
*------------------------------------------------------------------------------------------
*
*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 [email protected]
* 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
*"----------------------------------------------------------------------
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
*"----------------------------------------------------------------------
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.
*------------------------------------------------------------------------------------------
* 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.
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.
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.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
