Skip to Content
avatar image
Former Member

uploading excel to table

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Mar 28, 2011 at 11:56 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • avatar image
    Former Member
    Mar 28, 2011 at 04:42 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 28, 2011 at 09:59 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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