Reading data from Excel

[Former Member]

Hi I have to read data from Excell Sheet which has list of Usernames. Can you please help me on this.

Edited by: venki abc on Mar 20, 2009 1:30 PM

[former_member185086 · Accepted solution]

Hi

To know how the Upload the File in webdynpro refer this [thread|https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/00062266-3aa9-2910-d485-f1088c3a4d71]

To work with excel u have to use jxl library dc [this |; and [this |; for help.

further these are coding specific for excel file and uploading data into node

public void onActionUpload(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )
  {
    //@@begin onActionUpload(ServerEvent)
	    wdContext.nodeUsersFromFileDataNode().invalidate();
		IPrivateUserDataView.IUploadFileInformationElement element = wdContext.currentUploadFileInformationElement();
		if (element.getFileResource() != null)
		{
			IWDResource resource = element.getFileResource();
			if (resource.getResourceType().getFileExtension().equals("xls"))
			{
				wdContext.currentUploadFileInformationElement().setFileName("File Name : " + resource.getResourceName());
				try
				{
					InputStream inputStream = resource.read(false);
					Workbook workbook = Workbook.getWorkbook(inputStream);
					Sheet sheet = workbook.getSheet(0);
					int number_user = sheet.getRows() - 1;
					wdContext.currentUploadFileInformationElement().setNumberOfUser("Number of User : " + number_user);

					for (int i = 1; i < sheet.getRows(); i++)
					{
						IPrivateUserDataView.IUsersFromFileDataNodeElement userElement = wdContext.createUsersFromFileDataNodeElement();
// For parsing file data
						parseFileData(i, sheet, userElement);
					}
					workbook.close();
				} catch (Exception ex)
				{
				}
			} else
			{
				wdComponentAPI.getMessageManager().reportWarning("Please select valid file type.");
wdContext.currentUploadFileInformationElement().setFileInformationVisibility(WDVisibility.NONE);
			}
		}
    //@@end
  }

// For parseFileData(i, sheet, userElement);

public void parseFileData( int row, jxl.Sheet sheet, com.incture.admincomp.wdp.IPrivateUserDataView.IUsersFromFileDataNodeElement userElement )  {
    //@@begin parseFileData()
		for (int i = 0; i < sheet.getColumns(); i++)
		{
			if (i == 0)
				userElement.setUserId(sheet.getCell(0, row).getContents());
			if (i == 1)
				userElement.setPassword(sheet.getCell(1, row).getContents());
			if (i == 2)
				userElement.setLastName(sheet.getCell(2, row).getContents());
			if (i == 3)
				userElement.setFirstName(sheet.getCell(3, row).getContents());
			if (i == 4)
				userElement.setPhone(sheet.getCell(4, row).getContents());
			if (i == 5)
				userElement.setFax(sheet.getCell(5, row).getContents());
			if (i == 6)
				userElement.setEmail(sheet.getCell(6, row).getContents());
		}
		wdContext.nodeUsersFromFileDataNode().addElement(userElement);
		wdContext.nodeUsersFromFileDataNode().setLeadSelection(-1);
		
		
    //@@end
  }

Best Regards

Satish Kumar

[pravesh_verma]

Hi Venki,

You just require a simple JAVA code which will read the excel sheet. Please check these links:

http://www.javabeat.net/articles/41-apache-poi-reading-excel-sheet-using-java-1.html

http://conceptoftheday.blogspot.com/2007/09/program-to-read-excel-sheet-using-java.html

I am sure you can solve this using this code. Probably you can create a method in webdynpro and copy paste the code given in the link. Call the method whereever required.

I hope this helps!!

Thanks and Regards

Pravesh