Creating Excel sheets in background [slightly OT]
Bibby, David
david.bibby at linklaters.com
Tue Nov 7 09:01:00 EST 2006
Mike,
This report is scheduled to run once a week in background and e-mails
the results in XLS (tab delimited) format.
I set the doc type when bulding the packing list details, See further
down.
Basically the structure of my program follows this:
PERFORM get_result_data.
PERFORM get_actors.
IF NOT gt_recipients IS INITIAL.
PERFORM build_email_content.
PERFORM build_file_content.
PERFORM build_document_data.
PERFORM build_packing_list.
PERFORM send_email.
ELSE. "Do not continue
MESSAGE i010(ad) WITH text-004.
ENDIF.
Once I've got the data I want to export to Excel in PERFORM
get_result_data
I read my rule in PERFORM get_actors.
FORM get_actors .
DATA:
lt_container TYPE TABLE OF swcont,
ls_container TYPE swcont,
lt_actor TYPE TABLE OF swhactor,
ls_actor LIKE swhactor.
MOVE 'REPORTNAMERECIPIENTS' TO ls_container-element.
MOVE 'ZMC_NEW_CLIENTS' TO ls_container-value.
MOVE '040' TO ls_container-elemlength.
MOVE 'C' TO ls_container-type.
APPEND ls_container TO lt_container.
CALL FUNCTION 'RH_GET_ACTORS'
EXPORTING
act_object = 'AC90000144'
TABLES
actor_container = lt_container
actor_tab = lt_actor
EXCEPTIONS
no_active_plvar = 1
no_actor_found = 2
exception_of_role_raised = 3
no_valid_agent_determined = 4
OTHERS = 5.
* Filter out any not added to the role as users e.g. US
LOOP AT lt_actor INTO ls_actor WHERE otype EQ 'US'.
CONCATENATE ls_actor-objid '@linklaters.com' INTO
gs_recipients-receiver.
gs_recipients-rec_type = 'U'.
gs_recipients-com_type = 'INT'.
gs_recipients-notif_ndel = 'X'.
APPEND gs_recipients TO gt_recipients.
ENDLOOP.
ENDFORM. " get_actors
Then I build the text in the e-mail:
*&---------------------------------------------------------------------*
*& Form build_email_content
*&---------------------------------------------------------------------*
FORM build_email_content .
MOVE 'Please find attached a recent New Client Report' TO
gs_email_body-line.
APPEND gs_email_body TO gt_email_body.
MOVE 'This is a system generated mail, please do not reply.' TO
gs_email_body-line.
APPEND gs_email_body TO gt_email_body.
ENDFORM. " build_email_content
Then the file contents, first the header columns, then the contents from
my internal table put it in a string (tab separated) then convert the
string to an internal table.
*&---------------------------------------------------------------------*
*& Form build_file_content
*&---------------------------------------------------------------------*
FORM build_file_content .
DATA: lv_mrp_name(81) TYPE c,
lv_mtp_name(81) TYPE c,
lv_mrp_locn(81) TYPE c,
lv_mtp_locn(81) TYPE c.
CONCATENATE gv_tmp_result_string
'Client Number'
'Client Name'
'Client Location'
'CUP Name'
'GUP Name'
'DUP Name'
'IP Name'
'Cup Number'
'GUP Number'
'DUP Number'
'IP Number'
'DUNS Number'
'SIC Code'
'SIC Description'
'MRP Name'
'MRP Location'
'1st Approving Partner Name'
'1st Approving Partner Location'
con_cret
INTO gv_tmp_result_string SEPARATED BY con_tab.
LOOP AT gt_result INTO gs_result.
CLEAR: lv_mrp_name, lv_mtp_name, lv_mrp_locn, lv_mtp_locn.
CONCATENATE gs_result-mrp_last gs_result-mrp_first INTO lv_mrp_name
SEPARATED BY ','.
CONCATENATE gs_result-mtp_last gs_result-mtp_first INTO lv_mtp_name
SEPARATED BY ','.
CONCATENATE gs_result-mrp_office1 gs_result-mrp_office2 INTO
lv_mrp_locn SEPARATED BY ','.
CONCATENATE gs_result-mtp_office1 gs_result-mtp_office2 INTO
lv_mtp_locn SEPARATED BY ','.
CONCATENATE gv_tmp_result_string
gs_result-partner
gs_result-name
gs_result-city1
gs_result-z_cup_name
gs_result-z_gup_name
gs_result-dup_name
gs_result-ip_name
gs_result-z_cup_number
gs_result-z_gup_number
gs_result-dup_number
gs_result-ip_number
gs_result-duns_number
gs_result-ind_sector
gs_result-text
lv_mrp_name
lv_mrp_locn
lv_mtp_name
lv_mtp_locn
con_cret
INTO gv_tmp_result_string SEPARATED BY con_tab.
ENDLOOP.
CALL FUNCTION 'SCMS_STRING_TO_FTEXT'
EXPORTING
text = gv_tmp_result_string
TABLES
ftext_tab = gt_file_contents.
ENDFORM. " build_file_content
Then I calculate the size and set the e-mail sensitivity:
*&---------------------------------------------------------------------*
*& Form build_document_data
*&---------------------------------------------------------------------*
FORM build_document_data .
DATA: lv_lines TYPE i.
gs_document_data-obj_name = 'SAPREPORT'.
gs_document_data-obj_descr = 'New Client Report'.
gs_document_data-obj_langu = sy-langu.
gs_document_data-sensitivty = 'P'. "Confidential
DESCRIBE TABLE gt_file_contents LINES lv_lines.
gs_document_data-doc_size = 255 * lv_lines.
ENDFORM. " build_document_data
Then the packing list:
*&---------------------------------------------------------------------*
*& Form build_packing_list
*&---------------------------------------------------------------------*
FORM build_packing_list .
*** Describe the body of the message
CLEAR gt_packing_list.
REFRESH gt_packing_list.
gs_packing_list-transf_bin = space.
gs_packing_list-head_start = 1.
gs_packing_list-head_num = 0.
gs_packing_list-body_start = 1.
DESCRIBE TABLE gt_file_contents LINES gs_packing_list-body_num.
gs_packing_list-doc_type = 'RAW'.
APPEND gs_packing_list TO gt_packing_list.
* Describe the attachment
CONCATENATE 'NewClientReport' p_dateto INTO gv_filename.
gs_packing_list-transf_bin = 'X'.
gs_packing_list-head_start = 1.
gs_packing_list-head_num = 1.
gs_packing_list-body_start = 1.
gs_packing_list-obj_descr = gv_filename.
gs_packing_list-doc_type = 'XLS'.
DESCRIBE TABLE gt_file_contents LINES gs_packing_list-body_num.
gs_packing_list-obj_name = gv_filename.
gs_packing_list-doc_size = gs_packing_list-body_num * 255.
APPEND gs_packing_list TO gt_packing_list.
ENDFORM. " build_packing_list
And finally send the e-mail
CALL FUNCTION 'SO_DOCUMENT_SEND_API1'
EXPORTING
document_data = gs_document_data
commit_work = 'X'
TABLES
packing_list = gt_packing_list
contents_bin = gt_file_contents
contents_txt = gt_email_body
receivers = gt_recipients
EXCEPTIONS
too_many_receivers = 1
document_not_sent = 2
document_type_not_exist = 3
operation_no_authorization = 4
parameter_error = 5
x_error = 6
enqueue_error = 7
OTHERS = 8.
Rgds
David
-----Original Message-----
From: sap-wug-bounces at mit.edu [mailto:sap-wug-bounces at mit.edu] On Behalf
Of Mike Pokraka
Sent: 03 November 2006 14:33
To: SAP Workflow Users' Group
Subject: RE: Creating Excel sheets in background [slightly OT]
Hi David,
That definitely sounds on target, I would be grateful for any hints.
Just the key bits where the file is created would be very helpful.
Thanks,
Mike
On Fri, November 3, 2006 12:28, Bibby, David wrote:
> Mike,
> Only just seen your mail but I recently had to write a program that
> runs in background and then e-mails the results in Excel format to a
> list of users (determines from a workflow rule).
>
> Not sure if it would help but if you want I can send you the relevant
> bits from the program.
> It's pretty basic i.e. no colour coding or multiple sheets but it
> might be a start for you.
> Rgds
> David
>
>
> -----Original Message-----
> From: sap-wug-bounces at mit.edu [mailto:sap-wug-bounces at mit.edu] On
> Behalf Of Mike Pokraka
> Sent: 03 November 2006 11:55
> To: SAP Workflow Users' Group
> Subject: RE: Creating Excel sheets in background [slightly OT]
>
> Hi Jocelyn, Kjetil & Andy,
>
> Thanks for your feedback on this. I'll rather write one reply to
> everyone to keep all info together.
>
> Kjetil: You'll be pleased to hear that your suggestion won't work. Why
> pleased? Because we don't have a .NOT team, and part of the reason it
> has to be in background is that the universe doesn't run on Windows
> (thankfully or we'd keep having to do the big bang thing all over
> again), we have a significant portion of mac users (and possibly some
> Linux).
>
> Andy: Yes, this is in many ways a unique environment. The FM's you
> mention are the ones that won't cut it in background - they require
> OLE and a SAPGUI session on a PC that has Excel installed.
>
> Jocelyn: Good idea, similar to our current best attempt at a hack -
> we're have some minor results using delimited files and using server
> cookies to change the mime types to get it to look like an Excel file
> when it's really a delimited file inside.
>
> Part of the original problem is that we send out PDF's, but some info
> is too complex for a PDF and needs to be in Excel. They even want
> formulas and multiple sheets - oh whilst we're at it could we format
> it with pretty colours and so on. Also playing with ideas of
> crowbar-ing data into a template file....
>
> Thanks for the input.
> Cheers,
> Mike
>
>
>
> On Fri, November 3, 2006 03:00, Dart, Jocelyn wrote:
>> Mike,
>> What if you just created a raw tab-delimited file and included
>> instructions in the message to suggest they convert to XLS on
>> download
> the attachment?
>>
>>
>> Regards,
>> Jocelyn Dart
>> Senior Consultant
>> SAP Australia Pty Ltd.
>
> (ridiculous disclaimer snipped)
>>
>> -----Original Message-----
>> From: sap-wug-bounces at mit.edu [mailto:sap-wug-bounces at mit.edu] On
>> Behalf
> Of Mike Pokraka
>> Sent: Friday, 03 November 2006 5:02 AM
>> To: sap-wug at mit.edu
>> Subject: Creating Excel sheets in background [slightly OT]
>>
>> Hi all,
>>
>> We have a requirement where a mail is sent out to a user with some
> info.
> Basic stuff, however the twist here is that they require this as an
> Excel
>> spreadsheet.
>>
>> Easy enough to do in dialog, but does anyonw know how to create an
>> Excel
> spreadsheet attachment in background? All methods we've lookied at
> come back to the use of OLE functions which require a GUI session of a
> user with Excel installed. This must be completely in background.
>>
>> I know this is on the far-flung borders of the workflow realm, but
>> seeing as we have some very knowledgeable people on this list I
>> figured I might
> throw the question out here.
>>
>> Any input appreciated.
>> Cheers,
>> Mike
>>
>>
>> _______________________________________________
>> SAP-WUG mailing list
>> SAP-WUG at mit.edu
>> http://mailman.mit.edu/mailman/listinfo/sap-wug
>>
>> _______________________________________________
>> SAP-WUG mailing list
>> SAP-WUG at mit.edu
>> http://mailman.mit.edu/mailman/listinfo/sap-wug
>>
>
>
>
>
> _______________________________________________
> SAP-WUG mailing list
> SAP-WUG at mit.edu
> http://mailman.mit.edu/mailman/listinfo/sap-wug
>
> _______________________________________________
> This message is confidential. It may also be privileged or otherwise
> protected by work product immunity or other legal rules. If you have
> received it by mistake please let us know by reply and then delete it
> from your system; you should not copy it or disclose its contents to
anyone.
> All messages sent to and from Linklaters may be monitored to ensure
> compliance with internal policies and to protect our business. Emails
> are not secure and cannot be guaranteed to be error free as they can
> be intercepted, amended, lost or destroyed, or contain viruses. Anyone
> who communicates with us by email is taken to accept these risks.
>
> The contents of any email addressed to our clients are subject to our
> usual terms of business; anything which does not relate to the
> official business of the firm is neither given nor endorsed by it.
>
> The registered address of the UK partnership of Linklaters is One Silk
> Street, London, EC2Y 8HQ. Please refer to
> http://www.linklaters.com/regulation for important information on the
> regulatory position of the firm.
>
>
> _______________________________________________
> SAP-WUG mailing list
> SAP-WUG at mit.edu
> http://mailman.mit.edu/mailman/listinfo/sap-wug
>
_______________________________________________
SAP-WUG mailing list
SAP-WUG at mit.edu
http://mailman.mit.edu/mailman/listinfo/sap-wug
More information about the SAP-WUG
mailing list