Can I ignore Excel warnings when creating spreadsheets using EPPlus?

Question

I save a combination of numeric and non-numeric values ​​in one column in a spreadsheet using C # and EPPlus. When I open a spreadsheet using Excel, it shows green triangles in cells with numerical values, warning that "Number is stored as text" and makes it possible to ignore it for a specific cell. Can I do this from code or is it a special Excel function?

Answer 1

You really have 2 options with the code:

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

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

As an alternative, I would not recommend relying on

• play with Excel properties and disable the ability to display alerts

Answer 2

From the EPPlus documentation:

My formats do not work. If you add numerical data as strings (for example, the original ExcelPackage), Excel will process the data as a string and 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

Answer 3

This is the output of the TechnoPriest answer that works for me - I added decimal value processing and changed the name of the method to more accurately document its true meaning:

 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; } } 

Answer 4

You can check if your value is an integer, convert it to int and assign the cell value to the number. Then it will be saved as a number, not a 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; }