Getting MDM Result Set of a table into MS Excel using MDM 5.5 Java API’s and JXL API.

The complete process can be divided into two steps:

1) Establishing connection with the MDM Server and getting the Result Set of a table into a WebDynpro context node.
2) Putting the data from a Context Node into a MS Excel sheet using JXL api.

Reason for using JXL API and not the Download to excel functionality.

SAP provides out-of-box Download to excel functionality which can be used in any application.
But the only problem that I faced in using them is that, if your table data contains any special characters like ‘&’
(like the particular case in MDM where I was working), then this will throw an exception.

The reason for this is because the table data is first converted to XML format and then into an Excel sheet and in the XML
data cannot have any such special charaters(the only reason that I could find).

In such a situation it was easier to use JXL api for the same. Moreover it has the advantage that you can have the data in any
Format , Font , Color etc.

Implementation 

Step 1 Step 2</p><p> </p><p>Now using the data present in the Web Dynpro Context node, we can use JXL API to download the same into an Excel sheet.The same is shown below.</p><textarea cols=”100″ rows=”40″>public void onActionToExcel(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )

  {

    //@@begin onActionToExcel(ServerEvent)

     String fileName = “output” + “.xls”;

     IWDCachedWebResource cachedExcelResource = null;

     java.util.Map columnInfos = null;

     try

     {

          File f = new File(“output.xls”);

          WritableWorkbook workbook =

          Workbook.createWorkbook(f);

          WritableFont auto = new WritableFont(WritableFont.ARIAL,

                    WritableFont.DEFAULT_POINT_SIZE,

                    WritableFont.NO_BOLD,

                    false,

                    UnderlineStyle.SINGLE,

                    Colour.AUTOMATIC);

          WritableCellFormat autoFormat = new WritableCellFormat(auto);

          WritableFont blue = new WritableFont(WritableFont.ARIAL,

                    WritableFont.DEFAULT_POINT_SIZE,

                    WritableFont.NO_BOLD,

                    false,

                    UnderlineStyle.NO_UNDERLINE,

                    Colour.BLUE);

          WritableCellFormat blueFormat = new WritableCellFormat(blue);

          WritableSheet sheet = workbook.createSheet(“First Sheet”, 0);

          columnInfos = getProductColumnInfos();

          int i=0;

          for (Iterator iter = columnInfos.keySet().iterator(); iter.hasNext();i++) {

                 String attributeName = (String) iter.next();

                 String headerName = (String) columnInfos.get(attributeName);

               Label nLabel = new Label(i,0,headerName,autoFormat);

               sheet.addCell(nLabel);

          }

          WritableCellFormat stringFormat = new WritableCellFormat();

          int m=0;

          int size = wdContext.nodeNd_record().size();

          for(int y=0;y<size;y++){

               for(Iterator iter1 =columnInfos.keySet().iterator(); iter1.hasNext();m++){

                    String attributeName1 = (String) iter1.next();

                    jxl.write.Label sText = new jxl.write.Label(m,y+1,wdContext.nodeNd_record().getElementAt(y).getAttributeAsText(attributeName1));

                    sheet.addCell(sText);

               }

               m=0;

          }

          workbook.setColourRGB(Colour.LIME, 0xff, 0, 0);

          workbook.write();

          FileInputStream excelCSVFile = new FileInputStream(f);

          cachedExcelResource =getCachedWebResource(excelCSVFile,fileName,WDWebResourceType.XLS);

          wdContext.currentContextElement().setExcelFileURL(cachedExcelResource.getURL());

          wdContext.currentContextElement().setExcelFileName(cachedExcelResource.getResourceName());

          workbook.close();

          openExcelLinkPopup();

     }

     catch(Exception ex){

          wdComponentAPI.getMessageManager().raiseException(ex.toString(),false);

     }</textarea> <p> </p><p> </p><p>In the above code we have first created an Excel file, defined the format for the file, <br />written the required data into the file (by providing the matrix location of the cell as parameters) and finally getting the URL to the file created.</p><p><br />In this way we can have the Result Set of the MDM table (with any search criteria) using MDM Java API’s into a context node and further (using JXL api) into an Excel sheet. </p><p> </p><p> </p><p> </p>