set formulae to truncate decimal to 2 decimal

Posted by: lier on 8 September 2017, 2:08 pm EST

  • Posted 8 September 2017, 2:08 pm EST

    hii,


    i need to set formulae to truncate decimal to 2 decimal in farpoint spread.


    i used the following code:


     fpspread.sheet[0].setformulae(0,0,"trunc(Convert.todecimal(fpspread.sheet[0].cells[0,0].text),2)


     but it is raising exception.


    ami going wrong somewhere?


    kindly help

  • Replied 8 September 2017, 2:08 pm EST

    Lier,

    An alternative approach would be to set the cell or the column in which the formula is being applied to a NumberCellType and set the decimal places for the entire column or cell as desired.And then apply the formula as required.Something as follows:

           Dim numcell As New FarPoint.Win.Spread.CellType.NumberCellType
            numcell.DecimalPlaces = 2
            FpSpread1.ActiveSheet.Cells(9, 3).CellType = numcell
            FpSpread1.ActiveSheet.Cells(9, 3).Formula = "Your formula"

    Thanks

  • Replied 8 September 2017, 2:08 pm EST

    lier,


    From your code, I am not sure what you are attempting to do.


    If you already have a value in the cell and you what it displayed to 2 decimal places then you could use a cell type as SureshD suggested.


    If you want to use a formula to compute a value to 2 decimal places then you could use code like...


        fpSpread1.Sheets[0].Cells[0, 0].Value = 1234.5678;
        fpSpread1.Sheets[0].Cells[0, 1].Formula = "TRUNC(A1,2)";


    or...


        fpSpread1.Sheets[0].SetValue(0, 0, 1234.5678);
        fpSpread1.Sheets[0].SetFormula(0, 1, "TRUNC(A1,2)");


    Spread's formulas support several rounding functions including ROUND, ROUNDDOWN, ROUNDUP, MROUND, TRUNC, CEILING, FLOOR.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels