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 GeneralCellType and setting the celltype of the cell to this type.


     


    I have also tried


     


    string som = tempval.ToString("%##,##.##");


    UnderlyingTable[rownum][colnum] = som;


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


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


     


    no go


     


    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.


     


    Thanks


    Anton

  • 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.

     
            private void btnApplyFormatting_Click(object sender, EventArgs e)
    {
    myFormatting gencell = new myFormatting();

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

    public class myFormatting : FarPoint.Win.Spread.CellType.GeneralCellType
    {
    public override string Format(object obj)
    {
    if (obj != null)
    return double.Parse((string)obj).ToString("###,##0.0#%");
    else
    return base
    .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


     

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

    if (double.TryParse(obj.ToString(), out att))
    {
    return att.ToString(FormatString);
    }
    else
    {
    return obj.ToString();
    }
    }
    else
    return base
    .Format(obj);

    }
    }
     
Need extra support?

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

Learn More

Forum Channels