uploading excel to table

[Former Member]

hi..,

i created on webdynpro project for upload excel .i created external library project, in this i created 2 puplic parts.i addes this project into my project component properties. for excel upload i used this code

public void onActionUpload(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )

{

//@@begin onActionUpload(ServerEvent)

IPrivateExcelUploadView.IContextElement element1 = wdContext.currentContextElement();

IWDResource resource = element1.getResource();

element1.setFileName(resource.getResourceName());

element1.setFileExtension(resource.getResourceType().getFileExtension());

wdComponentAPI.getMessageManager().reportSuccess("Resource Name: "+element1.getResource());

wdComponentAPI.getMessageManager().reportSuccess("File Extension:" +element1.getFileExtension());

text=null;

int temp=0;

try

{

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

FileOutputStream op = new FileOutputStream(file);

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

{

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

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

{

op.write(temp);

}

}

op.flush();

op.close();

path = file.getAbsolutePath();

wdComponentAPI.getMessageManager().reportSuccess(path);

}

catch(Exception e)

{

e.printStackTrace();

}

//@@end

}

For getting data into excel i used this code:

public void onActionUploadIntoTab(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )

{

//@@begin onActionUploadIntoTab(ServerEvent)

try

{

wdComponentAPI.getMessageManager().reportSuccess("Path:" +path);

wdComponentAPI.getMessageManager().reportSuccess("Uploading data");

Workbook wb=Workbook.getWorkbook(new File(path));

Sheet sh = wb.getSheet(0);

wdComponentAPI.getMessageManager().reportSuccess("Columns = "+sh.getColumns());

wdComponentAPI.getMessageManager().reportSuccess("Rows = "+sh.getRows());

int columns = sh.getColumns();

int rows = sh.getRows();

int i=0;

for(int j=1;j<=rows;j++)

{

ele=wdContext.nodeTable1().createTable1Element();

Cell c1 = sh.getCell(i,j);

ele.setName(c1.getContents());

wdContext.nodeTable1().addElement(ele);

wdComponentAPI.getMessageManager().reportSuccess("Element Inserted");

}

wb.close();

}

catch(Exception ex)

{

wdComponentAPI.getMessageManager().reportSuccess(ex.toString());

wdComponentAPI.getMessageManager().reportSuccess(ex.getMessage());

wdComponentAPI.getMessageManager().reportException(""+ex.getCause());

}

//@@end

}

bt i got ouput as

Path:D:\usr\sap\CE1\J00\j2ee\cluster\server0\NA_ExportCustomer_Information_Form_example.xls

Uploading data

jxl.read.biff.BiffException: Unable to recognize OLE stream

Unable to recognize OLE stream

null

[former_member196418]

Hello Everyone,

I'm also running into this exact problem. The first part completes and I can see a file written out in the J2EE directory /usr/sap/<SID>/JC02/j2ee/cluster/server3/TestUpload.xls

If I download this file from the portal Excel will not open it and excel throws the error "The file you are trying to open TestUpload.xls is in a different format than specified by the file extension"

So it seems like the problem may be occurring FileOutputStream section.

Anybody have any ideas?

Thanks

[Former Member]

Hi Bert,

The code should be look like ,

ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); 
		    wb.write(outputStream); 
		 
		    byte[] bytes = outputStream.toByteArray(); 
		    ByteArrayInputStream inputStream = new ByteArrayInputStream(bytes); 
		 
		    wdContext.currentContextElement().setDownloadExcel(WDResourceFactory.createResource(inputStream, "Title", WDWebResourceType.XLS, false )); 
		    outputStream.close(); 
		   inputStream.close(); 

Thanks,

Arun

[Former Member]

Hi Lakshmi,

Kindly verify the data in excel that you are uploading.

Probably some data type in the file being uploaded is not handled by the logic for reading data from excel.

Another probability is you are using an API that does not support newer formats of MS Excel ( .xlsx extensions). Try opening the file and saving in .xls extension and try.

Are you exporting the file from a WD application?

- Sudeep

[Former Member]

Hi Lakshmi,

Pls refer the links

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/d0d2ac59-e58f-2c10-9fa8-b8e93ff8d975

http://wiki.sdn.sap.com/wiki/display/WDJava/UploadingexcelfileusingWebDynproforJava

Its working for me.

Also pls paste the entire trace of your err.

Thanks,

Arun

[Former Member]

hi arun ,

thanks for ur reply. if i use that code for ms excel 2007 its not workinng. it is working for excel 2003. so what will i do for that?

[Former Member]

Hi Lakshmi,

Whats the error you are getting...?

Thanks,

Arun

[Former Member]

hi arun,

it is working for uploading excel but while getting data from that excel i got problem.i i am getting this error while runtime.

jxl.read.biff.BiffException: Unable to recognize OLE stream

Unable to recognize OLE stream

null

[Former Member]

hi Lakshmi,

jxl.read.biff.BiffException: 

You are getting this error because, Webdynpro cannot find the jxl library files. First create library project & refer that library project in your Webdynpro DC. This will surelky work.

If you simply add the JAR file in the project it wont work. If you see the log file you will come to know.

Thanks,

Arun