*--------------------------------------------------------------------*
"1) ideal: OOXML Office
" Since office 2007, XSLX is default extension for excel and in reality
" this extension just a compressed archive of XML text documents.
" workbook, sheets, data, shape...all are save in various xml file which
" you can easy see by change any extension of excel file from xlsx to zip
" With this new formation, also SAP provide a package S_OOXML_CORE to work
" with all kind of OOXML ( excel, ppt, word, zip) from version NW 702
" we can find a new way to read/write excel file in OOPs way
*--------------------------------------------------------------------*
*--------------------------------------------------------------------*
"2) Sample
" CL_XLSX_DOCUMENT: for excel
" CL_DOCX_DOCUMENT: for word
" CL_PPTX_DOCUMENT: for powerpoint
" https://codezentrale.de/tag/cl_xml_document/
*--------------------------------------------------------------------*
CLASS lcl_xlsx_document DEFINITION FINAL.
PUBLIC SECTION.
METHODS execute_xlsx.
CLASS-METHODS get_document IMPORTING VALUE(i_location) TYPE c
RETURNING VALUE(c_file) TYPE string.
METHODS get_xlsx_xml.
METHODS get_xlsx_data.
PRIVATE SECTION.
TYPES:
BEGIN OF sheet_data_holder,
sheet TYPE REF TO data,
END OF sheet_data_holder,
BEGIN OF shareds_conv,
index TYPE i,
value TYPE string,
END OF shareds_conv,
BEGIN OF sheet_conv,
sheet TYPE string,
row TYPE string,
cell TYPE string,
type TYPE string,
style TYPE REF TO cl_abap_elemdescr,
value TYPE string,
END OF sheet_conv,
BEGIN OF style_conv,
id TYPE i,
fmtid TYPE i,
abap_t TYPE REF TO cl_abap_elemdescr,
END OF style_conv.
DATA sheets_data TYPE TABLE OF sheet_data_holder.
DATA sheets_line TYPE REF TO data.
DATA tab_typ TYPE REF TO cl_abap_tabledescr.
DATA struct_typ TYPE REF TO cl_abap_structdescr.
CLASS-DATA mv_file_data TYPE xstring.
CLASS-DATA mv_shareds_xml TYPE xstring.
CLASS-DATA mv_styles_xml TYPE xstring.
CLASS-DATA mo_doc TYPE REF TO cl_xlsx_document.
CLASS-DATA mt_sheet_xml TYPE TABLE OF xstring.
CLASS-DATA mt_shareds_str TYPE TABLE OF shareds_conv.
CLASS-DATA mt_sheet_str TYPE TABLE OF sheet_conv.
CLASS-DATA mt_style_str TYPE TABLE OF style_conv.
CONSTANTS mc_sheet_xml TYPE i VALUE 2.
CONSTANTS mc_shared_str_xml TYPE i VALUE 3.
CONSTANTS mc_cell_syle_xml TYPE i VALUE 4.
CONSTANTS mc_application TYPE c VALUE 'A'.
CONSTANTS mc_presentation TYPE c VALUE 'P'.
CONSTANTS mc_base_date TYPE d VALUE '19000101'.
CONSTANTS mc_share_sst TYPE string VALUE 'sst'.
CONSTANTS mc_sheet_dat TYPE string VALUE 'sheetData'.
CONSTANTS mc_style_xfs TYPE string VALUE 'cellXfs'.
METHODS get_xml_data IMPORTING VALUE(iv_xml_index) TYPE i.
METHODS get_str_data IMPORTING VALUE(iv_xml_index) TYPE i.
METHODS get_abap_data IMPORTING style TYPE REF TO cl_abap_elemdescr
VALUE(val) TYPE any
RETURNING VALUE(ret) TYPE string.
METHODS xlsx2abap_strans.
METHODS xlsx2abap_xml.
METHODS create_xlsx.
METHODS create_dyn_tab IMPORTING VALUE(i_col) TYPE i.
METHODS display_alv.
METHODS display_xml.
ENDCLASS. "lcl_xlsx_document DEFINITION
CLASS lcl_xlsx_document IMPLEMENTATION.
METHOD execute_xlsx.
get_document( mc_presentation ).
xlsx2abap_xml( ).
display_alv( ).
* display_xml( ).
ENDMETHOD.
METHOD get_document.
DATA: ls_xfile TYPE x255,
lt_xfile TYPE TABLE OF x255,
sev_file TYPE dxfields-longpath.
DATA: l_len TYPE i,
l_size TYPE i.
CASE i_location.
WHEN mc_presentation. "2.2.1.1) From presentation server:
TRY.
mv_file_data = cl_openxml_helper=>load_local_file(
cl_openxml_helper=>browse_local_file_open(
iv_title = 'Select XLSX File'
iv_filename = 'C:\Users\z2j6142\Desktop\export.xlsx'
iv_extpattern = 'All files(*.*)|*.*' ) ).
CATCH cx_openxml_not_found.
EXIT.
ENDTRY.
WHEN mc_application. "2.2.1.2) From application server:
IF c_file IS INITIAL.
CALL FUNCTION 'F4_DXFILENAME_TOPRECURSION'
EXPORTING
i_location_flag = i_location
i_server = ''
* i_path = ''
* filemask = '*.*'
* fileoperation = 'R'
IMPORTING
o_path = sev_file
EXCEPTIONS
rfc_error = 1
error_with_gui = 2
OTHERS = 3.
IF sy-subrc <> 0.
EXIT.
ELSE.
c_file = sev_file.
ENDIF.
ENDIF.
TRY.
CLEAR ls_xfile.
OPEN DATASET c_file FOR INPUT IN BINARY MODE.
IF sy-subrc <> 0.
EXIT.
ENDIF.
DO.
READ DATASET c_file INTO ls_xfile LENGTH l_len.
IF sy-subrc <> 0.
IF l_len > 0.
l_size = l_size + l_len.
APPEND ls_xfile TO lt_xfile.
ENDIF.
EXIT.
ENDIF.
l_size = l_size + l_len.
APPEND ls_xfile TO lt_xfile.
ENDDO.
IF sy-subrc > 10. EXIT. ENDIF.
CLOSE DATASET c_file.
CHECK lt_xfile IS NOT INITIAL.
* CL_BCS_CONVERT
cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab = lt_xfile
im_size = l_size
IMPORTING ex_xstring = mv_file_data ).
CATCH cx_root INTO DATA(msg).
MESSAGE msg->get_text( ) TYPE 'E'.
LEAVE TO LIST-PROCESSING.
ENDTRY.
WHEN OTHERS.
ENDCASE.
TRY.
mo_doc = cl_xlsx_document=>load_document( iv_data = mv_file_data ).
CATCH cx_root INTO msg.
MESSAGE msg->get_text( ) TYPE 'E'.
LEAVE TO LIST-PROCESSING.
ENDTRY.
ENDMETHOD.
METHOD get_xlsx_xml.
"Read sheets
get_xml_data( mc_sheet_xml ).
"Read the shared string XML
get_xml_data( mc_shared_str_xml ).
"Read the cell style
get_xml_data( mc_cell_syle_xml ).
ENDMETHOD. "get_xlsx_xml
METHOD get_xlsx_data.
"Read the shared string XML
get_str_data( mc_shared_str_xml ).
"Read the cell style
get_str_data( mc_cell_syle_xml ).
"Read sheets
get_str_data( mc_sheet_xml ).
ENDMETHOD. "get_xlsx_data
METHOD get_xml_data.
* DATA xml_obj_part TYPE REF TO cl_openxml_part.
* DATA xml_obj_part_uri TYPE REF TO cl_openxml_parturi.
* DATA lx_root TYPE REF TO cx_root.
* DATA lv_uri TYPE string.
DATA(lr_zip) = NEW cl_abap_zip( ).
lr_zip->load( mv_file_data ).
TRY.
CASE iv_xml_index.
WHEN mc_sheet_xml.
"zip way
LOOP AT lr_zip->files INTO DATA(file) WHERE name CP 'xl/worksheets/*.xml'.
lr_zip->get( EXPORTING name = file-name
IMPORTING content = DATA(lv_xml_data) ).
APPEND lv_xml_data TO mt_sheet_xml.
ENDLOOP.
"xlsx way
* DO mo_doc->get_workbookpart( )->get_worksheetparts( )->get_count( ) TIMES.
* DATA(lv_xml_data) = mo_doc->get_workbookpart(
* )->get_worksheetparts(
* )->get_part( sy-index )->get_data( ).
* APPEND lv_xml_data TO mt_sheet_xml.
* ENDDO.
WHEN mc_shared_str_xml.
"zip way
lr_zip->get( EXPORTING name = 'xl/sharedStrings.xml'
IMPORTING content = mv_shareds_xml ).
* "xlsx way
* mv_shareds_xml = mo_doc->get_workbookpart( )->get_sharedstringspart( )->get_data( ).
WHEN mc_cell_syle_xml.
"zip way
lr_zip->get( EXPORTING name = 'xl/styles.xml'
IMPORTING content = mv_styles_xml ).
* "xlsx way
* mv_styles_xml = mo_doc->get_workbookpart( )->get_stylespart( )->get_data( ).
WHEN OTHERS.
ENDCASE.
CATCH cx_openxml_format cx_openxml_not_found INTO DATA(msg).
MESSAGE msg->get_text( ) TYPE 'E'.
ENDTRY.
ENDMETHOD. "get_xml_data
METHOD xlsx2abap_strans.
* DATA lv_shared_conv TYPE xstring.
* DATA lo_shared_str_dom TYPE REF TO if_ixml_document.
* DATA lo_shared_str_nodeset TYPE REF TO if_ixml_node.
* DATA lv_shared_str_xml TYPE xstring.
*
* TRY.
** CALL TRANSFORMATION znamespace
** SOURCE XML mv_shareds_xml
** RESULT XML lv_shared_conv.
*
* "Create a nodeset from the shared XML
* CALL FUNCTION 'SDIXML_XML_TO_DOM'
* EXPORTING
* xml = lv_shared_conv
* IMPORTING
* document = lo_shared_str_dom
* EXCEPTIONS
* invalid_input = 1
* OTHERS = 2.
* IF sy-subrc = 0.
* lo_shared_str_nodeset = lo_shared_str_dom->clone( ).
* ENDIF.
*
** CALL TRANSFORMATION zdeserialized
** PARAMETERS p_shared_string = lo_shared_str_nodeset
** SOURCE XML mv_sheet_data
** RESULT lt_spfli = mt_spfli.
* CATCH cx_xslt_exception.
* ENDTRY.
* "transform namespace:
** <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="http://sch emas.openxmlformats.org/spreadsheetml/2006/main" version="1.1">
**
** <xsl:strip-space elements="*"/>
** <xsl:output encoding="utf-8" indent="yes" method="xml" omit-xml-declaration="yes"/>
**
** <xsl:template match="/">
** <xsl:element name="sst" namespace="">
** <xsl:for-each select="ss:sst/ss:si">
** <xsl:element name="si" namespace="">
** <xsl:element name="t" namespace="">
** <xsl:value-of select="ss:t"/>
** </xsl:element>
** </xsl:element>
** </xsl:for-each>
** </xsl:element>
** </xsl:template>
**
** </xsl:stylesheet>
*
* "transform deserialized
**<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:sap="http://www.sap.com/sapxsl" xmlns:asx="http://www.sap.com/abapxml" exclude-result-prefixes="java"
**version="1.0">
**
** <xsl:param name="P_SHARED_STRING" select=""/>
**
** <xsl:strip-space elements="*"/>
** <xsl:output encoding="utf-8" indent="yes" omit-xml-declaration="yes"/>
**
** <xsl:variable name="V_SHARED_STRING">
** <xsl:if test="$P_SHARED_STRING">
** <xsl:copy-of select="$P_SHARED_STRING"/>
** </xsl:if>
** </xsl:variable>
**
** <xsl:template match="/" xmlns:cal="xalan://java.util.GregorianCalendar">
** <asx:abap version="1.0">
** <asx:values>
** <LT_SPFLI>
** <xsl:for-each select="ss:worksheet/ss:sheetData/ss:row">
** <xsl:if test="position() > 1">
** <item>
** <CARRID>
** <xsl:variable name="cell_id" select="concat('A', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </CARRID>
** <CONNID>
** <xsl:variable name="cell_id" select="concat('B', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </CONNID>
** <COUNTRYFR>
** <xsl:variable name="cell_id" select="concat('C', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </COUNTRYFR>
** <CITYFROM>
** <xsl:variable name="cell_id" select="concat('D', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </CITYFROM>
** <AIRPFROM>
** <xsl:variable name="cell_id" select="concat('E', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </AIRPFROM>
** <COUNTRYTO>
** <xsl:variable name="cell_id" select="concat('F', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </COUNTRYTO>
** <CITYTO>
** <xsl:variable name="cell_id" select="concat('G', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </CITYTO>
** <AIRPTO>
** <xsl:variable name="cell_id" select="concat('H', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </AIRPTO>
**
** <FLTIME>
** <xsl:variable name="cell_id" select="concat('I', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="translate($V_SHARED_STRING/sst/si[$v_index + 1]/t,':','')"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </FLTIME>
**
** <DEPTIME>
** <xsl:variable name="cell_id" select="concat('J', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:variable name="v_h" select="ss:c[@r=$cell_id]/ss:v"/>
** <xsl:variable name="v_s" select="translate(concat(format-number(floor($v_h ), '00:'),
** format-number(floor($v_h * 60 mod 60), '00:'),
** format-number(floor($v_h * 360 mod 360), '00')),':','')"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="translate(concat(format-number(floor($v_h ), '00:'),
** format-number(floor($v_h * 60 mod 60), '00:'),
** format-number(floor($v_h * 360 mod 360), '00')),':','')"/>
**<!-- <xsl:value-of select="translate($V_SHARED_STRING/sst/si[$v_index + 1]/t,':','')"/>-->
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </DEPTIME>
**
** <ARRTIME>
** <xsl:variable name="cell_id" select="concat('K', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="translate($V_SHARED_STRING/sst/si[$v_index + 1]/t,':','')"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </ARRTIME>
**
** <DISTANCE>
** <xsl:variable name="cell_id" select="concat('L', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </DISTANCE>
** <DISTID>
** <xsl:variable name="cell_id" select="concat('M', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </DISTID>
** <FLTYPE>
** <xsl:variable name="cell_id" select="concat('N', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </FLTYPE>
** <PERIOD>
** <xsl:variable name="cell_id" select="concat('O', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </PERIOD>
** </item>
** </xsl:if>
** </xsl:for-each>
** </LT_SPFLI>
** </asx:values>
** </asx:abap>
** </xsl:template>
**</xsl:transform>
ENDMETHOD. "transform_xml_to_abap
METHOD create_dyn_tab.
struct_typ = cl_abap_structdescr=>get(
VALUE #( FOR i = 1 UNTIL i > i_col
( name = |col{ i }|
type = CAST #( cl_abap_datadescr=>describe_by_name( 'STRING' ) ) ) ) ).
tab_typ = cl_abap_tabledescr=>create( struct_typ ).
ENDMETHOD.
METHOD display_alv.
FIELD-SYMBOLS <fdata> TYPE table.
LOOP AT sheets_data REFERENCE INTO DATA(sheets_line).
ASSIGN sheets_line->sheet->* TO <fdata>.
TRY.
cl_salv_table=>factory(
EXPORTING
list_display = if_salv_c_bool_sap=>true
IMPORTING
r_salv_table = DATA(salv) " Basis Class Simple ALV Tables
CHANGING
t_table = <fdata>
).
CATCH cx_salv_msg INTO DATA(msg).
MESSAGE msg TYPE 'I'.
ENDTRY.
salv->get_columns( )->set_optimize( ).
salv->display( ).
ENDLOOP.
ENDMETHOD. "display_alv
METHOD xlsx2abap_xml.
get_xlsx_xml( ).
get_xlsx_data( ).
ENDMETHOD.
METHOD display_xml.
DATA(xml_obj) = NEW cl_xml_document( ).
"Shared data
xml_obj->parse_xstring( stream = mv_shareds_xml ).
xml_obj->display( ).
"Cell styles
xml_obj->parse_xstring( stream = mv_styles_xml ).
xml_obj->display( ).
"Sheets data
LOOP AT mt_sheet_xml INTO DATA(lv_sheet_data).
xml_obj->parse_xstring( stream = lv_sheet_data ).
xml_obj->display( ).
ENDLOOP.
* DATA: lt_smum TYPE TABLE OF smum_xmltb,
* lt_return TYPE TABLE OF bapiret2.
*
* CALL FUNCTION 'SMUM_XML_PARSE'
* EXPORTING
* xml_input = mv_sheet_data"mv_shareds_xml
* TABLES
* xml_table = lt_smum
* return = lt_return.
*
** DELETE lt_smum WHERE type <> 'V'.
** BREAK-POINT.
*
* CALL FUNCTION 'DISPLAY_XML_STRING'
* EXPORTING
* xml_string = mv_sheet_data
* title = 'Sheet'
* STARTING_X = 5
* STARTING_Y = 5
* EXCEPTIONS
* no_xml_document = 1
* OTHERS = 2.
* IF sy-subrc <> 0.
* RETURN.
* ENDIF.
*
* CALL FUNCTION 'DISPLAY_XML_STRING'
* EXPORTING
* xml_string = mv_shareds_xml
* title = 'Shared'
* STARTING_X = 5
* STARTING_Y = 5
* EXCEPTIONS
* no_xml_document = 1
* OTHERS = 2.
* IF sy-subrc <> 0.
* RETURN.
* ENDIF.
ENDMETHOD.
METHOD create_xlsx.
***2.1) Create new excel file from scratch:
**LO_DOC = CL_XLSX_DOCUMENT=>CREATE_DOCUMENT( ).
*
** Get parts
**TRY.
**
*** Transformation for the sheet part
** lo_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
**
** CALL TRANSFORMATION salv_bs_xml_off2007_sheet
** SOURCE param = sheet_struct
** RESULT XML lo_writer.
**
** l_sheetxml = lo_writer->get_output( abap_true ).
**
** sharedstring_struct-t_strings = me->t_strings.
**
*** Transformation for the shared strings part
** lo_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
**
** CALL TRANSFORMATION salv_bs_xml_off2007_shared
** SOURCE param = sharedstring_struct
** RESULT XML lo_writer.
**
** l_shared_xml = lo_writer->get_output( abap_true ).
**
*** Transformation for the styles part
** lo_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
**
** CALL TRANSFORMATION salv_bs_xml_off2007_style
** SOURCE param = style_struct
** RESULT XML lo_writer.
**
** l_styles_xml = lo_writer->get_output( abap_true ).
**
*** Transformation for the drawing part
** IF image_struct IS NOT INITIAL.
** lo_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
**
** CALL TRANSFORMATION salv_bs_xml_off2007_drawing
** SOURCE param = image_struct
** RESULT XML lo_writer.
**
** l_drawing_xml = lo_writer->get_output( abap_true ).
** ENDIF.
**
** CATCH cx_xslt_abap_call_error. "#EC NO_HANDLER
** CATCH cx_xslt_deserialization_error. "#EC NO_HANDLER
** CATCH cx_xslt_format_error. "#EC NO_HANDLER
** CATCH cx_xslt_runtime_error. "#EC NO_HANDLER
** CATCH cx_xslt_serialization_error. "#EC NO_HANDLER
**ENDTRY.
*
ENDMETHOD.
METHOD get_str_data.
DATA: sheet_obj TYPE REF TO if_ixml_node,
shareds_obj TYPE REF TO if_ixml_node,
style_obj TYPE REF TO if_ixml_node,
rows_obj TYPE REF TO if_ixml_node_list,
cells_obj TYPE REF TO if_ixml_node_list.
FIELD-SYMBOLS <sheets_data> TYPE table.
FIELD-SYMBOLS <sheets_line> TYPE any.
DATA(xml_obj) = NEW cl_xml_document( ).
CASE iv_xml_index.
WHEN mc_sheet_xml.
REFRESH mt_sheet_str.
LOOP AT mt_sheet_xml INTO DATA(lv_sheet_data).
xml_obj->parse_xstring( stream = lv_sheet_data ).
sheet_obj = xml_obj->find_node( mc_sheet_dat ).
rows_obj = sheet_obj->get_children( ).
"Read rows & create table
DO rows_obj->get_length( ) TIMES.
cells_obj = rows_obj->get_item( sy-index - 1 )->get_children( ).
IF sy-index = 1.
DATA(col_count) = CONV i( substring_after( val = CAST if_ixml_element( rows_obj->get_item( 0 ) )->get_attribute_ns( 'spans' )
sub = ':' ) ).
create_dyn_tab( col_count ).
CREATE DATA sheets_line TYPE HANDLE struct_typ.
APPEND INITIAL LINE TO sheets_data ASSIGNING FIELD-SYMBOL(<fdata>).
CREATE DATA <fdata>-sheet TYPE HANDLE tab_typ.
ASSIGN <fdata>-sheet->* TO <sheets_data>.
ASSIGN sheets_line->* TO <sheets_line>.
ENDIF.
DO cells_obj->get_length( ) TIMES.
DATA(cell_obj) = CAST if_ixml_element( cells_obj->get_item( sy-index - 1 ) ).
ASSIGN COMPONENT sy-index OF STRUCTURE <sheets_line> TO FIELD-SYMBOL(<line_col>).
<line_col> = COND string( LET type = cell_obj->get_attribute_ns( 't' )
style = VALUE #( mt_style_str[ id = cell_obj->get_attribute_ns( 's' ) ]-abap_t OPTIONAL )
r_val = VALUE #( mt_shareds_str[ index = cell_obj->get_children( )->get_item( 0 )->get_value( ) ]-value OPTIONAL )
c_val = cell_obj->get_children( )->get_item( 0 )->get_value( )
IN WHEN type = 's' THEN get_abap_data( style = style val = r_val )
ELSE get_abap_data( style = style val = c_val ) ) .
ENDDO.
APPEND <sheets_line> TO <sheets_data>.
CLEAR <sheets_line>.
mt_sheet_str = VALUE #( BASE mt_sheet_str
FOR i = 0 UNTIL i = cells_obj->get_length( )
LET cell = CAST if_ixml_element( cells_obj->get_item( i ) )
IN ( sheet = |sheet{ sy-tabix }|
row = |row{ sy-index }|
cell = cell->get_attribute_ns( 'r' )
type = cell->get_attribute_ns( 't' )
style = VALUE #( mt_style_str[ id = cell->get_attribute_ns( 's' ) ]-abap_t OPTIONAL )
value = cell->get_children( )->get_item( 0 )->get_value( ) ) ).
ENDDO.
* "Map sheet data and shared string
* LOOP AT mt_sheet_str INTO DATA(ls_sheet) GROUP BY ( sheet = ls_sheet-sheet row = ls_sheet-row ).
*
* DATA(ls_str) = REDUCE string( INIT str TYPE string
* FOR mem IN GROUP ls_sheet
* NEXT str = str && |{ COND string( WHEN mem-type = 's'
* THEN VALUE #( mt_shareds_str[ index = mem-value ]-value OPTIONAL )
* ELSE mem-value ) },| ).
* mt_data = VALUE #( BASE mt_data ( line = ls_str ) ).
* ENDLOOP.
ENDLOOP.
WHEN mc_shared_str_xml.
xml_obj->parse_xstring( stream = mv_shareds_xml ).
shareds_obj = xml_obj->find_node( mc_share_sst ).
REFRESH mt_shareds_str.
DO shareds_obj->get_children( )->get_length( ) TIMES.
DATA(si_obj) = CAST if_ixml_element( shareds_obj->get_children( )->get_item( sy-index - 1 ) ).
mt_shareds_str = VALUE #( BASE mt_shareds_str
( index = sy-index - 1
value = REDUCE #( INIT value TYPE string
FOR i = 0 THEN i + 1 UNTIL i = si_obj->get_children( )->get_length( )
NEXT value = value && si_obj->get_children( )->get_item( i )->get_value( ) ) ) ).
ENDDO.
WHEN mc_cell_syle_xml.
*1 0
*2 0.00
*3 #,##0
*4 #,##0.00
*5 $#,##0_);($#,##0)
*6 $#,##0_);[Red]($#,##0)
*7 $#,##0.00_);($#,##0.00)
*8 $#,##0.00_);[Red]($#,##0.00)
*9 0%
*10 0.00%
*11 0.00E+00
*12 # ?/?
*13 # ??/??
*14 m/d/yyyy
*15 d-mmm-yy
*16 d-mmm
*17 mmm-yy
*18 h:mm AM/PM
*19 h:mm:ss AM/PM
*20 h:mm
*21 h:mm:ss
*22 m/d/yyyy h:mm
*37 #,##0_);(#,##0)
*38 #,##0_);[Red](#,##0)
*39 #,##0.00_);(#,##0.00)
*40 #,##0.00_);[Red](#,##0.00)
*45 mm:ss
*46 [h]:mm:ss
*47 mm:ss.0
*48 ##0.0E+0
*49 @
xml_obj->parse_xstring( stream = mv_styles_xml ).
style_obj = xml_obj->find_node( mc_style_xfs ).
REFRESH mt_style_str.
DO style_obj->get_children( )->get_length( ) TIMES.
DATA(xf_obj) = CAST if_ixml_element( style_obj->get_children( )->get_item( sy-index - 1 ) ).
mt_style_str = VALUE #( LET fmtid = xf_obj->get_attribute_ns( 'numFmtId' ) IN
BASE mt_style_str ( id = sy-index - 1
fmtid = fmtid
abap_t = SWITCH #( fmtid
WHEN 3 THEN cl_abap_elemdescr=>get_i( )
WHEN 4 THEN cl_abap_elemdescr=>get_f( )
WHEN 14 THEN cl_abap_elemdescr=>get_d( )
ELSE cl_abap_elemdescr=>get_string( )
) ) ).
ENDDO.
WHEN OTHERS.
ENDCASE.
ENDMETHOD.
METHOD get_abap_data.
CASE style->type_kind.
WHEN cl_abap_elemdescr=>typekind_int.
ret = |{ CONV i( val ) }|.
WHEN cl_abap_elemdescr=>typekind_date.
DATA date TYPE d.
date = mc_base_date + val - 2.
ret = |{ date DATE = ENVIRONMENT }|.
WHEN cl_abap_elemdescr=>typekind_float.
* ret = |{ CONV f( val ) }|.
ret = |{ val }|.
WHEN cl_abap_elemdescr=>typekind_string.
ret = val.
WHEN OTHERS.
ENDCASE.
ENDMETHOD.
ENDCLASS. "lcl_xlsx_document IMPLEMENTATION
START-OF-SELECTION.
DATA(lo_xlsx) = NEW lcl_xlsx_document( ).
lo_xlsx->execute_xlsx( ).
"1) ideal: OOXML Office
" Since office 2007, XSLX is default extension for excel and in reality
" this extension just a compressed archive of XML text documents.
" workbook, sheets, data, shape...all are save in various xml file which
" you can easy see by change any extension of excel file from xlsx to zip
" With this new formation, also SAP provide a package S_OOXML_CORE to work
" with all kind of OOXML ( excel, ppt, word, zip) from version NW 702
" we can find a new way to read/write excel file in OOPs way
*--------------------------------------------------------------------*
*--------------------------------------------------------------------*
"2) Sample
" CL_XLSX_DOCUMENT: for excel
" CL_DOCX_DOCUMENT: for word
" CL_PPTX_DOCUMENT: for powerpoint
" https://codezentrale.de/tag/cl_xml_document/
*--------------------------------------------------------------------*
CLASS lcl_xlsx_document DEFINITION FINAL.
PUBLIC SECTION.
METHODS execute_xlsx.
CLASS-METHODS get_document IMPORTING VALUE(i_location) TYPE c
RETURNING VALUE(c_file) TYPE string.
METHODS get_xlsx_xml.
METHODS get_xlsx_data.
PRIVATE SECTION.
TYPES:
BEGIN OF sheet_data_holder,
sheet TYPE REF TO data,
END OF sheet_data_holder,
BEGIN OF shareds_conv,
index TYPE i,
value TYPE string,
END OF shareds_conv,
BEGIN OF sheet_conv,
sheet TYPE string,
row TYPE string,
cell TYPE string,
type TYPE string,
style TYPE REF TO cl_abap_elemdescr,
value TYPE string,
END OF sheet_conv,
BEGIN OF style_conv,
id TYPE i,
fmtid TYPE i,
abap_t TYPE REF TO cl_abap_elemdescr,
END OF style_conv.
DATA sheets_data TYPE TABLE OF sheet_data_holder.
DATA sheets_line TYPE REF TO data.
DATA tab_typ TYPE REF TO cl_abap_tabledescr.
DATA struct_typ TYPE REF TO cl_abap_structdescr.
CLASS-DATA mv_file_data TYPE xstring.
CLASS-DATA mv_shareds_xml TYPE xstring.
CLASS-DATA mv_styles_xml TYPE xstring.
CLASS-DATA mo_doc TYPE REF TO cl_xlsx_document.
CLASS-DATA mt_sheet_xml TYPE TABLE OF xstring.
CLASS-DATA mt_shareds_str TYPE TABLE OF shareds_conv.
CLASS-DATA mt_sheet_str TYPE TABLE OF sheet_conv.
CLASS-DATA mt_style_str TYPE TABLE OF style_conv.
CONSTANTS mc_sheet_xml TYPE i VALUE 2.
CONSTANTS mc_shared_str_xml TYPE i VALUE 3.
CONSTANTS mc_cell_syle_xml TYPE i VALUE 4.
CONSTANTS mc_application TYPE c VALUE 'A'.
CONSTANTS mc_presentation TYPE c VALUE 'P'.
CONSTANTS mc_base_date TYPE d VALUE '19000101'.
CONSTANTS mc_share_sst TYPE string VALUE 'sst'.
CONSTANTS mc_sheet_dat TYPE string VALUE 'sheetData'.
CONSTANTS mc_style_xfs TYPE string VALUE 'cellXfs'.
METHODS get_xml_data IMPORTING VALUE(iv_xml_index) TYPE i.
METHODS get_str_data IMPORTING VALUE(iv_xml_index) TYPE i.
METHODS get_abap_data IMPORTING style TYPE REF TO cl_abap_elemdescr
VALUE(val) TYPE any
RETURNING VALUE(ret) TYPE string.
METHODS xlsx2abap_strans.
METHODS xlsx2abap_xml.
METHODS create_xlsx.
METHODS create_dyn_tab IMPORTING VALUE(i_col) TYPE i.
METHODS display_alv.
METHODS display_xml.
ENDCLASS. "lcl_xlsx_document DEFINITION
CLASS lcl_xlsx_document IMPLEMENTATION.
METHOD execute_xlsx.
get_document( mc_presentation ).
xlsx2abap_xml( ).
display_alv( ).
* display_xml( ).
ENDMETHOD.
METHOD get_document.
DATA: ls_xfile TYPE x255,
lt_xfile TYPE TABLE OF x255,
sev_file TYPE dxfields-longpath.
DATA: l_len TYPE i,
l_size TYPE i.
CASE i_location.
WHEN mc_presentation. "2.2.1.1) From presentation server:
TRY.
mv_file_data = cl_openxml_helper=>load_local_file(
cl_openxml_helper=>browse_local_file_open(
iv_title = 'Select XLSX File'
iv_filename = 'C:\Users\z2j6142\Desktop\export.xlsx'
iv_extpattern = 'All files(*.*)|*.*' ) ).
CATCH cx_openxml_not_found.
EXIT.
ENDTRY.
WHEN mc_application. "2.2.1.2) From application server:
IF c_file IS INITIAL.
CALL FUNCTION 'F4_DXFILENAME_TOPRECURSION'
EXPORTING
i_location_flag = i_location
i_server = ''
* i_path = ''
* filemask = '*.*'
* fileoperation = 'R'
IMPORTING
o_path = sev_file
EXCEPTIONS
rfc_error = 1
error_with_gui = 2
OTHERS = 3.
IF sy-subrc <> 0.
EXIT.
ELSE.
c_file = sev_file.
ENDIF.
ENDIF.
TRY.
CLEAR ls_xfile.
OPEN DATASET c_file FOR INPUT IN BINARY MODE.
IF sy-subrc <> 0.
EXIT.
ENDIF.
DO.
READ DATASET c_file INTO ls_xfile LENGTH l_len.
IF sy-subrc <> 0.
IF l_len > 0.
l_size = l_size + l_len.
APPEND ls_xfile TO lt_xfile.
ENDIF.
EXIT.
ENDIF.
l_size = l_size + l_len.
APPEND ls_xfile TO lt_xfile.
ENDDO.
IF sy-subrc > 10. EXIT. ENDIF.
CLOSE DATASET c_file.
CHECK lt_xfile IS NOT INITIAL.
* CL_BCS_CONVERT
cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab = lt_xfile
im_size = l_size
IMPORTING ex_xstring = mv_file_data ).
CATCH cx_root INTO DATA(msg).
MESSAGE msg->get_text( ) TYPE 'E'.
LEAVE TO LIST-PROCESSING.
ENDTRY.
WHEN OTHERS.
ENDCASE.
TRY.
mo_doc = cl_xlsx_document=>load_document( iv_data = mv_file_data ).
CATCH cx_root INTO msg.
MESSAGE msg->get_text( ) TYPE 'E'.
LEAVE TO LIST-PROCESSING.
ENDTRY.
ENDMETHOD.
METHOD get_xlsx_xml.
"Read sheets
get_xml_data( mc_sheet_xml ).
"Read the shared string XML
get_xml_data( mc_shared_str_xml ).
"Read the cell style
get_xml_data( mc_cell_syle_xml ).
ENDMETHOD. "get_xlsx_xml
METHOD get_xlsx_data.
"Read the shared string XML
get_str_data( mc_shared_str_xml ).
"Read the cell style
get_str_data( mc_cell_syle_xml ).
"Read sheets
get_str_data( mc_sheet_xml ).
ENDMETHOD. "get_xlsx_data
METHOD get_xml_data.
* DATA xml_obj_part TYPE REF TO cl_openxml_part.
* DATA xml_obj_part_uri TYPE REF TO cl_openxml_parturi.
* DATA lx_root TYPE REF TO cx_root.
* DATA lv_uri TYPE string.
DATA(lr_zip) = NEW cl_abap_zip( ).
lr_zip->load( mv_file_data ).
TRY.
CASE iv_xml_index.
WHEN mc_sheet_xml.
"zip way
LOOP AT lr_zip->files INTO DATA(file) WHERE name CP 'xl/worksheets/*.xml'.
lr_zip->get( EXPORTING name = file-name
IMPORTING content = DATA(lv_xml_data) ).
APPEND lv_xml_data TO mt_sheet_xml.
ENDLOOP.
"xlsx way
* DO mo_doc->get_workbookpart( )->get_worksheetparts( )->get_count( ) TIMES.
* DATA(lv_xml_data) = mo_doc->get_workbookpart(
* )->get_worksheetparts(
* )->get_part( sy-index )->get_data( ).
* APPEND lv_xml_data TO mt_sheet_xml.
* ENDDO.
WHEN mc_shared_str_xml.
"zip way
lr_zip->get( EXPORTING name = 'xl/sharedStrings.xml'
IMPORTING content = mv_shareds_xml ).
* "xlsx way
* mv_shareds_xml = mo_doc->get_workbookpart( )->get_sharedstringspart( )->get_data( ).
WHEN mc_cell_syle_xml.
"zip way
lr_zip->get( EXPORTING name = 'xl/styles.xml'
IMPORTING content = mv_styles_xml ).
* "xlsx way
* mv_styles_xml = mo_doc->get_workbookpart( )->get_stylespart( )->get_data( ).
WHEN OTHERS.
ENDCASE.
CATCH cx_openxml_format cx_openxml_not_found INTO DATA(msg).
MESSAGE msg->get_text( ) TYPE 'E'.
ENDTRY.
ENDMETHOD. "get_xml_data
METHOD xlsx2abap_strans.
* DATA lv_shared_conv TYPE xstring.
* DATA lo_shared_str_dom TYPE REF TO if_ixml_document.
* DATA lo_shared_str_nodeset TYPE REF TO if_ixml_node.
* DATA lv_shared_str_xml TYPE xstring.
*
* TRY.
** CALL TRANSFORMATION znamespace
** SOURCE XML mv_shareds_xml
** RESULT XML lv_shared_conv.
*
* "Create a nodeset from the shared XML
* CALL FUNCTION 'SDIXML_XML_TO_DOM'
* EXPORTING
* xml = lv_shared_conv
* IMPORTING
* document = lo_shared_str_dom
* EXCEPTIONS
* invalid_input = 1
* OTHERS = 2.
* IF sy-subrc = 0.
* lo_shared_str_nodeset = lo_shared_str_dom->clone( ).
* ENDIF.
*
** CALL TRANSFORMATION zdeserialized
** PARAMETERS p_shared_string = lo_shared_str_nodeset
** SOURCE XML mv_sheet_data
** RESULT lt_spfli = mt_spfli.
* CATCH cx_xslt_exception.
* ENDTRY.
* "transform namespace:
** <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="http://sch emas.openxmlformats.org/spreadsheetml/2006/main" version="1.1">
**
** <xsl:strip-space elements="*"/>
** <xsl:output encoding="utf-8" indent="yes" method="xml" omit-xml-declaration="yes"/>
**
** <xsl:template match="/">
** <xsl:element name="sst" namespace="">
** <xsl:for-each select="ss:sst/ss:si">
** <xsl:element name="si" namespace="">
** <xsl:element name="t" namespace="">
** <xsl:value-of select="ss:t"/>
** </xsl:element>
** </xsl:element>
** </xsl:for-each>
** </xsl:element>
** </xsl:template>
**
** </xsl:stylesheet>
*
* "transform deserialized
**<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ss="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:sap="http://www.sap.com/sapxsl" xmlns:asx="http://www.sap.com/abapxml" exclude-result-prefixes="java"
**version="1.0">
**
** <xsl:param name="P_SHARED_STRING" select=""/>
**
** <xsl:strip-space elements="*"/>
** <xsl:output encoding="utf-8" indent="yes" omit-xml-declaration="yes"/>
**
** <xsl:variable name="V_SHARED_STRING">
** <xsl:if test="$P_SHARED_STRING">
** <xsl:copy-of select="$P_SHARED_STRING"/>
** </xsl:if>
** </xsl:variable>
**
** <xsl:template match="/" xmlns:cal="xalan://java.util.GregorianCalendar">
** <asx:abap version="1.0">
** <asx:values>
** <LT_SPFLI>
** <xsl:for-each select="ss:worksheet/ss:sheetData/ss:row">
** <xsl:if test="position() > 1">
** <item>
** <CARRID>
** <xsl:variable name="cell_id" select="concat('A', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </CARRID>
** <CONNID>
** <xsl:variable name="cell_id" select="concat('B', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </CONNID>
** <COUNTRYFR>
** <xsl:variable name="cell_id" select="concat('C', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </COUNTRYFR>
** <CITYFROM>
** <xsl:variable name="cell_id" select="concat('D', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </CITYFROM>
** <AIRPFROM>
** <xsl:variable name="cell_id" select="concat('E', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </AIRPFROM>
** <COUNTRYTO>
** <xsl:variable name="cell_id" select="concat('F', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </COUNTRYTO>
** <CITYTO>
** <xsl:variable name="cell_id" select="concat('G', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </CITYTO>
** <AIRPTO>
** <xsl:variable name="cell_id" select="concat('H', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </AIRPTO>
**
** <FLTIME>
** <xsl:variable name="cell_id" select="concat('I', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="translate($V_SHARED_STRING/sst/si[$v_index + 1]/t,':','')"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </FLTIME>
**
** <DEPTIME>
** <xsl:variable name="cell_id" select="concat('J', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:variable name="v_h" select="ss:c[@r=$cell_id]/ss:v"/>
** <xsl:variable name="v_s" select="translate(concat(format-number(floor($v_h ), '00:'),
** format-number(floor($v_h * 60 mod 60), '00:'),
** format-number(floor($v_h * 360 mod 360), '00')),':','')"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="translate(concat(format-number(floor($v_h ), '00:'),
** format-number(floor($v_h * 60 mod 60), '00:'),
** format-number(floor($v_h * 360 mod 360), '00')),':','')"/>
**<!-- <xsl:value-of select="translate($V_SHARED_STRING/sst/si[$v_index + 1]/t,':','')"/>-->
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </DEPTIME>
**
** <ARRTIME>
** <xsl:variable name="cell_id" select="concat('K', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="translate($V_SHARED_STRING/sst/si[$v_index + 1]/t,':','')"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </ARRTIME>
**
** <DISTANCE>
** <xsl:variable name="cell_id" select="concat('L', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </DISTANCE>
** <DISTID>
** <xsl:variable name="cell_id" select="concat('M', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </DISTID>
** <FLTYPE>
** <xsl:variable name="cell_id" select="concat('N', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </FLTYPE>
** <PERIOD>
** <xsl:variable name="cell_id" select="concat('O', position())"/>
** <xsl:variable name="v_index" select="ss:c[@r=$cell_id][@t='s']/ss:v"/>
** <xsl:if test="$v_index">
** <xsl:value-of select="$V_SHARED_STRING/sst/si[$v_index + 1]/t"/>
** </xsl:if>
** <xsl:if test="not($v_index)">
** <xsl:value-of select="ss:c[@r=$cell_id]/ss:v"/>
** </xsl:if>
** </PERIOD>
** </item>
** </xsl:if>
** </xsl:for-each>
** </LT_SPFLI>
** </asx:values>
** </asx:abap>
** </xsl:template>
**</xsl:transform>
ENDMETHOD. "transform_xml_to_abap
METHOD create_dyn_tab.
struct_typ = cl_abap_structdescr=>get(
VALUE #( FOR i = 1 UNTIL i > i_col
( name = |col{ i }|
type = CAST #( cl_abap_datadescr=>describe_by_name( 'STRING' ) ) ) ) ).
tab_typ = cl_abap_tabledescr=>create( struct_typ ).
ENDMETHOD.
METHOD display_alv.
FIELD-SYMBOLS <fdata> TYPE table.
LOOP AT sheets_data REFERENCE INTO DATA(sheets_line).
ASSIGN sheets_line->sheet->* TO <fdata>.
TRY.
cl_salv_table=>factory(
EXPORTING
list_display = if_salv_c_bool_sap=>true
IMPORTING
r_salv_table = DATA(salv) " Basis Class Simple ALV Tables
CHANGING
t_table = <fdata>
).
CATCH cx_salv_msg INTO DATA(msg).
MESSAGE msg TYPE 'I'.
ENDTRY.
salv->get_columns( )->set_optimize( ).
salv->display( ).
ENDLOOP.
ENDMETHOD. "display_alv
METHOD xlsx2abap_xml.
get_xlsx_xml( ).
get_xlsx_data( ).
ENDMETHOD.
METHOD display_xml.
DATA(xml_obj) = NEW cl_xml_document( ).
"Shared data
xml_obj->parse_xstring( stream = mv_shareds_xml ).
xml_obj->display( ).
"Cell styles
xml_obj->parse_xstring( stream = mv_styles_xml ).
xml_obj->display( ).
"Sheets data
LOOP AT mt_sheet_xml INTO DATA(lv_sheet_data).
xml_obj->parse_xstring( stream = lv_sheet_data ).
xml_obj->display( ).
ENDLOOP.
* DATA: lt_smum TYPE TABLE OF smum_xmltb,
* lt_return TYPE TABLE OF bapiret2.
*
* CALL FUNCTION 'SMUM_XML_PARSE'
* EXPORTING
* xml_input = mv_sheet_data"mv_shareds_xml
* TABLES
* xml_table = lt_smum
* return = lt_return.
*
** DELETE lt_smum WHERE type <> 'V'.
** BREAK-POINT.
*
* CALL FUNCTION 'DISPLAY_XML_STRING'
* EXPORTING
* xml_string = mv_sheet_data
* title = 'Sheet'
* STARTING_X = 5
* STARTING_Y = 5
* EXCEPTIONS
* no_xml_document = 1
* OTHERS = 2.
* IF sy-subrc <> 0.
* RETURN.
* ENDIF.
*
* CALL FUNCTION 'DISPLAY_XML_STRING'
* EXPORTING
* xml_string = mv_shareds_xml
* title = 'Shared'
* STARTING_X = 5
* STARTING_Y = 5
* EXCEPTIONS
* no_xml_document = 1
* OTHERS = 2.
* IF sy-subrc <> 0.
* RETURN.
* ENDIF.
ENDMETHOD.
METHOD create_xlsx.
***2.1) Create new excel file from scratch:
**LO_DOC = CL_XLSX_DOCUMENT=>CREATE_DOCUMENT( ).
*
** Get parts
**TRY.
**
*** Transformation for the sheet part
** lo_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
**
** CALL TRANSFORMATION salv_bs_xml_off2007_sheet
** SOURCE param = sheet_struct
** RESULT XML lo_writer.
**
** l_sheetxml = lo_writer->get_output( abap_true ).
**
** sharedstring_struct-t_strings = me->t_strings.
**
*** Transformation for the shared strings part
** lo_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
**
** CALL TRANSFORMATION salv_bs_xml_off2007_shared
** SOURCE param = sharedstring_struct
** RESULT XML lo_writer.
**
** l_shared_xml = lo_writer->get_output( abap_true ).
**
*** Transformation for the styles part
** lo_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
**
** CALL TRANSFORMATION salv_bs_xml_off2007_style
** SOURCE param = style_struct
** RESULT XML lo_writer.
**
** l_styles_xml = lo_writer->get_output( abap_true ).
**
*** Transformation for the drawing part
** IF image_struct IS NOT INITIAL.
** lo_writer = cl_sxml_string_writer=>create( ignore_conversion_errors = abap_true ).
**
** CALL TRANSFORMATION salv_bs_xml_off2007_drawing
** SOURCE param = image_struct
** RESULT XML lo_writer.
**
** l_drawing_xml = lo_writer->get_output( abap_true ).
** ENDIF.
**
** CATCH cx_xslt_abap_call_error. "#EC NO_HANDLER
** CATCH cx_xslt_deserialization_error. "#EC NO_HANDLER
** CATCH cx_xslt_format_error. "#EC NO_HANDLER
** CATCH cx_xslt_runtime_error. "#EC NO_HANDLER
** CATCH cx_xslt_serialization_error. "#EC NO_HANDLER
**ENDTRY.
*
ENDMETHOD.
METHOD get_str_data.
DATA: sheet_obj TYPE REF TO if_ixml_node,
shareds_obj TYPE REF TO if_ixml_node,
style_obj TYPE REF TO if_ixml_node,
rows_obj TYPE REF TO if_ixml_node_list,
cells_obj TYPE REF TO if_ixml_node_list.
FIELD-SYMBOLS <sheets_data> TYPE table.
FIELD-SYMBOLS <sheets_line> TYPE any.
DATA(xml_obj) = NEW cl_xml_document( ).
CASE iv_xml_index.
WHEN mc_sheet_xml.
REFRESH mt_sheet_str.
LOOP AT mt_sheet_xml INTO DATA(lv_sheet_data).
xml_obj->parse_xstring( stream = lv_sheet_data ).
sheet_obj = xml_obj->find_node( mc_sheet_dat ).
rows_obj = sheet_obj->get_children( ).
"Read rows & create table
DO rows_obj->get_length( ) TIMES.
cells_obj = rows_obj->get_item( sy-index - 1 )->get_children( ).
IF sy-index = 1.
DATA(col_count) = CONV i( substring_after( val = CAST if_ixml_element( rows_obj->get_item( 0 ) )->get_attribute_ns( 'spans' )
sub = ':' ) ).
create_dyn_tab( col_count ).
CREATE DATA sheets_line TYPE HANDLE struct_typ.
APPEND INITIAL LINE TO sheets_data ASSIGNING FIELD-SYMBOL(<fdata>).
CREATE DATA <fdata>-sheet TYPE HANDLE tab_typ.
ASSIGN <fdata>-sheet->* TO <sheets_data>.
ASSIGN sheets_line->* TO <sheets_line>.
ENDIF.
DO cells_obj->get_length( ) TIMES.
DATA(cell_obj) = CAST if_ixml_element( cells_obj->get_item( sy-index - 1 ) ).
ASSIGN COMPONENT sy-index OF STRUCTURE <sheets_line> TO FIELD-SYMBOL(<line_col>).
<line_col> = COND string( LET type = cell_obj->get_attribute_ns( 't' )
style = VALUE #( mt_style_str[ id = cell_obj->get_attribute_ns( 's' ) ]-abap_t OPTIONAL )
r_val = VALUE #( mt_shareds_str[ index = cell_obj->get_children( )->get_item( 0 )->get_value( ) ]-value OPTIONAL )
c_val = cell_obj->get_children( )->get_item( 0 )->get_value( )
IN WHEN type = 's' THEN get_abap_data( style = style val = r_val )
ELSE get_abap_data( style = style val = c_val ) ) .
ENDDO.
APPEND <sheets_line> TO <sheets_data>.
CLEAR <sheets_line>.
mt_sheet_str = VALUE #( BASE mt_sheet_str
FOR i = 0 UNTIL i = cells_obj->get_length( )
LET cell = CAST if_ixml_element( cells_obj->get_item( i ) )
IN ( sheet = |sheet{ sy-tabix }|
row = |row{ sy-index }|
cell = cell->get_attribute_ns( 'r' )
type = cell->get_attribute_ns( 't' )
style = VALUE #( mt_style_str[ id = cell->get_attribute_ns( 's' ) ]-abap_t OPTIONAL )
value = cell->get_children( )->get_item( 0 )->get_value( ) ) ).
ENDDO.
* "Map sheet data and shared string
* LOOP AT mt_sheet_str INTO DATA(ls_sheet) GROUP BY ( sheet = ls_sheet-sheet row = ls_sheet-row ).
*
* DATA(ls_str) = REDUCE string( INIT str TYPE string
* FOR mem IN GROUP ls_sheet
* NEXT str = str && |{ COND string( WHEN mem-type = 's'
* THEN VALUE #( mt_shareds_str[ index = mem-value ]-value OPTIONAL )
* ELSE mem-value ) },| ).
* mt_data = VALUE #( BASE mt_data ( line = ls_str ) ).
* ENDLOOP.
ENDLOOP.
WHEN mc_shared_str_xml.
xml_obj->parse_xstring( stream = mv_shareds_xml ).
shareds_obj = xml_obj->find_node( mc_share_sst ).
REFRESH mt_shareds_str.
DO shareds_obj->get_children( )->get_length( ) TIMES.
DATA(si_obj) = CAST if_ixml_element( shareds_obj->get_children( )->get_item( sy-index - 1 ) ).
mt_shareds_str = VALUE #( BASE mt_shareds_str
( index = sy-index - 1
value = REDUCE #( INIT value TYPE string
FOR i = 0 THEN i + 1 UNTIL i = si_obj->get_children( )->get_length( )
NEXT value = value && si_obj->get_children( )->get_item( i )->get_value( ) ) ) ).
ENDDO.
WHEN mc_cell_syle_xml.
*1 0
*2 0.00
*3 #,##0
*4 #,##0.00
*5 $#,##0_);($#,##0)
*6 $#,##0_);[Red]($#,##0)
*7 $#,##0.00_);($#,##0.00)
*8 $#,##0.00_);[Red]($#,##0.00)
*9 0%
*10 0.00%
*11 0.00E+00
*12 # ?/?
*13 # ??/??
*14 m/d/yyyy
*15 d-mmm-yy
*16 d-mmm
*17 mmm-yy
*18 h:mm AM/PM
*19 h:mm:ss AM/PM
*20 h:mm
*21 h:mm:ss
*22 m/d/yyyy h:mm
*37 #,##0_);(#,##0)
*38 #,##0_);[Red](#,##0)
*39 #,##0.00_);(#,##0.00)
*40 #,##0.00_);[Red](#,##0.00)
*45 mm:ss
*46 [h]:mm:ss
*47 mm:ss.0
*48 ##0.0E+0
*49 @
xml_obj->parse_xstring( stream = mv_styles_xml ).
style_obj = xml_obj->find_node( mc_style_xfs ).
REFRESH mt_style_str.
DO style_obj->get_children( )->get_length( ) TIMES.
DATA(xf_obj) = CAST if_ixml_element( style_obj->get_children( )->get_item( sy-index - 1 ) ).
mt_style_str = VALUE #( LET fmtid = xf_obj->get_attribute_ns( 'numFmtId' ) IN
BASE mt_style_str ( id = sy-index - 1
fmtid = fmtid
abap_t = SWITCH #( fmtid
WHEN 3 THEN cl_abap_elemdescr=>get_i( )
WHEN 4 THEN cl_abap_elemdescr=>get_f( )
WHEN 14 THEN cl_abap_elemdescr=>get_d( )
ELSE cl_abap_elemdescr=>get_string( )
) ) ).
ENDDO.
WHEN OTHERS.
ENDCASE.
ENDMETHOD.
METHOD get_abap_data.
CASE style->type_kind.
WHEN cl_abap_elemdescr=>typekind_int.
ret = |{ CONV i( val ) }|.
WHEN cl_abap_elemdescr=>typekind_date.
DATA date TYPE d.
date = mc_base_date + val - 2.
ret = |{ date DATE = ENVIRONMENT }|.
WHEN cl_abap_elemdescr=>typekind_float.
* ret = |{ CONV f( val ) }|.
ret = |{ val }|.
WHEN cl_abap_elemdescr=>typekind_string.
ret = val.
WHEN OTHERS.
ENDCASE.
ENDMETHOD.
ENDCLASS. "lcl_xlsx_document IMPLEMENTATION
START-OF-SELECTION.
DATA(lo_xlsx) = NEW lcl_xlsx_document( ).
lo_xlsx->execute_xlsx( ).
Comments
Post a Comment