Disable warning about Excel text cell containing numeric value in OfficeOpenXml

Question

I'm using OfficeOpenXml to create an MS/Excel spreadsheet file. Some of the columns contain arbitrary text values. However, when cells in those columns are filled with numeric values (i.e., text values containing only digits), Excel displays those cells with a small green triangle in the corner, along with the warning that "The number in this cell is formatted as text or preceded by an apostrophe".

Which is technically correct, but I do not want Excel to display the warning.

So how do I format those columns, or the cells in those columns, to be strictly text values, so that they will not be flagged as numeric text values? How do I disable the warning, and force Excel to accept those cell values as text (only)?

Note: I've seen solutions for other OpenXML packages, but none specifically for OfficeOpenXml. I've also seen solutions for interpreting text cell values as numbers, but this is the exact opposite of what I want to do.

score 5 · Answer 1 · edited Feb 13 '21 at 00:58

5

Using DocumentFormat.OpenXml code in C# will look like this.

// Append sheetData to worksheet
worksheet.Append(sheetData);

// Ignoring errors for parsing
IgnoredErrors errors = new IgnoredErrors();
errors.AddChild(new IgnoredError() { NumberStoredAsText = true, SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A:Z" } });

// of type Worksheet
worksheet.Append(errors);

edited Feb 13 '21 at 00:58

Pang

9,564
146
81
122

answered Feb 12 '21 at 22:43

Damian Zapart

53
1
3

"A:Z" works! You can also have multiple cell ranges in one IgnoredError by separating them in InnerText with spaces. e.g. "A:A B1:C99 L:M". I tried adding an IgnoredError for each range and that made Excel complain. – ourmandave Oct 20 '21 at 18:22

score 2 · Answer 2 · answered Apr 02 '20 at 18:07

I was looking for the same answer and I think this will get you close. After the SheetData section, you need to create an IgnoredErrors section with IgnoredError elements. You can do a range such as:

<ignoredErrors>
    <ignoredError numberStoredAsText="1" sqref="G2:G10"/>
</ignoredErrors>

You can create more than one element and you can span other types of fields. In my case, I used the range "A1:{LastCell}" and it worked.

I figured it out by creating my "bad" xlsx document, going into excel and marking the range to ignore and saving as a copy. Then I used the Open XML 2.5 productivity tool compare files to see the difference. It didn't take long to find ignorederrors section and go from there.

score 0 · Accepted Answer · answered Mar 19 '21 at 18:44

Another solution is to us LoadFromText, which fills the cell text and seems to suppress the 'numeric text' warnings for the cell. So I use code like this for filling the cells that have this problem:

DataRow dr = ...;    // Query result row
...
cell[r, c].LoadFromText(Convert.ToString(dr["item"]));

Disable warning about Excel text cell containing numeric value in OfficeOpenXml

3 Answers3

Linked