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

    Hello,


    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:

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>

    try

    </FONT></FONT><FONT size=2>

    {


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


    formulaTextBoxforReport.ReadOnly = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>false</FONT></FONT><FONT size=2>;


    formulaTextBoxforReport.AutoComplete = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>true</FONT></FONT><FONT size=2>;


    }


     


    Thanks in advance for your help!


    Wil

    </FONT>
  • Replied 8 September 2017, 2:47 pm EST

    Wil,

    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


    <span class="Apple-tab-span" style="white-space:pre;"> </span>Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    <span class="Apple-tab-span" style="white-space:pre;"> </span>Dim n As New MyNumber

    <span class="Apple-tab-span" style="white-space:pre;"> </span>n.DecimalPlaces = 6

    <span class="Apple-tab-span" style="white-space:pre;"> </span>n.FixedPoint = False

    <span class="Apple-tab-span" style="white-space:pre;"> </span>FpSpread1.Sheets(0).Cells(1, 1).CellType = n


    <span class="Apple-tab-span" style="white-space:pre;"> </span>End Sub

    End Class


    Public Class MyNumber

    <span class="Apple-tab-span" style="white-space:pre;"> </span>Inherits FarPoint.Win.Spread.CellType.NumberCellType


    <span class="Apple-tab-span" style="white-space:pre;"> </span>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)

    <span class="Apple-tab-span" style="white-space:pre;"> </span>Dim x As Integer

    <span class="Apple-tab-span" style="white-space:pre;"> </span>If value > 1 Then

    <span class="Apple-tab-span" style="white-space:pre;"> </span>x = 4

    <span class="Apple-tab-span" style="white-space:pre;"> </span>End If

    <span class="Apple-tab-span" style="white-space:pre;"> </span>If Not value Is Nothing Then

    <span class="Apple-tab-span" style="white-space:pre;"> </span>value = Math.Round(CType(value, Double), 2)

    <span class="Apple-tab-span" style="white-space:pre;"> </span>End If


    <span class="Apple-tab-span" style="white-space:pre;"> </span>MyBase.PaintCell(g, r, appearance, value, isSelected, isLocked, zoomFactor)


    <span class="Apple-tab-span" style="white-space:pre;"> </span>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.<FONT color=#2b91af size=2><FONT color=#2b91af size=2>NumberCellType  (w/o CustomNumberType class))</FONT></FONT>

    <FONT size=2>

    I have

    <FONT color=#2b91af size=2><FONT color=#2b91af size=2><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>public</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>class</FONT></FONT><FONT size=2> </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>CustomNumberType</FONT></FONT><FONT size=2> : FarPoint.Win.Spread.CellType.</FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>NumberCellType</FONT></FONT>


    <FONT color=#2b91af size=2><FONT color=#2b91af size=2>{}

    </FONT></FONT>

     and when I try to use it:


    CustomNumberType</FONT></FONT><FONT size=2> NumberCell = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>new</FONT></FONT><FONT size=2> </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>CustomNumberType</FONT></FONT><FONT size=2>();


    </FONT> NumberCell.NegativeFormat = FarPoint.Win.Spread.CellType.</FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>NegativeFormat</FONT></FONT><FONT size=2>.NegativeSignBefore;


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2> // 3 flags for cases of negative formats</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>bool <FONT color=#000000>NegativeNumberRed<FONT size=2> ;</FONT></FONT></FONT></FONT>


    bool NegativeNumInParentheseRed;


    bool NegNumInParenthese;


     


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>if</FONT></FONT><FONT size=2> (NegativeNumberRed</FONT><FONT size=2> </FONT><FONT size=2>) || NegativeNumInParentheseRed)</FONT>


    <FONT size=2>{


    NumberCell.NegativeRed = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>true</FONT></FONT><FONT size=2>;


    }


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>if</FONT></FONT><FONT size=2> (NegNumInParenthese</FONT><FONT size=2> || NegativeNumInParentheseRed)</FONT>


    <FONT size=2>{


    NumberCell.NegativeFormat = FarPoint.Win.Spread.CellType.</FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>NegativeFormat</FONT></FONT><FONT size=2>.Parentheses;


    }


    I will appreciate it if you have any insight.


    Thanks again.

    </FONT>
  • 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

    Hello,

    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.

    <span class="Apple-tab-span" style="white-space:pre;"> </span>public partial class Form1 : Form

    <span class="Apple-tab-span" style="white-space:pre;"> </span>{

    <span class="Apple-tab-span" style="white-space:pre;"> </span>public Form1()

    <span class="Apple-tab-span" style="white-space:pre;"> </span>{

    <span class="Apple-tab-span" style="white-space:pre;"> </span>InitializeComponent();

    <span class="Apple-tab-span" style="white-space:pre;"> </span>}


    <span class="Apple-tab-span" style="white-space:pre;"> </span>private void Form1_Load(object sender, EventArgs e)

    <span class="Apple-tab-span" style="white-space:pre;"> </span>{

    <span class="Apple-tab-span" style="white-space:pre;"> </span>MyNumber n = new MyNumber();

    <span class="Apple-tab-span" style="white-space:pre;"> </span>n.DecimalPlaces = 6;

    <span class="Apple-tab-span" style="white-space:pre;"> </span>n.FixedPoint = false;

    <span class="Apple-tab-span" style="white-space:pre;"> </span>n.NegativeFormat = FarPoint.Win.Spread.CellType.NegativeFormat.Parentheses;

    <span class="Apple-tab-span" style="white-space:pre;"> </span>n.NegativeRed = true;

    <span class="Apple-tab-span" style="white-space:pre;"> </span>fpSpread1.Sheets[0].Cells[1, 1].CellType = n;


    <span class="Apple-tab-span" style="white-space:pre;"> </span>}

    <span class="Apple-tab-span" style="white-space:pre;"> </span>}


    <span class="Apple-tab-span" style="white-space:pre;"> </span>public class MyNumber : FarPoint.Win.Spread.CellType.NumberCellType

    <span class="Apple-tab-span" style="white-space:pre;"> </span>{

    <span class="Apple-tab-span" style="white-space:pre;"> </span>public override void PaintCell(Graphics g, Rectangle r, FarPoint.Win.Spread.Appearance appearance, object value, bool isSelected, bool isLocked, float zoomFactor)

    <span class="Apple-tab-span" style="white-space:pre;"> </span>{

    <span class="Apple-tab-span" style="white-space:pre;"> </span>if (value!=null)

    <span class="Apple-tab-span" style="white-space:pre;"> </span>value = Math.Round((double)(value), 2);

    <span class="Apple-tab-span" style="white-space:pre;"> </span>base.PaintCell(g, r, appearance, value, isSelected, isLocked, zoomFactor);

    <span class="Apple-tab-span" style="white-space:pre;"> </span>}

    <span class="Apple-tab-span" style="white-space:pre;"> </span>} 

  • 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