Always Get Data as String from Excel file using ExcelDataReader

Question

There is a cell in my Excel file containing this number: 5892101102012990 and other cells containing mixed data (also strings)
I get this cell's data like this :

var filestream = File.Open(@"D:\111XLS\File.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var reader = ExcelReaderFactory.CreateReader(filestream);    
while (reader.Read())
{
    var intt = int.Parse(textBox1.Text);                      
    var v1 = reader.GetValue(intt);
    var v2 = reader.GetValue(intt + 3);

    listBox1.Items.Add(v1 ?? ""); 
    listBox2.Items.Add(v2 ?? "");
}

When it gets to that cell in return I have this : 5.89210110201299E+15 If I change the cell format in Excel file to Special(Excel assumes it's a Zip-code) it will return exact number but editing the Excel file is out of options.

I'm aware that I can get the data using reader.GetDouble(intt); but because of mixed content this will cause more trouble.

Any advice on some sort of option to tell ExcelDataReader to not converting 5892101102012990 to this 5.89210110201299E+15?

is there a way to convert the FileStream's cells to string on the fly and then feed it to ExcelDataReader to fix the problem ? — Mohsen K, May 31 '18 at 09:48

shA.t · Answer 1 · 2018-10-20T08:11:29.203

I can suggest you to use GetFieldType(int index) function like this:

while (reader.Read())
{
    var intt = int.Parse(textBox1.Text);                      
    var v1 = reader.GetValue(intt);
    var v2 = reader.GetValue(intt + 3);

    var fieldType1 = reader.GetFieldType(intt);
    var fieldType2 = reader.GetFieldType(intt + 3);

    var value1 = v1 == null ? null: Convert.ChangeType(v1, fieldType1);
    var value2 = v2 == null ? null: Convert.ChangeType(v2, fieldType2);

    listBox1.Items.Add(value1?.ToString() ?? string.Empty); 
    listBox2.Items.Add(value2?.ToString() ?? string.Empty);
}

And if you want to use a Format-String before adding it to your list, use a code like this:

var valueString1 = value1?.TsString();
if (value1 != null && fieldType1 == typeof(double))
{
    valueString1 = ((double)value1).ToString("####");
}

score 0 · Answer 2 · answered May 31 '18 at 12:06

0

Consider this code:

var number = 5892101102012990D;
Console.WriteLine(number.ToString());
Console.WriteLine(number.ToString("F0"));

Output:

5.89210110201299E+15
5892101102012990

Your results sound like the implicit behaviour of object.ToString(), so this could be a formatting concern. If you want the same output as is displayed in Excel, you may be interested to format the value using the ExcelNumberFormat library

answered May 31 '18 at 12:06

user8728340

646
5
7

can you explain more about "F0" ? is there any reference on ToString("XX") ? (i mean the "XX" Part) – Mohsen K Jun 03 '18 at 13:25
[The Fixed-Point ("F") Format Specifier](https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings#FFormatString) – user8728340 Jun 03 '18 at 17:15

score -1 · Accepted Answer · answered May 31 '18 at 10:55

it seems that i have to answer my own question here after 14 views.

i just tried to convert the 5.89210110201299E+15 to a normal number using this :

Decimal.Parse("5.89210110201299E+15", NumberStyles.AllowExponent | NumberStyles.AllowDecimalPoint);

i don't know if i'm about to ran into a problem later on .

i'm still looking forward to a better solution. any advice will be appreciated.

Always Get Data as String from Excel file using ExcelDataReader

3 Answers3