Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
3,350

My new task was to create a complex MS Excel document containing a picture. I will describe it in future. That document need to be provided by WebDynpro. I examined a structure of XLSX file and found it pretty nice and transparent. By the way I found a method to read data of an XLSX file without having any MS Application installed and without any GUI operations. So here it is.

Reading XLSX file from a portal.

All of you know the functional module ALSM_EXCEL_TO_INTERNAL_TABLE it allows you to read an Excel file into the table. But these are disadvantages of this functional module:

1.      We are limited with length of data (50 characters).

2.      We need to provide functional module with start and end cells (we not always knows the bottom right cell of the document so why to ask us?).

3.      It uses a MS Office application installed on user’s PC.

4.      It uses frontend to get data so we can’t use it to extract data from a file in portal applications.

Terrible, isn’t it? But I need to solve a problem. That’s why I found that XLSX file have these parameters:

1.      The XLSX file is actually a ZIPped folder.

2.      Mostly it includes XML files.

3.      To store data on a separated sheet it holds just two meaning for us files: \xl\worksheets\sheet*.xml and \xl\sharedStrings.xml

The first file is to store positions of single values and some other attributes. The second file holds all strings of the document.

Here is my idea. We’re getting an xstring of a XLSX file. Then unzipping it. Then getting it’s data. It’s so simple!

I’ve created a little class to do this and some more tasks. Here it is.

CLASS zcl_gan_xlsx DEFINITION
 
PUBLIC
  FINAL
 
CREATE PUBLIC .

 
PUBLIC SECTION.
*"* public components of class ZCL_GAN_XLSX
*"* do not include other source files here!!!

   
TYPES:
     
BEGIN OF gty_strings,
         
index  TYPE i,
          string
TYPE string,
       
END OF gty_strings .

   
DATA si_example TYPE ixmltdom .
   
DATA:
      gt_strings
TYPE TABLE OF gty_strings .
   
DATA texts_count TYPE i VALUE -1.                     "#EC NOTEXT .
   
CONSTANTS c_si_tag TYPE char02 VALUE 'si'.              "#EC NOTEXT
   
CONSTANTS c_text_tag TYPE string VALUE '#text'.         "#EC NOTEXT
   
DATA gr_sh_str_dom TYPE REF TO if_ixml_mini_dom .
   
CONSTANTS c_shared_strings TYPE string VALUE 'xl/sharedStrings.xml'. "#EC NOTEXT
   
CONSTANTS c_sheet_file_mask TYPE string VALUE 'xl/worksheets/sheet@.xml'. "#EC NOTEXT
   
CONSTANTS c_replacement_char TYPE char01 VALUE '@'.     "#EC NOTEXT
   
DATA lr_zip TYPE REF TO cl_abap_zip .
   
DATA:
      gt_sheets
TYPE TABLE OF REF TO if_ixml_mini_dom .

   
CLASS-METHODS class_constructor .
   
METHODS constructor
     
IMPORTING
        !iv_template
TYPE any OPTIONAL
        !iv_xstring
TYPE xstring OPTIONAL .
   
METHODS add_text
     
IMPORTING
        !iv_string
TYPE string
      RETURNING
       
value(rv_result) TYPE i .
   
METHODS replace_file
     
IMPORTING
        !iv_file
TYPE string
        !iv_xstring
TYPE xstring .
   
METHODS replace_sheet
     
IMPORTING
        !iv_index
TYPE i
        !iv_xstring
TYPE xstring .
   
METHODS get_file
      RETURNING
       
value(rv_result) TYPE xstring .
   
CLASS-METHODS save_xlsx
     
IMPORTING
        !iv_stream
TYPE xstring .
    CLASS-METHODS read_xlsx_data
     
IMPORTING
        !iv_filepath
TYPE string OPTIONAL
        !iv_filecontent
TYPE xstring OPTIONAL
      PREFERRED
PARAMETER iv_filepath
      RETURNING
       
value(rt_result) TYPE zhr_gan_tabline_t .

private section.
*"* private components of class ZCL_GAN_XLSX
*"* do not include other source files here!!!

 
class-data GR_RENDER type ref to IF_IXML_MINI_RENDERER .
 
data SI_PARENT type ref to IF_IXML_MINI_NODE .
 
class-data GR_PARSER type ref to IF_IXML_MINI_PARSER .

 
methods ADD_STRING_LINE
   
importing
      !IV_STRING
type STRING
      !IV_POSITN
type I .
 
methods FILL_SHEETS .
 
methods FILL_SHARED_STRINGS .

METHOD class_constructor.
  gr_parser
= cl_ixml_mini=>create_parser( ).
 
gr_render = cl_ixml_mini=>create_renderer( ).
ENDMETHOD.

METHOD constructor.
 
DATA: lv_xstring TYPE xstring,
        ls_key    
TYPE wwwdatatab,
        lt_mime   
TYPE TABLE OF w3mime.
 
FIELD-SYMBOLS: <mime>   TYPE w3mime.
 
IF iv_xstring IS INITIAL.
    ls_key
-relid = 'MI'.
    ls_key
-objid = iv_template.
   
CALL FUNCTION 'WWWDATA_IMPORT'
     
EXPORTING
       
key    = ls_key
     
TABLES
        mime  
= lt_mime
     
EXCEPTIONS
       
OTHERS = 3.
   
IF sy-subrc <> 0.
* Implement suitable error handling here
     
RETURN.
   
ENDIF.

   
LOOP AT lt_mime ASSIGNING <mime>.
      lv_xstring
= lv_xstring && <mime>-line.
   
ENDLOOP.
 
ELSE.
    lv_xstring
= iv_xstring.
 
ENDIF.

 
CREATE OBJECT lr_zip.
  lr_zip
->load( lv_xstring ).

  fill_shared_strings
( ).
  fill_sheets
( ).
ENDMETHOD.

method ADD_STRING_LINE.
 
FIELD-SYMBOLS: <string> type gty_strings.
 
APPEND INITIAL LINE TO gt_strings ASSIGNING <string>.
  <string>
-index  = iv_positn.
 
<string>-string = iv_string.
endmethod.

METHOD fill_sheets.
 
DATA: lv_index   TYPE char10,
        lv_sheet  
TYPE string,
        lv_xstring
TYPE xstring,
        lr_dom    
TYPE REF TO if_ixml_mini_dom.
 
DO.
    lv_index
= sy-index.
   
CONDENSE lv_index NO-GAPS.
    lv_sheet
= c_sheet_file_mask.
   
REPLACE ALL OCCURRENCES OF c_replacement_char IN lv_sheet
     
WITH lv_index.
    lr_zip
->get(
     
EXPORTING
        name   
= lv_sheet
     
IMPORTING
        content
= lv_xstring
     
EXCEPTIONS
       
OTHERS  = 4 ).
   
IF sy-subrc <> 0.
     
RETURN.
   
ENDIF.
    gr_parser
->parse_xstring(
     
EXPORTING
        stream
= lv_xstring
     
IMPORTING
        dom   
= lr_dom
     
EXCEPTIONS
       
OTHERS = 4 ).
   
APPEND lr_dom TO gt_sheets.
 
ENDDO.
ENDMETHOD.

METHOD fill_shared_strings.
 
DATA: lv_xstring TYPE xstring,
        lv_string 
TYPE string.
 
FIELD-SYMBOLS: <si>   TYPE ixmltdom,
                 <text>
TYPE ixmltdom.

  lr_zip
->get( EXPORTING
                 name   
= c_shared_strings
              
IMPORTING
                 content
= lv_xstring
              
EXCEPTIONS
                
OTHERS  = 4 ).

 
IF lv_xstring IS INITIAL.
   
RETURN.
 
ENDIF.

  gr_parser
->parse_xstring(
   
EXPORTING
      stream
= lv_xstring
   
IMPORTING
      dom   
= gr_sh_str_dom
   
EXCEPTIONS
     
OTHERS = 4 ).

 
LOOP AT gr_sh_str_dom->dom_table ASSIGNING <si>
     
WHERE name = c_si_tag.
   
ADD 1 TO texts_count.
   
LOOP AT gr_sh_str_dom->dom_table ASSIGNING <text>
       
WHERE gid > <si>-gid.
     
IF <text>-name = c_si_tag.
       
EXIT.
     
ELSEIF <text>-name = c_text_tag.
        lv_string
= lv_string && <text>-value.
     
ENDIF.
   
ENDLOOP.
    add_string_line
( iv_string = lv_string
                     iv_positn
= texts_count ).
   
CLEAR lv_string.
 
ENDLOOP.
 
IF <si> IS ASSIGNED.
    si_example
= <si>.
   
CREATE OBJECT si_parent TYPE cl_ixml_mini_node
     
EXPORTING
        dom  
= gr_sh_str_dom
       
index = <si>-parent.
 
ENDIF.
ENDMETHOD.

METHOD add_text.
 
DATA: lr_si_node TYPE REF TO if_ixml_mini_node,
        lr_t_node 
TYPE REF TO if_ixml_mini_node,
        lv_gid    
TYPE i.
 
FIELD-SYMBOLS: <dom> TYPE ixmltdom,
                 <string>
TYPE gty_strings.

 
READ TABLE gt_strings ASSIGNING <string>
   
WITH KEY string = iv_string.

 
IF sy-subrc = 0.
    rv_result
= <string>-index.
 
ELSE.
    lv_gid
= lines( gr_sh_str_dom->dom_table ) + 1.
    gr_sh_str_dom
->add_node(
     
EXPORTING
        gid      
= lv_gid
       
type      = si_example-type
        name     
= si_example-name
        namespace
= si_example-namespace
        parent   
= si_parent
     
IMPORTING
        new_node 
= lr_si_node
     
EXCEPTIONS
       
OTHERS    = 4 ).
   
IF lr_si_node IS BOUND.
     
ADD 1 TO lv_gid.
   
ELSE.
     
RETURN.
   
ENDIF.
    gr_sh_str_dom
->add_node(
     
EXPORTING
        gid      
= lv_gid
       
type      = si_example-type
        name     
= 't'                                     "#EC NOTEXT
        parent   
= lr_si_node
     
IMPORTING
        new_node 
= lr_t_node ).
   
ADD 1 TO lv_gid.
    gr_sh_str_dom
->add_node(
     
EXPORTING
        gid      
= lv_gid
       
type      = lr_si_node->co_node_text
        name     
= c_text_tag
       
value     = iv_string
        parent   
= lr_t_node ).
   
ADD 1 TO texts_count.
    add_string_line
( iv_string = iv_string
                     iv_positn
= texts_count ).
   
rv_result = texts_count.
 
ENDIF.
ENDMETHOD.

METHOD replace_file.
 
DATA: lv_file TYPE string,
        lv_char
TYPE char10.
  lr_zip
->delete(
   
EXPORTING
      name
= iv_file
   
EXCEPTIONS
     
OTHERS = 4 ).
*  CHECK sy-subrc = 0.
  lr_zip
->add(
   
EXPORTING
      name   
= iv_file
      content
= iv_xstring ).
ENDMETHOD.

METHOD replace_sheet.
 
DATA: lv_file TYPE string,
        lv_char
TYPE char10.
 
READ TABLE gt_sheets INDEX iv_index TRANSPORTING NO FIELDS.
 
CHECK sy-subrc = 0.
  lv_char
= iv_index.
 
CONDENSE lv_char NO-GAPS.
  lv_file
= c_sheet_file_mask.
 
REPLACE c_replacement_char IN lv_file WITH lv_char.
  lr_zip
->delete(
   
EXPORTING
      name
= lv_file
   
EXCEPTIONS
     
OTHERS = 4 ).
 
CHECK sy-subrc = 0.
  lr_zip
->add(
   
EXPORTING
      name   
= lv_file
      content
= iv_xstring ).
ENDMETHOD.

METHOD get_file.
 
DATA: lv_xstring TYPE xstring.
  gr_render
->render_xstring(
   
EXPORTING
      dom   
= gr_sh_str_dom
   
IMPORTING
      stream
= lv_xstring
   
EXCEPTIONS
     
OTHERS = 4 ).
 
IF sy-subrc <> 0.
   
RETURN.
 
ENDIF.

  lr_zip
->delete(
   
EXPORTING
      name
= c_shared_strings
   
EXCEPTIONS
     
OTHERS = 4 ).
 
CHECK sy-subrc = 0.
  lr_zip
->add(
   
EXPORTING
      name   
= c_shared_strings
      content
= lv_xstring ).
 
rv_result = lr_zip->save( ).
ENDMETHOD.

METHOD save_xlsx.
 
DATA: lt_bintab TYPE solix_tab.
 
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
   
EXPORTING
     
buffer     = iv_stream
   
TABLES
      binary_tab
= lt_bintab.
 
CALL METHOD cl_gui_frontend_services=>gui_download
   
EXPORTING
      filename
= 'C:\Temp\Cur_xlsx_file.xlsx'               "#EC NOTEXT
      filetype
= 'BIN'
   
CHANGING
      data_tab
= lt_bintab
   
EXCEPTIONS
     
OTHERS   = 24.
 
IF sy-subrc <> 0.
*   MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*              WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
 
ENDIF.

ENDMETHOD.

METHOD read_xlsx_data.
 
DATA: lt_data    TYPE TABLE OF char200,
        lv_len    
TYPE i,
        lv_xstring
TYPE xstring,
        lr_xls_reader
TYPE REF TO zcl_gan_xlsx,
        lr_dom    
TYPE REF TO if_ixml_mini_dom,
        lr_row    
TYPE REF TO if_ixml_mini_node,
        lv_row_indx
TYPE i,
        lv_col_indx
TYPE i,
        lt_cells   
TYPE TABLE OF zhr_gan_alsmex_tabline,
        lr_child  
TYPE REF TO if_ixml_mini_node.
 
FIELD-SYMBOLS: <dom_line>   TYPE ixmltdom,
                 <cell>      
TYPE ixmltdom,
                 <cell_value>
TYPE zhr_gan_alsmex_tabline,
                 <value>     
TYPE ixmltdom,
                 <text>      
TYPE ixmltdom,
                 <text_cont> 
TYPE gty_strings.
 
IF iv_filecontent IS INITIAL.
   
CALL METHOD cl_gui_frontend_services=>gui_upload
     
EXPORTING
        filename  
= iv_filepath
        filetype  
= 'BIN'                                  "#EC NOTEXT
     
IMPORTING
        filelength
= lv_len
     
CHANGING
        data_tab  
= lt_data
     
EXCEPTIONS
       
OTHERS     = 19.
   
IF sy-subrc <> 0.
*   MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*              WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
   
ENDIF.

   
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
     
EXPORTING
        input_length      
= lv_len
     
IMPORTING
       
buffer             = lv_xstring
     
TABLES
        binary_tab        
= lt_data
*   EXCEPTIONS
*     FAILED             = 1
*     OTHERS             = 2
             
.
   
IF sy-subrc <> 0.
* Implement suitable error handling here
   
ENDIF.
 
ELSE.
    lv_xstring
= iv_filecontent.
 
ENDIF.

 
CREATE OBJECT lr_xls_reader
   
EXPORTING
      iv_xstring
= lv_xstring.

 
LOOP AT lr_xls_reader->gt_sheets INTO lr_dom.
   
LOOP AT lr_dom->dom_table ASSIGNING <dom_line>
     
WHERE name = 'row'.                                   "#EC NOTEXT
     
ADD 1 TO lv_row_indx.
     
LOOP AT lr_dom->dom_table ASSIGNING <cell>
       
WHERE name = 'c' AND                                "#EC NOTEXT
              parent
= <dom_line>-gid.
       
ADD 1 TO lv_col_indx.
       
APPEND INITIAL LINE TO lt_cells ASSIGNING <cell_value>.

        <cell_value>
-row = lv_row_indx.
        <cell_value>
-col = lv_col_indx.
       
LOOP AT lr_dom->dom_table ASSIGNING <value>
         
WHERE name = 'v' AND                              "#EC NOTEXT
                parent
= <cell>-gid.
         
READ TABLE lr_dom->dom_table ASSIGNING <text>
           
WITH KEY name = c_text_tag
                     parent
= <value>-gid.
         
IF sy-subrc = 0. "Value exists
           
READ TABLE lr_dom->dom_table
             
WITH KEY
              name
= 't'
             
value = 's'
              parent
= <cell>-gid
             
TRANSPORTING NO FIELDS.
           
IF sy-subrc = 0. "It's a text
             
READ TABLE lr_xls_reader->gt_strings
               
WITH KEY index = <text>-value
               
ASSIGNING <text_cont>.
             
IF sy-subrc = 0.
                <cell_value>
-value = <text_cont>-string.
               
EXIT.
             
ENDIF.
           
ENDIF.
            <cell_value>
-value = <text>-value.
           
EXIT.
         
ENDIF.
       
ENDLOOP.
     
ENDLOOP.
     
CLEAR lv_col_indx.
   
ENDLOOP.
   
EXIT.
 
ENDLOOP.
 
DELETE lt_cells WHERE value IS INITIAL.
 
rt_result = lt_cells.
ENDMETHOD.

That’s all. The last method allows read both file from frontend and XLSX structure from xstring stream. The structure zhr_gan_alsmex_tabline is the copy of alsmex_tabline where type CHAR50 replaced by type STRING_UNICODE.

Hope it can be useful!

3 Comments
Labels in this area