How to read an Excel file from Java webdynpro application

[Former Member]

Hi,

How to read an Excel file having multiple rows and columns from Java Webdynpro application.

Can any one help me out in this...?

Regards,

Lakshmi

[Former Member · Accepted solution]

Hi Laxmi

for getting the file path you can use the below steps.

1. create a context like filePath of type com.sap.ide.webdynpro.uielementdefinitions.Resource

2. Assign the context to the file upload childs resource properties.

3.Now you can use the below code to fetch the url.

wdContext.currentContextElement().getFilePath().getUrl(0));

this will help you to retrieve file path.

please revert if you require more info.

Regards

Narendra

[Former Member · Accepted solution]

Hi Narendra,

I have followed all the steps

Actually i created one file upload UI element and button on action of a button i have written the following code

workbook = Workbook.getWorkbook(new File("wdContext.currentContextElement().getFilepath().getUrl(0)"));

Sheet sheet = workbook.getSheet(0);

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

String b1 = a1.getContents();

wdComponentAPI.getMessageManager().reportSuccess("content"+b1);

But then also im not getting any output.

Is this code wrong??

Thanks & Regards,

Laxmi

[Former Member · Accepted solution]

Hi

did you go through the thread which i had posted in my earlier post. I dont think you will get any errors in that. Try that.

Regards

Raghu

[Former Member]

Hi Laxmi,

First try to print if you are getting the the full file path correctly?

Then tell me which APIs you are using jxl API or poi API

I have used POI API.

Try to use the below mentioned Code

InputStream in = new FileInputStream(fname);

HSSFWorkbook wb = new HSSFWorkbook(in);

wdComponentAPI.getMessageManager().reportSuccess("Successful"); //Statement 3

int sheetsNo = wb.getNumberOfSheets();

for (int i=0;i<sheetsNo;i++) {

HSSFSheet sheet = wb.getSheetAt(i);

Iterator rowsNo = sheet.rowIterator();

while(rowsNo.hasNext()) {

HSSFRow rows = (HSSFRow)rowsNo.next();

Iterator colsNo = rows.cellIterator();

while(colsNo.hasNext()) {

HSSFCell cell = (HSSFCell)colsNo.next();

wdComponentAPI.getMessageManager().reportSuccess("File uploaded" +

"successfully");

if(cell.getCellType()==1) {

wdComponentAPI.getMessageManager().reportSuccess("00000"+

cell.getStringCellValue());

}

else if(cell.getCellType()==0) {

String str=""+cell.getNumericCellValue();

wdComponentAPI.getMessageManager().reportSuccess("11111"+str);

}

}

[Former Member]

Hi Raghu,

Yes you are absolutely right.I could not get your point earlier.

Thanks

Narendra

[Former Member]

Hi,

No you can directly read a excel file from your local system only by passing the full path as i mentioned in my previous reply.

It worked for my case.

Regards

Narendra Singh

[Former Member]

Hi Narendra,

What i mean of cannot directly access excel file is? We cannot access the excel file by specifying C:\desktop\..... . We need to use IWDResource that was which you have specified in your previous post.

Regards

Raghu

[Former Member]

Hi,

First u need to create instance for WritableWorkbook then get the Sheet instance and Cell instance to get the Cell value as below

WritableWorkbook workbook = Workbook.getWorkbook(file.xls);

WritableSheet sheet = workbook.getSheet(workbook);

Cell cell = sheet.getCell(<rowIndex>,<columnIndex>);

You need to add the jxl.jar file in the Java Build path of your Web Dynpro application

Regards,

Srikanth Reddy.T

[Former Member]

Hi,

Refer this link

/docs/DOC-8061#58 [original link is broken]

Regards,

Sunaina Reddy T

[Former Member]

Hi laxmi

Please check this PDF link.It will help you

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/user-interface-tec...

Regards

Ruturaj

[Former Member]

Hi,

That you can achieve by using either jxl API or poi API. Here is the code using JXL API for this you need to download jxl.jar file and add to your external jars and also under Lib Directory.

import java.io.File;

import java.util.Date;

import jxl.*;

...

Workbook workbook = Workbook.getWorkbook(new File("test.xls"));

On getting access to the worksheet, once can use the following code piece to access individual sheets. These are zero indexed - the first sheet being 0, the second sheet being 1, and so on. (You can also use the API to retrieve a sheet by name).

Sheet sheet = workbook.getSheet(0);

After getting the sheet, you can retrieve the cell's contents as a string by using the convenience method getContents(). In the example code below, A1 is a text cell, B2 is numerical value and C2 is a date. The contents of these cells may be accessed as follows

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

Cell b2 = sheet.getCell(1,1);

Cell c2 = sheet.getCell(2,1);

String a1 = a1.getContents();

String b2 = b2.getContents();

String c2 = c2.getContents();

// perform operations on strings

.........

However in case we need to access the cell's contents as the exact data type ie. as a numerical value or as a date, then the retrieved Cell must be cast to the correct type and the appropriate methods called. The code piece given below illustrates how JExcelApi may be used to retrieve a genuine java double and java.util.Date object from an Excel spreadsheet. For completeness the label is also cast to it's correct type. The code snippet also illustrates how to verify that cell is of the expected type - this can be useful when performing validations on the spreadsheet for presence of correct datatypes in the spreadsheet.

String a1 = null;

Double b2 = 0;

Date c2 = null;

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

Cell b2 = sheet.getCell(1,1);

Cell c2 = sheet.getCell(2,1);

if (a1.getType() == CellType.LABEL)

{

LabelCell lc = (LabelCell) a1;

stringa1 = lc.getString();

}

if (b2.getType() == CellType.NUMBER)

{

NumberCell nc = (NumberCell) b2;

numberb2 = nc.getValue();

}

if (c2.getType() == CellType.DATE)

{

DateCell dc = (DateCell) c2;

datec2 = dc.getDate();

}

// operate on dates and doubles

...

It is recommended to, use the close() method (as in the code piece below) when you are done with processing all the cells.This frees up any allocated memory used when reading spreadsheets and is particularly important when reading large spreadsheets.

// Finished - close the workbook and free up memory

workbook.close();

Regards

Raghu

[Former Member]

Hi Raghu,

Thanks for the sample code,

I have tried with the given code but i am facing java.lang.NoClassDefFoundError: jxl.Workbook exception.

How to solve this??

Regards,

Laxmi.

[Former Member]

Hi Lakshmi,

Please add the jxl.jar file in the Java Build Path of your application.

Also, copy that file into the lib folder under your project in the Navigator View in NWDS.

Regards

Kishan

[Former Member]

Hi Raghu,

I have follwed the steps mentioned below but when i deploy the application im not seeing any thing

I have written the following code in init()

Workbook workbook = Workbook.getWorkbook(new File("Book1.xls"));

Sheet sheet = workbook.getSheet(0);

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

String b1 = a1.getContents();

wdComponentAPI.getMessageManager().reportSuccess("content"+b1);

But im not able to view the output.

Here Book1.xls is the excel file from which i want to read the content, and this is placed on my desktop.

Can you please help me out in this?

Thanks & Regards,

Laxmi.

[Former Member]

Hi,

Debug your code as to know at which line of code you are getting error. Probably that may be in

Workbook workbook = Workbook.getWorkbook(new File("test.xls"));

So have a work around to give the entire path of the excel file in getworkbook method.

Regards

Raghu

[Former Member]

Hi Raghu,

Can you please explain me how to get the entire path of the file.

As i have placed the Excel file on desktop i have given "Book1.xls".... Is this wrong??

workbook = Workbook.getWorkbook(new File("Book1.xls"));

I tried to print some success message after creating the workbook for this. But it is not displaying, I hope some error is placed in this line.

Can you please suggest me any alternative solution with clear details.

Thanks & Regards,

Laxmi.

[Former Member]

Hi,

I guess you can not read the excel file directly from your local desktop. For this you need to upload the excel file to server and from there you can get the absolute path of the file and later on you can create an instance of workbook in getworkbook() method. Go through the following link a clean Step by Step procedure is given along with Code

[;

Regards

Raghu

[Former Member]

Hi

Try this code create Resource value node and value atribute of ImgURL and ImgResource inside that and

create value attribute of type binary and name picture

if (wdContext.currentResourceElement().getImgResource() != null) {
	
//	   copy URL string from IWDResource object to
//	   context attribute 'UploadImgURL'. Image gets
//	   visible on the UI based on databinding definition.
	wdContext.currentResourceElement().setImgURL(
	wdContext.currentResourceElement().getImgResource().getUrl(WDFileDownloadBehaviour.OPEN_INPLACE.ordinal()));
	}
String str=wdContext.currentResourceElement().getImgURL(); 
         try
   {
                                // Open the file that is the first
                                // command line parameter
                                FileInputStream fstream = new
     FileInputStream(str");

                                // Convert our input stream to a
                                // DataInputStream
    DataInputStream in =
                                        new DataInputStream(fstream);

                                // Continue to read lines while
                                // there are still some left to read
                                while (in.available() !=0)
    {
                                        // Print file line to screen
     wdCompoentApi.getMessageManager().reportSuccess(in.readLine());
    }

    in.close();
   }
                        catch (Exception e)
   {
    wdCompoentApi.getMessageManager().reportException("File input error");
   }

Regards,

H.V.Swathi

[Former Member]

Hi Lakshmi,

Follow these steps,

WritableWorkbook workbook = Workbook.getWorkbook(<fileName>);
WritableSheet sheet = workbook.getSheet(workbook);
Cell cell = sheet.getCell(<rowNumber>,<columnNumber>);

Doing the above, you can get the value of a particular cell in your excel sheet. Now bind that value to an UI Element in your Web Dynpro application.

P.S: You need to add the jxl.jar file in the Java Build path of your Web Dynpro application to use this functionality.

Regards

Kishan

Edited by: kishan chandranna on Jan 5, 2009 8:05 AM

[Former Member]

Hi,

Use this third party API

http://www.onjava.com/pub/a/onjava/2003/04/16/poi_excel.html

[former_member192434]

Hi

Use this blog

/people/subramanian.venkateswaran2/blog/2006/10/02/enhanced-file-upload--uploading-and-processing-excel-sheets

check this link as well

thanks