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!
