Number format problem

Aspose.Cells Product Family
1

Hi,

I am trying to set a formula to the cell and then trying to change the display format of the data, but i am not able to get the expected result.

Please find the code below:

Setting the foemula to the cell

cell.getRow(i).getCell(element).setFormula("="+ cell.getRow(i).getCell(element).getValue() + "/" + 1000);

Setting the display style of the data to the cell.

Style style = cell.getRow(i).getCell(element).getStyle();

style.setCustom("# ###,00");

cell.getRow(i).getCell(element).setStyle(style);

Value in the cell: 4104644615,95/1000

Value in the cell after excel generation is: 4 1 04 645

Expected Formatted Data based on the style: 4 104 644,61 (Thousand seperated with spacs and after decimal only two digits)

When i try to get the format of the cell in the Excel, the format which i got is "#\ ## #00\ ", but the format which i have given is "# ###,00".

Please Suggest me the solution,

Thanks,

Suresh

2

Hi,

Kindly give us your input + output excel files and your complete sample code to reproduce the issue you have talked about. We will check it soon.

By the way which version of the product you are using?

Thank you.

3
Hi,

Well, after further investigation and for your requirement of number format, I think the thousand separator is locale dependent too. So you can set the custom format as:
style.setCustom("#,###.00");
If your locale is, such as, Sweden, the cell value will be shown as "4 104 644,61" automatically by MS Excel.
Thank you.
4

Hi Amjad,

Please find the Code below

import java.io.IOException;

import java.math.BigDecimal;

import com.aspose.cells.Cells;

import com.aspose.cells.Style;

import com.aspose.cells.Workbook;

import com.aspose.cells.Worksheet;

import com.aspose.cells.Worksheets;

public class Excel {

/**

* @param args

*/

public static void main(String[] args) {

Workbook book = new Workbook();

/*Style style = book.createStyle();*/

Worksheets sheets = book.getWorksheets();

Worksheet sheet = sheets.getSheet(0);

Cells cells = sheet.getCells();

BigDecimal bd = (new BigDecimal(4589653234123412341.2456)).divide(new BigDecimal(1000.00));

cells.getRow(5).getCell(10).setValue(bd.doubleValue());

System.out.println(bd.doubleValue());

System.out.println(bd);

cells.getRow(5).getCell(10).setFormula("="+ cells.getRow(5).getCell(10).getValue() + "/" + 1000);

Style style = cells.getRow(5).getCell(10).getStyle();

style.setCustom("# ##0,00");

cells.getRow(5).getCell(10).setStyle(style);

try {

book.save("Excel.xls");

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

Can you please try to run the program and u can see the variation in the format what we have given in Aspose and what we got in excel sheet.

Please suggest me further solution.

Thanks,

Suresh.

5

Hi,

Well, we don’t find your mentioned issue. What result you want to get, we are not sure about it? As we has said in our earlier reply, the decimal ‘,’ and thousand separator ‘.’ are all depend on the locale. For your information, we only support to set the format pattern in English fashion. If you set the right custom string in English fashion, you should get the correct formatted string in Excel automatically
And, even if we set the custom string as “# ##0,00”, we did not find it be shown as “#\ ## #00\ “.

As we told you before, you may change you line of code to:
style.setCustom(”#,###.00”);

Moreover, we recommend you to try our latest version v2.2.1: http://www.aspose.com/community/files/72/java-components/aspose.cells-for-java/entry238005.aspx


Thank you.