Beispiel für die Nutzung des Office Open XML (SpreadsheetML) Formates zur Datenausgabe von Nutzdaten aus internen Tabellen.
Links
Wikipedia – Office Open XML
Wikipedia – SpreadsheetML
SAP ABAP Doku
Beispiel 1
Beispiel 2
Beispiel – Speicherung Daten beliebiger interner Tabellen in eine von Microsoft Office Excel lesbare XML-Datei
* Dateien können ab Microsoft Office Excel 2002 / 2003 geöffnet werden
DATA: it_spfli TYPE STANDARD TABLE OF spfli.
DATA: gv_company TYPE string VALUE 'codezentrale.de'.
DATA: gv_user TYPE string VALUE 'Testuser'.
DATA: gv_sheetname TYPE string VALUE 'SPFLI'.
INITIALIZATION.
* Daten holen
SELECT * FROM spfli INTO TABLE @it_spfli.
START-OF-SELECTION.
DATA: o_cell TYPE REF TO if_ixml_element.
DATA: o_data TYPE REF TO if_ixml_element.
* Tabellenstruktur
DATA(o_table_descr) = CAST cl_abap_tabledescr( cl_abap_tabledescr=>describe_by_data( it_spfli ) ).
* Struktur einer Tabellenzeile
DATA(o_struct_descr) = CAST cl_abap_structdescr( o_table_descr->get_table_line_type( ) ).
* Komponenten (Spalten) einer Zeile
DATA(it_comp_tab) = o_struct_descr->get_components( ).
* Anzahl Spalten
DATA(lv_colcount) = lines( it_comp_tab ).
* ixml Factory
DATA(o_ixml) = cl_ixml=>create( ).
* Encoding UTF-8
DATA(o_encoding) = o_ixml->create_encoding( character_set = 'UTF-8'
byte_order = if_ixml_encoding=>co_none ).
* DOM Object Model
DATA(o_doc) = o_ixml->create_document( ).
* Processing Instructions setzen
* damit die XML-Datei gleich vom Windows als Excel-XML erkannt und korrekt geöffnet wird
DATA(o_pi) = o_doc->create_pi_parsed( name = 'mso-application' ).
o_pi->set_attribute( name = 'progid' value = 'Excel.Sheet' ).
o_doc->append_child( o_pi ).
* Workbook
DATA(o_workbook) = o_doc->create_simple_element( name = 'Workbook' parent = o_doc ).
o_workbook->set_attribute( name = 'xmlns' value ='urn:schemas-microsoft-com:office:spreadsheet' ).
o_workbook->set_attribute_node( o_doc->create_namespace_decl( name = 'o' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:office') ).
o_workbook->set_attribute_node( o_doc->create_namespace_decl( name = 'x' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:excel' ) ).
o_workbook->set_attribute_node( o_doc->create_namespace_decl( name = 'ss' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:spreadsheet') ).
o_workbook->set_attribute_node( o_doc->create_namespace_decl( name = 'html' prefix = 'xmlns' uri = 'http://www.w3.org/TR/REC-html40') ).
* DocumentProperties
* Zeitstempel erzeugen
DATA: lv_tsl TYPE timestampl.
GET TIME STAMP FIELD lv_tsl.
DATA(lv_created) = |{ substring( val = |{ lv_tsl TIMESTAMP = ISO }| off = 0 len = 19 ) }Z|.
DATA(o_prop) = o_doc->create_simple_element( name = 'DocumentProperties' parent = o_workbook ).
o_prop->set_attribute( name = 'xmlns' value ='urn:schemas-microsoft-com:office:office' ).
o_doc->create_simple_element( name = 'Author' value = gv_user parent = o_prop ).
o_doc->create_simple_element( name = 'LastAuthor' value = gv_user parent = o_prop ).
o_doc->create_simple_element( name = 'Created' value = lv_created parent = o_prop ).
o_doc->create_simple_element( name = 'Company' value = gv_company parent = o_prop ).
o_doc->create_simple_element( name = 'Version' value = '14.00' parent = o_prop ).
* OfficeDocumentSettings
DATA(o_set) = o_doc->create_simple_element( name = 'OfficeDocumentSettings' parent = o_workbook ).
o_set->set_attribute( name = 'xmlns' value ='urn:schemas-microsoft-com:office:office' ).
o_doc->create_simple_element( name = 'AllowPNG' parent = o_set ).
* ExcelWorkbook
DATA(o_excelwb) = o_doc->create_simple_element( name = 'ExcelWorkbook' parent = o_workbook ).
o_excelwb->set_attribute( name = 'xmlns' value ='urn:schemas-microsoft-com:office:excel' ).
o_doc->create_simple_element( name = 'ProtectStructure' value = 'False' parent = o_excelwb ).
o_doc->create_simple_element( name = 'ProtectWindows' value = 'False' parent = o_excelwb ).
* Styles
DATA(o_styles) = o_doc->create_simple_element( name = 'Styles' parent = o_workbook ).
* "Default" Style
DATA(o_style_default) = o_doc->create_simple_element( name = 'Style' parent = o_styles ).
o_style_default->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Default' ).
o_style_default->set_attribute_ns( name = 'Name' prefix = 'ss' value = 'Normal' ).
DATA(o_align) = o_doc->create_simple_element( name = 'Alignment' parent = o_style_default ).
o_align->set_attribute_ns( name = 'Vertical' prefix = 'ss' value = 'Bottom' ).
DATA(o_borders) = o_doc->create_simple_element( name = 'Borders' parent = o_style_default ).
DATA(o_font) = o_doc->create_simple_element( name = 'Font' parent = o_style_default ).
o_font->set_attribute_ns( name = 'FontName' prefix = 'ss' value = 'Arial' ).
o_font->set_attribute_ns( name = 'Family' prefix = 'x' value = 'Swiss' ).
o_font->set_attribute_ns( name = 'Size' prefix = 'ss' value = '11' ).
o_font->set_attribute_ns( name = 'Color' prefix = 'ss' value = '#000000' ).
DATA(o_interior) = o_doc->create_simple_element( name = 'Interior' parent = o_style_default ).
DATA(o_numberformat) = o_doc->create_simple_element( name = 'NumberFormat' parent = o_style_default ).
DATA(o_protection) = o_doc->create_simple_element( name = 'Protection' parent = o_style_default ).
* Bold Style "s62" für die Überschrift
DATA(o_style_bold) = o_doc->create_simple_element( name = 'Style' parent = o_styles ).
o_style_bold->set_attribute_ns( name = 'ID' prefix = 'ss' value = 's62' ).
DATA(o_font_bold) = o_doc->create_simple_element( name = 'Font' parent = o_style_bold ).
o_font_bold->set_attribute_ns( name = 'FontName' prefix = 'ss' value = 'Arial' ).
o_font_bold->set_attribute_ns( name = 'Family' prefix = 'x' value = 'Swiss' ).
o_font_bold->set_attribute_ns( name = 'Size' prefix = 'ss' value = '11' ).
o_font_bold->set_attribute_ns( name = 'Color' prefix = 'ss' value = '#000000' ).
o_font_bold->set_attribute_ns( name = 'Bold' prefix = 'ss' value = '1' ).
* Worksheet "SPFLI" einfügen
DATA(o_sheet1) = o_doc->create_simple_element( name = 'Worksheet' parent = o_workbook ).
o_sheet1->set_attribute_ns( name = 'Name' prefix = 'ss' value = gv_sheetname ).
* WorksheetOptions
DATA(o_ws_opt) = o_doc->create_simple_element( name = 'WorksheetOptions' parent = o_sheet1 ).
o_ws_opt->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:excel' ).
o_doc->create_simple_element( name = 'Selected' parent = o_ws_opt ).
o_doc->create_simple_element( name = 'ProtectObjects' value = 'False' parent = o_ws_opt ).
o_doc->create_simple_element( name = 'ProtectScenarios' value = 'False' parent = o_ws_opt ).
o_doc->create_simple_element( name = 'SplitHorizontal' value = '1' parent = o_ws_opt ).
o_doc->create_simple_element( name = 'TopRowBottomPane' value = '1' parent = o_ws_opt ).
o_doc->create_simple_element( name = 'FreezePanes' parent = o_ws_opt ).
o_doc->create_simple_element( name = 'FrozenNoSplit' parent = o_ws_opt ).
o_doc->create_simple_element( name = 'Unsynced' parent = o_ws_opt ).
o_doc->create_simple_element( name = 'ActivePane' value = '2' parent = o_ws_opt ).
* Page Setup
DATA(o_ps) = o_doc->create_simple_element( name = 'PageSetup' parent = o_ws_opt ).
DATA(o_ps_header) = o_doc->create_simple_element( name = 'Header' parent = o_ps ).
o_ps_header->set_attribute_ns( name = 'Margin' prefix = 'x' value = '0.3' ).
DATA(o_ps_footer) = o_doc->create_simple_element( name = 'Footer' parent = o_ps ).
o_ps_footer->set_attribute_ns( name = 'Margin' prefix = 'x' value = '0.3' ).
DATA(o_ps_margins) = o_doc->create_simple_element( name = 'PageMargins' parent = o_ps ).
o_ps_margins->set_attribute_ns( name = 'Bottom' prefix = 'x' value = '0.7' ).
o_ps_margins->set_attribute_ns( name = 'Left' prefix = 'x' value = '0.7' ).
o_ps_margins->set_attribute_ns( name = 'Right' prefix = 'x' value = '0.7' ).
o_ps_margins->set_attribute_ns( name = 'Top' prefix = 'x' value = '0.7' ).
* Splitter für Header / Data definieren
o_doc->create_simple_element( name = 'x:Selected' parent = o_ps ).
o_doc->create_simple_element( name = 'x:FreezePanes' parent = o_ps ).
o_doc->create_simple_element( name = 'x:FrozenNoSplit' parent = o_ps ).
o_doc->create_simple_element( name = 'x:Unsynced' parent = o_ps ).
o_doc->create_simple_element( name = 'x:ProtectObjects' value = 'False' parent = o_ps ).
o_doc->create_simple_element( name = 'x:ProtectScenarios' value = 'False' parent = o_ps ).
o_doc->create_simple_element( name = 'x:SplitHorizontal' value = '1' parent = o_ps ).
o_doc->create_simple_element( name = 'x:TopRowBottomPane' value = '1' parent = o_ps ).
* Named Range für Filter definieren
DATA(o_names) = o_doc->create_simple_element( name = 'Names' parent = o_sheet1 ).
DATA(o_named_range) = o_doc->create_simple_element( name = 'NamedRange' parent = o_names ).
o_named_range->set_attribute_ns( name = 'Name' prefix = 'ss' value = '_FilterDatabase' ).
o_named_range->set_attribute_ns( name = 'RefersTo' prefix = 'ss' value = |=Sheet1!R1C1:R1C{ lv_colcount }| ). " Spalte 1-x als Range "_FilterDatabase" definieren
o_named_range->set_attribute_ns( name = 'Hidden' prefix = 'ss' value = '1' ).
* Autofilter aktivieren
DATA(o_autofilter) = o_doc->create_simple_element( name = 'AutoFilter' parent = o_sheet1 ).
o_autofilter->set_attribute_ns( name = 'Range' prefix = 'x' value = |R1C1:R1C{ lv_colcount }| ). " Spalte 1-x als Filter-Range definieren
o_autofilter->set_attribute_ns( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:excel' ).
* Table für Header und Daten
DATA(o_table) = o_doc->create_simple_element( name = 'Table' parent = o_sheet1 ).
o_table->set_attribute_ns( name = 'FullColumns' prefix = 'x' value = '1' ).
o_table->set_attribute_ns( name = 'FullRows' prefix = 'x' value = '1' ).
o_table->set_attribute_ns( name = 'DefaultColumnWidth' prefix = 'x' value = '66' ).
o_table->set_attribute_ns( name = 'DefaultRowHeight' prefix = 'x' value = '14.25' ).
o_table->set_attribute_ns( name = 'DefaultColumnWidth' prefix = 'ss' value = '66' ).
o_table->set_attribute_ns( name = 'DefaultRowHeight' prefix = 'ss' value = '14.25' ).
* Header (Tabellenüberschriften) einfügen
DATA(o_hrow) = o_doc->create_simple_element( name = 'Row' parent = o_table ).
LOOP AT it_comp_tab ASSIGNING FIELD-SYMBOL(<fs_comp>).
o_cell = o_doc->create_simple_element( name = 'Cell' parent = o_hrow ).
o_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 's62' ). " Style Bold
* nur elementare Datentypen zulassen
IF <fs_comp>-type->kind = cl_abap_typedescr=>kind_elem.
o_doc->create_simple_element( name = 'Data' value = <fs_comp>-name parent = o_cell )->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
ELSE.
* alle anderen Spalten mit Strukturen, INCLUDES usw. einfach als DEEP kennzeichnen, Inhalt bleibt leer
o_doc->create_simple_element( name = 'Data' value = 'DEEP' parent = o_cell )->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).
ENDIF.
* Named Range "_FilterDatabase" für Filter setzen
o_doc->create_simple_element( name = 'NamedCell' parent = o_cell )->set_attribute_ns( name = 'Name' prefix = 'ss' value = '_FilterDatabase' ).
ENDLOOP.
* Daten einfügen
LOOP AT it_spfli ASSIGNING FIELD-SYMBOL(<fs_line>).
DATA(o_row_data) = o_doc->create_simple_element( name = 'Row' parent = o_table ).
o_struct_descr ?= cl_abap_structdescr=>describe_by_data( <fs_line> ).
LOOP AT o_struct_descr->get_components( ) ASSIGNING FIELD-SYMBOL(<fs_comp2>).
o_cell = o_doc->create_simple_element( name = 'Cell' parent = o_row_data ).
* nur elementare Datentypen zulassen, andernfalls gibts Exceptions bei der String-Konvertierung
* die Spalten mit kennzeichnung "DEEP" bleiben in der Ausgabe somit leer
IF <fs_comp2>-type->kind = cl_abap_typedescr=>kind_elem.
ASSIGN COMPONENT <fs_comp2>-name OF STRUCTURE <fs_line> TO FIELD-SYMBOL(<fs_cell>).
IF <fs_cell> IS ASSIGNED.
DATA: lv_data_type TYPE string.
DATA(lv_typekind) = cl_abap_elemdescr=>get_data_type_kind( <fs_cell> ).
CASE lv_typekind.
WHEN cl_abap_typedescr=>typekind_time OR cl_abap_typedescr=>typekind_date OR cl_abap_typedescr=>typekind_string OR cl_abap_typedescr=>typekind_char.
lv_data_type = 'String'.
WHEN cl_abap_typedescr=>typekind_num OR cl_abap_typedescr=>typekind_packed OR cl_abap_typedescr=>typekind_int OR cl_abap_typedescr=>typekind_float.
lv_data_type = 'Number'.
WHEN OTHERS.
lv_data_type = 'String'.
ENDCASE.
o_data = o_doc->create_simple_element( name = 'Data' value = |{ <fs_cell> }| parent = o_cell ).
o_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = lv_data_type ).
ENDIF.
ENDIF.
ENDLOOP.
ENDLOOP.
* XML-Dokument rendern
DATA(o_sf) = o_ixml->create_stream_factory( ).
DATA: lv_xml TYPE string.
DATA(o_stream) = o_sf->create_ostream_cstring( lv_xml ).
o_stream->set_encoding( encoding = o_encoding ).
o_stream->set_pretty_print( pretty_print = abap_true ).
DATA(o_render) = o_ixml->create_renderer( ostream = o_stream
document = o_doc ).
* XML-String in lv_xml generieren
DATA(lv_rc) = o_render->render( ).
* Dateigröße in Bytes
DATA(lv_size) = o_stream->get_num_written_raw( ).
* Stream schließen
o_stream->close( ).
IF lv_rc = 0 AND lv_size > 0.
* XML-String in XML-Document wandeln
DATA(o_xml_doc) = NEW cl_xml_document( ).
o_xml_doc->parse_string( lv_xml ).
* Ausgabe-Datei, "\" müssen quotiert werden
DATA(lv_fullpath) = |c:\\temp\\test.xml|.
* XML-Document als Datei speichern
o_xml_doc->export_to_file( CONV #( lv_fullpath ) ).
* lokale Datei im Excel aufrufen
* parameter muss wegen möglicher Leerzeichen im Pfad mit "" quotiert werden
cl_gui_frontend_services=>execute( application = 'excel.exe'
parameter = |"{ lv_fullpath }"| ).
ELSE.
WRITE: / 'Fehler beim Erzeugen der XML-Datei.'.
ENDIF.