<u>Introduction</u>One of the common requirements in Web Dynpro project, which is very common both in ABAP and JAVA Web Dynpro, is to download the Table data into Excel file. There is lot of material and help available on SDN to achieve the desired result particularly
- Excel Download Using Binary Cache On Demand Streams by Bertram Ganz
- Excel download using external api's by Venkatesh Subramaniam
- Latest by Ayyaparaj
All of the above Weblogs are useful in their own manner. Blog by Bertram Ganz is by far the most important weblogs, as per my opinion which is still being used most frequently and finds large audience especially in the community comprising up of Web Dynpro for Java Beginners. It is indeed a very well explained and well documented help available on SDN. When I received the requirement of downloading Table data into Excel from Web Dynpro, my first obvious choice was to implement using Binary Cache because of obvious reasons*. *In my project I had some restrictions relating to the use of external api's and/or freeware that can help to achieve the desired result and hence the only option available to me at last was to use On Demand Stream for Excel Download.
<u>Objective</u>.
In SDN forum every next day you can find a couple of questions relating to the formatting of excel generated through Binary Cache on Demand Stream. Questions like
- Excel formatting containing # in generated excel
- Additional columns getting generated
- Header not formatted properly
- Filter coming by Default
- How to change the color of the header and soon.
I also faced all these issues and trying to fix and get rid of all these I found a different way of achieving the desired result with Binary Cache with Slight modification.
In our project we achieved the desired result using the mechanism of Binary Cache with slight modifications and adhering to the Spreadsheet schema which has a rich collection of elements and attributes responsible for Excel Formatting. The idea was to generate the XML which is done to Normal Binary Cache but to modify the structure in a way that it starts adhering to the XML spreadsheet schema. XML spreadsheet schema contains a rich collection of elements and attributes which can be used to format the desired excel.
Following have been achieved very easily using binary cache directly but modifying the XML structure getting generated in the toStringformat
- Header Color
- Alignment
- Filter control
- Column width
- Column type like sting number float etc
Using the XML spreadsheet schema we can control almost all the features of an Excel without using any external API.
In this web blog I would like to share the learning and approach of using XML spread sheet schema to format Excel Generated using Binary Cache.
<u>Methods Used:</u>
I am starting the step by Step process keeping the Binary Cache example as reference and modifying only the method where node data is populated in the XML structure. I have not used the Component based approach which is more generalized and may suit lot of applications, you can always go for it and it is well documented in the Binary Cache Example available in the SDN tutorial. To make the example simple I have created a method in the Component controller and directly called it from the View action responsible for the Excel Generation. This action will pass the Table node to the Excel as reference and contains the downloadable data. Methods to achieve the same is mentioned below
- Create method toXMLSpreadsheet
- Create Method OpenLink
- Create Method CloseLink
Excel 2003 includes a new set of functionality related to the much popular XML data representation. It supports import of a valid XML schema and internally converts the same in to Excel document. XML imported can be generic, well formed XML or specially formatted XML spreadsheet using XML spreadsheet schema. In current method we will create an XML spreadsheet using spreadsheet schema instead of generic XML. This spreadsheet schema has a set of attributes to help us format the Excel generated out of the XML representation. We will be using this to format the Excel. Some of the features that can be very easily achieved using this schema are
- Header Coloring
- Column width
- Data Format
- Alignment
- Auto numbering
- Filtering
- Setting individual properties of the column as String Number currency etc
All the above features can be included in the Excel with the help of various attributes associated with the spreadsheet schema.
I am using simple String manipulation but a better approach would always be to go for XML parser to generate the XML as this comes handy with lot of methods to manipulate the XML document whereas here we have to take of this manually using string.
Another modification can be achieved in using MAP and iterator classes to populate the Header and Row elements but in my case I haven't used the same.
/*
* This method will format the Input Node as per the
* standard functionality for MS Excel
* with proper namespace and Header formatting
* HdrList - Contains List of Header Elements
* AttrList - Contains List of Attributes to be populated in Excel
*
*/
StringBuffer x = new StringBuffer(); HdrList = new ArrayList AttrList = new ArrayList AttrList.add(IPrivateDisplayExcelView.IOutputElement.NAME); AttrList.add(IPrivateDisplayExcelView.IOutputElement.SURNAME); StringBuffer Hdr = new StringBuffer(); StringBuffer Data = new StringBuffer();
Hdr.append(RowHdr);
for (int i = 0; i < HdrList.size(); i++) { Hdr.append(CellStrt).append(StrDataTag).append(HdrList.get(i))
.append(EndDataTag).append(CellEnd);
}
Hdr.append(RowEnd);
for (int i = 0; i < dataNode.size(); +i) {</p><p> IWDNodeElement dataNodeElement = dataNode.getElementAt(i);</p><p> Data.append(RowStart);</p><p> for (<strong>int</strong> j = 0; j < AttrList.size(); j) {</p><p> Data.append(CellStrt).append(StrDataTag).append(</p><p> dataNodeElement.getAttributeValue(AttrList.get(j)))</p><p> .append(EndDataTag).append(CellEnd);</p><p> }</p><p> Data.append(RowEnd);</p><p> }</p><p> </p><p> x.append(DocHdr).append(Worksheet);</p><p> for (<strong>int</strong> i = 0; i <= 11; i+) { x.append(columnwidth);
}
x.append(Hdr.toString())
.append(Data.toString()).append(EndSheet).append(EndWorkbook);
return x.toString();
//This method is called from the action in the controller and will //contain all the code for excel link window pop up to download the //same. All these methods are same as in Binary cache except some //have been omitted or tailored here
byte[] excelXMLFile; IWDCachedWebResource cachedExcelResource = null; String fileName = dataNode.getNodeInfo().getName() + ".xls";
try {
excelXMLFile = wdThis.toExcelString(dataNode).getBytes("UTF-8");
IWDResource resource = null; if (excelXMLFile != null) { resource = WDResourceFactory.createCachedResource(excelXMLFile, "PaymentList.xls", WDWebResourceType.XLS); }
if (resource != null) { wdContext.currentContextElement().
setExcelURL(resource.getUrl(WDFileDownloadBehaviour.OPEN_INPLACE.ordinal())); wdContext.currentContextElement().
setExcelFileName( resource.getResourceName());
openExcelLinkPopup();
} else { wdComponentAPI.getMessageManager().reportException(
"Failed to create Excel file from table!");
}
} catch (UnsupportedEncodingException e) { wdComponentAPI.getMessageManager().reportException( e.getLocalizedMessage());
}
<u>Conclusion:</u>After trying and getting the desired result from Binary Cache using On demand Stream tech, I have achieved same functionality which any external api will provide. However, Its an individual and sometimes project imposed decision to adopt the technique but the main reason in favor for using external api's is formatting is not the reason good enough to go for it and that's purely my personal opinion.
<u>Reference:</u>- v For More reference on the spreadsheet XML schema please use the MSDN link which has whole set of attributes and their usage description.
http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx- v Exporting Table Data Using On-Demand Streams - By Bertram Ganz
http://www.sdn.sap.com/irj/scn/wiki?path=/x/0mq </p>
