Skip to Content

I had a  dream. Imagine document designers who don’t need to ask developers how to  create or change an output layout of documents. Imagine developers who have one  simple tool to work with MS Office documents. Imagine there’s no need to keep  design and data extraction together. Suppose you can change your corporative  design in documents within minutes and you don’t need to use developers for  this purpose!
  Imagine you  can create a scheduled task that will send MS Office documents as attachments.  There’s no need of MS Office installed! There’s no need of GUI sessions. You  can manipulate documents from WDA, from background and there’s no more limits  of MS Office use.
  It was a  perfect dream. Since that time I’ve created a tool to fill MS Office documents  by names. But it needs GUI and MS Office installed. Then I’ve learnt  XSL-transformations and found them pretty fast and simple. But you need to  create a transformation each time you have document design changed. Furthermore  you can’t keep images within Excel files created by XSLT. It was a nightmare  when I got an issue to create an Excel document with graphics from WDA!
  Then I  found a tool abap2excel. It’s quite nice. But it’s quite huge and I didn’t find  there functions to use range names to fill data in a template file. Almost  forgot, I know a functional provided by Parazit to work with MS Office  documents.
  I have to  tell you honestly that I didn’t learn the tool abap2excel as soon as I had just  2 days to complete my task.
  Any task is  a challenge and I don’t like to tell “It’s impossible”. So I learnt a structure  of MS Excel document to fill desired sheet with desired data.
  But then I  got the new issue to create a report that sends SAP Query results by e-mail. Of  course this report should avoid GUI sessions as soon as it’s for background  tasks. Of course it would be nice to separate design and data extractions for  this task because someone can change his or her mind and redraw the document  template.
  So here are  tasks that I solved by my functionality:

  • Let  users store a template with corporate elements such as images
  • Designers  should have a way to describe where data appear
  • Developers  should have a simple interface to manipulate MS Excel document
  • Utility  should be able to work without GUI and without MS Office use

The  business process is very simple: Power user decides how the document will  appear and then Power user defines named ranges:

/wp-content/uploads/2013/11/image002_318783.jpg
  Here you  can see a common design of the document. It consists of:

  • Logo  image.
  • Texts  that can be common for any document with this template. Note texts can appear  at any place of the document. So if data table is to be inserted these texts  should appear correctly under the table. It means if data table has 3 lines the  footer text will appear in 13-th line.
  • Defined  named ranges. Here you can see the named range ‘HEADER_LINE’. This document  keeps ‘DATA_TAB’ named range also.
  • Two  additional sheets. These additional sheets are optional of course.

The idea of  my tool is to retrieve defined names of the document and then place data  correctly. For example, if I used this template to output table with four  columns they should appear in the same manner as it is for the first column. Here  is the example image:

/wp-content/uploads/2013/11/image004_318784.jpg
  But there  can be some situations when the document is static. For example, you wish to  create a table of top ten sold positions and it will look exactly in the same  way regardless data displayed:

/wp-content/uploads/2013/11/image006_318786.jpg
  If the  named range holds more than one row, it’s hard to determine which of these  styles to copy. When you’ll try to fill this ‘DATA_TAB’ named range with 5  lines with 4 columns, you should get next image:
  /wp-content/uploads/2013/11/image008_318787.jpg
  Now it’s  possible to work with templates stored either at web-repository (t-code SMW0)  or passed as xstring stream. The result is xstring. Here is an example code to  fill the document:
  DATA: lr_xlsx TYPE REF TO zcl_bas_xlsx_utils,
        lt_data TYPE TABLE OF e070,
        lv_binlen TYPE i,
        lv_xstring TYPE xstring,
        lt_content TYPE sdokcntbins.
 
  CREATE OBJECT lr_xlsx
    EXPORTING
      iv_template = ‘ZBAS_SQ01_TO_XLSX_EXAMPLE’.
  SELECT * FROM e070 INTO CORRESPONDING FIELDS OF TABLE lt_data.
 
  lr_xlsx->put_table_into_name(
    iv_name    = ‘DATA_TAB’
    it_table = lt_data ).
 
  lv_xstring = lr_xlsx->get_file( ).
 
  CALL FUNCTION ‘SCMS_XSTRING_TO_BINARY’
    EXPORTING
      buffer          = lv_xstring
    IMPORTING
      output_length   = lv_binlen
    TABLES
      binary_tab      = lt_content.
 
  CALL METHOD cl_gui_frontend_services=>gui_download
    EXPORTING
      bin_filesize              = lv_binlen
      filename                  = ‘C:\Temp\Temp.xlsx’
      filetype                  = ‘BIN’
    CHANGING
      data_tab                  = lt_content
    EXCEPTIONS
      others                    = 24
          .

And finally here is the reference to the source code. There are text files zipped. I’ve  tried to minimize code.
  Hope you’ll  enjoy it.
  Actually there is one  issue for this functional: when you open result file it reports an error  message. I’ll be happy if  anyone can solve it. Warning disappears if I open one of xml files of the Excel  zip structure and just re-save it.

Here you can find dictionary types description you probably need:

types mdp_tt_xml_docref type table of ref to IF_IXML_DOCUMENT.

types:

begin of ZBAS_ALSMEX_TABLINE,

          row type KCD_EX_ROW_N,

          col type KCD_EX_COL_N,

          VALUE type STRING_UNICODE,

end of ZBAS_ALSMEX_TABLINE,

        ZBAS_ALSMEX_TABLINE_T type table of ZBAS_ALSMEX_TABLINE.

To report this post you need to login first.

24 Comments

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

  1. Thomas Lichtenstein

    Hello Guryanov Alexandr,

    I wanted to try your class, but I’ve got the error, that the type
    mdp_tt_xml_docref is not defined. Where can I find this type?

    regards,

    Thomas

    (0) 
    1. Guryanov Alexandr Post author

      It’s a table type for IF_IXML_DOCUMENT. So you can create your own table type or find existing. Also you can find a type ZBAS_ALSMEX_TABLINE in my class. This is a table type for structure ZBAS_ALSMEX_TABLINE that is a copy of ALSMEX_TABLINE, where VALUE field has STRING_UNICODE type.

      (0) 
      1. Thomas Lichtenstein

        In the public section of your class ZCL_BAS_XLSX_UTILS the line 17

        “DATA sheets_tab TYPE mdp_tt_xml_docref .” don’t work.

        I get the Error:

        The Type “MDP_TT_XML_DOCREF” is unknown.

        I can’t find the Type ZBAS_ALSMEX_TABLINE in your class.

        (0) 
        1. Guryanov Alexandr Post author

          Dear Thomas, the type mdp_tt_xml_docref is:

          types mdp_tt_xml_docref type table of ref to IF_IXML_DOCUMENT. 

          Also you can either comment method READ_XLSX_FILE that uses type ZBAS_ALSMEX_TABLINE or create a dictionary types:

          types: begin of ZBAS_ALSMEX_TABLINE,

                    row type KCD_EX_ROW_N,

                    col type KCD_EX_COL_N,

                    VALUE type STRING_UNICODE,

                  end of ZBAS_ALSMEX_TABLINE,

          ZBAS_ALSMEX_TABLINE_T type table of ZBAS_ALSMEX_TABLINE.

          (0) 
  2. Mike Khrapov

    Hello, Alexander.

    Could you please upload the working example of xls template ? I’ve tried to make my own template, but the report abort with exception “NO_SUCH_NAME”. The report couldn’t find range DATA_TAB in my excel template…

    Thank you, Mikhail.

    (0) 
    1. Guryanov Alexandr Post author

      Hi Mikhail,

      You can use a file from the link: template

      To use this template you are to create a binary data (t-code SMW0) using the name ZBAS_SQ01_TO_XLSX_EXAMPLE and create it in desired package, $TMP for example.

      Hope this will help you!

      Best regards,

      Alexander

      (0) 
      1. Mike Khrapov

        Hello, Alexander.

        Thank you for template. I’ve got the result in .xlsx file. But now i can’t open it, because of the fail with converter by opening the file. What should i do to solve this problem or how can i re-save this file to dispose the error ?

        Thank you,

        Mikhail.

        (0) 
        1. Guryanov Alexandr Post author

          Hi Mikhail,

          Could you please give me your file? Usually it doesn’t need any further activities. So it might be a point to fix some issues.

          Thanks!

          Best regards,

          Alexander

          (0) 
            1. Guryanov Alexandr Post author

              Thank you!

              Of course, it’s an issue of mine! This error occures if these two conditions are met together:

              1- The template file contains no texts

              2- No values were passed to the file

              I’ll fix this problem soon and report it as an answer to your comment.

              Once again,

              thank you for testing,

              Best regards,

              Alexander

              (0) 
            2. Guryanov Alexandr Post author

              Hi Mikhail,

              It’s strange but I can’t reproduce your issue on a system. Could you give me a source code you have used? To fix that issue on your system you can comment lines from 20th to 25th of ADD_TEXT_TO_CELL method. But I’d better understand why such behavior exists.

              Best regards,

              Alexander

              (0) 
              1. Mike Khrapov

                Hello, Alexander.

                I’ve attached the .zip package http://filehoster.3dn.ru/load/0-0-0-391-20, it contains :

                Main_report_source.txt – report source, based on your source example,

                Class_source.txt –  source presentation of your class in listing view,

                Z_BAS_XLSX_UTILS_TEST.xlsx –  your template, that i’ ve used.

                I’ve commented lines in method ADD_TEXT_TO_CELL, but this didn’t give any results – the same error occurs by the opening the file.

                Thank you,

                Mikhail.

                (0) 
                1. Guryanov Alexandr Post author

                  Hello Mikhail,

                  I’m so realy sorry for such delay with my response! I was assigned onto one of projects ‘on fire’. I’ve reproduced your issue and fixed it. You can find new version of methods INSERT_CELLS_FOR_TABLE, FIND_ROW and FIND_CELL.

                  Thank you for your good testing!

                  Kind regards,

                  Alex

                  (0) 
                  1. Mike Khrapov

                    Hello, Alexandr !

                    Thank you for answer and fixing the issues. I will surely try it after new years holiday.

                    I congratulate you with coming New 2014 Year and wish you all the best !)

                    (0) 
                    1. Mike Khrapov

                      Hello, Alexandr !

                      I’m terribly sorry, but the same error occurs – failure in converter while opening the .xlsx file.

                      /wp-content/uploads/2014/01/screenshot_1_361985.jpg

                      I have installed Excel 2003 on my PC.

                      Thank you,

                      Mikhail

                      (0) 
  3. Paul Hardy

    The point about “open source” projects like ABAP2XLS is that it does not do the exact thing you want then you tell the authors that, or even better solve the problem yourself and than tell the world the solution.

    What you are doing here is re-iventing the wheel, ABAP2XLS does all the things you are talking about, why fight it?

    Cheersy Cheers

    Paul

    (0) 
    1. Guryanov Alexandr Post author

      Hi Paul,

      Thank you for your opinion! I gues you know abap2xlsx functional much more better than me so I’ll be glad if you can share here a code to do the same trik I’ve done.

      I mean could you please give me here a source code that will put content of an internal table into a template?

      Best regards,

      Alexander

      (0) 
      1. Ivan Femia

        abap2xlsx has also a reader functionality. I used in several project XLSX templates.

        So the idea is:

        – Create a XLSX template (programmatically or via application)

        – Read the template with xlsx2abap reader (included in the package)

        – Write back the XLSX file with updated values

        It is the same concept that stays behind the macro support

        https://www.youtube.com/watch?v=jg7MRm2uK3I

        Hope this clarify,

        Ivan

        (0) 
        1. Paul Hardy

          I’d just like to say what I do is have a table in the database with assorted templates both XLSX and XLSM stored as an XSTRING.

          I upload the empty spreadsheet from my desktop, and then store it in the database. Then our custom programs start with this template, fill it with data and then email it to the desired user or users. Most of these templates are crawling with macros, some are spreadsheets people have used for years to order sports shirts or whatever.

          @Mr.Ivan – can you sned me a direct message either via SCN or Linked In? i need to know if you have any issues with me writing a chapter on ABAP2XLSX in my forthcoming book.

          (0) 
          1. Guryanov Alexandr Post author

            Thank you for useful information!

            Now I have some spare time to learn deep abap2xlsx.

            I tried to make something flexible like PDF forms or so on. And the main idea was to separate design from the code. So if the data extractor works fine there is no more need in a developer to change the document if changes are just about the file design.

            Of course we can always use macroses to do whatever we like. But it’s always the weak part. First of all it’s a security issue and then all users should know how to switch on macro in MS Office if it’s disabled.

            @Paul_Hardy I was inspirited by your tool to read texts from a DTEL and created a tool alike. Really thank you for the idea! 🙂

            Kindest regards,

            Alex Guryanov

            (0) 
  4. Raghavendra Prabhu Mithal

    Good Work Alex – really appreciate your effort. Need to try your code. I had done someting on similar lines to generate excel as I was also reluctant to use abap2xlsx, but yours is more advanced.

    Regards,

    raghavendra

    (0) 

Leave a Reply