Getting float number instead of exact string while reading from excel using openxml

Question

I have a code to read Excel file into data-table using OpenXml, it works fine but however, when I read a string like this "7.5" it gives me "7.4999999" in float format why is it so? I just want to read it as it is i.e in the string. so this should "7.5" should be 7.5 only not 7.499 or any other thing. any help would be appreciated. here is my code to read Excel in datatable

private static string ConvertFileToDataTable(Stream fileStream, DataTable framworkDatatable, List<FileErrorModel> fileErrorModels)
    {
        string frameworkName;

        InitializeDataTable(framworkDatatable);

        using (var document = SpreadsheetDocument.Open(fileStream, true))
        {
            var workbookPart = document.WorkbookPart;

            var mysheet = (Sheet)document.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0);

            frameworkName = mysheet.Name;

            var worksheet = ((WorksheetPart)workbookPart.GetPartById(mysheet.Id)).Worksheet;

            var sheetData = (SheetData)worksheet.ChildElements.GetItem(4);

            foreach (var row in sheetData.Descendants<Row>())
            {
                if (row == null) continue;
                if (row.RowIndex.Value == Constants.ColumnNameRowIndex)
                {
                    if (row.Descendants<Cell>().Count() > Constants.ValidColumnCount || row.Descendants<Cell>().Count() < Constants.ValidColumnCount)
                    {
                        var error = new FileErrorModel
                        {
                            ErrorText = ValidationMessages.IncompatibleCoulmns,
                            RowNumber = null,
                            ColumnNumber = null
                        };
                        fileErrorModels.Add(error);
                    }
                }

                if (row.RowIndex.Value == Constants.TableNameRowIndex ||
                    row.RowIndex.Value == Constants.ColumnNameRowIndex) continue;

                framworkDatatable.Rows.Add();
                var i = 0;

                foreach (var cell in row.Descendants<Cell>())
                {
                    framworkDatatable.Rows[framworkDatatable.Rows.Count - 1][i] = GetCellValue(document, cell);
                    i++;
                }
            }
        }
        return frameworkName;
    }

this is how I initialize Datatable

private static void InitializeDataTable(DataTable framworkDatatable)
    {
        framworkDatatable.Columns.AddRange(new[] {
                new DataColumn(ImportSheetColumnNames.ControlFamilyIdentifier, typeof(string)),
                new DataColumn(ImportSheetColumnNames.ControlFamilyShortName, typeof(string)),
                new DataColumn(ImportSheetColumnNames.ControlFamilyDescription,typeof(string)),
                new DataColumn(ImportSheetColumnNames.ControlIdentifier,typeof(string)),
                new DataColumn(ImportSheetColumnNames.ControlType,typeof(string)),
                new DataColumn(ImportSheetColumnNames.ControlDescription,typeof(string)),
                new DataColumn(ImportSheetColumnNames.TestProcedure,typeof(string)),
                new DataColumn(ImportSheetColumnNames.BestPractice,typeof(string)),
                new DataColumn(ImportSheetColumnNames.Help,typeof(string)),
                new DataColumn(ImportSheetColumnNames.ActionPlan,typeof(string)),
                new DataColumn(ImportSheetColumnNames.Question,typeof(string))
        });
    }

ignore "ImportSheetColumnNames", they are just constants column names

score 0 · Answer 1 · answered Aug 08 '18 at 07:23

0

In short: (binary) floating point numbers cannot exactly represent the decimal 7.5. No reason to believe that something is wrong with your code. More details e.g. here: Why can't decimal numbers be represented exactly in binary?

answered Aug 08 '18 at 07:23

Stefan Hubert

18
4

thanks for the answer but can you just read my edited version of question again? i want to read it in string as it is. – Faraaz Aug 08 '18 at 07:29

Flydog57 · Answer 2 · 2018-08-15T20:10:54.507

What do you see if you open the sheet in Excel (not in the cell, but in the formula bar)?

If I create a new Excel spreadsheet and enter the following values in to the corresponding cells:

A1 = 7.5
A2 = 7.4999999
A3 = 7.49  (but formatted to have a single decimal point showing)

I see all three values as 7.5 in the sheet, but I see those values in the formula bar.

Now, if I look in the file (using the Open XML Productivity Tool - downloadable from Microsoft.com and essential for any Open XML work), I see the following saved in the file,under

filename
- /xl/workbook.xml
  - /xl/worksheets/sheet1.xml
    - x:worksheet
      - x:sheetData:

:

<x:sheetData xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:row r="1" spans="1:1" x14ac:dyDescent="0.25" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <x:c r="A1">
      <x:v>7.5</x:v>
    </x:c>
  </x:row>
  <x:row r="2" spans="1:1" x14ac:dyDescent="0.25" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <x:c r="A2">
      <x:v>7.4999998999999997</x:v>
    </x:c>
  </x:row>
  <x:row r="3" spans="1:1" x14ac:dyDescent="0.25" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <x:c r="A3" s="1">
      <x:v>7.49</x:v>
    </x:c>
  </x:row>
</x:sheetData>

That's what's in the file (the <x:v/> element represents "value"). Excel doesn't store the "string in the cell", it stores the value, and it also stores everything in the environment that describes how to display things to the user. A value of 7.4999999 is pretty much 7.5, and is shown as 7.5 in Excel, but it's stored as 7.4999999. The 7.5 is not stored, and there's no way to recreate it unless you try to recreate all of Excel's display rules.

Getting float number instead of exact string while reading from excel using openxml

2 Answers2