Skip to Content
avatar image
Former Member

how to download and upload data into EXCEL sheet

how transfer data into excell sheet .

and how get data from excell sheet .

give step by step plz....

its help full to me

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    avatar image
    Former Member
    Jul 02, 2008 at 12:24 PM

    Hi

    Below are the steps how u can download data to excel:

    create two methods in the component controller: ie 1.downlddata()

    2.getCachedWebResource() of return type IWDCachedWebResource (from java native lib) with the following parameters file of type java.io.inputstream

    name of type string

    and type of type WDWebResourceType

    try

    {

    String FileName = "nameofexcelfile.xls";

    IWDCachedWebResource CachedWebResource = null;

    File f = new File(FileName);

    WritableWorkbook workbook =Workbook.createWorkbook(f);

    WritableFont typeofFont =new WritableFont(WritableFont.ARIAL,WritableFont.DEFAULT_POINT_SIZE,

    WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,

    Colour.BLACK);//this defines the font properties for the text u want to display

    WritableCellFormat typeofFormat =

    new WritableCellFormat(typeofFont);

    typeofFormat.setAlignment(Alignment.CENTRE);//youc can set allignment and other formatting option

    WritableSheet excelsheetname =

    workbook.createSheet("Provide the name for the sheet", 0);///creates a sheet for your excel with the name provided by u.

    Label label= null;

    label = new Label(5,0,"Portal RoleDetails", typeofFormat);//provide the data as an argument of Label constructor ie 5- column no, 0- row no, provide the data to be written it could be the header for ur excel, column names and the data for each column comming from an context attribute and last argument is the obj u create to describe the format of data.

    excelsheetname.addCell(label);

    excelsheetname.mergeCells(5, 0, 10, 0);//if u want to merge columns so that the whole header is visible then u can use mergecell providing starting column,row and till column , row

    int columncount=0;//columncount counter

    int rowcount=0;//rowcount counter

    for(int i =0;i<wdContext.nodeVn_Excel().size();i++)//say data u want to display is in the context attribute then you can loop the size and display the values.

    {label =new Label(0,RowCount,wdContext.nodeVn_Excel().getVn_ExcelElementAt(i).getVa_value(),typeofFormat);

    excelsheetname.addCell(label);

    columncount=0;

    RowCount += 1;

    }

    workbook.write();

    FileInputStream ExcelFile = new FileInputStream(f);

    CachedWebResource =getCachedWebResource(ExcelFile,

    FileName,WDWebResourceType.getWebResourceType("xls","application/ms-excel"));

    wdContext.currentContextElement().setExcelURL(CachedWebResource.getURL());

    wdContext.currentContextElement().setExcelFileName(FileName);

    workbook.close();

    }

    catch(Exception e)

    {

    wdComponentAPI.getMessageManager().reportException("Exception occured in dwldExcel: " + e.getMessage() + " : " + e.toString(),false);

    }

    public com.sap.tc.webdynpro.services.sal.url.api.IWDCachedWebResource getCachedWebResource( java.io.InputStream file, java.lang.String name, com.sap.tc.webdynpro.services.sal.url.api.WDWebResourceType type )

    {

    //@@begin getCachedWebResource()

    IWDCachedWebResource objCah = null;

    try

    {

    if (file != null)

    {

    objCah = WDWebResource.getWebResource(file, type);

    objCah.setResourceName(name);

    this.openExcelWindow();

    }

    }

    catch(Exception e)

    {

    wdThis.wdGetAPI().getComponent().getMessageManager().reportException("Error Occurred in GetCachedWebResource Method of Component Controller. Please contact System Administrator",false);

    }

    finally

    {

    return objCah;

    }

    now for this u require a jxl.jar file so import it and then the build errors will be fixed.

    Regards,

    Jaspreet Kaur

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 23, 2008 at 03:46 PM

    Hi;

    You can realize this by using for example some open source alternatives like POI.

    http://poi.apache.org/

    Have a look at this short example code:

    HSSFWorkbook wb = new HSSFWorkbook();

    String title = "Excel1";

    HSSFSheet sheet = wb.createSheet(title);

    // add title row

    int actRow = 0;

    HSSFRow row = sheet.createRow((short)actRow++);

    row.createCell((short)0).setCellValue(title);

    .....

    fill Excel sheet

    .....

    ByteArrayOutputStream stream = new ByteArrayOutputStream();

    wb.write(stream);

    // content

    byte[] content = stream.toByteArray();

    // populate this array, probably get from context

    WDWebResourceType resourceType;

    // get web resource type

    // for example if you have the file extension "ext"

    // you can use the following commented statements

    IWDCachedWebResource webResource = WDWebResource.getWebResource(

    content, WDWebResourceType.XLS);

    webResource.setResourceName("excel.xls");

    wdComponentAPI.getWindowManager().createExternalWindow(

    webResource.getURL(),"Tipp download",true).open();

    }

    Kind regards

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 25, 2008 at 07:16 AM

    hi just go through this link

    Enhanced File Upload - Uploading and Processing Excel Sheets

    Excel download... and upload?

    Thanks and regards,

    Tulasi.P

    Edited by: tulasi palnati on Jul 2, 2008 1:44 PM

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 25, 2008 at 07:51 AM

    <Code Removed!>

    The first blog contains to Extract data to and from Excel file

    /people/subramanian.venkateswaran2/blog/2006/08/16/exporting-table-data-to-ms-excel-sheetenhanced-web-dynpro-binary-cache

    Contains code to convert table data in to XML format. The XML format can be saved as Excel file to view the Data in Excel table.

    /people/sap.user72/blog/2006/05/04/enhancing-tables-in-webdynpro-java-150-custom-built-table-utilities

    Edited by: Vinod V on Jun 25, 2008 1:30 PM

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 03, 2008 at 06:58 AM

    hi...

    Sorry i forgot to send the link...

    /people/sap.user72/blog/2006/05/04/enhancing-tables-in-webdynpro-java-150-custom-built-table-utilities

    this is the link..

    with

    Gobinath.R

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 03, 2008 at 09:33 AM

    Hi,

    You can use JExcel API for uploading data into excel sheet.

    Refer the following code:

    String fileName = "output.xls";

    IWDCachedWebResource cachedExcelResource = null;

    int i = 0;

    try{

    File f = new File("output.xls");

    WritableWorkbook workbook = Workbook.createWorkbook(f);

    WritableSheet sheet = workbook.createSheet("First Sheet", 0);

    for(Iterator coluinfoitr = columnInfos.keySet().iterator(); coluinfoitr.hasNext();)

    {

    Label label = new Label(i, 0, (String)columnInfos.get(coluinfoitr.next()));

    sheet.addCell(label);

    i++;

    }

    int j = 0;

    for(Iterator iter = columnInfos.keySet().iterator(); iter.hasNext();)

    {

    String attributeName = (String)iter.next();

    for(int index = 0; index < dataNode.size(); index++){

    try

    {

    IWDNodeElement nodeElement = dataNode.getElementAt(index);

    String colVal = nodeElement.getAttributeAsText(attributeName);

    Label value = new Label(j, index + 1, colVal);

    sheet.addCell(value);

    }

    catch (Exception e)

    {

    wdComponentAPI.getMessageManager().reportException(" Data Retrive Err :" + e.getLocalizedMessage(),false);

    }

    }

    j++;

    }

    workbook.write();

    FileInputStream excelCSVFile = new FileInputStream(f);

    cachedExcelResource = getCachedWebResource(excelCSVFile, fileName, WDWebResourceType.getWebResourceType("xls", "application/ms-excel"));

    wdContext.currentContextElement().setExcelDownload(cachedExcelResource.getURL());

    workbook.close();

    }

    catch(Exception ex){

    wdComponentAPI.getMessageManager().reportException(" To Excel Err "+ ex.getLocalizedMessage(),false);

    }

    return null;

    Regards,

    Subashini

    Add comment
    10|10000 characters needed characters exceeded