cancel
Showing results for 
Search instead for 
Did you mean: 

Internal table to Excel

Former Member
0 Kudos

Hi Experts,

I am using a custom button to download data from internal table into excel file.

There is a new requirement to append the "header and footer" into the excel file, do you know if this is possible?

If it's possible, could you please share some thoughts on how to proceed?

Thanks.

Tee

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

You have to use the XML coding to achieve the same.

Ex: if you have a excel file with all necessary colors and heading once done save this file.

Try to open this file as XML spreadsheet. Then open this file with notepad/wordpad to see the XML code.

Check thiswiki

http://wiki.sdn.sap.com/wiki/display/WDABAP/ModifyingexistingexcelsheetusingWDABAP?focusedCommentId=135987215#comment-135987215

Regards,

Lekha.

Edited by: Lekha on Nov 5, 2009 4:17 PM

Former Member
0 Kudos

Lekha,

I tried these ...

1) Create some data into the excel

2) Save as XML

But i got this error "Cannot save XML data because the workbook does not contain any XML mappings.".

Tee

Former Member
0 Kudos

Hi,

Open the Excel file->SAVE AS filtype as XML spreadsheet then saves file.xml..Then open this XML file with notepad then you will get the XML code.

You have to make use of these interfaces -

if_ixml

if_ixml_document

if_ixml_element

if_ixml_stream_factory

if_ixml_renderer

Regards,

Lekha.

Edited by: Lekha on Nov 5, 2009 4:35 PM

Former Member
0 Kudos

Lekha,

Did you manage to do the same without any error?

Thanks.

Tee

Former Member
0 Kudos

Lekha,

I managed to save the excel into xml.

I will try on these interfaces.

Thanks.

Tee

Former Member
0 Kudos

Hi,

The usage of these can be checked either in this forum or ABAP objects where they are mostyl used.

Regards,

Lekha.

Former Member
0 Kudos

Lekha,

I tried to look from other forums, but couldn't find suitable threads on these interfaces usages.

However, i managed to format excel using the Function Module 'SCMS_STRING_TO_XSTRING' and Method: 'WDR_TASK=>CLIENT_WINDOW->CLIENT->ATTACH_FILE_TO_RESPONSE'.

Have you tried inserting the picture into the XML spreadsheet? I tried that, but couldn't work, not sure if i missed out anything.

Thanks.

Tee

Former Member
0 Kudos

Hi,

Try this code in se38 and run it out...you can use the same code in th WDA ....

I havenot tried with the Images.....

DATA:
  lref_xml         TYPE REF TO if_ixml,
  lref_doc         TYPE REF TO if_ixml_document,
  lref_e_root      TYPE REF TO if_ixml_element,
  lref_e_attr      TYPE REF TO if_ixml_element,
  lref_worksheet   TYPE REF TO if_ixml_element,
  lref_table       TYPE REF TO if_ixml_element,
  lref_row         TYPE REF TO if_ixml_element,
  lref_column      TYPE REF TO if_ixml_element,
  lref_cell        TYPE REF TO if_ixml_element,
  lref_data        TYPE REF TO if_ixml_element,
  lref_attr        TYPE REF TO if_ixml_attribute,
  lref_ostream     TYPE REF TO if_ixml_ostream,
  lref_sfactory    TYPE REF TO if_ixml_stream_factory,
  lref_renderer    TYPE REF TO if_ixml_renderer.

* Local Data Declaration For Styles of Cells in Excel Workbook
DATA:
  lref_styles             TYPE REF TO if_ixml_element,
  lref_style              TYPE REF TO if_ixml_element,
  lref_format             TYPE REF TO if_ixml_element,
  lref_alignment          TYPE REF TO if_ixml_element,
  lref_borders            TYPE REF TO if_ixml_element,
  lref_border             TYPE REF TO if_ixml_element,
  lref_font               TYPE REF TO if_ixml_element,
  lref_interior           TYPE REF TO if_ixml_element,
  lref_protection         TYPE REF TO if_ixml_element.

DATA: ls_masterdata               TYPE zst_excel_master,
      lv_value                    TYPE string,
      lv_value_end                TYPE string,
      lv_rowstart                 TYPE string,
      lv_string                   TYPE string,
      ls_column                   TYPE lvc_s_fcat,
      lv_count                    TYPE string,
      lv_type                     TYPE c,
      lv_lines                    TYPE i,
      lv_start_plan               TYPE c,
      lv_columns                  TYPE string,
      lv_project_id               TYPE string,
      lv_str                      TYPE string,
      lref_worksheetoptions       TYPE REF TO if_ixml_element,
      lref_allowinsertrows        TYPE REF TO if_ixml_element,
      lref_allowsizecols          TYPE REF TO if_ixml_element,
      lref_allowdeleterows        TYPE REF TO if_ixml_element,
      lref_visible                TYPE REF TO if_ixml_element.

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

DATA: lv_rc TYPE i,
      lv_size TYPE i,
      lv_text            TYPE string,                       " varible for assistance class text
      l_xml_table       TYPE TABLE OF xml_line,
      gc_excel TYPE string,
      ev_xstring  TYPE xstring,
      client_window  TYPE REF TO cl_wdr_client_window.
* Field-Symbols
FIELD-SYMBOLS: <fs_data> TYPE ANY,
               <fs_val>  TYPE ANY,
               <fs_plan> TYPE ANY.

*PERNR
*WERKS
*BTRTL
*PERSG
*PERSK
*LAND1
DATA:
  ls_emp TYPE zst_emp_data,
  lt_emp TYPE TABLE OF zst_emp_data.

ls_emp-PERNR = '101'.
ls_emp-WERKS = 'Hyd'.
ls_emp-LAND1 = 'IN'.
append ls_emp to lt_emp.

ls_emp-PERNR = '102'.
ls_emp-WERKS = 'Bang'.
ls_emp-LAND1 = 'IN'.
append ls_emp to lt_emp.

ls_emp-PERNR = '301'.
ls_emp-WERKS = 'Canada'.
ls_emp-LAND1 = 'US'.
append ls_emp to lt_emp.

Regards,

Lekha.

Former Member
0 Kudos
*Create IXML factory
CALL METHOD cl_ixml=>create
  RECEIVING
    rval = lref_xml.

CALL METHOD lref_xml->create_document
  RECEIVING
    rval = lref_doc.

CALL METHOD lref_doc->create_simple_element
  EXPORTING
    name   = 'Workbook'
    parent = lref_doc
  RECEIVING
    rval   = lref_e_root.

lref_e_root->set_attribute( name  = 'xmlns' value = 'urn:schemas-microsoft-com:office:spreadsheet' ).
lref_attr    = lref_doc->create_namespace_decl( name   = 'ss'   prefix = 'xmlns'
                                                          uri    = 'urn:schemas-microsoft-com:office:spreadsheet' ).
lref_e_root->set_attribute_node( lref_attr ).

lref_attr  = lref_doc->create_namespace_decl( name   = 'x'      prefix = 'xmlns'
                                                        uri    = 'urn:schemas-microsoft-com:office:excel' ).
lref_e_root->set_attribute_node( lref_attr ).

lref_attr    = lref_doc->create_namespace_decl( name   = 'o'    prefix = 'xmlns'
                                                          uri    = 'urn:schemas-microsoft-com:office:office' ).
lref_e_root->set_attribute_node( lref_attr ).

lref_attr    = lref_doc->create_namespace_decl( name   = 'html' prefix = 'xmlns'
                                                          uri    = 'http://www.w3.org/TR/REC-html40' ).
lref_e_root->set_attribute_node( lref_attr ).

Former Member
0 Kudos
*  Style -
lref_styles  = lref_doc->create_simple_element( name = 'Styles'    parent = lref_e_root  ).
lref_style  = lref_doc->create_simple_element( name = 'Style' parent =  lref_styles  ).
lref_style->set_attribute_ns( name = 'ID' prefix = 'ss' value = 's22' ).
lref_format = lref_doc->create_simple_element( name = 'NumberFormat' parent = lref_style  ).
lref_format->set_attribute_ns( name = 'Format' prefix = 'ss' value = '0' ).

* Protected
lref_protection   = lref_doc->create_simple_element( name = 'Protection'  parent = lref_style  ).
lref_protection->set_attribute_ns( name = 'Protected'  prefix = 'ss' value = '0' ).

*-Creating the Worksheet
lref_worksheet = lref_doc->create_simple_element( name = 'Worksheet' parent = lref_e_root ).
lref_worksheetoptions = lref_doc->create_simple_element( name = 'WorksheetOptions' parent = lref_worksheet ).
lref_worksheetoptions->set_attribute( name = 'xmlns'   value = 'urn:schemas-microsoft-com:office:excel' ).
lref_visible        = lref_doc->create_simple_element( name = 'Visible'  value = 'SheetHidden'  parent = lref_worksheetoptions  ).
lref_allowsizecols   = lref_doc->create_simple_element( name = 'AllowSizeCols' parent = lref_worksheetoptions ).
lref_allowinsertrows = lref_doc->create_simple_element( name = 'AllowInsertRows' parent = lref_worksheetoptions ).
lref_allowdeleterows = lref_doc->create_simple_element( name = 'AllowDeleteRows' parent = lref_worksheetoptions ).
lref_worksheet->set_attribute_ns( name   = 'Name'   prefix = 'ss'  value  = 'EMPMASTER' ).

Former Member
0 Kudos
*Create a table
lref_table = lref_doc->create_simple_element( name = 'Table'  parent = lref_worksheet ).

*Create all the columsn first
lref_column = lref_doc->create_simple_element( name = 'Column' parent = lref_table ).
lref_column = lref_doc->create_simple_element( name = 'Column' parent = lref_table ).
lref_column = lref_doc->create_simple_element( name = 'Column' parent = lref_table ).

*Header row
lref_row    = lref_doc->create_simple_element( name = 'Row'  parent = lref_table ).
lref_cell = lref_doc->create_simple_element( name = 'Cell'  parent = lref_row ).
lref_data = lref_doc->create_simple_element( name = 'Data'  value = 'EmpNo' parent = lref_cell ).
lref_data->set_attribute_ns( name = 'Type'            prefix = 'ss'   value = 'String' ).
lref_cell = lref_doc->create_simple_element( name = 'Cell'  parent = lref_row ).
lref_data = lref_doc->create_simple_element( name = 'Data'  value = 'City' parent = lref_cell ).
lref_data->set_attribute_ns( name = 'Type'            prefix = 'ss'   value = 'String' ).
lref_cell = lref_doc->create_simple_element( name = 'Cell'  parent = lref_row ).
lref_data = lref_doc->create_simple_element( name = 'Data'  value = 'Country' parent = lref_cell ).
lref_data->set_attribute_ns( name = 'Type'            prefix = 'ss'   value = 'String' ).
Former Member
0 Kudos
DATA lv_count1 TYPE i VALUE 100.

*DYNAMIC WAY
loop at lt_emp into ls_emp.
  lref_row    = lref_doc->create_simple_element( name = 'Row'  parent = lref_table ).

  lref_cell = lref_doc->create_simple_element( name = 'Cell'  parent = lref_row ).
  lref_cell->set_attribute_ns( name = 'StyleID'         prefix = 'ss'   value = 's22' ).
  lv_count = ls_emp-pernr.
  CONDENSE lv_count NO-GAPS.
  lref_data = lref_doc->create_simple_element( name = 'Data'  value = lv_count parent = lref_cell ).
  lref_data->set_attribute_ns( name = 'Type'            prefix = 'ss'   value = 'Number' ).

  lv_count = ls_emp-werks.
  CONDENSE lv_count NO-GAPS.
  lref_cell = lref_doc->create_simple_element( name = 'Cell'  parent = lref_row ).
  lref_data = lref_doc->create_simple_element( name = 'Data'  value = lv_count parent = lref_cell ).
  lref_data->set_attribute_ns( name = 'Type'            prefix = 'ss'   value = 'String' ).

 lv_count = ls_emp-land1.
 CONDENSE lv_count NO-GAPS.
  lref_cell = lref_doc->create_simple_element( name = 'Cell'  parent = lref_row ).
  lref_data = lref_doc->create_simple_element( name = 'Data'  value = lv_count parent = lref_cell ).
  lref_data->set_attribute_ns( name = 'Type'            prefix = 'ss'   value = 'String' ).

endloop.
Former Member
0 Kudos
lref_sfactory = lref_xml->create_stream_factory( ).
lref_ostream = lref_sfactory->create_ostream_itable( table = l_xml_table ).
*   Rendering the document
lref_renderer = lref_xml->create_renderer( ostream  = lref_ostream
                                      document = lref_doc ).
lv_rc = lref_renderer->render( ).
*   Saving the XML document
lv_size = lref_ostream->get_num_written_raw( ).

CALL METHOD cl_gui_frontend_services=>gui_download
  EXPORTING
    bin_filesize = lv_size
    filename     = 'C:\Documents and Settings\13322\Desktop\demo.xls'
    filetype     = 'BIN'
  CHANGING
    data_tab     = l_xml_table
  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.

Edited by: Lekha on Nov 9, 2009 4:11 PM

Former Member
0 Kudos

Hi,

You can use the above code.....in SE38 as i have used the normal GUI_download FM for testing prurpose....You can use the same logic in underlying class...

The above code will generate the excel sheet with 3 columns and a header row...

You can explore more based on the above code....

All the best.

Regards,

Lekha.

Answers (0)