Skip to Content
Author's profile photo Former Member

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.


Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.