Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Introduction

Unlike Java, Webdynpro for ABAP does not provide any APIs for modifying Excel files. Conventional OLE approach also does not work with Webdynpro for ABAP. In such case, modifying excel becomes a bit of pain as only option left is to use OfficeControl UI. This article gives you a simpler approach to modify an existing excel file using iXML classes. Only pre requisite is that the excel file that has to be modified needs to be saved in XML spreadsheet 2003 format.

Steps for modifying excel file

1.     Upload/Read existing file

This step involves uploading/reading an excel file in HEX format. You can upload an excel file using FileUpload UI element. Context variable bound to property "data" of FileUpload UI element will provide you the excel contents in HEX format. Alternatively, if you are uploading excel file through some other method in BIN format, you can use any of the SAP standard function modules. For e.g. SCMS_BINARY_TO_XSTRING

2.     Load iXML library

Load definition of iXML library to enable use of methods of class CL_IXML

  CLASS cl_ixml DEFINITION LOAD.

3.     Create main factory and stream factory

*Creating the main factory for the iXML library

  lv_ixml = cl_ixml=>create( ).

*Create a main stream factory.

  CALL METHOD lv_ixml->create_stream_factory

    RECEIVING

      rval = lv_streamfactory.

4.     Create a stream

Create a stream based on stream factory object created above and the excel file that was upload in step 1.

  CALL METHOD lv_streamfactory->CREATE_ISTREAM_XSTRING

    EXPORTING

      STRING = lv_xstring      "hex string obtained from step 2

    RECEIVING

      RVAL   = lv_istream.

5.     Create new XML DOM Object

Create a new XML document in the same main factory object created in step 4.

              lv_obj_dom_xml = lv_ixml->create_document( ).

7.     Traverse the XML structure

Whenever an excel file is saved in XML Spreadsheet 2003 format, a complex XML structure gets created. You can view the XML data by opening the excel file in notepad or any other XML editor. Whole idea behind modifying the excel file here is to understand this XML structure and traverse it to set the value at required row and column.

The XML structure of an excel file saved in XML spreadsheet format looks somewhat like below:

The main nodes of our concern are the <Workbook>, <Worksheet>, <Table>, <Row> and <Cell>.

To modify or add a cell, we need to target the cell node and then the data node as highlighted below.     

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

 xmlns:o="urn:schemas-microsoft-com:office:office"

 xmlns:x="urn:schemas-microsoft-com:office:excel"

 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

 xmlns:html="http://www.w3.org/TR/REC-html40">

** Style related properties

** Other worksheet options properties

 <Worksheet ss:Name="Sheet1">

  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"

   x:FullRows="1" ss:DefaultRowHeight="15">

   <Row ss:Index="2">

    <Cell ss:Index="2" ss:StyleID="s63"><Data ss:Type="String">Cell 1</Data></Cell>

   </Row>

  </Table>

** Other worksheet properties

             ** Similarly code for other worksheets

</Worksheet>

</Workbook>

Using the document object created in the previous step, we start navigating through the nodes to reach the <cell> and <data> nodes.

  • No labels

1 Comment

  1. Guest

    Hi Nirav,

    Thanks the usefull wiki. It will be very helpful.

    Regards

    Hiren K.Chitalia