工作生活与爱好
工作中的记录； 生活中的记忆； 业余爱好的记载。2007-01-16
ABAP Object Oriented SpreadSheet with "Unlimited" Power
Content Author: Jayanta Narayan Choudhuri
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 ....'.
posted by lxy # 23:21 
Archives
1990年1月15日 2007年1月14日 2007年1月15日 2007年1月16日 2007年1月17日 2007年1月18日 2007年1月19日 2007年1月20日 2007年1月22日 2007年1月23日 2007年1月24日 2007年1月25日 2007年1月26日 2007年1月27日 2007年1月29日 2007年1月30日 2007年1月31日 2007年2月1日 2007年2月2日 2007年2月3日 2007年3月13日 2007年5月15日 2007年5月16日 2007年6月2日
