cancel
Showing results for 
Search instead for 
Did you mean: 

Read excel in webdynpro without using odbc (points awarded!)

Former Member
0 Kudos

Hello..I am creating a functionality in my project in which i have to read data from an excel sheet and show it in a table format in my application...It is sort of Import from excel just like you have export to excel,this is the reverse...

What i need to know is..How to implement this functionality without using odbc driver..Also i want code in webdynpro java...

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

<b>Hi

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

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

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>

Answers (4)

Answers (4)

Former Member
0 Kudos

Thankyou experts..the previous code worked....

I wud like to clear some points for ppl referring to this forum:

1) make the cardinality of the node as bold1:1

2) It is not necessary to store excel file on server before hand,

using above code, uploaded file will be stored directly on server..

Former Member
0 Kudos

Hi,

This is SampleCode to do it..

File f=new File(<ExcelFile>);

Workbook w=Workbook.getWorkbook(f);

Sheet sh=w.getSheet(0);

int cols=sh.getColumns();

int rows=sh.getRows();

String s1=null;

String s2=null;

for(int i=0;i<rows;i++)

{

s1=sh.getCell(0,i).getContents();

s2=sh.getCell(1,i).getContents();

// Code to proceed further(as per your requirement)

}

Finally add "jxl.jar" file to 'JavaBuildpath' of your project

Regards

LakshmiNarayana

Former Member
0 Kudos

I am getting this exception

C:\Trainees\Book1.xls (The system cannot find the path specified)

tried putting doublt quotes in filename too..still same probs!!

Former Member
0 Kudos

Hi,

If your exccel file is on a client system(other than your server), you need to upload this to the server and then do the reading.

other wise you wont be able to get the handle.

Regards

Ayyapparaj

chintan_virani
Active Contributor
0 Kudos

Esha,

Take a look at this [example|http://www.koders.com/java/fid5636164FDE72156B90C9973AC12EBBF49048BA4E.aspx] on how to use the HSSF POI class.

Chintan

Former Member
0 Kudos

Hi,

use apache POI for your purpose.

Regards

Ayyapparaj

Former Member
0 Kudos

hi..Could you give some method where apache POI too is bypassed?