on 11-05-2009 10:29 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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.
*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 ).
* 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' ).
*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' ).
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.
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
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.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.