Format column as text

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

    • Post Options:
    • Link

    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.

  • Posted 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!

  • Posted 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?

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

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

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

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

  • Posted 10 September 2017, 1:58 am EST

    Hello,

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

    [csharp]

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

    gcSpreadSheet1.ActiveSheet.Cells[0, 0].Value = 1234;[/csharp]

    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

  • Posted 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!

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

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