Format a databound string using GeneralCellType

Posted by: abergheim on 8 September 2017, 12:43 pm EST

  • Posted 8 September 2017, 12:43 pm EST

    Hi


    Hope sombody can help. I have a spread which has been databound.


    I have a cell which contains an empty string in the underlying table (data type is string). I have put a formula in the cell is FSpread (.NET windows version). I cant seem to format the cell in any way. I have tried using a <FONT color=#008080>GeneralCellType <FONT color=#000000>and setting the celltype of the cell to this type. </FONT></FONT>


    <FONT color=#008080 size=2><FONT color=#000000></FONT></FONT> 


    <FONT color=#008080><FONT color=#000000>I have also tried </FONT></FONT>


    <FONT color=#008080><FONT color=#000000></FONT></FONT> 

    <FONT color=#008080><FONT color=#0000ff>

    string</FONT><FONT color=#000000> som = tempval.ToString("%##,##.##");</FONT>


    UnderlyingTable[rownum][colnum] = som;


    spread.Sheets[0].Cells[rownum, colnum].Value = som;


    spread.Sheets[0].Cells[rownum, colnum].Text = som;


     


    <FONT size=2><FONT color=#000000 size=3>no go</FONT> </FONT>


    <FONT size=2></FONT> 


    <FONT color=#000000>Any ideas, I know that if I convert the underlying datatable column to be of a type double, the cell will accept the formatting, but I am trying to get away from this. </FONT>


    <FONT color=#000000></FONT> 


    <FONT color=#000000>Thanks</FONT>


    <FONT size=2><FONT color=#000000 size=3>Anton</FONT> </FONT>

    </FONT>
  • Replied 8 September 2017, 12:43 pm EST

    Hi Scott


     


    Thanks for the quick answer.


    "Formatting the data by creating a custom celltype and overriding the Format method will only format the data you see on the screen when displaying" is exactly what I want to do. The formula itself is evaluating just fine as the things it is looking at are bound doubles, it is just the display format of the cell containing the formula that I want to set (this cell is databaound as a string, if it were databound as a double it would show formatting just fine).


     


    Thanks


    Anton

  • Replied 8 September 2017, 12:43 pm EST

    Anton,


    Could you post a small zipped project showing the problem you are seeing for us to debug?

  • Replied 8 September 2017, 12:43 pm EST

    Hi Scott


     


    Sure, please find attached


     


    Thanks


    Anton


    2007/08/SpreadFormatTester.zip
  • Replied 8 September 2017, 12:43 pm EST

    Anton,


    Here is code to show how to create a custom celltype that overrides the Format method to format the string being passed into the DataModel as a number with the formatstring you want.

     
            <SPAN class=kwd>private void</SPAN> btnApplyFormatting_Click(<SPAN class=kwd>object</SPAN> sender, EventArgs e)
    {
    myFormatting gencell = <SPAN class=kwd>new</SPAN> myFormatting();

    fpSpread1.Sheets[0].Columns[0].CellType = gencell;
    }

    <SPAN class=kwd>public class</SPAN> myFormatting : FarPoint.Win.Spread.CellType.GeneralCellType
    {
    <SPAN class=kwd>public override string</SPAN> Format(<SPAN class=kwd>object</SPAN> obj)
    {
    <SPAN class=kwd>if</SPAN> (obj != <SPAN class=kwd>null</SPAN>)
    <SPAN class=kwd>return double</SPAN>.Parse((<SPAN class=kwd>string</SPAN>)obj).ToString(<SPAN class=st>"###,##0.0#%"</SPAN>);
    <SPAN class=kwd>else
    return base</SPAN>.Format(obj);
    }
    }
     
  • Replied 8 September 2017, 12:43 pm EST

    Anton,


    When you set the DataSource of the Spread to a DataSet/DataTable, the data from that object is put directly into the DataModel of the Spread, which is where calculation of the fomulas is done. Formatting the data by creating a custom celltype and overriding the Format method will only format the data you see on the screen when displaying. This will not update the data in the bound DataModel. If you are trying to format strings as numbers for formulas to calculate in the Spread, you would need to format the data in the DataSet/DataTable before setting this as the DataSource of the Spread.

  • Replied 8 September 2017, 12:43 pm EST

    Hi Scott


     


    Thats perfect thanks, works a charm.


     


    I made one slight ammendment to deal with having a formula


     

     
    <SPAN class=kwd>public class</SPAN> myFormatting : FarPoint.Win.Spread.CellType.GeneralCellType
    {
    <SPAN class=kwd>public override string</SPAN> Format(<SPAN class=kwd>object</SPAN> obj)
    {
    <SPAN class=kwd>if</SPAN> (obj != <SPAN class=kwd>null</SPAN>)
    {
    <SPAN class=kwd>double</SPAN> att = 0.0;

    <SPAN class=kwd>if</SPAN> (<SPAN class=kwd>double</SPAN>.TryParse(obj.ToString(), <SPAN class=kwd>out</SPAN> att))
    {
    <SPAN class=kwd>return</SPAN> att.ToString(FormatString);
    }
    <SPAN class=kwd>else</SPAN>
    {
    <SPAN class=kwd>return</SPAN> obj.ToString();
    }
    }
    <SPAN class=kwd>else
    return base</SPAN>.Format(obj);

    }
    }
     
Need extra support?

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

Learn More

Forum Channels