C# / Excel: Convert Excel Range to Array with Correct Data Type

Question

I opened *.htm file with Excel Application (Microsoft.Office.Interop.Excel). It was parsed excellent! So I can work with it. For more speed, I'm trying to get data from Excel Range and insert into System.Array and work with it:

Excel.Range range = ExcelWorksheet.get_Range("A1", "H1500"); // get all values
System.Array dataArray = (System.Array)(range.Cells.Value2); // insert into array

Problem is with data type. If Excel cell has time or date format, range.Cells.Value2 makes:
12.06.2012 to 41072 (Excel Cell Type - date)
14:48 to 0,616666666666667 (Excel Cell Type - time)
If I get single value form Excel Cell, I get correct value (with Cells.Text.ToString()):

ExcelWorksheet.get_Range("A1", "A1").Cells.Text.ToString()

Task: I need get values from Excel Sheet as they are, just like text, not as another type.
And don't want Excel thinks instead of me :)

blins · Accepted Answer · 2012-06-24 18:38:38Z

5

Why don't you just convert the returned values to the format you need? The FromOADate method of DateTime is designed for this (see http://msdn.microsoft.com/en-us/library/1ad4d8d6(v=vs.80).aspx). For example, the time value returned represents a fractional portion of a 24 our day. Hence the following example will output "14:48":

double oleDateTime = 0.616666666666667;
DateTime dt = DateTime.FromOADate(oleDateTime);
string time = dt.ToString("H:mm");
Console.WriteLine(time);

As far as the date values, you can use the same approach. The only difference will be the fact that the value from Excel (double) would be greater than zero because it is includes a date portion (not just time). The following will yield "12.06.2012":

dt = DateTime.FromOADate(41072);
string date = dt.ToString("dd.MM.yyyy");
Console.WriteLine(date);

To further illustrate, in the case that you are dealing with date AND time (returned Excel value is greater than zero), the following will yield "6/12/2012 2:48:00 PM":

dt = DateTime.FromOADate(41072.616666666666667);
Console.WriteLine(dt.ToString());

edited Jun 24, 2012 at 18:38

answered Jun 24, 2012 at 17:52

blins

2,53521 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chaki_Black Over a year ago

Of course you are right. The problem was too, when I convert range to array System.Array dataArray = (System.Array)(range.Cells.Value2), object cells of array (the same as object[,] dataArray) stores info of data type of base excel cell. So I can know, if cell of data array is datetime type like if(dataArray.GetValue(1,1) is DateTime) I can do actions above. I solved this problem like this: object[,] dataArray = (object[,])range.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);. DataTime values represent like '2012-06-01 0:00:00'. It is possible to work with them too.

Nazir A · Accepted Answer · 2013-03-11 09:08:48Z

0

Use this method when you want to read exactly what is visible/displayed in the Excel file.

Excel.Range range = workSheet.get_Range("A1", "D4");

int totalRows = range.Rows.Count;
int totalColumns = range.Columns.Count;
for (int rowCounter = 1; rowCounter <= totalRows; rowCounter++)
{
  for (int colCounter = 1; colCounter <= totalColumns; colCounter++)
  {
     var cellVal = workSheet.Cells[rowCounter, colCounter];
     var val = cellVal.Text;
  }
}

answered Mar 11, 2013 at 9:08

Nazir A

1213 silver badges4 bronze badges

1 Comment

sergeidave Over a year ago

This method is reading the values cell by cell, which is very slow compared to grabbing the values from the Excel Range as an array...

Collectives™ on Stack Overflow

C# / Excel: Convert Excel Range to Array with Correct Data Type

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related