Easiest way to reference a numeric cell's true value, not the formatted value?

Posted by: bradw on 8 September 2017, 1:43 pm EST

  • Posted 8 September 2017, 1:43 pm EST

    I have a spread control where users can enter prices.  I want the cells to display only 2 decimal places.  However, if the user wishes to enter more than two decimal places, the cell should allow that.  Say a particular price is 12.345.  I want the cell to accept 12.345, but display 12.35.

    I've tried the NumberCellType with DecimalPlaces = 2, but that prevents the user from entering the 5 in 12.345.  I've also tried the GeneralCellType with FormatString = "#,###.00" which allows the 3 decimal places and rounds up to 12.35.  Good so far.  However, when it's time to save this price to the database, both the cell's Text and Value property contain 12.35.  Where should I look to find 12.345?  I assume its stored somewhere?  Also, if the user does a cut or copy from this cell, how can I get 12.345 onto the clipboard?

     Any help is greatly appreciated.  Thanks.


  • Replied 8 September 2017, 1:43 pm EST


    The Text property gets the text representation that is displayed on the screen.  The Value property gets the raw value that is stored in the cell (which is what you want).  If I run the following code...

        GeneralCellType ct = new GeneralCellType();
        ct.FormatString = "#,###.00";
        fpSpread1.Sheets[0].Cells[0, 0].CellType = ct;

    and then enter the value 12.3456 in cell A1 and then run the following code...

        string text = fpSpread1.Sheets[0].Cells[0, 0].Text;
        object value = fpSpread1.Sheets[0].Cells[0, 0].Value;
        System.Diagnostics.Debug.WriteLine("text=" + text + " value=" + value.ToString());

    then I get the following output...

        text=12.35 value=12.3456

    Are you getting different results with this code or are you using different code?

    The built-in clipboard operations place the displayed text representation (i.e. Text property) on the clipboard.  This is the same behavior as found in Excel and OpenOffice.

    If you want the clipboard operations to place the raw value on the clipboard then you would need to write application code to perform the clipboard operations.

  • Replied 8 September 2017, 1:43 pm EST

    Thanks for your reply.  I'm assigning the celltype to the entire column since I'll have a series of prices that should all be formatted the same.

    fpSpread1.Sheets[0].Columns[0].CellType = ct;

    My assumption was this would apply the celltype to each cell in the column.  Is that not the case?

  • Replied 8 September 2017, 1:43 pm EST

    To apply cell type to entire cell, you may use the following code snippet:

    fpSpread1.Col = 1
    fpSpread1.Row = -1
    fpSpread1.CellType = CellTypeButton

    The above will make the all the cells of column 1 to be of Button type.





  • Replied 8 September 2017, 1:43 pm EST

    Well, I'm not sure why it wasn't working for me before, but the value property is indeed giving me the number I'm after.  Thanks for your help.
  • Replied 8 September 2017, 1:43 pm EST

    The earlier provided code was for Spread (COM) ActiveX version, in order to set all the cells of a column to a particular cell type in .net, you may use the following snippet:

            Dim c As FarPoint.Win.Spread.Column
              c = FpSpread1.ActiveSheet.Columns(1)
              c.CellType = New FarPoint.Win.Spread.CellType.ButtonCellType


    Thanks & Regards,




Need extra support?

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

Learn More

Forum Channels