Enhanced File Upload - Uploading and Processing Excel Sheets

[Former Member]

Requirement

In my Exporting table data to MS-Excel Sheet(enhanced Web Dynpro Binary Cache), we have seen how to pass data to an Excel File using MS-Excel formatting. There was another Web Dynpro Java a couple of weeks ago. This time, it was how to read data from an MS-Excel file.

There are Assessment for Excel Solutions in Java to do this. I used the jexcelapi which enables us to achieve this.

Pre-Requisites

1. Web Dynpro Binary Cache
2. File Upload - 1
3. File Upload - 2

Setup

The setup is exactly the same as shown Exporting table data to MS-Excel Sheet(enhanced Web Dynpro Binary Cache)

Implementation

To just demonstrate how this works , we need the following things:

Layout

1. File Upload UI Element
2. Upload Excel File Button UI Element - onAction -> Upload

Context Elements

1. FileName - bound to File Upload UI Element property - fileName
2. FileResource - bound to File Upload UI Element property - data

Code

onActionUpload

            IWDAttributeInfo attributeInfo = wdContext.getNodeInfo().getAttribute(IPrivateTestCompView.IContextElement.FILE_RESOURCE);             IWDModifiableBinaryType binaryType = (IWDModifiableBinaryType) attributeInfo.getModifiableSimpleType();             IContextElement element = wdContext.currentContextElement();                          if(element.getFileResource()!= null)             {               try               {                    String mimeType = binary.getMimeType().getFileExtension();                    wdComponentAPI.getMessageManager()=.reportSuccess("File " + binaryType.getFileName() + " has been successfuly uploaded ! ");               }               catch(Exception e)               {                    throw new WDRuntimeException(e);               }             }             else             {                    wdComponetAPI.getMessageManager().reportException("File could not be uploaded", true);             }                          // Till here the code remains the same as in File Upload tutorials             // Now the code for retrieving the values from the Excel sheet                          try             {                ByteArrayInputStream bais = new ByteArrayInputStream(element.getFileResource());                Workbook workbook = Workbook.getWorkbook(bais);                Sheet sheet = workbook.getSheet(0);                Cell a1 = sheet.getCell(0,0);                Cell a2 = sheet.getCell(0,1);                                wdComponentAPI.getMessageManager().reportSuccess("Value of cell a1 = " + a1.getContents());                wdComponentAPI.getMessageManager().reportSuccess("Value of cell a2 = " + a2.getContents());             }                          catch(Exception ex)             {               wdComponentAPI.getMessageManager().reportException(ex.getLocalizedMessage(),true);             }                        

Output

Excel Sheet

Application

Output - Application