OpenXML spreadsheet comma separated numbers gives content error

Question

I am creating an Excel document using OpenXML in a C# webrform project.

In one cell I input an ID, and in some cases multiple IDs separated by commas similar to this:

**ID1**         **ID2**
10756409    3090,3093,3130
10776516    3105
10806938    3123
10817453    3090,3093,3100

Each cell in both columns are given CellValues.String DataType.

When I open the spreadsheet I get the "Found a problem with content. Would you like us to recover..." warning.

When I click yes, the data is fine, but the cells with CSV values are left justtified (as opposed to right justified for the non-CSV values) have the green corner and say The number in this cell is formatted as text or preceded by an apostrophe.

How can I get the Excel to open without the warnings?

Add ' before content for the cells you need it to be treated as text. — Abbas
I hardcoded this into the SELECT and had the same warnings: '''111,111,111' as 'ID2', The resulting Excel cells show '111,111,111 — el-train
This can be "fixed" by using ', ' (comma + space) or a pipe. — el-train

User11040 User11040 · Accepted Answer · 2018-02-07T00:55:00

If you want to ignore the warning message. You can try the following code using the SAX approach:

openXMLWriter.WriteElement(new IgnoredErrors(new IgnoredError()
{
   SequenceOfReferences = new ListValue<StringValue> 
   { 
      InnerText = "A1:Z100" 
   },
   NumberStoredAsText = true
}));

It should create the following xml tag:

<ignoredErrors>
  <ignoredError sqref="A1:Z100" numberStoredAsText="1"/>
</ignoredErrors>

Adjust the cell range to fit your needs.

OpenXML spreadsheet comma separated numbers gives content error

1 Answers