Flexible MS Excel documents with ABAP/4

[former_member210277]

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:

  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:

  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:

  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:
 
  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.