Format column as text

Format column as text

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

• 

  patrickc

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 10 September 2017, 10: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.

• 

  seanl

  • Post Options:
  • Link
    Copy

  Posted 10 September 2017, 10: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!

• 

  patrickc

  • Post Options:
  • Link
    Copy

  Posted 10 September 2017, 10: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?

• 

  deepaksharma14

  • Post Options:
  • Link
    Copy

  Posted 10 September 2017, 10: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

• 

  patrickc

  • Post Options:
  • Link
    Copy

  Posted 10 September 2017, 10: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.

• 

  deepaksharma14

  • Post Options:
  • Link
    Copy

  Posted 10 September 2017, 10: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

• 

  patrickc

  • Post Options:
  • Link
    Copy

  Posted 10 September 2017, 10: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.

• 

  deepaksharma14

  • Post Options:
  • Link
    Copy

  Posted 10 September 2017, 10: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

• 

  Jose.Aguirre

  • Post Options:
  • Link
    Copy

  Posted 22 March 2021, 10: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!

• 

  kartik.sagar

  

  • Post Options:
  • Link
    Copy

  Posted 23 March 2021, 6:20 am 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

• 

  Jose.Aguirre

  • Post Options:
  • Link
    Copy

  Posted 23 March 2021, 12:36 pm EST

  Great! It helped. Thx!