CODEWITHSUNDEEP
c#excel
KeithDB
KeithDB

Reputation: 425

Reading integers from Excel using oledb

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?

Upvotes: 1

Views: 1603

Answers (3)

Joe Erickson
Joe Erickson

Reputation: 7227

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

Upvotes: 0

Kamran Khan
Kamran Khan

Reputation: 9986

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();

Upvotes: 0

Ed Harper
Ed Harper

Reputation: 21505

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

Upvotes: 4

Related Questions