Once you tired of ALV GRID

[former_member210277]

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:

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

and

.\xl\worksheets\sheet1.xml

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:

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:

Hope this may help or inspirit you!