on 01-05-2009 6:49 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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);
}
}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Raghu,
Yes you are absolutely right.I could not get your point earlier.
Thanks
Narendra
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Refer this link
/docs/DOC-8061#58 [original link is broken]
Regards,
Sunaina Reddy T
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi laxmi
Please check this PDF link.It will help you
Regards
Ruturaj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.