format column as text

Posted by: patrickc on 10 September 2017, 1:58 am EST

  • Posted 10 September 2017, 1:58 am EST

    How do I stop Spread from automatically formating a value?  I do not want Spread to change a part number which only consists of integers to scientific notation, for example.


     Thank you.

  • Replied 10 September 2017, 1:58 am EST

    In code, I want to set a column to be text format. This means I do not want it to change user input in any way; no dropping of leading or trailing zeros, no changing to scientific notation. I have been told twice in this post that this can be accomplished through the formatter. Twice I have said I see no example in the provided link to the formatter help file which tells me how to use the formatter to accomplish this.

    To compare to Excel, I want to accomplish, in code and using the formatter property, the same thing that is accomplished by right-clicking a cell, opening the format cells dialogue, and changing the format to "text" from the category choices under the "Number" tab.
  • Replied 10 September 2017, 1:58 am EST

    Hi,

    You can specify a format for the cells.  Here's the help topic about it:

    http://helpcentral.componentone.com/NetHelp/SpreadWPF/webframe.html#CellFormatF.html

    You can also format the cells in design time using Spread Designer:

    http://helpcentral.componentone.com/NetHelp/SpreadWPF/webframe.html#DesignerFormat.html

    Hope that helps!

  • Replied 10 September 2017, 1:58 am EST

    Hello,

    This the default behavior of Spread. When you enter a large number it turns it into a scientific format. This behavior can also be seen with Microsoft Excel.
    As suggested by Sean, you can set the format for a cell using the Formatter property. As given in the first link.

    Thanks,
    Deepak Sharma
  • Replied 10 September 2017, 1:58 am EST

    As I stated, the formatter property examples do not show me how to format a column as text. Please provide an example of how to use the formatter property to set a column's format to be text. Alternatively or additionally, provide examples on how to use the formatter property to set a cell's or range's format to be text.

    Thank you.
  • Replied 10 September 2017, 1:58 am EST

    Hello,

    General Formatter for Spread for Silverlight behaves like a text box. It accepts the string as input. You may set the Formatter explicitly if you want to format the cell for a number , currency etc.

    I would be better able to assist you if you elaborate your requirement further. What format do you want to set for a cell actually?


    Thanks,
    Deepak Sharma
  • Replied 10 September 2017, 1:58 am EST

    Thanks for the reply.


    I had read the info in the first link in the help files, and I don't see where that helps me stop the autoformatting.  I do see how it helps me change the formatting, just not to a text format. 


    I haven't used the Spread Designer, and I would like to avoid using it and work in code as much as possible.


    More help?

  • Marked as Answer

    Replied 10 September 2017, 1:58 am EST

    Hello,

    You may set the formatter for Spread cell using the code as follows:

    gcSpreadSheet1.ActiveSheet.Cells[0, 0].Formatter = new GeneralFormatter(FormatMode.StandardNumericMode, "D");
    gcSpreadSheet1.ActiveSheet.Cells[0, 0].Value = 1234;


    This number remains as "1234" it adds no trailing zeros, neither decimal places and neither it changes the entered number to scientific notation.

    Thanks,
    Deepak Sharma
  • Replied 22 March 2021, 1:53 am EST

    Hello,
    I have the same problem and the answers here do not help at all.
    I want to know how to format the cells when I have the following input and want the same output, it means to get exactly the same text, as in Excel e.g.:
    1,222 => 1,222
    1.222 => 1.222
    1222 => 1222
    1 222 => 1 222
    Using the formatter (FormatMode.StandardNumericMode, "D") is not working for some of the examples above. Please, can you try and say how to achieve my goal?

    Thanks!


  • Replied 22 March 2021, 9:20 pm EST

    Hi Jose,

    You can create a custom formatter by implementing the IFormatter interface which doesn't convert cell values to numeric as follows:

    public class TextFormatter : IFormatter
    {
    public string FormatString => string.Empty;

    public string Format(object obj)
    {
    return obj.ToString();
    }

    public object Parse(string str)
    {
    return str;
    }
    }

    And then you can use it accordingly as follows:

    spread.ActiveSheet[0, 0].Value = "145.345.43.5345433534543";
    spread.ActiveSheet.Cells[0, 0].Formatter = new TextFormatter();

    Please refer to the same from the attached sample. (see SpreadTextCellFormat.zip)

    Best Regards,
    Kartik

    SpreadTextCellFormat.zip
  • Replied 23 March 2021, 3:36 am EST

    Great! It helped. Thx!
Need extra support?

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

Learn More

Forum Channels