Skip to Content

Requirement

A couple of days ago there was a about excel export for WD! in Web Dynpro Forum to translate table data to Excel file. The quick answer was to refer to Web Dynpro Binary Cache. However, the requirement was to change font of header, change the colour of the cell etc.. Basically to have an MS-Excel sheet, rather than using the conventional approach of translating data to XML Format or CSV Format.Therefore our toExcelUsingXML() or toExcelUsingCSV() approach will not help in this case.

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

Setup

  • Download the file JExcelAPI
  • Add it to the library path

    Step 1

    Right Click on Properties

    Properties of Project

    Step 2

    Add the jar file to your project

    Properties of Project

  • Make sure you also add the jxl.jar file to the library as well

Implementation

wdDoInit

getCachedWebResource

Context Variables

Create a variable ExcelUrl in the context. This variable will hold the location of the Excel sheet

Context Variables

Output

Output Page

Output

Excel sheet

Excel Sheet

For more information regarding formatting refer the tutorial available with jexcelapi.

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

  1. Bertram Ganz
    Hi Subramanian,

    many thanks for your real “Value Added WebLog”. I highly appreciate such great enhancements of my tutorial material.

    I got many questions on how to enrich the excel output with formatting functions. Now I know where to find an answer.

    Best regards, Bertram

    P.S.: How did you format you code blocks? Very nice and compact.

    (0) 
  2. Chris Paine
    Excellent blog!

    where did you find out about this open source API? – it’s an excellent solution. Do you know of any other open source API’s – eg something to export to MS Word, MS Powerpoint, I can imagine the users going nuts if I could export a series of graphs directly to MS Powerpoint.

    I’ve had to code up some enchancements to XML exports to export headers and format fields as dates/numbers/text/currency, using this API would seem to be a fair bit easier, as using XML my biggest issue has been column sizing, does not seem to be possible in XML do dynamic column sizing.

    Great stuff, thanks for sharing.

    Chris

    (0) 
  3. Anoop Dwivedi
    I am facing a problem when i export the excel.
    It appears sometimes when the complete ” data list” file is not saved on the local PC, only part of it. Opening the file in excel gives the following error message:

    Microsoft Office Excel File Repair Log

    Errors were detected in file ‘C:\Documents and Settings\abcd\Desktop\pqrs\test.xls’

    The following is a list of repairs:

    Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted.

    Excel found errors that may cause some recovered data to be put in the wrong cells. Please check your recovered data carefully.

    (0) 
  4. Sheldon Lyttle
    Great article … a real life saver!

    One thing we haven’t been able to do however is figure out a way to export complex ‘rollup’ data to excel.   Not sure if it is even possible.

    Basically what we have to do is export data that is formatted as follows:

    Unit Sales      100
        USA              50
        Canada

    (0) 
  5. Sheldon Lyttle
    Great article … a real life saver!

    One thing we haven’t been able to do however is figure out how to export complex ‘roll-up’ data to excel.   Not sure if it is even possible.

    Basically what we have to do is export data that is formatted as follows:

    Unit Sales      100
        USA              50
        Canada           30
        Mexico           20

    Within the excel spreadsheet there would need to be a “+” sign next to Unit Sales in order to expand the constituent parts of the roll-up

    Any bright ideas?

    (0) 
  6. S B
    Hi,

    I have a requirement of reading a EXCEL file from my KM repository and then passing the contents to a Function Module in backend.

    I have to do this through POI API’s with JSP dynpage.

    Any help will be appreciated.

    (0) 
  7. Miguel Sanchez
    Hi,
    Thanks for this Blog!!!
    I have an error in this line:

    ByteArrayInputStream bais =  new ByteArrayInputStream(element.getFileResource());

    The error that is throwing is: “The constructor ByteArrayInputStream(String) is undefined”

    I tried changing the attribute to byte type but it still not working: “The constructor ByteArrayInputStream(byte) is undefined”

    Please help!!!

    (0) 
  8. Miguel Sanchez
    Hi,

    Does anybody have a sample DC using the JAR? I don´t know why the server is throwing this error. I have followed all the steps but the error continues.

    thanks!!!

    (0) 
  9. ramanan rs
    Hi all,

    I am trying to implement this functionality. I am not able to use the code
    Label label = new Label(0, 2, “A label record”);

    It says that the constructor with int,int,str as parameter is not available.

    I changed it to Label label = new Label(“A label record”); Then while deploying I get a run time excepiton saying ‘java.lang.ClassNotFoundException: jxl.format.CellFormat’. This ‘Cellformat’ is an interface and is available in the jar and am able to see it when i code. I am using the jar “jexcelapi_2_6_9_1.4” version. Do i have to use some other jar versions.
    Please help. Thanks in advance.

    Regards
    Ramanan

    (0) 
  10. Govindu Nagotla
    Hi ,

    Its very helpful blog for my requirment .
    Requirment as it is mentioned in the blog.

    i.e No slecet options and direct download
    and changing the color and font of the headers.

    But I used the below blog :
    [http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/user-interface-technology/wd%20java/wdjava%20archive/exporting%20context%20data%20into%20excel%20using%20the%20web%20dynpro%20binary%20cache.pdf]

    could you let me know how to contine from this to avaoid the additionla windows and to formate the headers.

    i did not get the statment “also add jxl.jar to library ” only one jar file that is jxl to be added to the java build path,where else we need to add the same ?

    thanks in advance.

    Regards,
    Govindu

    (0) 

Leave a Reply