SAP download 表格信息到本地 （二）

2、由于excel可以使用xml的格式直接打开，使用标准if_ixml的类：

REPORT ztestlian8.
DATA: BEGIN OF ls_itab,
  field_1(10) TYPE c,
  field_2(10) TYPE c,
  field_3(10) TYPE c,
  END OF ls_itab,
  lt_itab LIKE TABLE OF ls_itab.
DATA: i_contents_hex       LIKE solix   OCCURS 0 WITH HEADER LINE,
      filename TYPE string VALUE 'C:\Users\Lian.zhao\Desktop\TEST1.xlsx'.
ls_itab-field_1 = 'test1'.
ls_itab-field_2 = 'test2'.
ls_itab-field_3 = 'test3'.
APPEND ls_itab to lt_itab.

TYPES: BEGIN OF xml_line,
        data(255) TYPE x,
       END OF xml_line.

DATA: l_ixml            TYPE REF TO if_ixml,
      l_streamfactory   TYPE REF TO if_ixml_stream_factory,
      l_ostream         TYPE REF TO if_ixml_ostream,
      l_renderer        TYPE REF TO if_ixml_renderer,
      l_document        TYPE REF TO if_ixml_document.

DATA: l_element_root        TYPE REF TO if_ixml_element,
      ns_attribute          TYPE REF TO if_ixml_attribute,
      r_element_properties  TYPE REF TO if_ixml_element,
      r_element             TYPE REF TO if_ixml_element,
      r_worksheet           TYPE REF TO if_ixml_element,
      r_table               TYPE REF TO if_ixml_element,
      r_worksheet2         TYPE REF TO if_ixml_element,
      r_table2             TYPE REF TO if_ixml_element,
      r_column              TYPE REF TO if_ixml_element,
      r_row                 TYPE REF TO if_ixml_element,
      r_cell                TYPE REF TO if_ixml_element,
      r_data                TYPE REF TO if_ixml_element,
      l_value               TYPE string,
      l_type                TYPE string,
      l_text(100)           TYPE c,
      r_styles              TYPE REF TO if_ixml_element,
      r_style               TYPE REF TO if_ixml_element,
      r_style1              TYPE REF TO if_ixml_element,
      r_format              TYPE REF TO if_ixml_element,
      r_border              TYPE REF TO if_ixml_element,
      num_rows              TYPE i.

DATA: l_xml_table       TYPE TABLE OF xml_line,
      wa_xml            TYPE xml_line,
      l_xml_size        TYPE i,
      l_rc              TYPE i.

DEFINE create_simple_ele.
  &3 = l_document->create_simple_element( name = &1 parent = &2 ).
END-OF-DEFINITION.

DEFINE set_attri_ns.
  &4->set_attribute_ns( name = &1 prefix = &2 value = &3 ).
END-OF-DEFINITION.

DEFINE set_ceil_vale.
  &3 = l_document->create_simple_element( name = 'Cell'  parent = &1 ).
  &3->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = &2 ).
  &5 = l_document->create_simple_element( name = 'Data'  value = &4  parent = &3 ).
  &5->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).
END-OF-DEFINITION.


*createing a ixml factory
l_ixml = cl_ixml=>create( ).

*create the dom object model
l_document = l_ixml->create_document( ).

*create root node 'workbook'
l_element_root = l_document->create_simple_element( name      = 'Workbook' parent    = l_document ) .
l_element_root->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:spreadsheet' ).
ns_attribute = l_document->create_namespace_decl( name = 'ss' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:spreadsheet' ).
l_element_root->set_attribute_node( ns_attribute ).
ns_attribute = l_document->create_namespace_decl( name = 'x' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:excel' ).
l_element_root->set_attribute_node( ns_attribute ).

*create node to document properties
r_element_properties = l_document->create_simple_element( name = 'TEST' parent = l_element_root ).
l_value = sy-uname.
l_document->create_simple_element( name = 'Author' value = l_value parent = r_element_properties ).

*style
r_styles = l_document->create_simple_element( name = 'Styles' parent = l_element_root ).

*style for header
r_style = l_document->create_simple_element( name = 'Style' parent = r_styles ).
r_style->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Header' ).

r_format = l_document->create_simple_element( name = 'Font' parent = r_style ).
r_format->set_attribute_ns( name = 'Bold' prefix = 'ss' value = '1' ).

create_simple_ele 'Interior' r_style r_format.
set_attri_ns 'Color' 'ss' '#F9F900' r_format.
set_attri_ns 'Pattern' 'ss' 'Solid' r_format.

create_simple_ele 'Alignment' r_style r_format.
set_attri_ns 'Vertical' 'ss' 'Center' r_format.
set_attri_ns 'WrapText' 'ss' '1' r_format.

r_border  = l_document->create_simple_element( name = 'Borders'  parent = r_style ).

create_simple_ele 'Border' r_border r_format.
set_attri_ns 'Position' 'ss' 'Bottom' r_format.
set_attri_ns 'LineStyle' 'ss' 'Continuous' r_format.
set_attri_ns 'Weight' 'ss' '1' r_format.

create_simple_ele 'Border' r_border r_format.
set_attri_ns 'Position' 'ss' 'Left' r_format.
set_attri_ns 'LineStyle' 'ss' 'Continuous' r_format.
set_attri_ns 'Weight' 'ss' '1' r_format.

create_simple_ele 'Border' r_border r_format.
set_attri_ns 'Position' 'ss' 'Top' r_format.
set_attri_ns 'LineStyle' 'ss' 'Continuous' r_format.
set_attri_ns 'Weight' 'ss' '1' r_format.

create_simple_ele 'Border' r_border r_format.
set_attri_ns 'Position' 'ss' 'Right' r_format.
set_attri_ns 'LineStyle' 'ss' 'Continuous' r_format.
set_attri_ns 'Weight' 'ss' '1' r_format.

*style for data
r_style1  = l_document->create_simple_element( name = 'Style'   parent = r_styles  ).
r_style1->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Data' ).

r_border  = l_document->create_simple_element( name = 'Borders'  parent = r_style1 ).
create_simple_ele 'Border' r_border r_format.
set_attri_ns 'Position' 'ss' 'Bottom' r_format.
set_attri_ns 'LineStyle' 'ss' 'Continuous' r_format.
set_attri_ns 'Weight' 'ss' '1' r_format.

create_simple_ele 'Border' r_border r_format.
set_attri_ns 'Position' 'ss' 'Left' r_format.
set_attri_ns 'LineStyle' 'ss' 'Continuous' r_format.
set_attri_ns 'Weight' 'ss' '1' r_format.

create_simple_ele 'Border' r_border r_format.
set_attri_ns 'Position' 'ss' 'Top' r_format.
set_attri_ns 'LineStyle' 'ss' 'Continuous' r_format.
set_attri_ns 'Weight' 'ss' '1' r_format.

create_simple_ele 'Border' r_border r_format.
set_attri_ns 'Position' 'ss' 'Right' r_format.
set_attri_ns 'LineStyle' 'ss' 'Continuous' r_format.
set_attri_ns 'Weight' 'ss' '1' r_format.


* Worksheet
r_worksheet = l_document->create_simple_element( name = 'Worksheet'  parent = l_element_root ).
r_worksheet->set_attribute_ns( name = 'Name'  prefix = 'ss'  value = 'Header' ).

* Table
r_table = l_document->create_simple_element( name = 'Table'  parent = r_worksheet ).
r_table->set_attribute_ns( name = 'FullColumns'  prefix = 'x'  value = '1' ).
r_table->set_attribute_ns( name = 'FullRows'     prefix = 'x'  value = '1' ).

* Column Formatting
create_simple_ele 'Column' r_table r_column.
set_attri_ns 'Width' 'ss' '60' r_column.

create_simple_ele 'Column' r_table r_column.
set_attri_ns 'Width' 'ss' '40' r_column.

create_simple_ele 'Column' r_table r_column.
set_attri_ns 'Width' 'ss' '100' r_column.

* Column Headers Row
r_row = l_document->create_simple_element( name = 'Row'  parent = r_table ).
r_row->set_attribute_ns( name = 'AutoFitHeight'  prefix = 'ss'  value = '1' ).

set_ceil_vale r_row 'Header' r_cell 'Fieldname1' r_data.
set_ceil_vale r_row 'Header' r_cell 'Fieldname2' r_data.
set_ceil_vale r_row 'Header' r_cell 'Fieldname3' r_data.

LOOP AT lt_itab INTO ls_itab.

  r_row = l_document->create_simple_element( name = 'Row' parent = r_table ).

  l_value = ls_itab-field_1.
  CONDENSE l_value.
  set_ceil_vale r_row 'Data' r_cell l_value r_data.

  l_value = ls_itab-field_2.
  CONDENSE l_value.
  set_ceil_vale r_row 'Data' r_cell l_value r_data.

  l_value = ls_itab-field_3.
  CONDENSE l_value.
  set_ceil_vale r_row 'Data' r_cell l_value r_data.

ENDLOOP.

**********************new worksheet  **********************
**new worksheet
*  r_worksheet2 = l_document->create_simple_element( name = 'Worksheet'  parent = l_element_root ).
*  r_worksheet2->set_attribute_ns( name = 'Name'  prefix = 'ss'  value = 'Detail' ).
*
** new Table
*  r_table2 = l_document->create_simple_element( name = 'Table'  parent = r_worksheet2 ).
*  r_table2->set_attribute_ns( name = 'FullColumns'  prefix = 'x'  value = '1' ).
*  r_table2->set_attribute_ns( name = 'FullRows'     prefix = 'x'  value = '1' ).
*
**********************new worksheet  **********************

*creating a stream factory
l_streamfactory = l_ixml->create_stream_factory( ).

*create ainternal XML table to stream Factory
l_ostream = l_streamfactory->create_ostream_itable( table = l_xml_table ).

*rendering the document
l_renderer = l_ixml->create_renderer( ostream = l_ostream document = l_document ).
l_rc = l_renderer->render( ).

*save the XML document
l_xml_size = l_ostream->get_num_written_raw( ).

filename = 'C:\Users\Lia\Desktop\TEST1.xls'.

LOOP AT l_xml_table INTO wa_xml.
  CLEAR i_contents_hex.
  i_contents_hex-line = wa_xml-data.
  APPEND i_contents_hex.
ENDLOOP.

cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = l_xml_size
                                              filename     = filename
                                              filetype     = 'BIN'
                                     CHANGING data_tab     = i_contents_hex[] ).