CODEWITHSUNDEEP
excelopenxml
CharlieHorse
CharlieHorse

Reputation: 123

OpenXML: XML and Excel worksheet has different values

When I read an Excel worksheet using OpenXML, I have different values from the cell values in Excel. This happens in the OpenXML Productivity Tool, my code and when I change the extention to .zip and look at the XML in my browser. For instance, this is part of my Excel sheet (sorry, I don't have enough "reputation" to post an image of the worksheet here):

SAMPLE ID   ANALYTE ID  RESULT QUANTIFIER   RESULT
782-3M-000085   75-35-4 <   0.11
782-3M-000085   56-23-5 <   0.0006
782-3M-000085   156-59-2    <   0.497
782-3M-000085   75-69-4 <   0.0009
782-3M-000085   67-66-3 <   0.008
782-3M-000085   76-13-1 <   0.006
782-3M-000085   127-18-4        1.79

and the XML looks like this (for part of it):

 <row r="1" spans="1:18" ht="38.25" x14ac:dyDescent="0.2">
 <c r="A1" s="30" t="s">
  <v>103</v> 
  </c>
 <c r="B1" s="30" t="s">
  <v>6</v> 
  </c>
 <c r="C1" s="30" t="s">
  <v>24</v> 
  </c>
 <c r="D1" s="30" t="s">
  <v>104</v> 
  </c>
 <c r="E1" s="30" t="s">
  <v>105</v> 
  </c>
   </row>
 <row r="2" spans="1:18" x14ac:dyDescent="0.2">
 <c r="A2" t="s">
  <v>48</v> 
  </c>
 <c r="B2" t="s">
  <v>115</v> 
  </c>
 <c r="C2" s="36" t="s">
  <v>116</v> 
  </c>
 <c r="D2">
  <v>0.11</v> 
  </c>
 <c r="E2" t="s">
  <v>117</v> 
  </c>
   </row>
 <row r="3" spans="1:18" x14ac:dyDescent="0.2">
 <c r="A3" t="s">
  <v>48</v> 
  </c>
 <c r="B3" t="s">
  <v>123</v> 
  </c>
 <c r="C3" s="36" t="s">
  <v>116</v> 
  </c>
 <c r="D3">
  <v>5.9999999999999995E4</v> 
  </c>
   </row>
 <row r="4" spans="1:18" x14ac:dyDescent="0.2">
 <c r="A4" t="s">
  <v>48</v> 
  </c>
 <c r="B4" t="s">
  <v>124</v> 
  </c>
 <c r="C4" s="36" t="s">
  <v>116</v> 
  </c>
 <c r="D4">
  <v>0.497</v> 
  </c>
   </row>

Does anybody have any idea what I'm doing wrong? I'm trying to import the values from the worksheet into my code.

Upvotes: 1

Views: 1411

Answers (1)

Jesper Lund Stocholm
Jesper Lund Stocholm

Reputation: 2013

@psantiago is right - you are dealing with "shared strings".

The 't' attribute of the cell element tells you what type of data is in the cell's -element. (Section 18.3.1.4 in the OpenXml-standard). In this case, the 's'-value tells you that the value is an index into the SharedStrings-file. (section 18.18.11 ST_CellType (Cell Type)) of the standard. So at index 48 in the SharedStrings.xml-file you will find the information you need.

Look also at the difference to the cell containing the value "0.11". 

<c r="D2">
  <v>0.11</v> 
</c>

It has no "cell datatype" attribute so the value is simply "0.11" directly in the cell.

You can get the full document at http://standards.iso.org/ittf/PubliclyAvailableStandards/index.html - search the page for "ISO/IEC 29500-1:2012"

Upvotes: 2

Related Questions