Reading Excel file values in Webdynpro

[Former Member]

Hi All,

I have an excel document and i have uploaded it through file Upload UI and got the file resiurce as an InputStream

 try {
	WDResource resource = wdContext.currentVn_ExceluploadElement().getVa_FileResource();
	
	InputStream in  = resource.read(false);	
		
	}
catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} 

i got it as inputstream and i have also added the jxl jar file.

When i try get the values in the Excel using below code, i was not able to that.

its not throwing any error and its not at all excuting anything.

 Workbook workbookread;
         try {
               Workbookread = Workbook.getWorkbook(in); 
                   int rows = 0;
                   Sheet s;
                   int cols = 0;
                   s = workbookread.getSheet(0); 

Pls. help me out with procedure or code.

Thanks,

Suresh

[Former Member]

I have not tried the same using Web Dynpro Project but with DC.If you try using DC,please follow the steps below :

1- Create a DC of type External Library and add the jxl.jar to the Library folder.

Follow the steps in the blog below:

/people/bala.krishnan2/blog/2006/09/25/bid-adieu-to-bots--using-captchas

2- Add the Public parts as Used DC as mentioned in the above blog and it should work.

Regarding reading the Excel using jxl api ,refer the blog below,it is quite handy.

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

It has worked for me recently

[former_member185086]

Hi

Have u Publish jxl library as archive .

Best Regards

Satish Kumar

[Former Member]

Hi Sathish,

Sorry,

I didn't get you what you are trying to say?

could you pls say wat i should do?

Thanks

[former_member185086]

Hi

How u added the jxl library in your project ?

Create a new external library project ,put jxl lib inside the library folder and then right click on that select Development component here u have to select Publish as archive for compilation and assembly.

then use this component inside your webdynpro dc.

finally

2. Use the following code to populate the data in Context Node

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());
				wdContext.currentUploadFileInformationElement().setFileSize("File Size : " + getFileSize(resource));
				try
				{
					InputStream inputStream = resource.read(false);
					Workbook workbook = Workbook.getWorkbook(inputStream);
					Sheet sheet = workbook.getSheet(0);
//Change according to your requirement
					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();
						parseFileData(i, sheet, userElement);
					}
					workbook.close();
				} catch (Exception ex)
				{
				}
			} else
			{
				wdComponentAPI.getMessageManager().reportWarning("Please select valid file type.");
				
				
			}
		}

//For parsing the data

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

It will read the excel and populate data into your node.

Best Regards

Satish Kumar

[Former Member]

Hi Satish,

I have added jxl as an external jar file into the project.

and one morething is that i'm not working on DCs. its a local webdynpro project.

its getting the exception as null.

[former_member185086]

Hi

Local webdynpro project means it just a learning exercise not for development ?

what is your NWDS version

Best Regards

Satish Kumar

[Former Member]

Hi Sathish,

its nwds7.0.13..... im jus doing it locally and sending across the project.

is there any way to solve this issue..

[former_member185086]

Hi

Here adding the jxl u can take help from [this|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/4512] [original link is broken] [original link is broken] [original link is broken]; thread (Follow step -by -step then ) which is suitable for nwds7.0 version.

then you can proceed.

further take help from [this|https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/202a850a-58e0-2910-eeb3-bfc3e081257f]

if using Binary element else use the above code.

Best Regards

Satish Kumar