use tcode OAOR to place the excel template.
***INCLUDE ZGEN_EXCEL .
TYPE-POOLS: soi.
TYPES : type_doc(64),format_amount(18).
DATA: tranges TYPE soi_range_list,
tcontents TYPE soi_generic_table,
setranges TYPE soi_full_range_table,
rangesdef TYPE soi_dimension_table,
tformat TYPE soi_format_table,
tcell TYPE soi_cell_table.
DATA: struc_ranges TYPE soi_range_item,
struc_generic TYPE soi_generic_item,
struc_setrange TYPE soi_full_range_item,
struc_rangesdef TYPE soi_dimension_item,
struc_cell TYPE soi_cell_item.
*--- Object Instance Declaration ---
DATA: container TYPE REF TO cl_gui_custom_container.
DATA: control TYPE REF TO i_oi_container_control.
DATA: document TYPE REF TO i_oi_document_proxy.
DATA: excel TYPE REF TO i_oi_spreadsheet.
DATA: bds_doc TYPE REF TO cl_bds_document_set.
DATA: link_server TYPE REF TO i_oi_link_server.
*--- Other Objects/Variable Declaration ---
DATA: no_flush,
error TYPE REF TO i_oi_error,
retcode(256) TYPE c,
version(64) TYPE c.
DATA: document_type(80) TYPE c VALUE soi_doctype_word_document,
document_format(6) TYPE c,
doc_url TYPE bapiuri-uri, "MSDocument URL Address
pic_url TYPE bapiuri-uri. "Logo URL Address
* Error Handling after call a method
CLASS c_oi_errors DEFINITION LOAD.
*---------------------------------------------------------------------*
* FORM select_new_sheet *
*---------------------------------------------------------------------*
FORM select_new_sheet USING p_sheet_name.
CALL METHOD excel->select_sheet
EXPORTING name = p_sheet_name
no_flush = no_flush
IMPORTING error = error
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
CALL METHOD excel->set_selection
EXPORTING
left = 2
top = 2
rows = 4
columns = 1
no_flush = no_flush
updating = -1
IMPORTING error = error
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
ENDFORM. " select_new_sheet
*---------------------------------------------------------------------*
* FORM select_document *
*---------------------------------------------------------------------*
FORM select_document USING nclass TYPE type_doc
objkey TYPE type_doc
doc_id TYPE type_doc
CHANGING adr_url TYPE bapiuri-uri.
* Tables and WAs:
DATA: doc_signature TYPE sbdst_signature,
wa_doc_signature LIKE LINE OF doc_signature,
doc_components TYPE sbdst_components,
wa_doc_components LIKE LINE OF doc_components,
doc_uris TYPE sbdst_uri,
wa_doc_uris LIKE LINE OF doc_uris.
* IDs:
DATA: doc_classname TYPE sbdst_classname," VALUE 'ZCO_IMR',
doc_classtype TYPE sbdst_classtype VALUE 'OT',
doc_object_key TYPE sbdst_object_key," VALUE 'WPL',
doc_mimetype LIKE bapicompon-mimetype.
*----------------------------------------------------------------------
CLEAR: wa_doc_signature, wa_doc_components, wa_doc_uris.
REFRESH: doc_signature, doc_components, doc_uris.
doc_classname = nclass.
doc_object_key = objkey.
wa_doc_signature-prop_name = 'DESCRIPTION'.
wa_doc_signature-prop_value = doc_id.
APPEND wa_doc_signature TO doc_signature.
* Availability of document checked
CALL METHOD bds_doc->get_info
EXPORTING classname = doc_classname
classtype = doc_classtype
object_key = doc_object_key
client = sy-mandt
CHANGING components = doc_components
signature = doc_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 AND sy-subrc NE 1.
"Error in the Business Document Service (BDS)
MESSAGE e016(demoofficeintegratio).
ENDIF.
IF sy-subrc = 1.
MESSAGE e017(demoofficeintegratio).
"There are no documents that meet the search criteria
ENDIF.
* Get URL address
CALL METHOD bds_doc->get_with_url
EXPORTING classname = doc_classname
classtype = doc_classtype
object_key = doc_object_key
CHANGING uris = doc_uris
signature = doc_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 AND sy-subrc NE 1.
MESSAGE e016(demoofficeintegratio).
ENDIF.
IF sy-subrc = 1.
MESSAGE e017(demoofficeintegratio).
ENDIF.
READ TABLE doc_components INTO wa_doc_components INDEX 1.
READ TABLE doc_uris INTO wa_doc_uris INDEX 1.
doc_mimetype = wa_doc_components-mimetype.
adr_url = wa_doc_uris-uri.
CASE doc_mimetype.
WHEN 'application/x-rtf' OR 'text/rtf'.
document_format = soi_docformat_rtf.
WHEN 'application/x-oleobject'.
document_format = soi_docformat_compound.
WHEN 'text/plain'.
document_format = soi_docformat_text.
WHEN OTHERS.
document_format = soi_docformat_native.
ENDCASE.
ENDFORM. " select_document
*---------------------------------------------------------------------*
* FORM formatting_rangesdef_tab *
*---------------------------------------------------------------------*
FORM formatting_rangesdef_tab.
LOOP AT tcontents INTO struc_generic.
struc_rangesdef-rows = 1.
struc_rangesdef-columns = 1.
struc_rangesdef-row = struc_generic-row.
struc_rangesdef-column = struc_generic-column.
struc_generic-row = '1'.
struc_generic-column = '1'.
MODIFY tcontents FROM struc_generic INDEX sy-tabix.
APPEND struc_rangesdef TO rangesdef.
ENDLOOP.
ENDFORM.
*---------------------------------------------------------------------*
* FORM set_data *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
FORM set_data.
CALL METHOD excel->set_ranges_data
EXPORTING
ranges = tranges
contents = tcontents
rangesdef = rangesdef
updating = -1
no_flush = no_flush
IMPORTING error = error
retcode = retcode.
CALL METHOD excel->screen_update
EXPORTING updating = 'X'
no_flush = no_flush
IMPORTING error = error
retcode = retcode.
CLEAR : tcontents, rangesdef.
REFRESH : tcontents, rangesdef.
ENDFORM. " set_data
*---------------------------------------------------------------------*
* FORM prog_indicator *
*---------------------------------------------------------------------*
FORM prog_indicator_excel USING t_pers t_text.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
percentage = t_pers
text = t_text.
ENDFORM.
*---------------------------------------------------------------------*
* FORM format_amount *
*---------------------------------------------------------------------*
FORM format_amount USING p_amount
CHANGING axx TYPE format_amount.
DATA : p_val TYPE p DECIMALS 2.
p_val = p_amount.
IF p_val < 0.
p_val = p_val * -1.
WRITE p_val TO axx CURRENCY 'IDR'.
CONCATENATE '-' axx INTO axx.
ELSE.
WRITE p_val TO axx CURRENCY 'IDR'.
ENDIF.
CONDENSE axx NO-GAPS.
DO 5 TIMES.
REPLACE '.' WITH '' INTO axx.
ENDDO.
CONDENSE axx NO-GAPS.
ENDFORM.
*---------------------------------------------------------------------*
* FORM format_amount_coma *
*---------------------------------------------------------------------*
FORM format_amount_coma USING p_amount
CHANGING axx TYPE format_amount.
DATA : p_val TYPE p DECIMALS 2.
p_val = p_amount.
IF p_val < 0.
p_val = p_val * -1.
WRITE p_val TO axx CURRENCY 'USD'.
CONCATENATE '-' axx INTO axx.
ELSE.
WRITE p_val TO axx CURRENCY 'USD'.
ENDIF.
CONDENSE axx NO-GAPS.
DO 5 TIMES.
REPLACE '.' WITH '' INTO axx.
ENDDO.
CONDENSE axx NO-GAPS.
ENDFORM.
*---------------------------------------------------------------------*
* FORM format_origin *
*---------------------------------------------------------------------*
FORM format_origin USING p_amount CHANGING axx TYPE format_amount.
DATA : p_val TYPE p DECIMALS 2.
p_val = p_amount.
IF p_val < 0.
p_val = p_val * -1.
WRITE p_val TO axx.
CONCATENATE '-' axx INTO axx.
ELSE.
WRITE p_val TO axx.
ENDIF.
CONDENSE axx NO-GAPS.
DO 5 TIMES.
REPLACE '.' WITH '' INTO axx.
ENDDO.
CONDENSE axx NO-GAPS.
ENDFORM.
*---------------------------------------------------------------------*
* FORM f_format_amt_excel *
*---------------------------------------------------------------------*
FORM f_format_amt_excel USING pu_dmbt pu_curr
CHANGING pc_dmbt TYPE format_amount.
DATA : lv_dmbt TYPE p DECIMALS 2.
lv_dmbt = pu_dmbt.
IF lv_dmbt < 0.
lv_dmbt = lv_dmbt * -1.
WRITE lv_dmbt TO pc_dmbt CURRENCY pu_curr.
CONCATENATE '-' pc_dmbt INTO pc_dmbt.
ELSE.
WRITE lv_dmbt TO pc_dmbt CURRENCY pu_curr.
ENDIF.
CONDENSE pc_dmbt NO-GAPS.
DO 5 TIMES.
REPLACE '.' WITH '' INTO pc_dmbt.
ENDDO.
CONDENSE pc_dmbt NO-GAPS.
ENDFORM.
*---------------------------------------------------------------------*
* FORM format_quantity *
*---------------------------------------------------------------------*
FORM format_quantity USING pu_meng
CHANGING axx TYPE format_amount.
DATA : p_val TYPE p DECIMALS 3.
p_val = pu_meng.
IF p_val < 0.
p_val = p_val * -1.
WRITE p_val TO axx.
CONCATENATE '-' axx INTO axx.
ELSE.
WRITE p_val TO axx.
ENDIF.
CONDENSE axx NO-GAPS.
DO 5 TIMES.
REPLACE '.' WITH '' INTO axx.
ENDDO.
CONDENSE axx NO-GAPS.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form MODIFY_to_cell
*&---------------------------------------------------------------------*
FORM modify_to_cell USING p_cols p_rows
p_value.
struc_generic-column = p_cols.
struc_generic-row = p_rows.
struc_generic-value = p_value.
APPEND struc_generic TO tcontents.
ENDFORM. " MODIFY_to_cell
*---------------------------------------------------------------------*
* FORM modify_to_cell_format *
*---------------------------------------------------------------------*
FORM modify_to_cell_format USING p_typ p_cols p_rows
p_value.
DATA : lvxdmbt(18).
IF p_typ = 'C'.
PERFORM format_amount USING p_value
CHANGING lvxdmbt.
ELSEIF p_typ = 'D'.
PERFORM format_amount_coma USING p_value
CHANGING lvxdmbt.
ELSEIF p_typ = 'Q'.
PERFORM format_quantity USING p_value
CHANGING lvxdmbt.
ELSE.
PERFORM format_origin USING p_value CHANGING lvxdmbt.
ENDIF.
struc_generic-column = p_cols.
struc_generic-row = p_rows.
struc_generic-value = lvxdmbt.
APPEND struc_generic TO tcontents.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form insert_logo
*&---------------------------------------------------------------------*
FORM insert_logo.
CALL METHOD document->paste_clipboard
EXPORTING
no_flush = no_flush
IMPORTING
error = error
retcode = retcode.
ENDFORM. " insert_logo
*---------------------------------------------------------------------*
* FORM insert_logo_posxy *
*---------------------------------------------------------------------*
FORM insert_logo_posxy USING p_left p_top p_rows p_colm.
CALL METHOD excel->set_selection
EXPORTING
left = p_left
top = p_top
rows = p_rows
columns = p_colm
no_flush = no_flush
updating = -1
IMPORTING error = error
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
CALL METHOD document->paste_clipboard
EXPORTING
no_flush = no_flush
IMPORTING
error = error
retcode = retcode.
ENDFORM. " insert_logo
*---------------------------------------------------------------------*
* FORM connect_to_excel *
*---------------------------------------------------------------------*
FORM connect_to_excel USING p_place p_class p_obj p_doc
p_logo p_nlogo TYPE type_doc.
PERFORM prog_indicator_excel USING 0 'Link SAP to Excel..'.
IF bds_doc IS INITIAL.
CREATE OBJECT bds_doc.
ENDIF.
IF container IS INITIAL.
CREATE OBJECT container
EXPORTING
container_name = 'CONTAINER'.
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING control = control
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
CALL METHOD control->init_control
EXPORTING r3_application_name = p_doc
inplace_enabled = 'X'
parent = container
IMPORTING retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
* Excel
PERFORM select_document USING p_class p_obj p_doc
CHANGING doc_url.
* logo
IF p_logo = 'X'.
PERFORM select_document USING p_class p_obj p_nlogo
CHANGING pic_url.
ENDIF.
CALL METHOD control->get_document_proxy
EXPORTING
document_format = document_format
document_type = document_type
no_flush = no_flush
IMPORTING
document_proxy = document
error = error
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
* Open link server
CALL METHOD control->get_link_server
EXPORTING
no_flush = no_flush
IMPORTING
error = error
link_server = link_server
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
CALL METHOD link_server->start_link_server
EXPORTING
no_flush = no_flush
server_name_suffix = 'SERVER_LINK'
IMPORTING
error = error
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
IF p_logo = 'X'.
* Open logo to buffer
CALL METHOD link_server->add_binary_item
EXPORTING
item_name = p_nlogo
no_flush = no_flush
table_url = pic_url
IMPORTING
error = error
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
* Copy logo to buffer
CALL METHOD link_server->copy_link_item_to_clipboard
EXPORTING
item_name = p_nlogo
no_flush = no_flush
IMPORTING
error = error
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
ENDIF.
* Open the Office document
* ‘X’ : MS Word will displayed in a custom container area
* Blank : MS Word will displayed in a new window.
CALL METHOD document->open_document
EXPORTING document_url = doc_url
open_inplace = p_place
IMPORTING retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
CALL METHOD document->get_spreadsheet_interface
EXPORTING no_flush = no_flush
IMPORTING sheet_interface = excel
error = error
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
PERFORM prog_indicator_excel USING 100 'Link Complate!'.
ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
* FORM close_excel_link *
*---------------------------------------------------------------------*
FORM close_excel_link.
* Close/release link server object
CALL METHOD link_server->stop_link_server
EXPORTING
no_flush = no_flush
IMPORTING
error = error
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
CLEAR bds_doc.
CLEAR container.
MESSAGE i016(zcomsg01).
ENDFORM.
*---------------------------------------------------------------------*
* FORM run_macro *
*---------------------------------------------------------------------*
FORM run_macro USING p_macro_string.
CALL METHOD document->execute_macro
EXPORTING macro_string = p_macro_string
no_flush = no_flush
script_name = ''
IMPORTING error = error
retcode = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
ENDFORM.
*----------------------------------------------------------------------
FORM RUN_MACRO_PARAM USING P_MACRO_STRING
P_PARAM_COUNT
P_PARAM01 P_PARAM02 P_PARAM03 P_PARAM04
P_PARAM05 P_PARAM06 P_PARAM07 P_PARAM08
P_PARAM09 P_PARAM10 P_PARAM11 P_PARAM12.
CALL METHOD DOCUMENT->EXECUTE_MACRO
EXPORTING MACRO_STRING = P_MACRO_STRING
NO_FLUSH = NO_FLUSH
SCRIPT_NAME = ''
PARAM_COUNT = P_PARAM_COUNT
PARAM1 = P_PARAM01
PARAM2 = P_PARAM02
PARAM3 = P_PARAM03
PARAM4 = P_PARAM04
PARAM5 = P_PARAM05
PARAM6 = P_PARAM06
PARAM7 = P_PARAM07
PARAM8 = P_PARAM08
PARAM9 = P_PARAM09
PARAM10 = P_PARAM10
PARAM11 = P_PARAM11
PARAM12 = P_PARAM12
IMPORTING ERROR = ERROR
RETCODE = RETCODE.
CALL METHOD C_OI_ERRORS=>SHOW_MESSAGE EXPORTING TYPE = 'E'.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form f_header_std_excel
*&---------------------------------------------------------------------*
FORM f_header_std_excel USING pu_xpos pu_ypos
pu_bukrs pu_spmon pu_title.
DATA : lvtxt(40),lvxpos(4) TYPE c,lvypos(4) TYPE c.
SELECT SINGLE butxt INTO lvtxt FROM t001
WHERE bukrs = pu_bukrs.
CONCATENATE 'COMPANY : PT.' lvtxt INTO lvtxt SEPARATED BY space.
TRANSLATE lvtxt TO UPPER CASE.
lvxpos = pu_xpos. lvypos = pu_ypos.
PERFORM modify_to_cell USING lvxpos lvypos lvtxt.
lvypos = lvypos + 1.
PERFORM modify_to_cell USING lvxpos lvypos pu_title.
PERFORM f_get_month_excel USING pu_spmon+4(2) CHANGING lvtxt.
CONCATENATE 'FOR THE MONTH :' lvtxt ',' pu_spmon+0(4)
INTO lvtxt SEPARATED BY space.
lvypos = lvypos + 1.
PERFORM modify_to_cell USING lvxpos lvypos lvtxt.
ENDFORM. " f_header_std_excel
*---------------------------------------------------------------------*
* FORM f_get_month_EXCEL *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> PMONAT *
* --> PMOTXT *
*---------------------------------------------------------------------*
FORM f_get_month_excel USING pmonat CHANGING pmotxt.
CASE pmonat.
WHEN 1. pmotxt = 'JANUARY'.
WHEN 2. pmotxt = 'FEBRUARY'.
WHEN 3. pmotxt = 'MARCH'.
WHEN 4. pmotxt = 'APRIL'.
WHEN 5. pmotxt = 'MAY'.
WHEN 6. pmotxt = 'JUNE'.
WHEN 7. pmotxt = 'JULY'.
WHEN 8. pmotxt = 'AUGUST'.
WHEN 9. pmotxt = 'SEPTEMBER'.
WHEN 10. pmotxt = 'OCTOBER'.
WHEN 11. pmotxt = 'NOVEMBER'.
WHEN 12. pmotxt = 'DECEMBER'.
ENDCASE.
ENDFORM.
No comments:
Post a Comment