Question Re: How to export the data as integer into excel sheet? in Technology Q&A

How to export the data as integer into excel sheet?

Former Member — Wed, 30 Apr 2008 06:33:24 GMT

Hi All,

I am working on export to excel functionality and using JEXCEL API to create it. When I export the data to excel sheet, the data are stored in text format in the excel sheet. If I click on summation button on excel sheet, it is not summing up the column values since it is stored as the text format.

I am writing the following code:

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.getAttributeAsTex(attributeName);

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

sheet.addCell(value);

}

j++;

}

Here colVal is the variable which holds the data in string format. So I was just trying to convert it into integer format and used Integer.parseInt(colVal).

But Label keyword accepts only the int,int,string arguments.

Is there any other option to change it as integer value while exporting to excel sheet.

Pls suggest.

This is very urgent.

Regards,

Subashini.

Re: How to export the data as integer into excel sheet?

Former Member — Wed, 30 Apr 2008 07:36:13 GMT

Hi All,

Pls suggest for this issue. Hope I have explained my issue clearly. If not, Pls let me know as this is very urgent.

Regards,

Subashini.

Re: How to export the data as integer into excel sheet?

nikhil_bose — Wed, 30 Apr 2008 07:44:47 GMT

tell me what you need excatly?

where and what for you writing this code?

you need colValue in Excel as integer or what?

nikhiL

Re: How to export the data as integer into excel sheet?

Former Member — Wed, 30 Apr 2008 07:48:41 GMT

Hi Subhasini,

I have never used JExcel API as such. But while searching google, JExcel Api is developed by TeamDev and Sourceforge. Which of it you are using? Also please tell, sheet is the object of which class. Only after seeing API, people can help you. Can you post the link of java doc of JExcel API?

In the meantime, you can try the following:

Label value = new Label(j, index + 1, "" + Integer.parseInt(colVal));

Regards,

Gopal

Re: How to export the data as integer into excel sheet?

Former Member — Wed, 30 Apr 2008 07:49:15 GMT

refer this link..

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/user-interface-technology/wd%20java/wdjava%20archive/exporting%20context%20data%20into%20excel%20using%20the%20web%20dynpro%20binary%20cache.pdf

Hope it may help u..

Re: How to export the data as integer into excel sheet?

nikhil_bose — Wed, 30 Apr 2008 07:53:29 GMT

Take a look at following links, may be worth for you

https://www.sdn.sap.com/irj/sdn/wiki?path=/x/0mq

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

nikhiL

Re: How to export the data as integer into excel sheet?

Former Member — Wed, 30 Apr 2008 08:58:18 GMT

Hi Gopal,

Pls refer the following link.

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

I have used the same coding which is mentioned in this link.

And also I cannot use integer.parseInt(colval) because new Label() will accept only the int,int,string as parameters.

Here goes my to excel method code which exports the data to excel sheet .

private FileInputStream toExcel(IWDNode dataNode, Map columnInfos) {

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();

}

It is exporting the data in text format. So I am unable to sum up the column values in the excel sheet.

I need an alternate solution wherein I can export the data in integer format to excel sheet and then sum up the column values by clicking on summation button.

Hope I am clear now.

Pls suggest.

Regards,

Subashini.

Re: How to export the data as integer into excel sheet?

Former Member — Wed, 30 Apr 2008 09:06:33 GMT

Hi Subhasini,

I tried to get javadoc for JExcel API but the link http://jexcelapi.sourceforge.net/ is not opening.

Regards,

Gopal

Re: How to export the data as integer into excel sheet?

Former Member — Wed, 30 Apr 2008 09:49:33 GMT

Hi Gopal,

Did you try to open the link which i sent before?

Pls try to open the below link.

http://www.andykhan.com/jexcelapi/tutorial.html

Regards,

Subashini.

Edited by: Subashini on Apr 30, 2008 11:50 AM

Re: How to export the data as integer into excel sheet?

nikhil_bose — Wed, 30 Apr 2008 09:54:01 GMT

why don't you use double instead Label?

nikhiL

Re: How to export the data as integer into excel sheet?

Former Member — Wed, 30 Apr 2008 09:54:47 GMT

Hi Subhasini,

In the link you provided in last message I found out the following code which may be helpful to you:

WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER);

Number number2 = new Number(0, 4, 3.141519, integerFormat);

sheet.addCell(number2);

Regards,

Gopal

Re: How to export the data as integer into excel sheet?

Former Member — Wed, 30 Apr 2008 10:04:06 GMT

Hi Gopal/Nikhil,

I am not asking how to format an integer. I just want to know how to convert string into integer.

I have tried with integer.parseInt.

But label accepts only int,int,string as arguments.

Nikhil - Can you explain how to use double?

Can you send any sample code for this?

Regards,

Subashini.

Re: How to export the data as integer into excel sheet?

Former Member — Wed, 30 Apr 2008 10:10:08 GMT

Instead of :

String colVal = nodeElement.getAttributeAsTex(attributeName);
Label value = new Label(j, index + 1, colVal);
sheet.addCell(value);

use code:

String colVal = nodeElement.getAttributeAsTex(attributeName);

WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER);
Number number = new Number(j, index + 1, Integer.parseInt(colVal), integerFormat);
sheet.addCell(number);

Regards,

Gopal

Re: How to export the data as integer into excel sheet?

nikhil_bose — Wed, 30 Apr 2008 10:11:26 GMT

set cell format as number


WritableCell cell = sheet.getWritableCell(2, 4);

NumberFormat twodps = new NumberFormat("####.##");
WritableCellFormat cellFormat = new WritableCellFormat(twodps);
cell.setFormat(cellFormat);

Add Number Cell


 Number number = new Number(2, 4, colValue);
 sheet.addCell(number);

nikhiL

Edited by: Nikhil Bos on Apr 30, 2008 3:42 PM