How to Convert String to Number for negative values in Java

Aspose.Cells Product Family
,
1

How to convert string to number for negative values in Java using Aspose.Cells?
e.g.,
Input: For input string: “(123.96)”
Expected output: -123.96

2

@Aneesha21,

Generally, you should change/specify the cell’s number formatting accordingly to display the value in the cell. Could you share your sample Excel file which contains your desired string in some cell and which you want to convert to negative values.

PS. please zip the Excel file prior attaching.

3

test.zip (2.4 KB)

please check column T
Cell Type is String and one cell value is like “(123.96)”.It is a negative value.
My question is how to convert Text to number format for these type of values(i.e “(123.96)”) for only one column?

Input: “(123.96)”
Expected output: -123.96

can you please help me ? Iam using aspose library for my project and I am in trail period.I am checking the functionalities and I will buy the license.

4

@Aneesha21,
You can convert Text Numeric Data to Numbers using the function convertStringToNumericValue. Please give it a try and share your feedback.

Workbook workbook = new Workbook("test 2.xls");
workbook.getWorksheets().get(0).getCells().convertStringToNumericValue();
System.out.println(workbook.getWorksheets().get(0).getCells().get("T4").getValue());

Output
-123.96

Refer to the following article for more information:
Convert Text Numeric Data to Number

5

but It has to be applied for particular column, not all the columns.
Remaining columns need to String and convert only T column from text to string.
can you please help me in this?

6

@Aneesha21,
You may set the individual column style number to 0 and try the scenario again.

Workbook workbook = new Workbook("test 2.xls");
workbook.getWorksheets().get(0).getCells().convertStringToNumericValue();
Style s = workbook.getWorksheets().get(0).getCells().getColumns().get(19).getStyle();
s.setNumber(0);
StyleFlag flag= new StyleFlag();
flag.setAll(true);
workbook.getWorksheets().get(0).getCells().getColumns().get(19).applyStyle(s, flag);
System.out.println(workbook.getWorksheets().get(0).getCells().get("T4").getValue());
workbook.save("test 2Saved.xls");
7

Another better way is to just browse the T column cells and convert these to string using Cell.putValue() overloads. See the following sample code that works fine for your needs:
e.g
Sample code:

Workbook workbook = new Workbook("f:\\files\\Test.xls");
        //Obtaining the reference of the first worksheet
        WorksheetCollection worksheets = workbook.getWorksheets();
        Worksheet sheet =  worksheets.get(0);
        Cells cells = sheet.getCells();

        int col = CellsHelper.columnNameToIndex("T");
        int lRow = cells.getLastDataRow(col);

        for(int r =0;r <= lRow;r++)
        {
            Cell cell = cells.checkCell(r, col);

 if(cell != null)
            {
                cell.putValue(cell.getStringValue(), true);
            }
        }

        workbook.save("f:\\files\\out1.xls");

Hope, this helps a bit.

8

thank you so much for quick response

9

@Aneesha21,

Good to know that the suggested code segment works for your needs well. In the event of further queries or issue, feel free to write us back.