
Dear abapers and warriors of the humankind,
I'm proposing you a solution to print Product Orders through Excel instead of smartforms or others stuff. This is valid from Excel 2007 and up.
It depends on your needs but I think sapscripts and smartforms have a very low adaptable level to the company.
Standard sapscript is PSFC_STD_LAYOUT. Go and see it if you want :smile:
I would like to get a feedback too, to prove if this solution could exist or it has to be thrown from the window
This is a brief guide throughout the "final solution".
Let's start from the beginning.
If you're looking for smartforms: no way :smile: try to design a print on a smartform with boxes , lines, different width, thickness, colors, etc..
Hell would be better I suppose :smile: Try and let me know.
Instead you can try to do the following.
- Transaction OPK8 (mantain print control production orders)
From there you define your Lists, spool parameters for each user, print programs for each list, etc.
Start from an order type and a list. Example : LK03 Component-related list 03
Assign a ZPRINTORDR program and go on.
- Print Report ZPRINTORDR
Get order data using data from CO_PRINT_IMPORT_DATA ( read logic inside this function module).
Create 2 or more tables with data you need from Header, Items and other Stuff like Long Text;
This tables will be passed to the excel sheet to feed header data, positions data and others.
- Create an Excel template file
Design an Excel template file with all the data you need. Think about worksheets you need for Data Retrieval. For example:
As you can see you'll have HeaderData , ItemData. In these two worksheets you'll have the same columns of your internal table used in ZPRINTORDR.
There you'll pass data to feed Excel Testata Sheet (the final sheet that will be printed)
Define a Print Area in excel for all sheets you'll want to be printed.
This template will be upload in SAP through Business Document Service (BDS) .
Pay attention that this template will have inside VB code. I'll put some Instructions after...
- Transaction OAOR
Business Document Navigator. There you can upload a template you can reference in all your programs.
In our case we'll use Class name : SOFFICEINTEGRATION and Class type OT.
We don't need to create a new one. Search on internet for How to upload in OAOR a template excel.
Detail of this template excel will be used in program ZPRINTORDR. You'll need:
sbdst_classname VALUE 'SOFFICEINTEGRATION';
sbdst_classtype VALUE 'OT';
sbdst_object_key VALUE 'SOFFICEINTEGRATION',
description_char255 VALUE ''YOUR_TEMPLATE_NAME'
Pay attention when you download, upload template into Business Document Repository cause you'll have to mantain versions on local computer or directly on Business Document Service.
- Custom Function for Excel data manage;
My suggest is to create a custom function to be launched in a New Task, with no interference with standard process of saving/registering print order from CO01/CO02/CO03.
This function module will receive:
CLSNAME Business Document Service: class name;
CLSTYPE Business Document Service: class type;
OBJKEY Business Document Service: object key;
DESCR BDS description;
PRINTER Printer output name;
VISIBLE If you want to see or not see excel working in background;
There will be 2 tables:
TAB_HEADER_DATA;
TAB_ITEM_DATA.
Code section.
Three phases of: open_document, feed_table_data, print_document.
Define some structure you'll need:
Phase Open Document
To manage data in Excel you'll have to create a container control. You can manage messages too, in case you'll want to inform user of every error.
* Create container control
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = gr_control
retcode = gwf_retcode.
IF gwf_retcode NE c_oi_errors=>ret_ok.
PERFORM store_messages USING 'E' sy-uname ' ' ' ' ' ' '011'.
EXIT.
ENDIF.
* Initialize Custom Control
CREATE OBJECT gr_container
EXPORTING
container_name = 'CONTROL'. "Custom Control Name
IF sy-subrc NE 0.
PERFORM store_messages USING 'E' sy-uname ' ' ' ' ' ' '011'.
EXIT.
ENDIF.
* Set container visible or not visible (look function module parameter )
CALL METHOD gr_container->SET_VISIBLE
EXPORTING
visible = gr_visible_container. "X for visible
* Init control
CALL METHOD gr_control->init_control
EXPORTING
r3_application_name = 'ANYNAME_EXCEL'
inplace_enabled = GR_INPLACE "External or Internal SAP " abap_false IF visible
inplace_scroll_documents = GR_INPLACE "External or Internal SAP " abap_false IF visible
parent = gr_container
NO_FLUSH = C_X "constant
REGISTER_ON_CLOSE_EVENT = C_X
REGISTER_ON_CUSTOM_EVENT = C_X
IMPORTING
retcode = gwf_retcode.
IF gwf_retcode NE c_oi_errors=>ret_ok.
PERFORM store_messages USING 'E' sy-uname ' ' ' ' ' ' '011'.
EXIT.
ENDIF.
* Retrieve template from Business Document Service
* Create object for cl_bds_document_set
CREATE OBJECT gr_document.
IF sy-subrc NE 0.
PERFORM store_messages USING 'E' sy-uname ' ' ' ' ' ' '011'.
EXIT.
ENDIF.
* Get Document with URL
locwa_signature-prop_name = 'DESCRIPTION'.
* Description of the table template in OAOR
locwa_signature-prop_value = p_descr. "Function Input parameter
APPEND locwa_signature TO locint_signature.
CALL METHOD gr_document->get_with_url
EXPORTING
classname = P_CLSNAM "Function input parameter
classtype = P_CLSTYP "Function input parameter
object_key = P_OBJKEY "Function input parameter
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.
PERFORM store_messages USING 'E' P_CLSNAM P_CLSTYP P_OBJKEY ' ' '012'.
EXIT.
ENDIF.
* Get Proxy to manage Excel application
READ TABLE locint_uris INTO locwa_uris INDEX 1.
CALL METHOD gr_control->get_document_proxy
EXPORTING
document_type = 'Excel.Sheet'
NO_FLUSH = C_X
IMPORTING
document_proxy = gr_proxy
retcode = gwf_retcode.
IF gwf_retcode NE c_oi_errors=>ret_ok.
PERFORM store_messages USING 'E' sy-uname ' ' ' ' ' ' '011'.
EXIT.
ENDIF.
** Open Document
CALL METHOD gr_proxy->open_document
EXPORTING
document_title = 'ANYNAME_EXCEL'
document_url = locwa_uris-uri
open_inplace = GR_INPLACE "Open document inside or outside SAP
protect_document = abap_false "Protect Document initially
NO_FLUSH = C_X
* STARTUP_MACRO = gr_macro "<- if you need any particular macro to be launched on startup
IMPORTING
retcode = gwf_retcode.
IF gwf_retcode NE c_oi_errors=>ret_ok.
PERFORM store_messages USING 'E' sy-uname ' ' ' ' ' ' '011'.
EXIT.
ENDIF.
**
* Get Excel Interface THIS is important to get Excel sheet_interface
CALL METHOD gr_proxy->get_spreadsheet_interface
IMPORTING
sheet_interface = gr_excel
retcode = gwf_retcode.
IF gwf_retcode NE c_oi_errors=>ret_ok.
PERFORM store_messages USING 'E' sy-uname ' ' ' ' ' ' '011'.
EXIT.
ENDIF.
Phase Feed_table_data
CALL METHOD gr_excel->SELECT_SHEET
EXPORTING
NAME = 'HeaderData'.
IF sy-subrc NE 0.
f_error = C_X.
ELSE.
" Go on -> "It's important to register exceptions
ENDIF.
REFRESH locint_fields.
* Get field attributes of the table to be displayed
CALL FUNCTION 'DP_GET_FIELDS_FROM_TABLE'
TABLES
data = gt_list_xlshead "<= Function input table for header data
fields = locint_fields "<= here this function module will put info on fields (columns to fill excel sheet)
EXCEPTIONS
dp_invalid_table = 1
OTHERS = 2.
IF sy-subrc <> 0.
f_error = C_X.
ENDIF.
* Define range on excel sheet -> create a form for this, will be useful
* Select area for entries to be displayed
l_top, l_left represent where to start:
example
l_top = 2; l_left = 1 means start from 2nd row 1st column of excel
That's important to define area where data will be put on excel
l_row, l_column represent the extreme range for data.
l_row can be obtained with a DESCRIBE TABLE LINES of gt_list_xlshead
l_col can be obtained with a DESCRIBE TABLE LINES of locint_fields
l_range must be an ID for methods of class gr_excel to identify this area
Put for example HEAD for header and COMPN for items
CALL METHOD gr_excel->set_selection
EXPORTING
top = l_top
left = l_left
rows = l_row
columns = l_column.
* Define Range
CALL METHOD gr_excel->insert_range
EXPORTING
name = l_range
rows = l_row
columns = l_column
IMPORTING
error = gr_error.
IF gr_error->has_failed = abap_true.
CALL METHOD gr_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
* finally put data on sheet HeaderData
* Insert the table entries into Excel
CALL METHOD gr_excel->insert_one_table
EXPORTING
fields_table = locint_fields[] "Defn of fields
data_table = gt_list_xlshead[] "Data
rangename = 'HEAD' "Range Name
IMPORTING
error = gr_error
retcode = gwf_retcode.
IF sy-subrc NE 0.
f_error = C_X.
ENDIF.
**** => DO THE SAME FOR COMPN and Other sheet you'll want to feed
* At this point you'll have to manage a MACRO on excel. VB code is better than ABAP for this solution.
* Macro will do all the stuff silently on our sheet.
In this case I'll call a MAIN macro on my template
CALL METHOD gr_proxy->EXECUTE_MACRO
EXPORTING
MACRO_STRING = 'MainModule.Main' "<= pay attention Full Name required <NameModule>.<NameMacro>
IMPORTING
ERROR = gr_error
RETCODE = gwf_retcode.
* CHANGING
* ERROR_STRING =
* RETVALUE =.
ELSE.
PERFORM store_messages USING 'E' sy-uname ' ' ' ' ' ' '009'.
ENDIF.
**** AT THIS POINT SAP pass control to EXCEL
You must define RANGE NAMES in excel to let macro know where cell start and ends. For example to count records in a sheet or to find columns, data, etc..
Define then START_HEAD range for the first CELL VALUE in Header Data
In macro you'll can manage data loop to your needs. In ourcase we had to put some images (icons of dangerous, etc) and copya rows for items in Testata sheet.
Do the same thing for components and just call a routine to copy data from a template_row to the others
This will copy your template row already defined with Formulas in Testata sheet.
Data on your template_row will be already mantain for the FIRST row of ItemData sheet.
With a copy routine you'll go put all others records.
Formula and other stuff will be copied
Formula are useful for your main need (hide empty data, put in a correct output format, reference data between sheets, etc)
I've a lot of stuff to show, like routine to put breakpoints dinamically, or to adapting dinamically cell dimension for Long Text passed from SAP, dinamically set printer with all problems related,
but this document would be very long.
It took quite 1 month to define every detail and would be difficult to read all this stuff.
So furthers details can be defined later.
After defined Macro in Excel Template the final step is Print your document.
Final steps are from SAP if you want to hide sheets or other do following:
CALL METHOD gr_excel->get_sheets
* EXPORTING
* NO_FLUSH = ' '
* UPDATING = -1
IMPORTING
SHEETS = loc_sheets
ERROR = gr_error
RETCODE = gwf_retcode.
LOOP AT loc_sheets INTO loc_wa_sheets. <= you'll have sheet names
With names you can pass a string with a list to a macro in excel and let print sheet from excel.
The print logic in VB is quite complicated. If you'll need I'll pass you.
Final closing instructions:
CALL METHOD gr_proxy->close_document
* EXPORTING
* DO_SAVE = ' '
* NO_FLUSH = ' '
IMPORTING
* ERROR = ERROR
* HAS_CHANGED = HAS_CHANGED
retcode = gwf_retcode.
IF gwf_retcode NE c_oi_errors=>ret_ok.
PERFORM store_messages USING 'E' sy-uname ' ' ' ' ' ' '013'.
ELSE.
CLEAR gwf_retcode.
CALL METHOD gr_proxy->release_document
* EXPORTING
* NO_FLUSH = ' '
IMPORTING
* ERROR = ERROR
retcode = gwf_retcode.
IF gwf_retcode NE c_oi_errors=>ret_ok.
PERFORM store_messages USING 'E' sy-uname ' ' ' ' ' ' '013'.
ENDIF.
ENDIF.
FREE gr_excel.
FREE gr_proxy.
FREE gr_control.
FREE gr_container.
IF gr_error_set EQ C_X.
PERFORM show_messages.
ENDIF.
That's all folks. This document want to be only a Starting point for the development of ideas.
It's written quite bad but it's free :wink: :smile:
I hope you enjoy on how to comunicate between SAP and Excel / Office without spend a lot of money and time.
Free your code and you'll free yourself oneday ,perhaps :smile:
Bye
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 |