file is corrupted error while creating excel using xml

[Former Member]

  Hi have created one excel file using XML code and sent it to mail as attachement. But when i open it, it displays error  message - file is corrupted and cannot be opened.

below is my code - please review and tell what is wrong in it ASAP :

* Creating a ixml Factory

   l_ixml = cl_ixml=>create( ).

* Creating 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 for document properties.

   r_element_properties = l_document->create_simple_element( name = 'TEST_REPORT'  parent = l_element_root ).

   l_value = sy-uname.

   l_document->create_simple_element( name = 'Author'  value = l_value  parent = r_element_properties  ).

* Styles

   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' ).

   r_format  = l_document->create_simple_element( name = 'Interior' parent = r_style  ).

   r_format->set_attribute_ns( name = 'Color'   prefix = 'ss'  value = '#92D050' ).

   r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss'  value = 'Solid' ).

   r_format  = l_document->create_simple_element( name = 'Alignment'  parent = r_style  ).

   r_format->set_attribute_ns( name = 'Vertical'  prefix = 'ss'  value = 'Center' ).

   r_format->set_attribute_ns( name = 'WrapText'  prefix = 'ss'  value = '1' ).

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

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).

   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).

   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).

   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).

   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).

   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).

   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).

   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).

   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).

   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).

   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).

   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).

   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

* 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 ).

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).

   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).

   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).

   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).

   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).

   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).

   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).

   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).

   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).

   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).

   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).

   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).

   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

* Worksheet

   r_worksheet = l_document->create_simple_element( name = 'Worksheet'  parent = l_element_root ).

   r_worksheet->set_attribute_ns( name = 'Name'  prefix = 'ss'  value = 'Sheet1' ).

*  r_worksheet->set_attribute_ns( name = 'Protected'  prefix = 'ss'  value = '1' ).    " WORKING

* 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

   r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).

   r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '40' ).

   r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).

   r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '90' ).

   r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).

   r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '140' ).

   r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).

   r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

   r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).

   r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '90' ).

   r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).

   r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '90' ).

   r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).

   r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '90' ).

   r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).

   r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '90' ).

   r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).

   r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '90' ).

* Blank Row

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

* 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' ).

* RFQ No.

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

   r_data = l_document->create_simple_element( name = 'Data'  value = 'RFQ No.'  parent = r_cell ).

   r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

* RFQ Line Item No

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

   r_data = l_document->create_simple_element( name = 'Data'  value = 'RFQ Line Item No.'  parent = r_cell ).

   r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

* Material

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

   r_data = l_document->create_simple_element( name = 'Data'  value = 'Material'  parent = r_cell ).

   r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

* Quantity

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

   r_data = l_document->create_simple_element( name = 'Data'  value = 'Quantity'  parent = r_cell ).

   r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

*  Order UNIT

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

   r_data = l_document->create_simple_element( name = 'Data'  value = 'Order Unit'  parent = r_cell ).

   r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

*  Delivery Date

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

   r_data = l_document->create_simple_element( name = 'Data'  value = 'Delivery Date'  parent = r_cell ).

   r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

*  RFQ Creation Date

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

   r_data = l_document->create_simple_element( name = 'Data'  value = 'RFQ Creation Date'  parent = r_cell ).

   r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

*  RFQ Deadline Date

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

   r_data = l_document->create_simple_element( name = 'Data'  value = 'RFQ Deadline Date'  parent = r_cell ).

   r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

* Price

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

   r_data = l_document->create_simple_element( name = 'Data'  value = 'Net Price'  parent = r_cell ).

   r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

** Login

*  r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

*  r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

*  CONCATENATE 'Login - ' lv_date+6(2) '/' lv_date+4(2) '/' lv_date+0(4) INTO l_value.

*  r_data = l_document->create_simple_element( name = 'Data'  value = l_value  parent = r_cell ).

*  r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

* Blank Row after Column Headers

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

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

   r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

   r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

* Data Table

   LOOP AT it_final1 INTO wa_final1.

     CLEAR l_value.

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

* RFQ No.

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

     CLEAR l_value.

     l_value = wa_final1-ebeln .

*    CONDENSE l_value NO-GAPS.

     r_data = l_document->create_simple_element( name = 'Data'  value = l_value  parent = r_cell ).           " Data

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                               " Cell format

* Line Item No

     CLEAR l_value.

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

     l_value = wa_final1-ebelp.

     r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).           " Data

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'Number' ).                               " Cell format

* Material

     CLEAR l_value.

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

     l_value = wa_final1-txz01.

     r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).           " Data

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                               " Cell format

* RFQ QTY

     CLEAR l_value.

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

     l_value = wa_final1-ktmng.

     r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).           " Data

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'Number' ).                               " Cell format

* UNIT

     CLEAR l_value.

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

     l_value = wa_final1-meins.

     r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).          " Data

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                              " Cell format

* DElivery Date

     CLEAR l_value.

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

     l_value = wa_final1-eindt.

     r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).          " Data

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                              " Cell format

* RFQ Date

     CLEAR l_value.

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

     l_value = wa_final1-aedat.

     r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).          " Data

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                              " Cell format

* RFQ Deadline

     CLEAR l_value.

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

     l_value = wa_final1-agdat.

     r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).          " Data

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                              " Cell format

* price

     CLEAR l_value.

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data' ).

*    r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).          " Data

*    r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).                              " Cell format

   ENDLOOP.

* Creating a Stream Factory

   l_streamfactory = l_ixml->create_stream_factory( ).

* Connect Internal 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( ).

* Saving the XML Document

   l_xml_size = l_ostream->get_num_written_raw( ).

[former_member212124]

Hi Seema,

Try to debug that code.

follow these steps.

click on variable lv_buffer.

   Choose 'XML browser' .

Check that xml code and it will be easy to find the error.

If you find any difficulty send the screen shot of XML code from XML browser.

thanks,

vidyasagar

[former_member212124]

And one more quick info.

normally errors occur due to following reasons.

1) passing negative value example l_value = '4-' try to reverse it to '-4'.

2) not creating a row at proper places.

3) not condensing l_value after passing a numeric value.

4) preparing wrong formula(not using proper condense and concatenate statements).

Hope these help you to resolve issues.

thanks,

vidyasagar

[Former Member]

Thanks . Condensing the numeric value worked. You really saved my day.

one more thing. I want to know, which method you have used in creating xml file (in the screenshot ). because my output file is getting saved in  SOLIX type structure. so it cannot be opened as XML browser while debugging. Please tell me , I am really curious to know.

and Thanks again.

Regards,

Seema

[Former Member]

One more help,,,, I need to lock(make non-editable) only particular column for the user in this excel.

I know how to lock the whole excel sheet ( r_worksheet->set_attribute_ns( name = 'Protected'  prefix = 'ss'  value = '1' ).),

but need to know for only one column .

Could you please help in this.

Regards,

Seema

[former_member212124]

How to make a particular column non editable?

Make your style protected and pass that style name to your required column while passing data.

for example:

Find below the code for protection of a style.

Last two lines are very important.

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

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

   r_format  = l_document->create_simple_element( name = 'Interior' parent = r_style  ).
   r_format->set_attribute_ns( name = 'Color'   prefix = 'ss'  value = '#B2FF64' ).      
   r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss'  value = 'Solid' ).

   r_format  = l_document->create_simple_element( name = 'Alignment'  parent = r_style  ).
   r_format->set_attribute_ns( name = 'Vertical'  prefix = 'ss'  value = 'Bottom' ).
   r_format->set_attribute_ns( name = 'WrapText'  prefix = 'ss'  value = '1' ).

   r_border  = l_document->create_simple_element( name = 'Borders'  parent = r_style ).
   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

   r_format  = l_document->create_simple_element( name = 'Border'   parent = r_border  ).
   r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
   r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
   r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

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

[Former Member]

Hello Vidyasagar,

It didnt worked .

Thanks & Regards,

Seema

[former_member212124]

Hi,

Sorry for late response I am bit busy here.

try and use this with your style for header.

add this code at the end of the style for header code section.

   r_format  = l_document->create_simple_element( name = 'Protection'  parent = r_style  ).

   r_format->set_attribute_ns( name = 'Protected'  prefix = 'ss'  value = '1' ).

thanks,

vidyasagar

[Former Member]

Hello ,

I didn't worked.

Tried for changing style header

Please suggest.

Regards

[former_member212124]

Hi,

Try this code from attachment this will give you some idea.

Double click on the 'column2' which is marked in yellow.You will see it is read only.

[Former Member]

Thanks Vidyasagar, It worked. But when I click on 'Uprotect Sheet' option in excel (after opening it), all the locked cells/columns becomes unprotected. any way to avoid this?

Regards,

Seema

[Former Member]

Hi Vidyasagar,

This solution was very helpful and I am now able to download an xls and xlsx file using this approach.

I have column headings included in my excel template. But I noticed one thing, When I enter data to be uploaded in this template and hit save , it saves it as an XML Spreadsheet, with a file extension of .XML. Is this the normal behavior, can I prevent this default.

Also, When I upload the excel file, it is pulling it up as an XML structure and not as an internal table

as I expected. The xlsx version is working fine, but the xls is coming up as XML.

I am doing the upload and download in WDA.

For uploading the xls version, I use the following approach -

	CALL FUNCTION 'HR_KR_XSTRING_TO_STRING'
	EXPORTING
	in_xstring = lv_data_source
	IMPORTING
	out_string = l_string.

where lv_data_source is the file content from the WDA.

Here l_string is returned all gibberish.

If I save it as .XML, it returns the XML structure.

Using the following option for xlsx file and that is working fine.

*	Create object of class to read .xlsx file contents
	CREATE OBJECT lref_excel
	EXPORTING
	document_name = lv_file_name
	xdocument	= lv_data_source.

Can anyone help me with the xls version, how do we read the file without having to work with the XML. Or if I am using the wrong FM to retrieve the data from the file.

Appreciate your input.

Thank you

Lalitha

[former_member212124]

Hi lalitha,

I will be sending you some example for uploading this excel data soon.

thanks,

vidyasagar

[former_member212124]

Hi seema,

Yeah I know the sheet becomes unprotected on click of  'Unprotect Sheet', I am trying to find a solution for that.

I will get back to you as soon as I get some solution.

thanks,

vidyasagar

[Former Member]

Hi Vidyasagar,

I have been debugging this further, here is what I have found.

When I download the file as an xls file, it gets saved as a "Microsoft Excel 97-2003 Worksheet".

Now when I upload it as it is, without adding any data , the xstring is converted to XML -

<?xml version="1.0"?><workbook xmlns="urn:schemas-Microsoft-com: office: spreadsheet" etc

When I view this xstring in the XML browser , I can see the whole XML structure.(image one, after the split at the horizontal tab.

But if the same file, I open and add some data, and then do a save as , it defaults to an xml spreadsheet. (image 2) . I have office 2003 in my system.

Also I noticed that users with 2010 and above are not able to download the xlsx version , they get the error  "Excel cannot open the file 'Blank Template.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file" (image 3).

But I am able to download this. and also the upload for this works fine.

So am in a fix right now. XLS downloads ok, but not able to upload using it.

XLSX issue downloading, but upload works.

Please do let me know if you need any other info.

Thank you

Lalitha

[former_member212124]

Hi Lalitha,

Thank you for details.

Can you send the code for both upload and download that you are using, so that it will help me to understand the problem better.

thanks,

vidyasagar

[Former Member]

Hi Vidyasagar,

Shd I copy paste the whole code here, I do not see an option to attach a text file.

Please advise.

Thank you

Lalitha

[former_member212124]

Hi You can attach a file.On the right hand top side of your reply screen there is a 'Use advanced editor'

option use that one.

[Former Member]

Hi Vidyasagar,

I do not see that option in my system, Is there any other option. I will try from a different system to see if it lets me. But I do not see the "Use Advanced Editor " option in this screen.

Can I email it to you directly ?

Thank you

Lalitha

[former_member212124]

You can email it to me directly or you can do this.

1) Click on reply

2) you can see use advanced editor on right hand side of reply box.

[Former Member]

Hi Vidyasagar,

Nope, do not see that option at all....... can't send u a direct message also.

Can u let me know where to email this to you. My email ID is <email removed by moderator>

Thank you

Lalitha

Message was edited by: Manish Kumar

[former_member212124]

Hi Lalitha,

Please go to my profile and you will get my email address.

thanks,

vidyasagar

[matt]

Moderator message

please review and tell what is wrong in it ASAP :

Please note - the use of "ASAP" and "urgent" and similar words is not permitted and can result in your post being rejected - which if you're in a hurry presumably isn't what you want. Everyone wants an answer ASAP. Everyone's needs are urgent.

Please do not do this again.

[Former Member]

Ok Matthew . I will take care of it.

[Former Member]

Hi Vidyasagar,

Your input over here helped me a lot, thanks for the help.

Just wanted to know, if it is possible to display image in Excel using this method ?

Also, I just wonder every time, how do you people come to know, about these different functionality,

is there any SAP document for these classes ?

Thanking You All.

[former_member212124]

Hi Ankit,

I am happy that you were benefited by this.

I think we can display image using this method but I still have to figure out how to do that.As soon as I figure it out ill be sharing it with you.

And how I got to know about this class? to be honest my senior colleague used this method and I learnt it from him.

thanks,

vidyasagar

[Former Member]

This message was moderated.