EXCEL BDS模板上传及赋值
上传模板事务代码：OAER
l 功能代码：向EXCEL模板中写入数据示例代码如下
REPORT ZEXCEL_DOI.
“doi type pools
TYPE-POOLS: soi.
*SAP Desktop Office Integration Interfaces
DATA: container TYPE REF TO cl_gui_custom_container,
control TYPE REF TO i_oi_container_control,
document TYPE REF TO i_oi_document_proxy,
spreadsheet TYPE REF TO i_oi_spreadsheet,
error TYPE REF TO i_oi_error,
errors TYPE REF TO i_oi_error OCCURS 0 WITH HEADER LINE.
*dynpro fields
CONTROLS: exceldata TYPE TABLEVIEW USING SCREEN 0100.
DATA: tablename(10),ok_code(15),
save_ok(15),
row(4),column(4),data(39).
*spreadsheet interface structures of the selected R/3 table
DATA: cellitem TYPE soi_generic_item,
rangeitem TYPE soi_range_item,
ranges TYPE soi_range_list,
excel_input TYPE soi_generic_table,
excel_input_wa TYPE soi_generic_item,
initialized(1),
retcode TYPE soi_ret_string.
*screen fields
SET SCREEN 100.
INCLUDE ZEXCEL_DOI_STATUS_0100O01.
*&———————————————————————*
*& Module USER_COMMAND_0100 INPUT
*&———————————————————————*
* text
*———————————————————————-*
MODULE USER_COMMAND_0100 INPUT.
save_ok = ok_code.
CLEAR ok_code.
CASE save_ok.
WHEN ‘BACK’.
SET SCREEN 0.
WHEN ‘OUTPUT’.
DATA: fields_table TYPE TABLE OF rfc_fields,
tabname TYPE x030l-tabname,
ret TYPE i VALUE 0.
CHECK NOT tablename IS INITIAL.
tabname = tablename.
DATA: dref TYPE REF TO data,
dref_it TYPE REF TO data,
len_table TYPE i,
len_excel_len TYPE i.
FIELD-SYMBOLS: <tablewa> TYPE any,
<it_table> TYPE any table.
CATCH SYSTEM-EXCEPTIONS create_data_unknown_type = 1
OTHERS = 2.
CREATE DATA dref TYPE (tabname).
CREATE DATA dref_it TYPE TABLE OF (tabname).
ENDCATCH.
IF sy-subrc <> 0.
EXIT.
ENDIF.
ASSIGN: dref->* TO <tablewa>,
dref_it->* TO <it_table>.
DATA: count TYPE i.
CLEAR count.
DATA: BEGIN OF la_row,
row TYPE c LENGTH 500,
END OF la_row,
excel_table LIKE TABLE OF la_row,
myrow TYPE c LENGTH 500.
REFRESH excel_table.
SELECT * INTO TABLE <it_table>
FROM (tabname).
“create an excel range for data
DATA: rows_number TYPE i,
columns_number TYPE i.
CALL FUNCTION ‘RFC_GET_STRUCTURE_DEFINITION’
EXPORTING
tabname = tabname
TABLES
fields = fields_table
EXCEPTIONS
table_not_active = 1
OTHERS = 2.
DESCRIBE TABLE fields_table LINES columns_number.
DESCRIBE TABLE <it_table> LINES rows_number.
CALL METHOD spreadsheet->insert_range_dim
EXPORTING name = ‘SAP_Table’
top = ‘2′
left = ‘2′
rows = rows_number
no_flush = ‘X’
columns = columns_number
IMPORTING error = errors.
APPEND errors.
CALL METHOD spreadsheet->set_color
EXPORTING rangename = ‘SAP_Table’
back = ‘50′
front = ‘2′
no_flush = ‘X’
IMPORTING error = errors.
APPEND errors.
“Create range for comments
CALL METHOD spreadsheet->insert_range_dim
EXPORTING name = ‘SAP_Comments’
top = ‘2′
left = ‘1′
rows = rows_number
no_flush = ‘X’
columns = 1
IMPORTING error = errors.
APPEND errors.
CALL METHOD spreadsheet->set_color
EXPORTING rangename = ‘SAP_Comments’
back = ‘25′
front = ‘2′
no_flush = ‘X’
IMPORTING error = errors.
APPEND errors.
DATA: title TYPE c LENGTH 30.
REFRESH:ranges,excel_input.
rangeitem-name = ‘SAP_Comments’.
rangeitem-columns = 1.
rangeitem-rows = 1.
APPEND rangeitem TO ranges.
excel_input_wa-column = 1.
excel_input_wa-row = 1.
CONCATENATE ‘R/3 table’ tabname ‘- comments’ INTO title
SEPARATED BY space.
excel_input_wa-value = title.
APPEND excel_input_wa TO excel_input.
CALL METHOD spreadsheet->set_ranges_data
EXPORTING ranges = ranges
contents = excel_input
IMPORTING error = errors.
APPEND errors.
“transfer R/3 table data into excel range
CALL METHOD spreadsheet->insert_one_table
EXPORTING data_table = <it_table>
ddic_name = tabname
rangename = ‘SAP_Table’
no_flush = ‘X’
wholetable = ‘X’
IMPORTING error = errors.
APPEND errors.
“protect the R/3 data against input
CALL METHOD spreadsheet->fit_widest
EXPORTING name = space
no_flush = ‘X’.
CALL METHOD spreadsheet->protect_range
EXPORTING name = ‘SAP_Table’
protect = ‘X’
no_flush = ‘X’
IMPORTING error = errors.
APPEND errors.
“no flush automation queue
CALL METHOD control->set_focus
EXPORTING no_flush = ‘ ’
IMPORTING error = errors.
APPEND errors.
LOOP AT errors.
CALL METHOD errors->raise_message
EXPORTING type = ‘E’.
ENDLOOP.
FREE errors.
REFRESH:excel_input,ranges.
CLEAR :excel_input_wa,rangeitem.
ENDCASE.
ENDMODULE. “ USER_COMMAND_0100 INPUT
*&———————————————————————*
*& Module CREATE_BASIC_OBJECTS OUTPUT
*&———————————————————————*
* text
*———————————————————————-*
MODULE CREATE_BASIC_OBJECTS OUTPUT.
CHECK initialized IS INITIAL.
“first get doi i_oi_container_control interface
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING control = control
error = error.
“check no errors occured
CALL METHOD error->raise_message EXPORTING type = ‘E’.
“create a control container as defined in dynpro 0100
CREATE OBJECT container
EXPORTING container_name = ‘CONTAINER’.
“initialize the SAP DOI container,tell it to run in the container
“specified above and tell it to run excel inplace
CALL METHOD control->init_control
EXPORTING r3_application_name = ‘R/3 Basis’
inplace_enabled = ”
inplace_scroll_documents = ”
parent = container
register_on_close_event = ‘X’
register_on_custom_event = ‘X’
no_flush = ‘X’
IMPORTING error = errors.
* save error object in collection
APPEND errors.
* ask the SAP DOI container for a i_oi_document_proxy for Exel
CALL METHOD control->get_document_proxy
EXPORTING document_type = ‘Excel.Sheet’
no_flush = ‘X’
IMPORTING document_proxy = document
error = errors.
*and then create a new Excel Sheet
CALL METHOD document->create_document
EXPORTING open_inplace = ”
document_title = ‘R/3 table contents in Excel’
no_flush = ‘X’
IMPORTING error = errors.
APPEND errors.
* check if our document proxy can serve a spread sheet interface
DATA: has TYPE i.
CALL METHOD document->has_spreadsheet_interface
EXPORTING no_flush = ‘X’
IMPORTING is_available = has
error = errors.
APPEND errors.
CALL METHOD document->get_spreadsheet_interface
EXPORTING no_flush = ‘ ’
IMPORTING sheet_interface = spreadsheet
error = errors.
APPEND errors.
* now loop through error collection because
* Get_spreadsheet_interface flushed and synchronized
* the automation queue !
LOOP AT errors.
CALL METHOD errors->raise_message
EXPORTING type = ‘E’.
ENDLOOP.
initialized = ‘X’.
ENDMODULE. “ CREATE_BASIC_OBJECTS OUTPUT
l 打开excel模板示例代码如下：
METHODS: open_excel_template IMPORTING im_clsnam TYPE sbdst_classname im_clstyp TYPE sbdst_classtype im_objkey TYPE sbdst_object_key im_desc TYPE char255, “打开报表模板
*———————————————-*
* METHOD open_excel_template *
* 打开excel模板
*———————————————-*
METHOD open_excel_template.
DATA: locint_signature TYPE sbdst_signature,
locint_uris TYPE sbdst_uri,
locwa_signature LIKE LINE OF locint_signature,
locwa_uris LIKE LINE OF locint_uris.
* Create object for cl_bds_document_set
CREATE OBJECT r_document.
* Get Document with URL
locwa_signature-prop_name = ‘DESCRIPTION’.
* Description of the table template in OAOR
locwa_signature-prop_value = im_desc.
APPEND locwa_signature TO locint_signature.
CALL METHOD r_document->get_with_url
EXPORTING
classname = im_clsnam
classtype = im_clstyp
object_key = im_objkey
CHANGING
uris = locint_uris
signature = locint_signature
EXCEPTIONS
nothing_found = 1
error_kpro = 2
internal_error = 3
parameter_error = 4
not_authorized = 5
not_allowed = 6.
IF sy-subrc NE 0.
MESSAGE ‘Error Retrieving Document’ TYPE ‘E’.
ENDIF.
* Create container control
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = r_control
error = r_error.
IF r_error->has_failed = ‘X’.
CALL METHOD c_oi_errors=>raise_message
EXPORTING
type = ‘E’.
ENDIF.
* Initialize Custom Control
CREATE OBJECT r_container
EXPORTING
container_name = ‘TAB_EXCEL’. “Custom Control Name
CALL METHOD r_control->init_control
EXPORTING
r3_application_name = ‘EXCEL INPLACE BDS’
inplace_enabled = abap_false
inplace_scroll_documents = abap_true
parent = r_container
IMPORTING
error = r_error.
IF r_error->has_failed = ‘X’.
CALL METHOD c_oi_errors=>raise_message
EXPORTING
type = ‘E’.
ENDIF.
* Create object for cl_bds_document_set
CREATE OBJECT r_document.
READ TABLE locint_uris INTO locwa_uris INDEX 1.
CALL METHOD r_control->get_document_proxy
EXPORTING
document_type = ‘Excel.Sheet’
IMPORTING
document_proxy = r_proxy
error = r_error.
IF r_error->has_failed = ‘X’.
CALL METHOD c_oi_errors=>raise_message
EXPORTING
type = ‘E’.
ENDIF.
* Open Document
CALL METHOD r_proxy->open_document
EXPORTING
document_url = locwa_uris-uri
open_inplace = abap_false
protect_document = abap_false “Protect Document initially
IMPORTING
error = r_error.
IF r_error->has_failed = ‘X’.
CALL METHOD c_oi_errors=>raise_message
EXPORTING
type = ‘E’.
ENDIF.
* Get Excel Interface
CALL METHOD r_proxy->get_spreadsheet_interface
IMPORTING
sheet_interface = r_excel
error = r_error.
IF r_error->has_failed = ‘X’.
CALL METHOD c_oi_errors=>raise_message
EXPORTING
type = ‘E’.
ENDIF.
CALL METHOD r_proxy->get_spreadsheet_interface
IMPORTING
sheet_interface = r_handle.
ENDMETHOD.
ENDCLASS.
l 逐个CELL输入数据的示例代码如下:
fill_cell IMPORTING im_x TYPE i
im_y TYPE i
im_value TYPE char255, “填充单元格
———————————————-*
* METHOD fill_cell *
* 向excel的单元格中插入数据
*———————————————-*
METHOD fill_cell.
DATA: lv_columns TYPE i,
lv_rows TYPE i.
lv_columns = 1.
lv_rows = 1.
CALL METHOD r_handle->insert_range_dim
EXPORTING
NAME = ‘cell’
NO_FLUSH = ‘X’
TOP = im_x
LEFT = im_y
ROWS = lv_rows
COLUMNS = lv_columns
IMPORTING
error = r_error.
DATA: lt_ran TYPE soi_range_list,
lt_cont TYPE soi_generic_table,
la_ran LIKE LINE OF lt_ran,
la_cont LIKE LINE OF lt_cont.
la_ran-name = ‘cell’.
la_ran-columns = lv_columns.
la_ran-rows = lv_rows.
APPEND la_ran TO lt_ran.
la_cont-column = 1.
la_cont-row = 1.
la_cont-value = im_value.
APPEND la_cont TO lt_cont.
CALL METHOD r_handle->set_ranges_data
EXPORTING ranges = lt_ran
contents = lt_cont
IMPORTING
error = r_error.
CALL METHOD r_handle->fit_widest
EXPORTING
name = space
no_flush = ‘X’.
ENDMETHOD.
