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
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
STRING = lv_xstring "hex string obtained from step 2
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.
** Style related properties
** Other worksheet options properties
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"
<Cell ss:Index="2" ss:StyleID="s63"><Data ss:Type="String">Cell 1</Data></Cell>
** Other worksheet properties
** Similarly code for other worksheets
Using the document object created in the previous step, we start navigating through the nodes to reach the <cell> and <data> nodes.