I'm using OpenXmlReader in a C# program, to read excel files. For my purposes (comparison of values), I need the exact cell values, without any modifications.
However, OpenXmlReader interprets certain Excel cell value types (concretely, percentages, dates and times) in an irritating way, that I seemingly have no control over.
Imagine this to be an Excel row (what I expect to be read):
BNK | BNK_DC | GROUP | GROUP | 8 | 0,12% | 0,14% | 0,15% | 0,17% | 0,18%
This is how the row appears on my debug console:
BNK | BNK_DC | GROUP | GROUP | 8 | 1.1561E-3 | 1.3554245994127801E-3 | 1.5389154190192354E-3 | 1.7010741265663695E-3 |1.8389123386463213E-3
Basically, the strings are being read correctly, the simple integer as well. However, the percentages are transformed into something I can't grasp or work with.
Something similar happens when I try to read dates or times.
The excel file row:
04.04.2018 | Adam Smith | 09:30 | 17:30 | 8,0 | Apr | Q2
What OpenXmlReader returns:
43194 | Adam Smith | 0.39583333333333331 | 0.72916666666666663 | 8 | Apr | Q2
As you see, the date and times have been transformed into something I don't understand.
I guess this may have to do with a certain way percentages, times and dates are being stored internally by Excel. If so, how do I transform these values back into a readable format?
This is the exact code I use to read Excel files with the OpenXmlReader class and display them onto my Debug console:
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Diagnostics;
public void ExcelReader(SpreadsheetDocument document)
{
var wbp = document.WorkbookPart;
var wsp = wbp.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(wsp);
string text;
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
string cellValue;
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
SharedStringItem ssi = wbp.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));
cellValue = ssi.Text.Text;
}
else
{
cellValue = c.CellValue?.InnerText;
}
Debug.Write($"{cellValue} | ");
}
} while (reader.ReadNextSibling());
Debug.Write("\n");
}
}
document.Close();
}
I took this code mostly from the first answer to the following thread: Using OpenXmlReader
So, how do I get my actual percentages, dates, times from Excel, instead of these strange values? Thank you in advance.
