0

I wrote an Excel parser in .NET/C#. It utilizes the XMLReader class in System.XML and works great. But I have a strange bug where an empty cell comes through as the string "1074." If you look at the Excel file in the GUI the cell is empty. But if you unzip the file, it shows the following for that same cell. 

<c r="Y311" s="1" t="s"><v>1074</v></c>

This issue is repeated over and over again under the following circumstances:

  • it only occurs in this column (column Y)
  • only when the field is empty
  • only after Y311 on the spreadsheet
  • it's always represented by "1074?"

Because the XML says there is a value there, when the XMLReader gets to that part of the spreadsheet, it parses the value as "1074" instead of as an empty cell.

Additionally, prior to Y311, all empty cells have the following XML form (which is correct of course): 

<c r="Y300" s="1"/>

And then no <v></v> attribute after that, because the cell is empty. This is how all empty cells should appear.

Any idea why some of these cells are populating with "1074" even though they should be empty?

joshmcode
  • 3,471
  • 1
  • 35
  • 50
  • Maybe the cell values really _are_ 1074 but they are hidden for some reason when viewing in Excel? I know the cell is "empty", but if you put the cell cursor on it in Excel, what shows in the formula bar? Is there conditional formatting? Does the cell contain a formula with an `=if(...)`? – Jim Garrison Mar 10 '16 at 22:27
  • Thanks for the idea. I've cleared all of the formatting and verified that there are no formulas. The value is still 1074. – joshmcode Mar 10 '16 at 22:34
  • Construct a minimal version of a single-cell spreadsheet by finding one of the phantoms and deleting all rows above and below and all columns left and right, leaving it at A1. Then take a look at the XML. – Jim Garrison Mar 10 '16 at 22:39
  • I'll give it a try and see if it shows anything. – joshmcode Mar 10 '16 at 22:45

1 Answers1

1

The type of the cell is SharedString - t="s" - which means that the value - <v>1074</v> - is a reference into the shared strings table rather than representing the value shown in the cell

If you look at sharedstrings.xml in the xl folder of the .xlsx file, you will find the relevant string

Details on the various cell types are in this answer

Community
  • 1
  • 1
barrowc
  • 10,444
  • 1
  • 40
  • 53