Seperation of excel sheets in Webdynpro Java using HTML API

[Former Member]

Hi Guys,

I have a requirement where in i want to seperate the excel sheets based on the different projects. Say first 3 similar projects in 1 sheet and another set of projects in another sheets. Currently I am using HTML  Tags API and not JXL.Jar file . Can some one please let me know the code for it to seperate the sheets based on the project differentiation.

Any help would be highly appreciated.

[Qualiture]

I think you really should (re-)read the file upload/download documents thoroughly, and then you will understand why your code won't work;

1. If you upload a file into an IWDResource object, it then resides 'on the server', ie. you don't have a reference to your original file on the client.
2. Your assumption to write to a local file ('C:\whatever') is something that simply can't happen from a serverside application; if it would, then that would be a huge security issues. Virus developers all over the world would be more than happy to write executables to your local system 🙂

So,

1. Although I really don't see the need for storing the original client file location (it can differ from client to client), you can simply retrieve that from your FileUpload control and store that separately.
2. If you want to write a file to a client, you should use the FileDownload control

Hope this helps your understanding 🙂

Best regards,

Robin

[Former Member]

Hi Robin,

Thanks for the solution approach. Is there any thread in sdn which gives the solution approach to handle this. I have been tryng to do this from long but couldnt do it.

[Qualiture]

Sure! Have a look at the de-facto document regarding file upload/download written by :

http://scn.sap.com/docs/DOC-2562

[Sharathmg]

Please share the name of the api used to build excel?

Regards,

Sharath

[Former Member]

HTML tags and also i have jxl.jar. As of now am using HTML tags but i will move to jxl.jar

[Sharathmg]

jxl.jar make your life excel very simple.

But, remember jxl.jar does not support the newer versions of Excel.

In case u need 2 use newer excels, use Apache POI jar file.

Regards,

Sharath

[Former Member]

Hi i have already used jar files but i need ro seperate the files . coikd you please help me in that

[Sharathmg]

You mean separate the sheets?

	Workbook excelWB = Workbook.getWorkbook(wdContext.currentContextElement().getExcelResource().read(true));
	Sheet excelSheet = excelWB.getSheet(0);// Get the first sheet of the excel Sheet excelSheet2 = excelWB.getSheet(1); and so on for otother sheets. Regards, Sharath

[Former Member]

Do you have any step by step tutorial to implement the jxl.jar file in NWDS 7.3. i am trying but i am getting some errors when following the tutorial for 7.0. Can i get the tutorial for 7.3 and also how to read the contents of the file.

Help is highly appreciated.

[Sharathmg]

It should be the same as conceptually, you will always wrap the external lib into Server DC and Deploy the DC.

Did you try the sane steps described for 7.0? Did you face any issue/roadblock?

Regards,

Sharath

[Former Member]

I am facing the issue in reading the contents of the excel sheet and uplaoding the same .my prime goal is to read the contents and send the same to the RFC .

[Sharathmg]

"issue in reading the contents of the excel sheet " - Where exactly are you facing the issue?

Are they compiler/build errors?

Are you able to get the object related to the excel file?

Regards,

Sharath

[Former Member]

This is What i have written and am not ggetting the local path of the file to be sent to the backend and its coming as the application server path.

1. IPrivateUploadCompView.IZfm_Upload_InputElement element_upload = null;

        IPrivateUploadCompView.IEp_MessageElement element_message = null;

        element_upload = wdContext.nodeZfm_Upload_Input().createAndAddZfm_Upload_InputElement();

//      element_message = wdContext.nodeEp_Message().createAndAddEp_MessageElement();

          InputStream text = null;

            int temp=0;

            try

            {

//            wdComponentAPI.getMessageManager().reportSuccess("1");

                 File file = new File(wdContext.currentContextElement().getResource().getResourceName().toString());

                 FileOutputStream op = new FileOutputStream(file);

                 if(wdContext.currentContextElement().getResource()!=null)

                 {

                     wdComponentAPI.getMessageManager().reportSuccess("2");

                      text=wdContext.currentContextElement().getResource().read(false);

                      while((temp=((InputStream) text).read())!=-1)

                      {

                           op.write(temp);                                     

                      }

                 }

                 wdComponentAPI.getMessageManager().reportSuccess("4");

                 op.flush();

                 op.close();

                  path = file.getAbsolutePath();

//               wdComponentAPI.getMessageManager().reportSuccess("5");

            }

            catch(Exception e)

            {

                 e.printStackTrace();

            }

//      File file = new File(wdContext.currentContextElement().getResource().getResourceName().toString());

//      path = file.getAbsolutePath();

            element_upload.setIp_Filepath(path);

                   wdContext.nodeZfm_Upload_Input().bind(element_upload);

                 wdComponentAPI.getMessageManager().reportSuccess(path);

        wdThis.wdGetUploadCompController().executefm_Upload();

        wdComponentAPI.getMessageManager().reportSuccess("5");

when executing this am getting the path as usr/sap/...........

and not getting as C:/Desktop/and file name. That is the reason am not able to get the data .

[ErvinSzolke]

Saphire added some more on this in another thread which I am merging now here:

I have a Requirement where in i have to generate the excel sheet based on some projects. I have different sets of projects and i want to display different projects in different excel sheets like sheet 1 proj 1 sheet2 proj 2 ..and so on..Currently i have used HTML tags and its working fine the sheets are opening but its all opening in one sheet. Can you please suggest me the code to write in HTML tags so that either i can seperate it based on the projects.
Option2 : If i can change the color of the excel sheets after proj1 so that there is a differentiation on it.;

[Former Member]

Hi Ervin,

Looking for your expert advice and the code if you have on the same. Am still not able to resolve this issue. I tried using HTML tags and was not success . After that i tried using Jxl.jar but got stuck up and now looking for your help. Could you please provide me the steps to seperate the excel based on the differentiator. Projects ID are dynamic and  I dont know how many might be there. So based on the different projects different sheets i want to have.

[Sharathmg]

		Workbook excelWB = Workbook.getWorkbook(wdContext.currentContextElement().getExcelResource().read(true));
		Sheet excelSheet1 = excelWB.getSheet(0);// Get the first sheet 1 of the excel Sheet excelSheet2 = excelWB.getSheet(1);// Get the first sheet 2 of the excel Sheet excelSheet3 = excelWB.getSheet(2);// Get the first sheet 3 of the excel

Now, for each sheet:

for (int j = 0; j < excelSheet.getColumns(); j++) {

     int excelRows = excelSheet.getRows();

     for (int i = 1; i < excelRows; i++) {

          Cell cell = excelSheet.getCell(j, i);// We will read the part numbers only from the first column, second row - This is my custom logic where I need to read only first column and start from second row

          CellType type = cell.getType();

          if (type != CellType.EMPTY) { // You can check if cell type is label/number/... The code can be writen to behave differently for label, numbers etc.

               // Here get the cell value and load it into a context

          partNoEle.setMatnr(cell.getContents()); // partNoEle a sample element referring to the context node.

          wdContext.nodeExcelPartNos().addElement(partNoEle);

     }

}

}

Print out messages, to check if you are reading texts and then, do the addition. But, remeber the template has to be fixed. Any deviation has to agreed as a mistake on the part of the user. Else, if you start considering all test cases then you will spend all effort on this.

Share a template also as part of it, if you want people to follow it.

Regards,

Sharath

[Former Member]

Hi Ervin,

Require your advice on the below link

http://scn.sap.com/thread/3434418

Thanks