How to have a spreadsheet with the formular/input bar on top that show more decimal places than the displayed number in a cell (just like Excel.)

Posted by: wil1299 on 8 September 2017, 2:47 pm EST

  • Posted 8 September 2017, 2:47 pm EST


    I need to have a spreadsheet display numbers with 2 decimals places, but if the cell is click, the number/value is shown in the top formula/input bar with all the decimal places that it has. For example, in Excel, the cell has "11.2345" in its, but since the celltype is set to Number with 2 decimals, it displayed as 11.23. However, if you click on the cell, the top "formulat/input" bar showed 11.2345.

    If there a way to do that in Farpoint, and to get and keep the actual number with all the precision even though the "visual" displayed number is only 11.23?

    I try to do:



    FarPoint.Win.Spread.FormulatTextBox formulaTextBoxforReport.Attach(fpSpread1);

    formulaTextBoxforReport.ReadOnly = false;

    formulaTextBoxforReport.AutoComplete = true;



    Thanks in advance for your help!


  • Replied 8 September 2017, 2:47 pm EST


    You can create a custom celltype that overrides the PaintCell method to format the number the way you want to paint in the cell. The value in the DataModel will stay the way you typed the number in and the Formula TextBox will show the data as it is in the DataModel.

    Public Class Form1

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim n As New MyNumber

    n.DecimalPlaces = 6

    n.FixedPoint = False

    FpSpread1.Sheets(0).Cells(1, 1).CellType = n

    End Sub

    End Class

    Public Class MyNumber

    Inherits FarPoint.Win.Spread.CellType.NumberCellType

    Public Overrides Sub PaintCell(ByVal g As System.Drawing.Graphics, ByVal r As System.Drawing.Rectangle, ByVal appearance As FarPoint.Win.Spread.Appearance, ByVal value As Object, ByVal isSelected As Boolean, ByVal isLocked As Boolean, ByVal zoomFactor As Single)

    Dim x As Integer

    If value > 1 Then

    x = 4

    End If

    If Not value Is Nothing Then

    value = Math.Round(CType(value, Double), 2)

    End If

    MyBase.PaintCell(g, r, appearance, value, isSelected, isLocked, zoomFactor)

    End Sub

    End Class 

  • Replied 8 September 2017, 2:47 pm EST

    Thanks Scott for the quick reply.

    I got it to work where it shows more precision on the bar.

    However, I also have the format for the negative: red or parenthese, etc... And now, it does not work correctly somehow. (It works previously with the original FarPoint.Win.Spread.CellType.NumberCellType  (w/o CustomNumberType class))

    I have

    public class CustomNumberType : FarPoint.Win.Spread.CellType.NumberCellType


     and when I try to use it:

    CustomNumberType NumberCell = new CustomNumberType();

     NumberCell.NegativeFormat = FarPoint.Win.Spread.CellType.NegativeFormat.NegativeSignBefore;

     // 3 flags for cases of negative formats

    bool NegativeNumberRed ;

    bool NegativeNumInParentheseRed;

    bool NegNumInParenthese;


    if (NegativeNumberRed ) || NegativeNumInParentheseRed)


    NumberCell.NegativeRed = true;


    if (NegNumInParenthese || NegativeNumInParentheseRed)


    NumberCell.NegativeFormat = FarPoint.Win.Spread.CellType.NegativeFormat.Parentheses;


    I will appreciate it if you have any insight.

    Thanks again.

  • Replied 8 September 2017, 2:47 pm EST

    I just fixed a problem, now if I set the negative format to be: Parenthese, Red.

    I do have (5.22) in red for - 5.22.

    However, the bar shows 5.22!

    The PaintCell probably needs some additional logic. (I will see to it).

  • Replied 8 September 2017, 2:47 pm EST


    There is no reason to do anything else in the PaintCell method. The CellType object has properties to do what you want. Here is my code to get the project to work as you are wanting.

    public partial class Form1 : Form


    public Form1()




    private void Form1_Load(object sender, EventArgs e)


    MyNumber n = new MyNumber();

    n.DecimalPlaces = 6;

    n.FixedPoint = false;

    n.NegativeFormat = FarPoint.Win.Spread.CellType.NegativeFormat.Parentheses;

    n.NegativeRed = true;

    fpSpread1.Sheets[0].Cells[1, 1].CellType = n;



    public class MyNumber : FarPoint.Win.Spread.CellType.NumberCellType


    public override void PaintCell(Graphics g, Rectangle r, FarPoint.Win.Spread.Appearance appearance, object value, bool isSelected, bool isLocked, float zoomFactor)


    if (value!=null)

    value = Math.Round((double)(value), 2);

    base.PaintCell(g, r, appearance, value, isSelected, isLocked, zoomFactor);


  • Replied 8 September 2017, 2:47 pm EST

    Thanks Scott for your help.

    I was able to fix my problem (not thru PaintCell) so it is fine now.

    Thanks a lot for your help!

Need extra support?

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

Learn More

Forum Channels