Unable to read the percentage value from excel using open xml sdk

Category: open xml format sdk

Question

RahulPundlik on Tue, 17 May 2016 07:44:33


Hi, 

I am using Open xml SDK to read the data from excel and it is working fine for string data. but when we try to read the cell value containing percentage e.g. 20% it reads that value as 0.2 which is invalid, please let me know if there is any way to solve this issue.

Regards

Rahul Pundlik

Replies

Sergio Russo on Tue, 17 May 2016 08:36:33


Hi Rahul.

Take a look at this...

https://msdn.microsoft.com/en-us/library/office/hh298534.aspx

You have to check the data type and based on that use different ways.

if (theCell != null)
        {
            value = theCell.InnerText;

            // If the cell represents an integer number, you are done. 
            // For dates, this code returns the serialized value that 
            // represents the date. The code handles strings and 
            // Booleans individually. For shared strings, the code 
            // looks up the corresponding value in the shared string 
            // table. For Booleans, the code converts the value into 
            // the words TRUE or FALSE.
            if (theCell.DataType != null)
            {
                switch (theCell.DataType.Value)
                {
                    case CellValues.SharedString:
                        
                        // For shared strings, look up the value in the
                        // shared strings table.
                        var stringTable = 
                            wbPart.GetPartsOfType<SharedStringTablePart>()
                            .FirstOrDefault();
                        
                        // If the shared string table is missing, something 
                        // is wrong. Return the index that is in
                        // the cell. Otherwise, look up the correct text in 
                        // the table.
                        if (stringTable != null)
                        {
                            value = 
                                stringTable.SharedStringTable
                                .ElementAt(int.Parse(value)).InnerText;
                        }
                        break;

                    case CellValues.Boolean:
                        switch (value)
                        {
                            case "0":
                                value = "FALSE";
                                break;
                            default:
                                value = "TRUE";
                                break;
                        }
                        break;
                }
            }


Fei Xue on Wed, 18 May 2016 05:28:55


Hi Bubu, 

If you want to retrive the value of the cell, you can follow the suggestion by Segio metioned. And if you want to retrive the data format in the Excel with value too, we need to apply it by analyze the cell format.

For example, if we want to retrieve the percent format of value, we need to retrieve the style index from the XML element like below:

<x:c r="A1" s="1" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:v>0.2</x:v>
</x:c>

Then we need to read the style from the CellFormats via the style index:

 <cellXfs count="3">
  <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
  <xf numFmtId="9" fontId="0" fillId="0" borderId="0" xfId="1" applyFont="1"/>
  <xf numFmtId="165" fontId="0" fillId="0" borderId="0" xfId="1" applyNumberFormat="1" applyFont="1"/>
 </cellXfs>

And normally, if you doesn't change the number format, the format which like '20%' is no 'applyNumberFormat' attribute. If the cell format element contain the applyNumberFormat attribute, we also need to get the real format from the 'numFmts' like XML below:

 <numFmts count="1">
  <numFmt numFmtId="165" formatCode="0.000%"/>
 </numFmts>

In addition, to get a quick start sample of Open XML, we recommend you that use the Open XML productivity tool to reflect the code.

Hope it is helpful.

Regards & Fei