Skip to Content
related page 1
related page 2
related page 3


NOTE: Before beginning, the XLSX Workbench functionality must be available in the your system.

Suppose we need to generate Excel-file in the background mode.

For ease of example, lets create form, that contains only the classical phrase “Hello world !” nested in the rectangte area. The resultant Excel-file we will send via SAP-mail (in this case – to themselves).

1 PREPARE A PRINTING PROGRAM.

As you can see, most of the code takes the mailing (does not apply to the form creation) :

REPORT  z_hello_world .

* declare and fill context

DATA gs_context TYPE lvc_s_tabl .

DATA gv_document_rawdata  TYPE mime_data .

gs_context-value = ‘Hello world!’ .

* call the form

CALL FUNCTION ‘ZXLWB_CALLFORM’

   EXPORTING

     iv_formname    = ‘HELLO_WORLD’

     iv_context_ref = gs_context

     iv_viewer_suppress  = ‘X’

   IMPORTING

     ev_document_rawdata = gv_document_rawdata

   EXCEPTIONS

     OTHERS         = 2 .

IF sy-subrc NE 0 .

   MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

           WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 .

ENDIF .

* mailing

PERFORM send_mail USING gv_document_rawdata .

*&———————————————————————*

*&      Form  send_mail

*&———————————————————————*

FORM send_mail USING pv_document_rawdata TYPE mime_data .

   DATA:

     lv_attachment_size  TYPE sood-objlen ,

     lv_subject          TYPE so_obj_des ,

     lv_document_size    TYPE i ,

     lt_document_table   TYPE solix_tab .

   DATA:

     lr_send_request     TYPE REF TO cl_bcs ,

     lr_mail_message     TYPE REF TO cl_document_bcs ,

     lr_recipient        TYPE REF TO if_recipient_bcs ,

     lr_error            TYPE REF TO i_oi_error ,

     ls_retcode          TYPE soi_ret_string ,

     lv_attachment_type  TYPE soodk-objtp VALUE ‘XLS’ .

   CALL FUNCTION ‘SCMS_XSTRING_TO_BINARY’

     EXPORTING

       buffer        = pv_document_rawdata

     IMPORTING

       output_length = lv_document_size

     TABLES

       binary_tab    = lt_document_table.

   lr_send_request = cl_bcs=>create_persistent( ) .

   lv_subject = ‘test mail’ .

   lr_mail_message = cl_document_bcs=>create_document(

       i_type      = ‘RAW’

       i_subject   = lv_subject ) .

   lv_attachment_size = lv_document_size .

   TRY .

       lr_mail_message->add_attachment(

           i_attachment_type     = lv_attachment_type

           i_attachment_subject  = space

           i_attachment_size     = lv_attachment_size

           i_att_content_hex     = lt_document_table ) .

     CATCH cx_document_bcs .

   ENDTRY .

   lr_send_request->set_document( lr_mail_message ) .

   lr_recipient = cl_sapuser_bcs=>create( sy-uname ).

   lr_send_request->set_send_immediately( abap_on ) .

   lr_send_request->add_recipient(

       i_recipient = lr_recipient

       i_express   = abap_on ) .

   lr_send_request->send( i_with_error_screen = abap_on ) .

   COMMIT WORK .

ENDFORM .                    “send_mail


2 PREPARE A FORM.

2.1 Launch XLSX Workbench, and in the popup window specify a form name HELLO_WORLD , and then press the button «Process»:

00_3.PNG

Empty form will be displayed:

123.PNG

2.2 Push button444_19_2.PNGto save the form.

2.3 Assign context LVC_S_TABL to the form:


Herewith, you will be prompted to create a form’s structure automatically (based on context):

00_6_3.PNG

Let’s press the button: .

As result,  «Pattern» () and «Value» () will be added under the «Sheet» in the form structure tree :

124.PNG

Added components will already have a binding with context. For this components, only template binding is required.

We’ll do it later, but first we perform markup of template.

2.4 Make markup in the Excel template:



2.5 Template binding:


Assign «Pattern» to a target area in the Excel-template; For assigning, You have to perform next steps successively:

  • Pose cursor on the node in the form’s structure tree;
  • Select a cell range [A1 : C3] in the Excel-template;
  • Press a button located in the item «Area in the template» of the Properties tab:

Similary, assign «Value» to a target area in the Excel-template; For assigning, You have to perform next steps successively:

  • Pose cursor on the node in the form’s structure tree;
  • Select a cell range [B2] in the Excel-template;
  • Press a button located in the item «Area in the template» of the Properties tab:

Scheme of bindings:

2.6 Activate form by pressing button444_30.PNG.

3 EXECUTION.


Launch SE38 and run your report Z_HELLO_WORLD in background mode :



125.PNG


126.PNG

To report this post you need to login first.

68 Comments

You must be Logged on to comment or reply to a post.

    1. Luís Pérez Grau

      Here’s some differences I see (sorry If I’m saying the obvius)

      – Not all the customers accept the import of community software

      – This framework is supported by SAP, the other by the community, I couldn’t say which support wil be better, one enthusiastic community vs a “concerned” software vendor.

      (0) 
      1. Paul Hardy

        Second point first:-

        – are you saying the XLSX Workbench is an SAP delivered, SAP supported solution, part of the standard SAP framework? Are you sure? Can you point me to an official SAP site, outside of the SCN where it is mentioned? Or is it actually just another community project? It looks to me like the code lives on a Google project site.

        – I am aware that a lot of companies refuse to allow open source software such as SAPLINK / ABAP2XLSX / MOCKA and so forth. As I said before, I would like a list of such companies so I can buy shares in their competitors. The difference is, if an SAP supported framework has bugs or lacks functionality I want, I have to put in a support request which will be ignored, and in an open source framework I can either sk for help with a far better chance of a response or even better I can enhance / fix it myself the same day and then publish my change so everyone else can benefit from it.

        The open source model is more and more common these days and even SAP is waking up to it hence “Open UI5”.

        Mind you, the actual point I am making is that a lot of people are unaware of ABAP2XLSX and thus waste their time inventing something that does more or less the exact same thing. You tend to see at least one post a month on SCN from someone who has discovered how to download an excel file in the background from ABAP.

        If the XLSX workbench has a different function than ABAP2XLSX then fine, that answers the actual question I asked in the first place, but the title of this blog was about downloading an excel sheet in the background, which was not a new thing.

        Cheersy Cheers

        Paul

        (0) 
        1. Luís Pérez Grau

          You just killed me here… You are absolutely right, is another community project, my bad. 🙁

          I agree with the rest of your comments, so nothing to say, but

          The difference is, if an SAP supported framework has bugs or lacks functionality I want, I have to put in a support request which will be ignored, and in an open source framework I can either sk for help with a far better chance of a response or even better I can enhance / fix it myself the same day and then publish my change so everyone else can benefit from it.

          That’s quite complex, The customers should pay twice? You will develop the changes/fixes on your spare time or in a project?

          (0) 
          1. Igor Borodin Post author

            Hi Luis !

            First I want clarify that the XLSX Workbench is my personal project (it is not supported by SAP).

            Also, Paul Hardy was right XLSX Workbench is the one of the many solutions to generate Excel forms. However, my solution differs from the others in that it has a visual interface (this is the main difference). I think, visual approach has profits, as for development and for support.

            Regards, Igor.

            (0) 
            1. Shai Sinai

              Hi Igor,

              First, I would like to say that I really appreciate your effort and willing to share your project.

              Second, Have you thought on using ABAP2XLSX for the internal generation of XLSX instead of your own code?

              (0) 
              1. Igor Borodin Post author

                Shai, now i have no time for so deep reworking of my solution.

                I am afraid that both solutions implement quite different basic principles of generating resultant file. For example, I do not create the resultant file “from scratch”; instead of this, I take Template (with its cell styles, settings, etc.) and enrich it according to form structure and context data.

                (0) 
                1. Shai Sinai

                  Hi,

                  The template file is also XLSX, isn’t it?

                  I just wondered if there was/is any special reason not to use ABAP2XLSX for it.

                  I agree that refactoring it now will require some time, though.

                  (0) 
    2. Shai Sinai

      Well, as far as I can tell, it it isn’t the same.

      XLSX Workbench offers a design-time environment (similar to Smartforms) for generation of XLSX files.

      However, I agree that it should have been implemented as another layer on top of ABAP2XLSX and make use of it internally.

      (0) 
      1. Ivan Femia

        I reached Igor Borodin some months ago to try to work on this way, but unfortunately working is limiting our time to talk and collaborate.

        I like the UI of the XLSX Workbench and I agree that the engine should be the abap2xlsx that is now a mature product with an incredible list of features.

        If anyone whats to join into this discussion is really welcomed and Community can improve and merge two ideas into a new powerful open source tool.

        Ivan

        (0) 
  1. Eitan Rosenberg

    Hi,

    I would like to raise a question.

    Why can’t we use OLE to create Excel in background Job ?

    Is that because we do not have Microsoft office installed on the application server ?

    Can we use OLE if we did ?

    The reason I am asking this is that I know for a fact that my colleagues are generating and serving Excel files using OLE as part of a web server agents (the server is a domino server from IBM) .  they have on the server a copy of office and that’s it….

    regards.

    (0) 
    1. Ivan Femia

      AFAIK you can’t use OLE2, it works only in foreground from SAPGui.

      Maybe your colleagues are using some tricks with other middle-ware (JSP, PHP or ..) can do the job, but it is a huge complication, for something that can be achieved easily from abap using abap2xlsx.

      (0) 
      1. Eitan Rosenberg

        Hi,

        Object Linking and Embedding (OLE) is part of the windows operating system.

        (See http://en.wikipedia.org/wiki/Object_Linking_and_Embedding) .

        No tricks are involve.

        My colleagues are using lotus script and this language have a built in support to use ole .

        it looks like this:

        Dim msExcel As Variant

        Set msExcel = GetObject(“Excel.Application”)

        Call msExcel.Workbooks.Add

        And this is VBScript:

        Set objExcel = CreateObject(“Excel.Application”)

        objExcel.Visible = False

        objExcel.Workbooks.Add

        objExcel.Cells(1, 1).Value = “Test value”

        objExcel.ActiveWorkbook.SaveAs (“C:\excelvbscript.xls”)

        objExcel.Quit

        This look very similar to the ABAP code don’t you think ?

        You might be right if SAP is limiting ABAP when using OLE .

        I wonder if anyone tries to use OLE in back ground job ?

        regards. 

        (0) 
        1. Dominik Di Lorenzo

          Hi Aitan,

          AFAIK it often raises problems if you depend on external things like OLE.

          Sometimes the Windows machine or the installed office gets an update or something is reconfigured for other purposes and your XLS-Solution does not work anymore.

          Ivans abap2xlsx is a pure ABAP solution and it does not depend on other system components and I can tell you from my experience, that it is very stable.

          I did not yet try it, but I think Igors XLSX Workbench is also solution which only depends on ABAP and therefore it also has a big advantage compared to any OLE solution.

          Best regards,

          Dominik

          (0) 
            1. Ivan Femia

              So you prefer java libraries instead of ABAP library. What is the advantage?

              ABAP is creating an XML file, call external java for processing the XML and then creates a XLSX. Does it seem to complex for just a Excel?

              abap2xlsx creates file directly from ABAP (no external calls needed) accordingly to OOXML standard (ECMA International as ECMA-376) that is used in excel, freeoffice, openoffice and many others So what is the risk?

              (0) 
                1. Ivan Femia

                  Looking at Creating Excel the Java way

                  Step 1. Sample XML created from SAP

                  Who trust the guy that generates the XML?

                  Step 2. Sample Excel generated by the java program .

                  Who trust the guy that wrote the java program?

                  Microsoft has some API (OLE) to execute actions in excel. That could be an advantage, but what is the risk of having a java code running on the server bash?

                  The advantage of an open source: having a community of expert developers that close a gap that two companies don’t want to cover… And the code can be verified by everyone.

                  my 2 cents

                  (0) 
        2. Ivan Femia

          When I was saying trick I mean another tool, language, middle ware.

          In your case a Lotus Script. But you can also create with other scripting languages.

          abap2xlsx is just ABAP base, no other scriptings are required. So having this in mind you can understand that the complexity of the entire flow is lower.

          Even more having the file generated directly from ABAP, the access to SAP data to be exported is faster and also more secure.

          (0) 
            1. Matthew Billingham

              Eitan Rosenberg wrote:

              The question now is why this limitation ?…

              Isn’t it obvious? For the same reason that you can’t download to your PC file system in background.

              (0) 
              1. Eitan Rosenberg

                Hi,

                Sorry but I disagree .

                If you make a folder on your local PC accessible from your SAP .

                Using UNC naming you can write directly to your desktop .

                Of course you have to have the computer on…..

                No dialog will be shown to the user .

                Regards. 

                (0) 
                1. Matthew Billingham

                  UNC is just a way of identifying files across heterogenous systems. For it to work, you have to correctly configure both windows and linux. This will require admin rights and some linux jiggery-pokery. So it’s a little more complex than “just give the UNC name”. It doesn’t just work out of the box and isn’t provided by SAP. Similarly for OLE you’ll need transport layer software – why should SAP provide this? What’s in it for them? If there was a real customer need being unmet that they could make money out of, I’m sure they’d do it.

                  Why do you want to use OLE anyway? It’s a pain to work with – both in ABAP and in Java. You seem to think the open source POI which natively generates Excel from Java is ok, so why is abap2xlsx a problem?

                  I’m sorry, but you really seem to be grasping at straws here.

                  (0) 
                  1. Eitan Rosenberg

                    Hi,

                    1 – To have OLE in the back ground open up new options currently not available .

                    2 – Regarding office integration. There is a package in SAP called SOFFICEINTEGRATION ,

                        I would like to use it in back ground mode, Can I ?

                       

                        Because if we could no body would bother to create Java code , abap2xlsx or the recent XLSX Workbench .

                       

                    3-  If there is a real customer need ?

                       

                        I do not know if this reflect “customer need” but I can see that there is a a lot of interested people here .

                    /wp-content/uploads/2015/02/screenshot_021_650241.png   

                    /wp-content/uploads/2015/02/screenshot_03_650242.png

                    Regards. 

                    (0) 
    2. Matthew Billingham

      I looked into this twelve years ago. At the time there was software available for Unix, which could communicate with a windows server running MS Word, Excel etc.

      Since it would take quite some time to implement, it was decided that DOI and running in foreground was sufficient for the business requirement.

      (0) 
                  1. Eitan Rosenberg

                    Hi,

                    My application server is windows server.

                    OLE is part of the operating system .

                    OLE as came out from Microsoft do not need any interactive session .

                    I want to use OLE as part of a back ground job .

                    The only way to use it based on what you are saying is to use external command like cscript.exe which is a shame….

                       

                    Regards. 

                    (0) 
                    1. Matthew Billingham

                      Most appservers run on Unix, which has no OLE. In all the many sites I’ve worked on in the past 18 years, I don’t recall one that used Windows for the appservers – so the demand I think is quite low. Further, any OLE at appserver level would only work with a Windows server, which kind of goes against the whole OS agnostic paradigm.

                      The man doth protest too much, methinks.

                      (0) 
  2. Paul Hardy

    In regard to the OLE vs ABAP2XLSX debate. Here are some reasons I think ABAP2XLSX comes out on top:-

    ℹ Most ABAP programmers do not have the skills to go mucking around in the OS level, as that is dangerous if you do not know what you are doing. With ABAP2XLSX everything is done at the application server layer i.e. in ABAP.

    (2) OLE takes forever to download data – the cells get filled out one by one and you can watch the spreadsheet filling out in slow motion.

    (3) OLE is an officially supported SAP mechanism which means if something is wrong, you log an OSS call, wait fifteen weeks, and then get told it is a consulting issue. With an open source project you can track the progess of issue resoution, I have found things get fixed really quickly, and the best bit is you can fix it yourself. Often I know what SAP needs to do to fix their standard code, and tell them, but they won’t believe me as I am a “customer” as opposed to someone pulled out of a bus queue and thrust into first level support.

    (4) Most importantly, no bull fighter ever shouts “ABAP2XLSX! ABAP2XLSX!” whilst waving a red cape in front of a bull.

    I think SAP Press are releasing a book on the 3rd of April 2015 about all the latest ABAP features, which has a chapter all about ABAP2XLSX but I cannot for the life of me remember who the author is.

    Cheersy Cheers

    Paul

    (0) 
        1. Paul Hardy

          I am in Sydney, Australia the time with me is currently 7:09 AM on the 23rd of February.

          In the UK, it is 8:09 PM on the 22nd of Februray.

          in Boston, USA, it is 3:09 PM on the 22nd of February.

          Happy to clear this up.

          Cheersy Cheers

          Paul

          (0) 
  3. kiran rachamalla

    hai mr.Igor Borodin,

          I really want to appreciate For your great work , and i done that and it worked for me as expected i attached and send mail also.

       but i have a one question though i passed ‘ iv_protect              = ‘X’ ‘ this way still i can open the attached or saved document without any password to enable password do we need to do any more for that .

    Regards,

    kiran rachamalla.

    (0) 
    1. Igor Borodin Post author

      Hi, Kiran! This option protects file from changes by users (makes the workbook read only). But it doesn’t protect file from opening.

      Regards, Igor.

      (0) 
      1. kiran rachamalla

        Ohh Yeh then its working Fine for me

        Any way do we have that option to protect the generated file with Password .

        And one more i am Getting Dump When i put a table type in the structure like fields

        DAOPR_T_REF 1 Types ZT_B0DATA_REF
        FLSET_T_REF 1 Types ZT_B0DATA_REF

        At this Point of Code

        LOOP AT ls_buffer_rsrowstructure ASSIGNING <rowstructure> .

                 IF sytabix GT 1 .

                   CONCATENATE lv_comp_fullpath ‘-LINE-‘ INTO lv_comp_fullpath .

                   lv_offset = strlen( lv_comp_fullpath ) .

                   <rowstructure>loop_relpath = <rowstructure>loop_fullpath+lv_offset .

                 ENDIF .


        In class ‘ lcl_formruntime’ Method ‘ compose_process_grid’ .


        /wp-content/uploads/2016/07/1212_993517.png


        (0) 
        1. Igor Borodin Post author

          Kiran, it does not support the password to open the file.

          Concerning the dump:  What version of workbench do you use?  Can you explain ZT_B0DATA_REF structure?

          Regards, Igor

          (0) 
        2. Igor Borodin Post author

          Kiran, perhaps the dump appears because you have placed two tables on the same context level. The Grid component allows only one table per context level.

          Regards, Igor

          (0) 
          1. kiran rachamalla

            Ohh yes i placed 2 tables at the same level ,

            Do you have any idea regarding how to place a password protection while opening a file (as u might have checked may references) .

            and regarding dump can we avoid the dump (at least can we avoid dump )  i mean need to change code ? but for that need understanding of that code i think .

            any way i am using the new version only.

            Regards,

            kiran rachamalla.

            (0) 
            1. Igor Borodin Post author

              Regarding to password. Maybe I will add this option in future versions, but now this is not supported. You can use callback form and set password via OLE.

              Regarding to dump. You are using a Grid component, am I right? The one table per one context level – this is base principle of the Grid (it does not apply to the whole context, but only apply to the subtable which is assigned to Grid). Here are two ways to avoid the dump. First way is changing context. Second way is using Loop/Pattern/Value construction instead the Grid component.


              Regards, Igor

              (0) 
              1. kiran rachamalla

                yes Using Grid only , Yeh i will try to use the Loop/ Pattern . Thank you for the great response and if done password protection using callback will that work ? 

                (0) 
                  1. kiran rachamalla

                    Hai Igor Borodin,

                              I want to change some details on them as of my requirement but i am not knowing how it is handled , i want to see the [content…].xml file so where can i see that file , i want to see how the content is there is it willingly hidden if not may i know where can i see that xml file .

                    Regards,

                    kiran rachamalla,

                    (0) 
                    1. Igor Borodin Post author

                      Hi, Kiran . Just open XLSX-file as ZIP-archive (you can change file extension from .XLSX to .ZIP and open). There are several XML-files for describing content of spreadsheet.


                      Regards, Igor

                      (0) 
  4. Domenico Criaco

    Hi Igor,

    thanks for amazing tool. I implemented and works very well with Microsoft Office.

    Unfortunately I have Open Office 4.1.2 and does not work.

    Any suggestions?

    Thanks.

    Kind Regards,

    Domenico.

    (0) 
      1. Domenico Criaco

        Hello Igor, I implemented a form with your tools, and Microsoft Office. If I open the file on a PC with Microsoft Office Excel works fine but if I open it with a PC with Open Office then I just get a blank screen.

        (0) 
        1. Igor Borodin Post author

          Hi Domenico, try so:

          * save file to desktop
          CALL FUNCTION ZXLWB_CALLFORM

           
          EXPORTING

              iv_formname        = ‘HELLO_WORLD’

              iv_context_ref     = gs_context
              iv_viewer_suppress = ‘X’
              iv_save_as         = ‘C:\Users\Igor\Desktop\QQQ.XLSX’

           
          EXCEPTIONS

             
          OTHERS
                       = 2.
          IF
          sy-subrc NE 0 .
           
          MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                   
          WITH
          sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 .
          ENDIF
          .

          *open file in OpenOffice
          cl_gui_frontend_services=>execute(
            EXPORTING
              synchronous        = ‘X’
              application        = ‘SOFFICE.EXE’
              parameter          = ‘C:\Users\Igor\Desktop\QQQ.XLSX’
              operation          = ‘OPEN’
              maximized          = ‘X’
            EXCEPTIONS
              OTHERS             = 10 ).

          (0) 

Leave a Reply