Exporting table data to MS-Excel Sheet(enhanced Web Dynpro Binary Cache)

[Former Member]

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

  
  
  

  

  Step 2

  Add the jar file to your project

  
  
  

  

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

Implementation

wdDoInit

         public void wdDoInit()  {    //@@begin wdDoInit() String fileName = "output" + ".xls"; IWDCachedWebResource cachedExcelResource = null; try {  File f = new File("output.xls");  WritableWorkbook workbook =   Workbook.createWorkbook(f);  WritableFont red = new WritableFont(WritableFont.ARIAL,                                            WritableFont.DEFAULT_POINT_SIZE,                                            WritableFont.NO_BOLD,                                            false,                                            UnderlineStyle.SINGLE,                                            Colour.RED);  WritableCellFormat redFormat = new WritableCellFormat(red);  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);  Label label = new Label(0, 0, "Heading1",redFormat);  sheet.addCell(label);  label = new Label(1,0,"Heading2",redFormat);  sheet.addCell(label);  WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER);  jxl.write.Number number1 = new jxl.write.Number(0, 1, 5, integerFormat);     sheet.addCell(number1);  number1 = new jxl.write.Number(1, 1, 5, integerFormat);  sheet.addCell(number1);  workbook.setColourRGB(Colour.LIME, 0xff, 0, 0);  workbook.write();  FileInputStream excelCSVFile = new FileInputStream(f);  cachedExcelResource =   getCachedWebResource(    excelCSVFile,    fileName,    WDWebResourceType.getWebResourceType(     "xls",     "application/ms-excel"));  wdContext.currentContextElement().setExcelURL(cachedExcelResource.getURL());  workbook.close(); } catch (Exception ex) {  ex.printStackTrace(); } }      

getCachedWebResource

    private IWDCachedWebResource getCachedWebResource(  InputStream file,  String name,  WDWebResourceType type) {  IWDCachedWebResource cachedWebResource = null;  if (file != null)  {   cachedWebResource = WDWebResource.getWebResource(file, type);   cachedWebResource.setResourceName(name);  }  return cachedWebResource; }  

Context Variables

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

Output

Output Page

Excel sheet

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