Reading contents of uploaded excel file in web dynpro java

[saurabh_vakil]

Hi All.

I am aware how to provide facility to upload files in web dynpro java. But my requirement is that on uploading a particular file (for eg. an excel file having 10 columns), I need to read the contents of that file and store it in a table in R/3.Can anyone suggest a way how I can read the contents of the uploaded file?

Thanks and Regards,

Saurabh.

[Former Member · Accepted solution]

Hi,

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

Are you using POI jar file for reading excel sheet?

Regards

Narendra

[saurabh_vakil · Accepted solution]

Hi Narendra.

Thanks for your help. My problem is solved.

I was getting the error NoClassDefFound error for jxl.Workbook. I copied the jxl.jar file into my project's lib folder and it is working now.

[Former Member]

HI Saurabh Vakil

Can you please share the coding with me. Because i have a same problem, I need upload the 100 records through excel file. and please give the Jar file name also.

Hi Narendra,

I am fallowing your code but i am facing the " java.lang.NoClassDefFoundError: org/apache/poi/poifs/filesystem/POIFSFileSystem "

could please tell me the which is exact POI file i need to import

Thanks & Regards,

Suresh

[Former Member]

Hello Experts,

I am also facing same poblem. I get following error:

java.lang.ClassNotFoundException: org.apache.poi.poifs.filesystem.POIFSFileSystem -

---

Loader Info -

---

ClassLoader name: [demo.sap.com/testclarif] Living status: alive Direct parent loaders: [system:Frame] [service:servlet_jsp] [service:ejb] [sap.com/tc_wdapi] [library:tc_blexception_{lib] [library:tc}bl_loggingapi] [library:tc_jewebservices_{lib] [service:tc}je_webservicesapi] [library:tc_wdwslib~api] Resources: /usr/sap/C01/J00/j2ee/cluster/apps/demo.sap.com/testclarif/servlet_jsp/webdynpro/resources/demo.sap.com/testclarif/root/WEB-INF/lib/app.jar -

---

I had used

poi-3.5-FINAL-20090928.jar

poi-contrib-3.5-FINAL-20090928.jar

poi-scratchpad-3.5-FINAL-20090928.jar

servlet.jar

please let us know the correct jar files, or any nice blog for reading excel file.

Thanks in advance,

Yogesh

[saurabh_vakil]

I followed this weblog - [https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/4591] [original link is broken] [original link is broken] [original link is broken];

However when I press the Upload button I am getting the exception - java.lang.NoClassDefFoundError: jxl.Workbook

I have added the jxl.jar file in the Library of my project.

How can I correct this?

[saurabh_vakil]

Hi.

I am having the following requirement : I have a FileUpload UI element where user clicks Browse button, selects a file from the local system and presses a Upload button. Upon pressing Upload, the name of the selected file and the contents of the file should be shown.

In the View context, I have two value attributes: FileName of type String and FileResource of type binary.

This is the code that I have in the Upload button action handler :

  public void onActionUpload(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )
  {
    //@@begin onActionUpload(ServerEvent)

    IWDAttributeInfo attributeInfo = wdContext.getNodeInfo().getAttribute(
    	    IPrivateReadExcelView.IContextElement.FILE_RESOURCE);
    IWDModifiableBinaryType binaryType = (IWDModifiableBinaryType)attributeInfo.
    	    getModifiableSimpleType();
    IPrivateReadExcelView.IContextElement element = wdContext.createContextElement();
    String fname = binaryType.getFileName();
    wdComponentAPI.getMessageManager().reportSuccess("File selected - "+fname);  //Statement 1
	
	try {
		wdComponentAPI.getMessageManager().reportSuccess("Successful");    // Statement 2
		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);
					}
				}
			}
		}
	}
	catch(Exception e) { wdComponentAPI.getMessageManager().raisePendingException();
	}	
    //@@end
  }

On pressing Upload button, name of selected file is being shown(Statement 1). I am also getting Statement 2 in the output. However I am not getting Statement 3 onwards.

Where am I going wrong? Can anyone shed some light on this?

[saurabh_vakil]

Thanks for your prompt reply Narendra.

I have a question. When user clicks on Browse button (in FileUpload UI element) and selects a file from the local system, how do I retrieve that filename, since I have to pass the file in the following line:

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));

Regards,

Saurabh.

[Former Member]

Hi Saurabh,

For reading a excel file you can to use POI jar file.POI jar file you can download from internet, its totally free.

below is the straight forward code you can use to read the excel file you have uploaded.

try {

// Pass the uploaded file path.

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFSheet sheet = wb.getSheetAt(0);

HSSFRow row;

HSSFCell cell;

int rows; // No of rows

rows = sheet.getPhysicalNumberOfRows();

int cols = 0; // No of columns

int tmp = 0;

// This trick ensures that we get the data properly even if it doesn't start from first few rows

for(int i = 0; i < 10 || i < rows; i++) {

row = sheet.getRow(i);

if(row != null) {

tmp = sheet.getRow(i).getPhysicalNumberOfCells();

if(tmp > cols) cols = tmp;

}

}

for(int r = 0; r < rows; r++) {

row = sheet.getRow(r);

if(row != null) {

for(int c = 0; c < cols; c++) {

cell = row.getCell((short)c);

if(cell != null) {

// Your code here

}

}

}

}

} catch(Exception ioe) {

ioe.printStackTrace();

}

This can really solve your problem.

Regards

Narendra