Skip to Content
Actions

XLSX Workbench for SAP(ABAP) – tool for exporting data to Excel

April 22, 2014 | 2,766 Views |
 abapabap excelcode exchangeconvertdownloadeditorexcel
Follow

PROJECT LINK:


RELATED PAGES:


YOUTUBE CHANNEL:

                            XLSX_WORKBENCH_LOGO_1.png


IDEA

For a long time only OLE/DOI-approach was available for creating spreadsheets. As you know, OLE/DOI-approach is quite flexible, but it has some restrictions. For example, it requires installed MS Excel application (this makes impossible to use background job for generate spreadsheet). Moreover, OLE-approach has low performance (this problem can be partially solved using VBA, but this requires manually changing of security settings MS-Excel application on each front-end computer).

Since, ZIP-folder processing tools became available in the SAP environment, we are able to generate spreadsheets with new Open XML Format (XLSX). This approach does not have lacks of OLE.

But, in my opinion, the flaw of all existing solutions for generate spreadsheets from ABAP- is many lines of ABAP code required directly for creating and formatting spreadsheet layout. As result, the same Excel forms are quite differently implemented by the different developers. That approach heavily complicates a support of these developments.

My idea is creating the design-time environment for visual form editing (like a SMARTFORMS tool, for example).

A unified approach to development is realized because no need to write the code for the Excel form formatting – you only care about source data for the form.

Runtime printing form is being generated with the new Open XML Format (XLSX), without using of Microsoft Excel application (it makes possible to get result in the background jobs SAP).

DESIGN-TIME AND RUNTIME

Where are two modes of XLSX Workbench tool.

  • Design-time mode starts with a call transaction ZXLWB_WORKBENCH and provides completely visual interface, which allows you to quickly create or change the form layout even you don’t have ABAP skills:
    IMG01.PNG
  • Runtime mode occurs each time the printing programm is being called.
    The printing program prepares the source data (and fill the context) and finally calls the functional module ‘ZXLWB_CALLFORM’, which compiles and displays the form:
    IMG02.PNG

    Developer decides how to use the runtime form:
    • display on the front-end computer via MS Excel application;
    • save on the front-end computer as file (.XLSX) with a specified path;
    • return into the printing program as rawdata.

WHAT DOES DEVELOPMENT PROCESS LOOK LIKE ?

You need to develop three components:

  • CONTEXT.  As a Context, you can use any suitable Table type or Structure or Data element from the ABAP-dictionary. Or just create your own Context via transaction SE11.
  • FORM. Call transaction ZXLWB_WORKBENCH and build the form structure in the visual editor. Forms are stored in the SAP WEB-repository (transaction SMW0, binary data for WebRFC-applications), and represented as XLSX-files. All data about form’s structure is inside the file.
  • PRINTING PROGRAM. It must contain next steps:

    • declare a context (just variable which refers to ABAP-dictionary type);

    • fill context with data;

    • call function module ‘ZXLWB_CALLFORM’.

To report this post you need to login first.

67 Comments

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

        1. Igor Borodin Post author

          Hello, Wolfgang.

          XLSX Workbench provides graphical UI and uses visual approach (like SMARTFORMS, for example).

          Best regards, Igor.

          (0) 
            1. Igor Borodin Post author

              Hello, Ivan.

              I didn’t mean that is the only difference, but I had in mind that the visual approach of the form design is the main idea of my work. Both tools are quite different.

              P.S. XLSX Workbench does not support reading functionality to upload and edit excel files.

              Best regards, Igor.

              (0) 
  3. Jozef Vaclavek

    upon trying to install using saplink, I got a dump in SAPLINK’s method GETOBJECTINFOFROMIXMLDOC, no attribute ( AttrNode) found in rootAttr.

    Using saplink 0.1.4. Is the problem in saplink or in the nugget?

    (0) 
      1. Jozef Vaclavek

        Thanks for the update. I tried now, but unfortunately it seems I’m not able to find a working active FUGR plugin for the SAPLINK. All the resources aim at Downloads –  saplink-plugins –   SAPlink plugins – Google Project Hosting. And there seems to be only a deprecated version for the FUGR object type. In fact whole this SAPLINK stuff would be a lot nicer, if it had one single place to live.

        However, it is working fine. Maybe one more comment – in the installation instructions, I should create the trannsaction for program ZXLWB_WORKBENCH, which does not exist. ZXLWB is ok with this.

        (0) 
  10. Laurent DONNY

    Hi Igor,

    Very impressive work, more than 18000 lines of code in ZXLWB_INCLUDE. Wow.

    I was wondering if there is a way to replace the Author and Company properties of the xlsx files actually sets with your name and your site?

    Can you please also explain what is the usage of the 77 lines of Hexa data (from line 2725 to 2802) in ZXLWB_INCLUDE ? How can we see the detailed content of these lines ?

    Thanks for your reply.

    Laurent

    (0) 
    1. Igor Borodin Post author

      Hi Laurent.

      Secial for you I have released a new (2.1) version. There new importing parameter IT_DOCPROPERTIES was added to the ZXLWB_CALLFORM function module. How to use it see here.

      The lines of Hexa data in the XLWB INCLUDE contains an INITIAL (ie used only when you create NEW form) template of Excel document, that you see at right-hand frame of the workbench window.

      Igor.

      (0) 
      1. Laurent DONNY

        Hi Igor,

        Thanks very much for making possible to update document properties.

        About the template loaded from hex lines hard coded in the include, I think it should be better to be able to load a personal template.

        As you can imagine, hex data in a program could be seen as a blackbox and could frighten Security guys.

        Moreover, template loading will offer flexibility to adapt the template for different situations.

        In your constructor method of lcl_excel, this could be something like:

        *      ‘74796C65732E786D6C504B0102130014000600080000002100DAEB1BFFA0060000861B00001300000000000000000000000000E30B0000786C2F7468656D652F7468656D65312E786D6C504B010214001400000008007A6659445FEBF2DC330100001002’ ,
        *      ‘00001800000000000000010020000000B4120000786C2F776F726B7368656574732F7368656574312E786D6C504B010214001400000008002D6659445FA65CD1440100007C02000011000000000000000100200000001D140000646F6350726F70732F63’ ,
        *      ‘6F72652E786D6C504B0102140014000000080019665944DE01D331A80100009A030000100000000000000001002000000090150000646F6350726F70732F6170702E786D6C504B05060000000009000900F3060000661700000000000000000000000000’ .
        *
        *
        *
        *      CALL FUNCTION ‘SCMS_BINARY_TO_XSTRING’
        *        EXPORTING
        *          input_length = lv_docsize
        *        IMPORTING
        *          buffer       = lv_rawdata
        *        TABLES
        *          binary_tab   = lt_doctab.

               DATA:
                 lv_filename             TYPE string,
                 lv_retcode              TYPE char1 ,
                 ls_fields               TYPE sval ,
                 lt_fields               TYPE STANDARD TABLE OF sval .

               ls_fields–tabname   = ‘SI_RQ_CONTENT’ .
               ls_fields–fieldname = ‘PHYSNAME’ .
               ls_fields–fieldtext = ‘Select XLSX Template’ .

               APPEND ls_fields TO lt_fields .

               CALL FUNCTION ‘POPUP_GET_VALUES_USER_HELP’
                 EXPORTING
        *         F1_FORMNAME               = ‘ ‘
        *         F1_PROGRAMNAME            = ‘ ‘
                   f4_formname               = ‘POPUP_XLSX_TEMPLATE_F4’
                   f4_programname            = sy–repid
        *         FORMNAME                  = ‘ ‘
                   popup_title               = ‘XLSX Template’
        *         PROGRAMNAME               = ‘ ‘
        *         START_COLUMN              = ‘5’
        *         START_ROW                 = ‘5’
        *         NO_CHECK_FOR_FIXED_VALUES = ‘ ‘
                 IMPORTING
                   returncode                = lv_retcode
                 TABLES
                   fields                    = lt_fields
                 EXCEPTIONS
                   error_in_fields           = 1
                   OTHERS                    = 2.

               IF sy–subrc <> 0.
                 MESSAGE ID sy–msgid TYPE ‘S’ NUMBER sy–msgno
                         WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4 .
                 EXIT .
               ENDIF.
               IF lv_retcode NE space .
                 MESSAGE s000(lp) WITH ‘Operation was terminated by the user’.
                 EXIT .
               ENDIF .

               READ TABLE lt_fields INTO ls_fields INDEX 1 .
               CHECK sy–subrc EQ 0 .
               lv_filename = ls_fields–value .

               TRY.
                   lv_rawdata = cl_openxml_helper=>load_local_file( lv_filename ).
                 CATCH cx_openxml_not_found.
               ENDTRY.

               rawdata_set( lv_rawdata ) .


        and new form for F4 template selection:


        *&———————————————————————*
        *&      Form  popup_xlsx_template_f4
        *&———————————————————————*
        *       text
        *———————————————————————-*
        *      –>TABNAME    text
        *      –>FIELDNAME  text
        *      –>DISPLAY    text
        *      –>RETURNCODE text
        *      –>VALUE      text
        *———————————————————————-*
        FORM popup_xlsx_template_f4 USING   tabname fieldname display
                              CHANGING returncode value.

           DATA: lv_filename TYPE string.

           lv_filename = cl_openxml_helper=>browse_local_file_open( iv_title = ‘Select XLSX File’
                                                                    iv_filename = ”
                                          
                                 iv_extpattern = ‘All files(*.*)|*.*’ ).
           value = lv_filename.
        ENDFORM.                    “popup_xlsx_template_f4


        What do you think ?

        Laurent

        (0) 
        1. Igor Borodin Post author

          Hi Laurent, I have released a new (2.2) version.

          Thank you, but unfortunately class cl_openxml_helper isn’t available in my system…

          • I agree with you, that hard coded hex lines seen as a blackbox and it’s not better solution. I have used it only for minimizing number of objects that have to be installed.
            If security guys are frightened by the hardcoded template, you can just delete method TEMPLATE_GETFROM_HARDCODE from the class LCL_EXCEL. But instead, you have to upload an empty XLSX-file to WEB-repository (SMW0 transaction) with object name ZXLWB_&TEMPLATE& .
          • If you want to upload XLSX-template from frontend, see here. But I don’t recomend to do it. Because the uploaded file may have invalid format or it may contain unwanted embedded objects, external links etc, that can cause an error while compiling the resultant file .

          Igor.

          (0) 
  11. Laurent DONNY

    Hi Igor,

    1. I have inserted a pivot table in a XLSX form but when I execute it I get an error when Excel 2013 open the file: “We found an error with some contents in ‘xxxxxxxxxxxxxxxxxxxxxx.xlsx’. Do you want us to try to recover as much as we can ? if you trust the source of this workbook, click yes.”

    When I do it with Excel 2010 it works well

    2. From the pivot table, I have tried to generate a chart in the form. I have done the Excel part in the template then I have created a new pattern, assigned an area in the template, added a Drawing, used ‘Drawing from template’ as source and bind it with the chart. The form is correctly activated but at execution I have the same error as in paragraph 1.

    3.I have also tried to create a form with your 3.2.12 Dynamic merge cells example but unfortunately at execution, at each new level in the context, the next used cell is not on the right of the previous one but starts on a new row. Do you have an idea what I am missing ? Can it be an issue with Excel 2013 or because my SAP version is not at the same level as yours ? Mine is a 7.02.

    Thanks for you reply

    Laurent

    (0) 
    1. Igor Borodin Post author

      Hi Laurent !


      3) Version of SAP or Excel does not matter in this case. I can assume that “Merge cells” option of your Resizable pattern now is switched to “Span columns” mode (instead required “Span rows”). But, to be more specific, I have to see your form’s structure. You can send me screenshot or your downloaded form at email igorborodin.55@gmail.com .


      1) and 2) XLSX Workbench is designed just for creation of static printing forms.

      Unfortunately, at the moment it does not support any calculations, embedded objects, external data sources etc. including pivot tables. All calculations has to be performed in the printing program. Maybe I will do pivot tables support in the future, but not now.

      Component “Drawing” now supports only Pixel image (Excel menu: Insert tab > Illustrations group > Picture), and Vector drawing (Excel menu: Insert tab > Illustrations group > Shapes) and does not support Charts.

      Igor.

      (0) 
      1. Laurent DONNY

        Hi Igor,

        3. I have sent you an email today with my Form details.

        2. & 1.  Embedding Pivot table and chart would be great. I hope you can do it in a near future.

        Thanks

        Laurent

        (0) 
  14. Raja Kesavan

    Hi Igor,

    Thank you very much. This is really a wonderful tool.

    I tried few examples, it worked 🙂 .

    Can we control the color of any line in excel based on the data from context. Say if I get an error line item, can I make that line red in excel generated. Basically, my doubt is like can we control something based on data we get from context.

    Thanks in advance 🙂

    Regards,

    Raja

    (0) 
    1. Igor Borodin Post author

      Hi, Raja ! Of course you can!

      (a) First, you have to add two fields (type char1) into context (table line level). This fields should be named WHITE_ROW and RED_ROW, for example. To show red-color row, pass ‘X’ to field RED_ROW in the printing program. Otherwise, pass ‘X’ to field WHITE_ROW.

      (b) Secondly, prepare patterns for white-colored row (already exists in your form) and  red-colored row (just copy from white row and modify it). In the item ‘Appearance at runtime’ of Properties tab, assign pattern to appropriate context field from (a).

      In addition, see using of ‘Appearance at runtime’  in the examples 3.2.08a  and 3.2.10 .


      Regards,

      Igor

      (0) 
  15. David Planansky

    Hi,

    very nice work. I start to use it and it is fine. I have a one question to the functionality. Is it possible to have the output excel sheet protected with password? Because when I am trying to lock the sheet in template, the output sheet is not protected. Right now I am running version 2.09.

    Regards,

    David

    (0) 
  16. Alejandro Bindi

    This is an outstanding piece of work.

    Will have to try it when I have some spare time (I hope that moment is in the near future 🙁 ). I don’t know if you created it all by yourself or with a group of people but in any case I suppose it took a lot of time.

    Congratulations Igor!

    (0) 
  17. sap manie

    Hi Igor,

    This is a great development. Well done.

    I am displaying an EAN number from MEAN-EAN11 onto the Excel sheet and instead of an 18 digit number like 1234567890000, it is being displayed in scientific format like  1.1223456E+12

    I can manually set the format of the cell to ‘number’ which corrects the display, but I’m wondering if this can be done in the tool? I tried setting the format in the XLWB but it didn’t seem to stick.

    Thanks,

    Sap

    (0) 
      1. sap manie

        Hi,

        You mean change the format in the XLWB? Yes, I changed the format of the cell to Text, saved and activated but it seems to revert back to “general” when the file is opened.

        Thanks,

        Sap

        (0) 
        1. Igor Borodin Post author

          It is very strange that cell format is different in design-time and runtime… are you sure? what XLWB version are you using? ms office version?

          As fast solution, you can insert leading space to the ean number, this should help.

          Regards,

          Igor

          (0) 
          1. sap manie

            Thanks – the “space” idea seemed to have worked.

            I changed the field to ‘text’ in XLWB and activated. When opening the Excel, it’s back to ‘general’. I have Office 2013 and XLWB 3.04

            Thanks,

            Sap

            (0) 
    2. sap manie

      Hi,

      Would there be any reason why the toolbar (containing the Info, Activate buttons, etc.) would suddenly not be visible anymore? I debugged and can see the init_appltoolbar( ) method is being called, with no errors.

      The ‘top’ toolbar is now the buttons to create a component etc. so I can’t save/activate etc. a form anymore…

      I also tried creating a  brand new form, but the buttons still do not appear.

      Thks.

      (0) 
  18. Charles Owens

    Hi,

    Great development!

    I added a new field to the structure that the form is based on, and I can’t seem to display it in XLSX. When I click on’value’ for a field, it shows me the ‘old’ structure – is it possible to ‘refresh’ this so the new structure gets pulled in?

    Thks,

    CO

    (0) 
      1. Charles Owens

        Thanks Igor.

        I had to open and close it a few times before the structure became visible for some reason, but it’s working fine now. Thanks!

        (0) 
  19. Nimisha Chawak

    Hi IGOR,

      Nice work. I have a question though. I have a smart form in message control and want to convert that output form into XLS . Is that possible with this tool?

    Thanks and Regards

    Nimisha Chawak.

    (0) 
    1. Igor Borodin Post author

      Hi Nimisha. What exactly do you mean by ‘smart form in message control’?

      You have to build a form using XLSX Workbench. You can use a printing program of Smartform, and, perhaps, interface parameter as context for XLSX Workbench form (if interface parameter is just one).


      Regards, Igor.

      (0) 
      1. Nimisha Chawak

        HI,

          I have a shipment and a  output type . To the output type they have a smart form attached. In todays worls they justy have a printout from the output type. But now they also want an email and attachment as xls.

        In the smart form they have 2 internal tables been printed.

        Question here is , is there a way to use this tool to convert the current smart form into excel.

        Thanks and Regards

        Nimisha.

        (0) 
  21. Simone Cattozzi

    Great tool!! But there’s a way to import data from excel to internal table?
    Or maybe a suggestion to perform an import like the tool does an export.

    Thanks

    (0) 
    1. Igor Borodin Post author

      Simone, unfortunately this tool is only for export data to excel, not for import. But there are many ways for importing excel data, google to the rescue 🙂

      Best regards, Igor

      (0) 
  22. Bohdan Petrushchak

    Hello Igor,

     

    Thank you for an article! We’ve installed it and will check how it works:)

     

    P.S. By the way, did you consider moving your project to GitHub in order to control versioning / track the issues more easily? In case, you would be interested in that, you might also consider using (and contributing:) to a dedicated tool abapGit, that might be used to integrate development environment in SAP with GitHub.

    (0) 
    1. Igor Borodin Post author

      Hello Bohdan, Thank You. About GitHub: I had thought about it, but I decided that centralized development provides more order and quality documentation, than multi-fork development.

      Best regards, Igor

      (0) 
  23. Klaus Spassin

    Very awsome job. Thanks Igor, it solve my problem. I had to install it by myself, without saplink and had no issues.

    Very professional tool.

    (0) 
  24. Igor Borodin Post author

    Hi guys, version 4.04 is ready. Now it supports .XLSM file format and embeded VBA-macro (of course, only in dialogue runtime mode). Read more .

    Best regards, Igor

    (0) 

Leave a Reply