8

I'm storing mix of numeric and non-numeric values in a single column in spreadsheet using C# and EPPlus. When I open spreadsheet with Excel it shows green triangles in the cells with numeric values giving warning that 'Number Stored as Text' and giving option to ignore it for particular cell. Can I do it from code or is it some Excel specific feature?

Chris
  • 1,533
  • 2
  • 17
  • 33
Demarsch
  • 1,419
  • 19
  • 39

4 Answers4

7

You really have 2 options using code:

  • change the .NumberFormat property of Range to TEXT (I believe equivalent in epplus is Cell[row, column].Style.NumberFormat.Format)

  • prefix any number with ' (a single quote) - Excel then treats the number as TEXT - visually, it displays the number as is but the formula will show the single quote.

Alternatively, which I wouldn't recommend relying on

  • play with Excel's properties and untick the option to display warnings
6

From the EPPlus documentation:

My number formats does not work If you add numeric data as strings (like the original ExcelPackage does), Excel will treat the data as a string and it will not be formatted. Do not use the ToString method when setting numeric values.

string s="1000"
int i=1000;
worksheet.Cells["A1"].Value=s; //Will not be formatted
worksheet.Cells["A2"].Value=i; //Will be formatted
worksheet.Cells["A1:A2"].Style.Numberformat.Format="#,##0";

http://epplus.codeplex.com/wikipage?title=FAQ&referringTitle=Documentation

Demat
  • 39
  • 2
  • 12
Christian Sauer
  • 10,351
  • 10
  • 53
  • 85
  • 3
    I'm not trying to format numeric values, I want them to be displayed as is. So I don't have 'int i' value I rather have 'string s' value and Excel mark it with warning sign – Demarsch Oct 21 '14 at 10:56
2

This is a derivation of TechnoPriest's answer that works for me - I've added handling of decimal values, and changed the name of the method to more accurately document its true raison d'etre:

public static void ConvertValueToAppropriateTypeAndAssign(this ExcelRangeBase range, object value)
{
    string strVal = value.ToString();
    if (!String.IsNullOrEmpty(strVal))
    {
        decimal decVal;
        double dVal;
        int iVal;

        if (decimal.TryParse(strVal, out decVal))
        {
            range.Value = decVal;
        }
        else if (double.TryParse(strVal, out dVal))
        {
            range.Value = dVal;
        }
        else if (Int32.TryParse(strVal, out iVal))
        {
            range.Value = iVal;
        }
        else
        {
            range.Value = strVal;
        }
    }
    else
    {
        range.Value = null;
    }
}
B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • 1
    This is just what I needed. I did add a null object check before value.ToString() because I was sometime sending null. – juicebyjustin Mar 26 '18 at 18:17
1

You can check if your value is integer, convert it to int and assign number to cell's value. Then it will be saved as number, not string.

public static void SetValueIntOrStr(this ExcelRangeBase range, object value)
{
    string strVal = value.ToString();
    if (!String.IsNullOrEmpty(strVal))
    {
        double dVal;
        int iVal;

        if (double.TryParse(strVal, out dVal))
            range.Value = dVal;
        else if (Int32.TryParse(strVal, out iVal))
            range.Value = iVal;
        else
            range.Value = strVal;
    }
    else
        range.Value = null;
}
technopriest
  • 161
  • 2
  • 5