XML cells incorrectly being shown as SharedStrings when they are not

Question

I am parsing through an Excel spreadsheet and had some problems with some values, so on the suggestions of a stackoverflow member, I evaluated them as shared strings. Now, however, some of the cell values are not shared strings and my conditional still evaluates as true, meaning they are SharedStrings. So I am wondering if my code for evaluating these is correct or maybe there's something wrong with the Excel spreadsheets I've been working with. Here is an example conditional, which in this case should evaluate false as the SerialNumber column is not a shared string, but it is evaluated as it being a shared string and therefore causes the program to crash.

bool isSharedString = (((Cell)r.ChildElements[0]).DataType.Value == CellValues.SharedString);

row["SerialNumber"] = isSharedString ? stringTable.SharedStringTable.ElementAt(int.Parse(r.ChildElements[0].InnerText)).InnerText : r.ChildElements[0].InnerText;

This is the inner outer xml code for the row that I am working with. The only ones that are not shared strings are A2 and G2.

<x:row r="2" spans="1:14" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:c r="A2"><x:v>20000001</x:v></x:c>
    <x:c r="B2" t="s"><x:v>14</x:v></x:c>
    <x:c r="C2" s="1" t="s"><x:v>19</x:v></x:c>
    <x:c r="D2" t="s"><x:v>19</x:v></x:c>
    <x:c r="E2" t="s"><x:v>19</x:v></x:c>
    <x:c r="F2" t="s"><x:v>19</x:v></x:c>
    <x:c r="G2"><x:v>0</x:v></x:c>
    <x:c r="H2" t="s"><x:v>19</x:v></x:c>
    <x:c r="I2" t="s"><x:v>19</x:v></x:c>
    <x:c r="J2" t="s"><x:v>20</x:v></x:c>
    <x:c r="K2" t="s"><x:v>22</x:v></x:c>
    <x:c r="L2" t="s"><x:v>20</x:v></x:c>
    <x:c r="M2" t="s"><x:v>22</x:v></x:c>
    <x:c r="N2" t="s"><x:v>19</x:v></x:c>
</x:row>

Stud

Is r definitely the row you think it is? Could you output the CellReference property of the Cell? If it is the correct one, could you extract the raw XML from the excel file in question and show the XML from xl\worksheets\sheet1.xml for the row in question? – Mimimimic

Ok, so I looked at the XML code for my rows, and it appears that yes I am looking at the right row. But i have noticed the difference between the SharedString cells have either s="1" or t="s", while the non-SharedString cells don't have either of these. Do you know what these mean and how I can differentiate between them in code? – Stud

I put the whole xml code in the question so you can see it if you'd like. – Stud

Never mind, I got it. So I think the t in the xml code is type, with some not having one. So My code wasn't crashing the program because the ternary conditional was allowing the non-SharedString parts to pass through, but it was crashing because I was trying to access the value of the DataType from a cell had a null datatype. So I ended up chaning my ternary conditional to row["Description"] = (((Cell)r.ChildElements[13]).DataType != null) ? stringTable.SharedStringTable.ElementAt(int.Parse(r.ChildElements[13].InnerText)).InnerText : r.ChildElements[13].InnerText; and now it works. – Stud

That makes sense. The t="s" means it's a shared string whereas t="str" means it's inline. I think a non-existent type denotes that the value is inline although I can't find any documentation. The s=1 refers to the style being applied to the cell. Is your code now working? – Mimimimic

Yes, after making those changes, my code is working. Thanks for all your help. – Stud

It might be worth you adding your findings in an answer and accepting it for future visitors. If you'd like me to write one let me know. – Mimimimic

Do you know that it is not necessary that all sentences are in only one paragraph ? And for code, do you know that it is not necessary to write long lines when short lines are possible. – Bilge

Answer 1

I figured it out after petelids told me to look at the XML from the excel spreadsheet. My original program kept crashing from what I though was ternary conditional always being true, even when the particular excel cells were not using SharedStrings. However, the xml shows that while the cells with the t="s", the ones that do not have SharedStrings do not, meaning that the non-sharedString cells did not have a corresponding DataType. Because of this, the code was failing because of DataType attribute of the (((Cell)r.ChildElements[0]), which was actually null, so trying to get the Value of it resulted in a null pointer exception. To fix this, I ended up simply checking to see if the DataType was null. When it was, then I just got the inner xml contents. When it was not, then I used the xml contents as the SharedStringsTable index to find the actual string. Case closed!

Stud