How to export the data as integer into excel sheet?

[Former Member]

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.

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

refer this link..

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/user-interface-tec...

Hope it may help u..

GS

[nikhil_bose · Accepted solution]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Hi Subhasini,

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

Regards,

Gopal

[Former Member · Accepted solution]

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

[nikhil_bose · Accepted solution]

why don't you use double instead Label?

nikhiL

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

[nikhil_bose · Accepted solution]

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

[nikhil_bose]

tell me what you need excatly?

where and what for you writing this code?

you need colValue in Excel as integer or what?

nikhiL