Skip to Content

Today is the day you may learn how to show table data!

No, not again! Not again one of those REUSE_ALV_GRID or CL_GUI_ALV_GRID nor CL_SALV_TABLE. Not a bit!

Imagine you need to provide users with great layout he or she familiar with.

Furthermore you want to let users edit functionality.

Please feel free to use one of those techniques. But CL_SALV_TABLE fals out since you need edit function.

You shouldn’t expect each user to be happy with SAP standard interface. But almost all users are pretty familiar with MS Office.

Let’s imagine if we can create an Excel workbook with data users want to see. It would be a great deal because almost everyone in the modern world is familiar with its interface:

http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/2158.ExcelResults.png

This nice-looking interface has additional strengths. At least one: it may manipulate with data that is not shown on the screen.

Some of data may be lost if someone do copy-paste operation with a huge amount of data working with ALV GRID.

It’s surely solved with MS Excel.

Pros and cons:

ALV Grid:

+ Already exists

+ Easy to use

+ Can be used in backgroud jobs

+ Pretty (? Sure it is pretty if you’re a SAP professional)

– Not transparent behavior for users and developers in case of editable Grid

– Not as flexible as Excel

– Takes time to get used to it

MS Excel

+ Pretty

+ Works with great amount of data

+ Users are familiar with it

+ Can have Charts, Logo and so on

– Needs some magic to implement it (it is discussed below)

– Needs MS Office to be installed onto user’s PC (being honest: it’s almost a standard to have MS Office on each work Win PC)

– Holds some extra space of user’s screen (shown below)

Why we don’t try to create this MS Excel integration in our report?

What we need: a template XLSX file. This will hold styles, charts and logos (if realy need it).

Perhaps you know it already XLSX file is actualy a zip archive with several XML files within it.

We need only two:

.\xl\sharedStrings.xml

Без имени-2.jpg

and

.\xl\worksheets\sheet1.xml

Без имени-3.jpg

A quick info: the first is to store all unique texts of the workbook cells and the second is to keep the first worksheet.

All we need is to add new strings into the first one and to put a table into the second.

I’ve created a simple tool to add new texts into a XLSX file and its use looks like:

Без имени-4.jpg

You may find it at my posts if you realy need it.

And then I’ve created a XSL-transformation to fill the worksheet:

Please forgive me for placing it here:

<xsl:transform version=”1.0″

   xmlns:xsl=”http://www.w3.org/1999/XSL/Transform

   xmlns:sap=”http://www.sap.com/sapxsl

>

<xsl:strip-space elements=”*”/>

<xsl:template match=”/”>

<worksheet xmlns=”http://schemas.openxmlformats.org/spreadsheetml/2006/main” xmlns:r=”http://schemas.openxmlformats.org/officeDocument/2006/relationships” xmlns:mc=”http://schemas.openxmlformats.org/markup-compatibility/2006” mc:Ignorable=”x14ac”

xmlns:x14ac=”http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac“>

   <dimension ref=”A1:J2″/>

   <sheetViews>

     <sheetView tabSelected=”1″ workbookViewId=”0″/>

   </sheetViews>

   <sheetFormatPr defaultRowHeight=”14.4″ x14ac:dyDescent=”0.3″/>

   <cols>

     <col min=”1″ max=”1″ width=”9″ bestFit=”1″ customWidth=”1″/>

     <col min=”2″ max=”2″ width=”28″ bestFit=”1″ customWidth=”1″/>

     <col min=”3″ max=”4″ width=”10.109375″ bestFit=”1″ customWidth=”1″/>

     <col min=”5″ max=”5″ width=”20″ bestFit=”1″ customWidth=”1″/>

     <col min=”6″ max=”6″ width=”7.21875″ bestFit=”1″ customWidth=”1″/>

     <col min=”7″ max=”7″ width=”8.5546875″ bestFit=”1″ customWidth=”1″/>

     <col min=”8″ max=”8″ width=”12.109375″ bestFit=”1″ customWidth=”1″/>

     <col min=”9″ max=”9″ width=”8.6640625″ bestFit=”1″ customWidth=”1″/>

     <col min=”10″ max=”10″ width=”9.21875″ bestFit=”1″ customWidth=”1″/>

   </cols>

   <sheetData>

     <row r=”1″ spans=”1:10″ ht=”30.6″ x14ac:dyDescent=”0.3″>

       <c r=”A1″ s=”1″ t=”s”>

         <v>0</v>

       </c>

       <c r=”B1″ s=”1″ t=”s”>

         <v>1</v>

       </c>

       <c r=”C1″ s=”1″ t=”s”>

         <v>2</v>

       </c>

       <c r=”D1″ s=”1″ t=”s”>

         <v>3</v>

       </c>

       <c r=”E1″ s=”1″ t=”s”>

           <v>9</v>

       </c>

       <c r=”F1″ s=”1″ t=”s”>

         <v>4</v>

       </c>

       <c r=”G1″ s=”1″ t=”s”>

         <v>5</v>

       </c>

       <c r=”H1″ s=”1″ t=”s”>

         <v>6</v>

       </c>

       <c r=”I1″ s=”1″ t=”s”>

         <v>7</v>

       </c>

       <c r=”J1″ s=”1″ t=”s”>

         <v>8</v>

       </c>

     </row>

     <xsl:for-each select=”//ITEMS/*”>

     <row spans=”1:10″ x14ac:dyDescent=”0.3″>

       <xsl:attribute name=”r”>

          <xsl:value-of select=”INDX”/>

       </xsl:attribute>

       <c s=”2″ t=”s”><v><xsl:value-of select=”F1″/></v>

       </c>

       <c s=”2″ t=”s”><v><xsl:value-of select=”F2″/></v>

       </c>

       <c s=”2″ t=”s”><v><xsl:value-of select=”F3″/></v>

       </c>

       <c s=”2″ t=”s”><v><xsl:value-of select=”F4″/></v>

       </c>

       <c s=”2″ t=”s”><v><xsl:value-of select=”F10″/></v>

       </c>

       <c s=”3″><v><xsl:value-of select=”F5″/></v>

       </c>

       <c s=”3″><v><xsl:value-of select=”F6″/></v>

       </c>

       <c s=”3″><v><xsl:value-of select=”F7″/></v>

       </c>

       <c s=”3″><v><xsl:value-of select=”F8″/></v>

       </c>

       <c s=”3″><v><xsl:value-of select=”F9″/></v>

       </c>

     </row></xsl:for-each>

   </sheetData>

   <sheetProtection password=”DE25″ sheet=”1″ formatCells=”0″ formatColumns=”0″ formatRows=”0″ insertColumns=”0″ insertRows=”0″ insertHyperlinks=”0″ deleteColumns=”0″ deleteRows=”0″ sort=”0″ autoFilter=”0″ pivotTables=”0″/>

   <pageMargins left=”0.7″ right=”0.7″ top=”0.75″ bottom=”0.75″ header=”0.3″ footer=”0.3″/>

   <pageSetup paperSize=”9″ orientation=”portrait” horizontalDpi=”0″ verticalDpi=”0″ r:id=”rId1″/>

</worksheet>

</xsl:template>

</xsl:transform>

Here are thre points to be discussed:

1. Text values go with <c t=”s”> tag (note attribute t value). And I pass here indexes from my tool.

2. Number values go without attribute t for tag ‘c’.

3. Protection for the sheet is set on.

The last point will forbid any unexpected changes. In my example there is a difference between <c s=”2″> style and <c s=”3″>.  The main purpose is to portect uneditable cells and let the rest be changeable.

At last we are to show the XLSX.

Let we have:

1. A binary string with zip content of an XLSX file

2. A screen with a container

And here is my example to show the MS Excel file:

     DATA: lv_string TYPE char1024.

*       container   TYPE REF TO cl_gui_container,

*       doi_proxy   TYPE REF TO i_oi_document_proxy.

*       l_control   TYPE REF TO i_oi_container_control

*       control     TYPE REF TO i_oi_ole_container_control

*       xlsx_string TYPE xstring ” holds binary of the XLSX file

     CHECK container IS NOT BOUND.

     CREATE OBJECT container TYPE cl_gui_custom_container

       EXPORTING

         container_name = ‘CONTAINER’.                       “#EC NOTEXT

     c_oi_container_control_creator=>get_container_control(

       IMPORTING

         control = l_control ).

     control ?= l_control.

     CALL METHOD control->init_control

       EXPORTING

         r3_application_name      = ‘Demo’                   “#EC NOTEXT

         inplace_enabled          = abap_true

         inplace_scroll_documents = abap_true

         parent                   = container

         register_on_close_event  = abap_true

         register_on_custom_event = abap_true.

     CALL METHOD control->get_document_proxy

       EXPORTING

         document_type      = ‘Excel.Sheet’                  “#EC NOTEXT

         register_container = abap_true

       IMPORTING

         document_proxy     = doi_proxy.

     DATA: lt_table TYPE enh_version_management_hex_tb,

           lv_size  TYPE i.

     CALL FUNCTION ‘ENH_XSTRING_TO_TAB’

       EXPORTING

         im_xstring = xlsx_string

       IMPORTING

         ex_data    = lt_table

         ex_leng    = lv_size.

     doi_proxy->open_document_from_table(

       document_table = lt_table

       document_size  = lv_size

       open_inplace   = abap_true ).

And the final screenshot to encourage:

Без имени-5.jpg

Hope this may help or inspirit you!

To report this post you need to login first.

6 Comments

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

  1. Paul Hardy

    Have you heard of ABAP2XLSX? It is an open source ABAP project dedicated to uploading and downloading EXCEL sheets from/to the ABAP system using a very similar technique to the one you describe.

    I don’t think very many people have heard of it as there is a blog every couple of weeks about EXCEL integration.

    (0) 
    1. Guryanov Alexandr Post author

      Hi Paul!

      Thanks for your notice. This issue is not about creating XLSX. The main goal is to highlight a posibility of XLSX use instead of ALV GRID.

      And yes I heard about ABAP2XLSX.

      Best regards,

      Alex

      (0) 
  2. Raghavendra Prabhu Mithal

    Hello Guryanov,

    Really good and different approach. Though I dont understand XSL syntax well, but dont you feel we need to have different XSLs for different tables as this one looks like a hard coded one. The one approach is to dynamically build the xls XML with appropriate styles, somewhat like what abap2xls does.

    (0) 
    1. Guryanov Alexandr Post author

      Hi Raghavendra,

      This post is to fix a posibility of MS Office documents integration right into the SAP GUI, without macro and files manipulations. Of course the given example is a hardcode. At the same time you don’t have to make many changes to make that file more flexible.

      You can also use ABAP2XLSX tool to create a file that will be integrated into your reports.

      Kindest regards,

      Alex

      (0) 
      1. Rainer Hübenthal

        ABAP2XLSX can display Spreadsheets in the SAPGUI as well. If you crate a file or not, if you download it or not depends on what you are doing in the program. I guess you misinterpreted ABAP2XLSX. It creates an xstring containing the zipped XML files. What happens with that string depends on further programming.

        (0) 

Leave a Reply