0

I've got an excelsheet which is generated by a thirdparty which I need to read data from. One column is formated as percentage in the excelsheet and when I read data from the sheet I get eg. 27%. But If I open excel and click the cell, I can see that the actual value of the cell is 0,27673528. How can I get that value without opening the excelfile and modifying the column type?

I'm using the connectionstring:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Chr(34) & uploadFileName & Chr(34) & ";Extended Properties=" & Chr(34) & "Excel 8.0;HDR=NO;IMEX=1;" & Chr(34)
user3532232
  • 257
  • 8
  • 19

2 Answers2

0

you can change the Column type through code, that not require to open the sheet.

 xlYourRange.NumberFormat = "@";  //for text
 xlYourRange.Value = "'0123456";   // for number

For more details to change the format/Type see here :Set data type like number, text and date in excel column using Microsoft.Office.Interop.Excel in c#

Community
  • 1
  • 1
sumngh
  • 566
  • 2
  • 10
0

Well would you know. Ran into excel/Oledb problmes again and thought I google it. And apparently I've already posted a question here.

Though this time I've found some sort of solution.

Since I only have the JET engine to work with this answer is only valid for that, I guess.

Problem: Say I have a cell in excel with underlying value 2017-09-01 14:40:37. Though the cell only displays 2017-09-01 14:40. When I retreive the value with OleDbConnection and JET and IMEX=1 then I only receive 2017-09-01 14:40.

Solution: To get the whole value I have to set IMEX=0.

Caveat: The JET engine makes a Column-Type assumption based on the first rows. I had text in the forst rows so alla dates were ignored on the fetch. I made a workaround by first updating the cells that didn't have a date value to null. And then I fetched all the values with IMEX=0

user3532232
  • 257
  • 8
  • 19