Excel Export in Web Dynpro JAVA
Exporting data into MS Excel table is a standard functionality in ABAP, but this functionality is not available in WD JAVA. Here’s a piece of code, which will help in generating the excel sheet containing the data that can be downloaded.
For exporting front end data to Microsoft Excel table, we need to import an external jar file, named jxl.jar. The file should also be saved in the lib folder of the WD Project.
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, “Ele1”,redFormat);
sheet.addCell(label);
label = new Label(1,0,“Ele2”,redFormat);
sheet.addCell(label);
label = new Label(2,0,“Ele3”,redFormat);
sheet.addCell(label);
WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER);
//***********************************************************//
// Code For Reading Table Begins //
//***********************************************************//
for(int x=0; x<wdContext.nodeContext_Table().size(); x++)
{
//Generic Approach
IWDNodeElement element2 = wdContext.nodeContext_Table().getElementAt(x);
Object type = element2.getAttributeValue(“ele1”);
wdContext.currentContextElement().setTest001(type + “”);
Label number1 = new Label(0, x+1, wdContext.currentContextElement().getTest001());
sheet.addCell(number1); // For adding the element to excel sheet
}
for(int x=0; x<wdContext.nodeContext_Table().size(); x++)
{
//Generic Approach
IWDNodeElement element2 = wdContext.nodeContext_Table().getElementAt(x);
Object type = element2.getAttributeValue(“ele2”);
wdContext.currentContextElement().setTest001(type + “”);
Label number1 = new Label(1, x+1, wdContext.currentContextElement().getTest001());
sheet.addCell(number1); // For adding the element to excel sheet
}
for(int x=0; x<wdContext.nodeContext_Table().size(); x++)
{
//Generic Approach
IWDNodeElement element2 = wdContext.nodeContext_Table().getElementAt(x);
Object type = element2.getAttributeValue(“ele3”);
wdContext.currentContextElement().setTest001(type + “”);
Label number1 = new Label(2, x+1, wdContext.currentContextElement().getTest001());
sheet.addCell(number1); // For adding the element to excel sheet
}
for(int x=0; x<wdContext.nodeContext_Table().size(); x++)
{
//Generic Approach
IWDNodeElement element2 = wdContext.nodeContext_Table().getElementAt(x);
Object type = element2.getAttributeValue(“ele4”);
wdContext.currentContextElement().setTest001(type + “”);
Label number1 = new Label(3, x+1, wdContext.currentContextElement().getTest001());
sheet.addCell(number1); // For adding the element to excel sheet
}
for(int x=0; x<wdContext.nodeContext_Table().size(); x++)
{
//Generic Approach
IWDNodeElement element2 = wdContext.nodeContext_Table().getElementAt(x);
Object type = element2.getAttributeValue(“ele4”);
wdContext.currentContextElement().setTest001(type + “”);
Label number1 = new Label(3, x+1, wdContext.currentContextElement().getTest001());
sheet.addCell(number1); // For adding the element to excel sheet
}
//***********************************************************//
// Code For Reading Table Ends //
//***********************************************************//
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();
}
Following will explain the functionality of the code:-
String fileName = “output” + “.xls”;
IWDCachedWebResource cachedExcelResource = null;
try
{
File f = new File(“output.xls”);
WritableWorkbook workbook =
Workbook.createWorkbook(f);
The above code is used to give a name to the file. The extension name “.xls” should not be changed.
WritableFont red = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.SINGLE,
Colour.RED);
WritableCellFormat redFormat = new WritableCellFormat(red);
This code is for defining the font, bold property, underline style and color.
WritableSheet sheet = workbook.createSheet(“First Sheet”, 0);
This is used to give the excel page a name.
Label label = new Label(0, 0, “Ele1”,redFormat);
sheet.addCell(label);
Label is used to write a value in a particular cell of the Excel table. (0,0) means the first element of the table. The cell matrix is represented as(column, row). This is used to write the desired content in a cell.