CODEWITHSUNDEEP
c#excelxsltopenxml-sdk
JasonWH
JasonWH

Reputation: 179

Reading a Number from an Excel Sheet is changing the value

I have a C# program that reads in an excel workbook, and then builds an XML file that can be ran against an XSLT. The problem that has cropped up is that one of the fields is a number, and when reading it out of the excel sheet the value is being changed. Here is the example:

The excel spreadsheet is read in, and the data is loaded into a data table. One of the ways I do this is by taking the spreadsheet document I create, and pass the cell reference into this method here:

dataRow[columnIndex] = GetCellValue(spreadSheetDocument, cell);

private static string GetCellValue(SpreadsheetDocument document, Cell cell)
    {
        //This process uses the OpenXML SDK to get individual cells values to populate the DataTable
        SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
        string value = "";
        //One of the things that needed to be accounted for was empty cells
        try
        {
            value = cell.CellValue.InnerXml;
        }
        catch (Exception)
        {
            value = "";
        }
        //Setting cell data type right now just sets everything to strings
        //Later, the better option will be to work on the Date Conversions and Data Types here
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
        }
        else
        {
            return value;
        }
    }

So as an example, if the cell that it is reading in is 115, then the output looks like this:

114.99999999999999

Then at other times if the value is 125 then the output looks like this:

125.00000000000001

the inconsistency in the output is a bit perplexing. Was hoping maybe I could get some insight into what is causing this, rather than just fixing it in the XSLT later on.

Upvotes: 0

Views: 129

Answers (1)

JasonWH
JasonWH

Reputation: 179

So I found a workaround, more than an actual solution. Apparently this is a bug in the OpenXML SDK. I found the initial documentation that pointed me in this direction here

What I found as a way to work around was this:

private static string GetCellValue(SpreadsheetDocument document, Cell cell)
    {
        //This process uses the OpenXML SDK to get individual cells values to populate the DataTable
        SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
        string value = "";
        //One of the things that needed to be accounted for was empty cells
        try
        {
            value = cell.CellValue.InnerXml;
        }
        catch (Exception)
        {
            value = "";
        }
        //Checking to see if this string contains a decimal with values on either side
        if (Regex.IsMatch(value, regexpattern))
        {
            value = Math.Round(Double.Parse(value), 0, MidpointRounding.AwayFromZero).ToString();
        }
        //Setting cell data type right now just sets everything to strings
        //Later, the better option will be to work on the Date Conversions and Data Types here
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
        }
        else
        {
            return value;
        }
    }

I'm using Regex to determine if this bug is being encountered and then compensating using some rounding. It is interesting to note that only appears to happen with integers.

Thanks!

Upvotes: 1

Related Questions