- SAP Managed Tags:
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.
24 Comments
| User | Count |
|---|---|
| 7 | |
| 6 | |
| 6 | |
| 4 | |
| 4 | |
| 4 | |
| 4 | |
| 3 | |
| 2 | |
| 2 |
