<sheetData>
<row r="1" x14ac:dyDescent="0.25" spans="1:107">
<c r="A1" t="s">
<v>21</v>
</c>
<c r="B1" t="s">
<v>22</v>
</c>
<c r="C1" t="s">
<v>23</v>
</c>
</row>
</sheetData>
<sst count="309" uniqueCount="83">
<si>
<t>MANDT</t>
</si>
<si>
<t>CARRID</t>
</si>
<si>
<t>CONNID</t>
</si>
<si>
<t>COUNTRYFR</t>
</si>
</sst>
CLASS xlsx_reader DEFINITION.
PUBLIC SECTION.
METHODS: read IMPORTING file TYPE string
first TYPE abap_bool
ddic TYPE string
EXPORTING tab TYPE REF TO data,
extract_xml IMPORTING iv_xml_index TYPE i
xstring TYPE xstring
RETURNING VALUE(rv_xml_data) TYPE xstring.
ENDCLASS.
CLASS xlsx_reader IMPLEMENTATION.
METHOD read.
TYPES: BEGIN OF ty_row,
value TYPE string,
index TYPE abap_bool,
END OF ty_row,
BEGIN OF ty_worksheet,
row_id TYPE i,
row TYPE TABLE OF ty_row WITH EMPTY KEY,
END OF ty_worksheet,
BEGIN OF ty_si,
t TYPE string,
END OF ty_si.
" Excel varaibles
DATA: data TYPE TABLE OF ty_si,
sheet TYPE TABLE OF ty_worksheet.
" RTTS variables
DATA: lo_struct TYPE REF TO cl_abap_structdescr,
table TYPE abap_component_tab.
FIELD-SYMBOLS: <table> TYPE STANDARD TABLE.
TRY. " loading XLSX zip from file
DATA(xstring_xlsx) = cl_openxml_helper=>load_local_file( file ).
CATCH cx_openxml_not_found.
ENDTRY.
"Read the sheet XML
DATA(xml_sheet) = extract_xml( EXPORTING xstring = xstring_xlsx iv_xml_index = 2 ).
"Read the shared data XML
DATA(xml_data) = extract_xml( EXPORTING xstring = xstring_xlsx iv_xml_index = 3 ).
TRY.
" transforming sheet structure into ABAP
CALL TRANSFORMATION zsheet
SOURCE XML xml_sheet
RESULT root = sheet.
" transforming shared data into ABAP
CALL TRANSFORMATION zxlsx
SOURCE XML xml_data
RESULT root = data.
CATCH cx_xslt_exception.
CATCH cx_st_match_element.
CATCH cx_st_ref_access.
ENDTRY.
DATA(header_line) = VALUE #( sheet[ 1 ]-row OPTIONAL ).
IF first IS NOT INITIAL AND header_line IS NOT INITIAL. "building itab from first line
table = VALUE #( BASE table FOR ls_key IN header_line
( name = data[ ls_key-value + 1 ]-t
type = CAST #( cl_abap_datadescr=>describe_by_name( VALUE #( data[ ls_key-value + 1 ]-t OPTIONAL ) ) )
)
).
DELETE sheet INDEX 1.
ELSE. "building itab of strings
DELETE header_line WHERE value IS INITIAL.
DO lines( header_line ) TIMES.
APPEND VALUE #( name = 'field' && sy-index type = CAST #( cl_abap_typedescr=>describe_by_name( 'STRING' ) ) ) TO table.
ENDDO.
ENDIF.
" creating structure from DDIC structure
IF ddic IS NOT INITIAL.
lo_struct ?= cl_abap_structdescr=>describe_by_name( ddic ).
ELSEIF table IS NOT INITIAL.
" create structure from previously constructed type handle
TRY.
lo_struct = cl_abap_structdescr=>create( table ).
CATCH cx_sy_struct_creation .
ENDTRY.
ENDIF.
" creating table from structure
CHECK lo_struct IS BOUND.
DATA(dyntable_type) = cl_abap_tabledescr=>create( p_line_type = lo_struct ).
CREATE DATA tab TYPE HANDLE dyntable_type.
ASSIGN tab->* TO <table>.
* mapping structure and data
LOOP AT sheet ASSIGNING FIELD-SYMBOL(<fs_row>).
APPEND INITIAL LINE TO <table> ASSIGNING FIELD-SYMBOL(<line>).
DELETE <fs_row>-row WHERE value IS INITIAL.
LOOP AT <fs_row>-row ASSIGNING FIELD-SYMBOL(<fs_cell>).
ASSIGN COMPONENT sy-tabix OF STRUCTURE <line> TO FIELD-SYMBOL(<fs_field>).
CHECK sy-subrc = 0.
<fs_field> = COND #( WHEN <fs_cell>-index = abap_false THEN <fs_cell>-value ELSE VALUE #( data[ <fs_cell>-value + 1 ]-t OPTIONAL ) ).
ENDLOOP.
ENDLOOP.
ENDMETHOD.
METHOD extract_xml.
TRY.
DATA(lo_package) = cl_xlsx_document=>load_document( iv_data = xstring ).
DATA(lo_parts) = lo_package->get_parts( ).
CHECK lo_parts IS BOUND AND lo_package IS BOUND.
DATA(lv_uri) = lo_parts->get_part( 2 )->get_parts( )->get_part( iv_xml_index )->get_uri( )->get_uri( ).
DATA(lo_xml_part) = lo_package->get_part_by_uri( cl_openxml_parturi=>create_from_partname( lv_uri ) ).
rv_xml_data = lo_xml_part->get_data( ).
CATCH cx_openxml_format cx_openxml_not_found.
ENDTRY.
ENDMETHOD.
ENDCLASS.
<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates" template="main">
<tt:root name="root"/>
<tt:template name="main">
<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" xmlns:x14ac=
"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3=
"http://schemas.microsoft.com/office/spreadsheetml/2016/revision3">
<tt:skip count="4"/>
<sheetData>
<tt:loop name="row" ref="root">
<row>
<tt:attribute name="r" value-ref="row_id"/>
<tt:loop name="cells" ref="$row.ROW">
<c>
<tt:cond><tt:attribute name="t" value-ref="index"/><tt:assign to-ref="index" val="C('X')"/></tt:cond>
<tt:cond>
<v>
<tt:value ref="value"/>
</v>
</tt:cond>
</c>
</tt:loop>
</row>
</tt:loop>
</sheetData>
<tt:skip/>
</worksheet>
</tt:template>
</tt:transform>
<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates" template="main">
<tt:root name="ROOT"/>
<tt:template name="main">
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<tt:loop name="line" ref=".ROOT">
<si>
<t>
<tt:value ref="t"/>
</t>
</si>
</tt:loop>
</sst>
</tt:template>
</tt:transform>
START-OF-SELECTION.
PARAMETERS: p_file TYPE string LOWER CASE DEFAULT `C:\table.xlsx`.
SELECTION-SCREEN BEGIN OF BLOCK out WITH FRAME TITLE text-s01.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(25) text-002.
PARAMETERS: p_hdr TYPE xfeld MODIF ID hdr USER-COMMAND hdr.
SELECTION-SCREEN COMMENT 30(25) text-001.
PARAMETERS: p_ddic TYPE string MODIF ID dic.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK out.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
p_file = cl_openxml_helper=>browse_local_file_open( iv_title = 'Select XLSX File' iv_filename = '' iv_extpattern = 'All files(*.*)|*.*' ).
AT SELECTION-SCREEN OUTPUT.
IF p_hdr = abap_true.
DATA(imp) = 1.
CLEAR: p_ddic.
ELSE.
imp = 0.
ENDIF.
LOOP AT SCREEN.
CASE screen-group1.
WHEN 'DIC'.
SCREEN-input = COND #( WHEN imp = 1 THEN 0 ELSE 1 ).
ENDCASE.
MODIFY SCREEN.
ENDLOOP.
AT SELECTION-SCREEN.
FIELD-SYMBOLS: <fs_out> TYPE ANY.
IF sy-ucomm = 'ONLI'.
DATA(reader) = NEW xlsx_reader( ).
reader->read( EXPORTING file = p_file first = p_hdr ddic = p_ddic IMPORTING tab = DATA(tab) ).
ASSIGN tab->* TO FIELD-SYMBOL(<table>).
ENDIF.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
3 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |