[C#] Reading integers from Excel using oledb

Question

Hi, I am using oledb to read data from an excel file and store it in a dataset. My excel file contents are like as follows:

0    0    somestring somestring
500  200  somestring somestring

When i checked the contents of my data set, the values of Columns 1 & 2 are not stored as integers but rather as DateTime values.

How will I make it be stored as integer values instead of DateTime?

Answer 1

Have you tried adding IMEX=1 to your OLEDB connection string?

Answer 2

Are you sure its a number? Following could be a few options:

1. Right click the columns in excel and change the format to Text/Custom.
2. Look into the NamedRange.FormatConditions Property; change the format the data when you read it from excel, see MSDN
3. Or try deleting an existing format on a range:

that is,

Excel.Worksheet sheet = this.Application.ActiveSheet as Excel.Worksheet;
Excel.Range range = sheet.get_Range("A1", "A5") as Excel.Range; 

//delete previous validation rules  
range.Validation.Delete();

Answer 3

You could use a 3rd party component like SpreadsheetGear for .NET which lets you get the underlying values of cells (with IWorkbook.Worksheets["MySheet"].Cells[rowIndex, colIndex].Value) regardless of the cell format, or you can get the formatted result with IRange.Text.

You can see live ASP.NET samples here and download the free trial here.

Disclaimer: I won SpreadsheetGear LLC