how to download and upload data into EXCEL sheet

[Former Member]

how transfer data into excell sheet .

and how get data from excell sheet .

give step by step plz....

its help full to me

[Former Member · Accepted solution]

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

[Former Member]

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

[Former Member]

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

[Former Member]

<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

[Former Member]

hi just go through this link

Thanks and regards,

Tulasi.P

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

[jrg_unterste-heufken]

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