Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Automating Multi-Sheet Excel Report Creation and Emailing in SAP ABAP

md_wasim_akhtar
Explorer
‎2024 Nov 18 10:38 AM
1,744

Introduction

Automating routine tasks is key to boosting efficiency and minimizing manual effort in today’s fast-paced business environments. One such task is the creation and distribution of Excel reports, which are often required for presenting data in a structured format. In SAP ABAP, automating the generation of multi-sheet Excel reports and emailing them as attachments can significantly streamline the process.

In this blog, we’ll guide you through automating the creation of multi-sheet Excel reports, formatting the sheets, and sending them via email in SAP ABAP. Whether you're working with standard or custom data, this step-by-step guide will help you simplify your reporting process, improve accuracy, and ensure timely delivery of important business information.

Prerequisites

To follow along with this post, you'll need to have abapGit, an open-source Git client for SAP ABAP. Created by the ABAP community, abapGit allows developers to manage ABAP code in Git repositories, bridging SAP and GitHub.

With abapGit, you can:

Now, Clone the ABAP2XLSX Repository:

Install the Project:

  • Pull the ABAP2XLSX repository into your SAP development system using abapGit.
  • Activate all the objects after they are imported to ensure the library is ready to use.

Now that ABAP2XLSX is installed and ready to use in your development system, we have all the necessary tools to write the business logic for generating multi-sheet Excel reports. With the library in place, we can seamlessly create, format, and populate Excel files with dynamic data.

Let’s dive into the core of the solution and start implementing the business code to automate Excel report creation and emailing in SAP ABAP.

Data Declaration

We start by defining variables for creating and formatting the Excel file, as well as for handling email content, recipients, and attachments.

 

DATA: send_request     TYPE REF TO cl_bcs,
      document         TYPE REF TO cl_document_bcs,
      recipient        TYPE REF TO if_recipient_bcs,
      bcs_exception    TYPE REF TO cx_bcs,
      main_text        TYPE bcsy_text,
      lv_subject       TYPE so_obj_des,
      lt_recipients    TYPE TABLE OF ad_smtpadr,
      excel            TYPE REF TO zcl_excel,
      worksheet        TYPE REF TO zcl_excel_worksheet,
      excel_writer     TYPE REF TO zif_excel_writer,
      excel_as_xstring TYPE xstring,
      lt_raw_data      TYPE solix_tab.

DATA: lv_tabcolor     TYPE zexcel_s_tabcolor,
      style_worksheet TYPE REF TO zcl_excel_style,
      style_title     TYPE REF TO zcl_excel_style.

 

Creating the Excel Object

We create an instance of the Excel object to start building the spreadsheet.

 

CREATE OBJECT excel.

 

Adding Data from VBAK Table to the First Worksheet

  • First, we retrieve the data from the VBAK table using the FOR ALL ENTRIES statement for optimized performance.
  • We create the first worksheet and bind the data from VBAK, setting the title and applying a blue color to the tab.

 

DATA lt_vbak TYPE TABLE OF vbak.

" Fetch unique VBAK data based on keys
SELECT * FROM vbak INTO TABLE lt_vbak WHERE vbeln IN ( ... ).

worksheet = excel->get_active_worksheet( ).
worksheet->set_title( ip_title = 'Sales Orders (VBAK)' ).

" Set tab color to blue
lv_tabcolor-rgb = zcl_excel_style_color=>create_new_argb( ip_red = '00' ip_green = '00' ip_blu = 'FF' ).
worksheet->set_tabcolor( lv_tabcolor ).

" Add bold style to title
style_title = excel->add_new_style( ).
style_title->font->bold = abap_true.
worksheet->set_cell( ip_column = 'A' ip_row = 1 ip_value = 'Sales Orders' ip_style = style_title->get_guid( ) ).

" Bind data from VBAK
worksheet->bind_table( ip_table = lt_vbak ).

 

Adding Data from VBAP Table to the Second Worksheet

  • We create a second worksheet, set the tab color to green, and bind data from VBAP.
  • For better performance and to avoid duplicate records, we use FOR ALL ENTRIES to fetch VBAP records linked to VBAK entries, joining on sales document keys.

 

DATA lt_vbap TYPE TABLE OF vbap.

" Fetch corresponding VBAP entries for VBAK records
SELECT * FROM vbap INTO TABLE lt_vbap
 FOR ALL ENTRIES IN lt_vbak
 WHERE vbap-vbeln = lt_vbak-vbeln.

worksheet = excel->add_new_worksheet( ip_title = 'Sales Order Items (VBAP)' ).

" Set tab color to green
lv_tabcolor-rgb = zcl_excel_style_color=>create_new_argb( ip_red = '00' ip_green = 'FF' ip_blu = '00' ).
worksheet->set_tabcolor( lv_tabcolor ).

" Add bold style to title
worksheet->set_cell( ip_column = 'A' ip_row = 1 ip_value = 'Sales Order Items' ip_style = style_title->get_guid( ) ).

" Bind data from VBAP
worksheet->bind_table( ip_table = lt_vbap ).

 

Writing the Excel File to XSTRING Format

After setting up the worksheets, we use an Excel writer object to write the entire Excel workbook to an XSTRING format, which we will later use as an email attachment.

 

CREATE OBJECT excel_writer TYPE zcl_excel_writer_2007.
excel_as_xstring = excel_writer->write_file( excel ).

 

Converting XSTRING to Binary Format for Email Attachment

The XSTRING is converted to a binary format (solix_tab) compatible with email attachments.

 

lt_raw_data = cl_bcs_convert=>xstring_to_solix( EXPORTING iv_xstring = excel_as_xstring ).

 

Preparing the Email

We set up the email content, recipients, and subject line, and prepare the email text to explain the attached report.

 

TRY.
    send_request = cl_bcs=>create_persistent( ).

    " Create document with attachment
    document = cl_document_bcs=>create_document(
      i_type    = 'RAW'
      i_text    = main_text
      i_subject = lv_subject ).

    " Add the spreadsheet as an attachment
    document->add_attachment(
      i_attachment_type    = 'XLS'
      i_attachment_subject = 'Sales Data Report'
      i_attachment_size    = |{ xstrlen( excel_as_xstring ) }|
      i_att_content_hex    = lt_raw_data ).

    send_request->set_document( document ).

    " Add recipients
    LOOP AT lt_recipients INTO DATA(lv_recipient).
      recipient = cl_cam_address_bcs=>create_internet_address( lv_recipient ).
      send_request->add_recipient( recipient ).
    ENDLOOP.

    " Send email
    DATA(sent_to_all) = send_request->send( i_with_error_screen = 'X' ).
    COMMIT WORK.

    IF sent_to_all IS INITIAL.
      MESSAGE i500(sbcoms) WITH lv_recipient.
    ELSE.
      MESSAGE s022(so).
    ENDIF.

  CATCH cx_bcs INTO bcs_exception.
    MESSAGE i865(so) WITH bcs_exception->error_type.
ENDTRY.

 

Uploading the Excel File to the Application Server

In addition to sending the Excel report via email, the program allows you to upload the file to the SAP application server for storage and retrieval. This is particularly useful for long-term archiving or integration with other SAP systems.

 

DATA(lv_path) = '/tmp/suppdata_report.xlsx'. " Adjust path if needed
OPEN DATASET lv_path FOR OUTPUT IN BINARY MODE.
IF sy-subrc = 0.
  LOOP AT lt_raw_data INTO DATA(ls_raw).
    TRANSFER ls_raw TO lv_path.
  ENDLOOP.
  CLOSE DATASET lv_path.
  WRITE: / 'File successfully written to AL11: ', lv_path.
ELSE.
  WRITE: / 'Error opening file in AL11: ', lv_path.
ENDIF.

 

GUI Download for Local Testing For testing in a local development environment, you can use the GUI_DOWNLOAD method to download the file to your local system

 

TRY.
    cl_gui_frontend_services=>gui_download(
      EXPORTING
        filename     = 'C:\Temp\Export91.xlsx' " Adjust path for your system
        filetype     = 'BIN'
        bin_filesize = xstrlen( excel_as_xstring )
      CHANGING
        data_tab     = lt_raw_data ).
  CATCH cx_root INTO DATA(ex_txt).
    WRITE: / ex_txt->get_text( ).
ENDTRY.

 

Note: You cannot directly download files in XLSX format from the AL11 transaction. To perform such operations, use the transactions CG3Y and CG3Z instead.

Automating File Upload/Download Using Background Jobs

To streamline the file upload and download processes, you can automate them by scheduling the program as a background job in SAP. Background jobs enable scheduled execution at specific intervals or times, reducing manual intervention and increasing efficiency. Here’s how it works:

Job Scheduling:

Schedule the program using Transaction SM36, specifying the job name, start conditions (such as specific times or periodic intervals), and the program to execute.

  1. Log and File Monitoring:
    After execution, monitor the job using Transaction SM37 to confirm success. Verify the file's presence in the SAP application server directory (AL11).

Zipping the Excel File

Once the Excel file is generated as an xstring, we can proceed to zip it. Zipping is particularly useful when dealing with large reports or when you want to minimize the file size for email transmission. The following code snippet demonstrates how to use the cl_abap_zip class to create a ZIP archive.

 

*&-------- Zip the file --------------------------------
DATA lc_zipper TYPE REF TO cl_abap_zip.
DATA lv_filename TYPE string.
DATA lv_size TYPE i.
DATA lt_att_hex TYPE solix_tab.
DATA zip TYPE xstring.

CONCATENATE 'Supplier_data' '.XLSX' INTO lv_filename.

CREATE OBJECT lc_zipper.

" Add file to zip
CALL METHOD lc_zipper->add
  EXPORTING
    name = lv_filename
    content = excel_as_xstring.

" Save zip
CALL METHOD lc_zipper->save
  RECEIVING
    zip = zip.

 

Sending Emails with Attachments in SAP Using BCS (Business Communication Services)

If you're working with SAP, you might have come across the Business Communication Services (BCS) functionality. The BCS API in ABAP is widely used for handling email communications, including sending emails with attachments. An example of this can be seen in standard SAP programs like BCS_EXAMPLE_*, which demonstrate how to use BCS to send emails programmatically. These examples show how to handle email content, attach files, and send them through the SAP system, all of which are key when automating report deliveries or other communications directly from SAP.

Conclusion

In this blog, we've covered how to leverage SAP's Business Communication Services (BCS) API to send emails with attachments, which is a valuable tool for automating report distributions and enhancing communication workflows within the SAP system. By following the steps outlined above, you can easily create and send emails with attached files, making it simpler to share important data directly from your SAP environment.

We hope this guide helps you get started with email communication in SAP. For more advanced scenarios, such as adding multiple attachments, customizing email templates, or automating email processes, you can explore additional BCS functionalities and enhance your ABAP email workflows.

Feel free to share your thoughts or ask questions in the comments below, and stay tuned for more SAP tips and tricks!

 

5 Comments
Jelena_Perfiljeva
Active Contributor
‎2024 Nov 27 4:32 PM

Thank you for sharing this and for illustrating the use of open-source projects such as abapGit and ABAP2XLSX! This is definitely still a popular requirement in SAP even in 2024. (As a matter of fact, we were discussing exactly this type of request with a colleague just yesterday. 🙂 )

I have to say though that the code examples are a bit behind on what is expected in modern ABAP. For example, use of FAE is highly discouraged. At minimum, when we do that, we need to check if table is not blank. Otherwise you'll get the opposite of good performance. 🙂 And this would've been picked up by a code check. I'd encourage you to run at minimum Extended Check on all the code you write (it's available right in ABAP Editor) or, better yet, use ATC.

There are different thoughts about Hungarian notation but at minimum, just stick with one naming approach: either use HN or not. Mixing different conventions is the worst. Please take a look at Clean ABAP for recommendations on this.

I also highly recommend this cheat sheet to help modernizing the code: https://www.brandeis.de/en/blog/cheat-sheet-modern-abap-en

E.g. CREATE OBJECT and CALL METHOD are no longer needed (for 10 years already probably) and inline declarations are very useful to avoid "the wall of DATA" in every block. (I think there are so few systems running on pre-7.4 ABAP these days that we can stop focusing on the code samples for the old releases.) There are also some guidelines in ABAP documentation but I'd recommend the resources above first.

Cheers!

md_wasim_akhtar
Explorer
‎2024 Dec 03 5:51 AM

@Jelena_Perfiljeva 

Thank you for the detailed feedback and for sharing your thoughts on modern ABAP practices.

I appreciate your suggestions regarding the use of FAE, naming conventions, and the benefits of tools like Extended Check and ATC. These are excellent points for developers aiming to adopt modern ABAP standards. For this blog, my primary goal was to focus on providing a simple, practical solution for automating Excel tasks, as many developers still work in systems where immediate functionality takes precedence over modernization.

your recommendations, including the Clean ABAP guide and cheat sheet, are fantastic resources, and I’ll consider incorporating more modern practices in future posts. Thank you again for taking the time to share your insights!

ATC 

Clean-abap  , Cheat Sheet Modern ABAP 

Sandra_Rossi
Active Contributor
‎2024 Dec 03 6:30 AM

Thanks. Just one remark about writing a "binary" file to the application server, if you have an XSTRING variable (Excel_As_Xstring), you may directly TRANSFER it once, you don't need to use the internal table. In your case, you already had the internal table (used by BCS) so it's harmless, but many people think that an internal table is needed to transfer an XSTRING variable to the application server and do this useless conversion.

md_wasim_akhtar
Explorer
‎2024 Dec 04 7:24 AM

@Sandra_Rossi 

Thanks for the input! You're absolutely right — if you already have an XSTRING variable like excel_as_xstring, you can directly use the TRANSFER statement to write the file in one go without converting it to an internal table. The conversion is often unnecessary and can lead to confusion about the requirement for an internal table.

 

" Open the dataset in binary mode
OPEN DATASET lv_path FOR OUTPUT IN BINARY MODE.
IF sy-subrc = 0.
  TRANSFER excel_as_string TO lv_path.      " Directly write the XSTRING in one step
  CLOSE DATASET lv_path.
  WRITE: / 'File successfully written to AL11: ', lv_path.
ELSE.
  WRITE: / 'Error opening file in AL11: ', lv_path.
ENDIF.

 

 

Sandra_Rossi
Active Contributor
‎2024 Dec 04 8:05 AM
0 Kudos

Thanks! You may also participate in reducing the confusion by editing your blog post to replace "LOOP AT lt_raw_data... TRANSFER ls_raw... ENDLOOP" with "TRANSFER excel_as_string" 😉

Labels in this area
Popular Blog Posts