Skip to Content

Web Dynpro read excel cell

Hi,

How can I read a specific excel cell via Web Dynpro ?

It will be great if someone will provide a short sample of code for that ...

10x in advance

Hadar

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Jul 26, 2007 at 06:52 AM

    <b>Hi

    First You have to download the jxl.jar file. You can get this file from the Below site

    JExcelApi" target="_blank">http://www.andykhan.com/jexcelapi/download.html">JExcelApi v2.6.4 (1747kbytes)

    It will be in Compressed Fromat So Unzip it to get the Contents

    After Unzipping The File You will get a Folder (jexcelapi/jxl.jar)

    Now in NWDS open web dynpro explorer, Right Click Your Project, a popup menu will appear and in that click Properties

    You will get window displaying your Project Properties

    On Left Side of the window You Will Find "Java Build Path"

    Click That "Java Build Path" and you will get 4 Tabs Showing ( Source,Projects,Libraries,Order and Export)

    Click Libraries Tab

    You will find options many options buttons

    In that click the Button "Add External Jars"

    You will get Window in order to fecth the jxl.jar file from the location you had stored

    After selecting the jxl.jar i will get displayed and click ok

    Now Open Navigator

    Open Your Project

    You will find Lib folder

    Copy the jxl.jar to that lib folder

    Note : You cannot Read the Content from the excel file directly

    First You Have to copy that file to the Server,

    And from the Server you can get the file absolute path

    With the absolute path you can read the contents of the Excel file

    You have to save the Excel file as .xls Format and Not as xlsx format i will not accept that...

    You have Upload the Excel file from the Server Using the File Upload UI Element

    This Coding will extract 3 columns from the Xls File

    Coding

    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.InputStream;
    
    import jxl.Cell;
    import jxl.Sheet;
    import jxl.Workbook;
    
    import com.sap.fileupload.wdp.IPrivateFileUpload_View;
    import com.sap.tc.webdynpro.services.sal.datatransport.api.IWDResource;
    
    public void onActionUpload_File(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )
      {
        //@@begin onActionUpload_File(ServerEvent)
        IPrivateFileUpload_View.IContextElement element1 = wdContext.currentContextElement();
        IWDResource resource = element1.getFileResource();
        element1.setFileName(resource.getResourceName());
        element1.setFileExtension(resource.getResourceType().getFileExtension());
        //@@end
      }
    
    public void onActionUpload_File_in_Server(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )
      {
        //@@begin onActionUpload_File_in_Server(ServerEvent)
        InputStream text=null;
        int temp=0;
        try
        {
        	File file = new File(wdContext.currentContextElement().getFileResource().getResourceName().toString());
        	FileOutputStream op = new FileOutputStream(file);
        	
        	if(wdContext.currentContextElement().getFileResource()!=null)
        	{
        		text=wdContext.currentContextElement().getFileResource().read(false);
        		while((temp=text.read())!=-1)
        		{
        			op.write(temp);    			    			
        		}
        	}
        	op.flush();
        	op.close();
        	path = file.getAbsolutePath();
        	wdComponentAPI.getMessageManager().reportSuccess(path); 
        }
    
        catch(Exception e)
        {
        	e.printStackTrace();
        }
        //@@end
      }
    
    public void onActionUpload_Data_into_Table(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )
      {
        //@@begin onActionUpload_Data_into_Table(ServerEvent)
        try
        {
    		Workbook wb =Workbook.getWorkbook(new File(path));
    		Sheet sh = wb.getSheet(0);
    		
    		//wdComponentAPI.getMessageManager().reportSuccess("Columns = "+sh.getColumns());
    		//wdComponentAPI.getMessageManager().reportSuccess("Rows = "+sh.getRows());
    		
    		int columns = sh.getColumns();
    		int rows = sh.getRows();
    		int i=0;
        	for(int j=1;j<=rows;j++)
        	{
    		    	ele=wdContext.nodeTable_Data().createTable_DataElement();
    		    
    		    	Cell c1 = sh.getCell(i,j);
    		   	ele.setTab_Name(c1.getContents());
    		    			    	
    		    	Cell c2 = sh.getCell(i+1,j);
    		    	ele.setTab_Degree(c2.getContents());
    		    
    		       	Cell c3 = sh.getCell(i+2,j);
    		    	ele.setTab_Percentage(c3.getContents());
    		    		    	
    		    	wdContext.nodeTable_Data().addElement(ele);
    		}
    		
        }
        catch(Exception ex)
        {
        	wdComponentAPI.getMessageManager().reportSuccess(ex.toString());
        }
        //@@end
      }
    
    
    /*
       * The following code section can be used for any Java code that is 
       * not to be visible to other controllers/views or that contains constructs
       * currently not supported directly by Web Dynpro (such as inner classes or
       * member variables etc.). </p>
       *
       * Note: The content of this section is in no way managed/controlled
       * by the Web Dynpro Designtime or the Web Dynpro Runtime. 
       */
      //@@begin others
      String path;
      IPrivateFileUpload_View.ITable_DataElement ele;
    //@@end

    Regards

    Chandran S</b>

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Chandran,

      Workbook wb =Workbook.getWorkbook(new File(path));

      It is getting executed correctly before this line. But when I include this line it is showing internal server error. Please help me out. I am new to webdynpro Java.

      Thank You.

      Regards

      Janani

  • avatar image
    Former Member
    Jul 25, 2007 at 01:14 PM

    Hi,

    use this codes in doinit() method to read excel file

    u have to add jxl.jar file to lib for that

    goto navigator then for the particular project u have a lib folder there u have to add(copy) the jxl.jar file.

    Workbook wb=Workbook.getWorkbook(new File("path of the xl file"));
    		    Sheet sh=wb.getSheet(0))<give the sheet no which u want to read>;
    		    Cell c=sh.getCell(0,0)<give the row and column no which u want to read>;
    		    String s=c.getContents();
    		   wdComponentAPI.getMessageManager().reportSuccess(s);

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 25, 2007 at 02:12 PM

    Hi Hadar,

    Try this code.

    import java.io.IOException;

    import java.io.*;

    import jxl.Sheet;

    import jxl.Workbook;

    import jxl.read.biff.BiffException;

    public class ExcelRead {

    public static void getDataFromExcel(String path) {

    try {

    Workbook workbook = Workbook.getWorkbook(new File(path));

    for (int shNo = 0; shNo < workbook.getNumberOfSheets(); shNo++) {

    Sheet sheet = workbook.getSheet(shNo);

    for (int i = 1; i < sheet.getRows(); i++) {

    for (int j = 0; j < sheet.getColumns(); j++) {

    System.out.print(" Cell("i","j") : "+ sheet.getCell(j, i).getContents());

    }

    System.out.println();

    }

    }

    } catch (IOException e) {

    e.printStackTrace();

    } catch (BiffException e) {

    e.printStackTrace();

    }

    }

    public static void main(String[] args) {

    System.out.println("----


    ");

    String filename = "C:/Documents and Settings/Administrator/Desktop/employee.xls";

    getDataFromExcel(filename);

    System.out.println("----


    ");

    }

    }

    Must add the <b>jxl.jar</b> under Java Build Path->Libraries

    Steps are given in this document.

    /people/subramanian.venkateswaran2/blog/2006/08/16/exporting-table-data-to-ms-excel-sheetenhanced-web-dynpro-binary-cache

    Regards,

    Mithu

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 25, 2007 at 12:58 PM

    See these threads :

    Regarding Copying of Data From Excel file to Web Dynpro Java Table

    Use Excel file as resource in Web Dynpro

    See this blog as well:

    /people/subramanian.venkateswaran2/blog/2006/08/16/exporting-table-data-to-ms-excel-sheetenhanced-web-dynpro-binary-cache

    Regards,

    Ashwani Kr Sharma

    Add comment
    10|10000 characters needed characters exceeded