how to read data from an excel sheet

[Former Member]

Hi all,

how to read data from an excel sheet which is there in my local system

Regards

Padma N

[Former Member · Accepted solution]

Hi,

You need to upload the data using file upload then you can either use POI or else jxl

Regards

Ayyapparaj

[Former Member · Accepted solution]

Hi Ayyapparaj,

i used file upload action.on action UPload the code i used is given below

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();

}

but it is giving error at Workbook w=Workbook.getWorkbook(f);

saying that there is no constructor for workbook with File as an argument

can u plz help me out

Regards

Padma N

[former_member751941 · Accepted solution]

Hi Padma,

There may be some problem in your jxl.jar file

Open jxl.jar file in WinRAR Archive

Then open the Workbook.class file in java de compiler and check whether getWorkbook(java.io.File file) method is there or not.

public static Workbook getWorkbook(java.io.File file)

throws IOException, BiffException

{

return getWorkbook(file, new WorkbookSettings());

}

Download the jxl.jar file once again and add it in the class path.

Make sure you have added the following jar Files.

import java.io.File;

import java.io.FileOutputStream;

import java.io.InputStream;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

Put the Code with in try-catch block and chatch block.

try

{

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();

}

}

catch(Exception ex)

{

wdComponentAPI.getMessageManager().reportSuccess(ex.toString());

}

Check this thread also.

Regards,

Mithu

[Former Member · Accepted solution]

I agree with Mithu,

I had the same problem. But it is solved now.

[Former Member · Accepted solution]

Hi Experts,

My problem didnt get solved.I am explain the procedure which i did.

I created an UI element FileUplad and binded the resource to UIElemts Resource.

Now in onactionUpload() i am getting build error at File f=new File("..........");

"Everywhere itis given that parameter to the new File() is "Excel File" as

new File("<ExcelFile>");

Actually what should be given as parameter to the new File("?")there...and where the full path of the Excel file i.e "d:\Excelfolder\abc.xls" is stored in the context.

IS there any limit to the lengh of the ExcelFile that is used for uploading i.e 2KB or 200000KB or MB

Regards

Padma N

[former_member751941 · Accepted solution]

Hi Padma,

Try this

wdContext.currentContextElement().setPath("d:\\Excelfolder\\abc.xls");
or
wdContext.currentContextElement().setPath("d:/Excelfolder/abc.xls");

File f = new File(wdContext.currentContextElement().getPath());

Regards,

Mithu

[Former Member · Accepted solution]

Hi Mithu,

In the context i have only one ValueAttribute that maps to the FileUPladUI Element i.e FIleResource of type Resource.

Now where should be the path selected by the user is stored in the context.Is it that it is stored in FileResource of type Resource..

then what should be the code in this case??

try

{

IPrivateFileUploadView.IContextElement element =

wdContext.currentContextElement();

IWDResource resource=element.getFileResource();

Workbook w=Workbook.getWorkbook(resource);

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();

}

}

catch(Exception ex)

{

* wdComponentAPI.getMessageManager().reportSuccess(ex.toString());*

}

Is the workbook have constructor that have resource as an argument

[Former Member · Accepted solution]

Hi Mithu,

i got strucked again.The flow is going to the exception at WorkBook w=Workbook.getWorkbook(f )

as "The Specified file path is not available"

I think at server the file which i selected id not there..i didnt written any code for uploading file from local systemm to the server.

Can u plz help me out..the remaing code i wrote is

try

{

String strPath=wdContext.currentcontextelement.getPath();

File f= new File(strPath);

WorkBook w=Workbook.getWorkbook(f )

Sheet sheet = workbook.getSheet(0);

Cell a1 = sheet.getCell(0,0);

Cell a2 = sheet.getCell(0,1);

wdComponentAPI.getMessageManager().reportSuccess("Value of cell a1 = " +

a1.getContents());

wdComponentAPI.getMessageManager().reportSuccess("Value of cell a2 = " + a2.getContents());

}catch(Exception e)

{

wdComponentAPI.getMessageManager().reportSuccess(This is in exception"+e.getMessage());

}

can u plz let me know what should i do to proceed further

Regards

Padma N

[Former Member]

Hi,

Refer

https://forums.sdn.sap.com/watches!add.jspa?forumID=52&threadID=635293

Regards,

P.Manivannan

[Former Member]

Hi

see the folloing link

[http://poi.apache.org/|http://poi.apache.org/]

Regards

Hazrath

Edited by: Hazrath on Mar 26, 2008 10:02 AM