0

I need to give the value of Range into a multidimensional object but error occurs:

Cannot implicitly convert type object to object[*,*]. An explicit conversion exists (are you missing a cast?)

Here is the code:

Excel.Application oXL = new Excel.Application();
Excel.Workbook oWB = oXL.Workbooks.Open(rtbExcelPath.Text.Trim(), Missing.Value, 
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
Missing.Value, Missing.Value, Missing.Value);

Excel.Worksheet oWS = oWB.Worksheets[1] as Excel.Worksheet;

int countRows = oWS.UsedRange.Rows.Count;
int countColumns = oWS.UsedRange.Columns.Count;

object[,] data = oWS.Range[oWS.Cells[1, 1], oWS.Cells[countRows, 
countColumns]].Cells.Value2;

Please help to check what is wrong... :(

ArcX
  • 687
  • 8
  • 20
user3200634
  • 5
  • 4
  • 1
    Which line throws the error? Is it when you are trying to open the workbook? – Zac Sep 24 '19 at 11:52
  • this one: object[,] data = oWS.Range[oWS.Cells[1, 1], oWS.Cells[countRows, countColumns]].Cells.Value2; – user3200634 Sep 24 '19 at 11:53
  • Value2 returns the value of a specific cell, it won't be an array. Google "c# excel copy range into array" to find code, too many hits to recommend the best one. – Hans Passant Sep 24 '19 at 12:31

2 Answers2

0

I was able to resolve your issue by using the dynamic variable type instead of explicit type object[,].

dynamic data = oWS.Range[oWS.Cells[1, 1], oWS.Cells[countRows, 
countColumns]].Cells.Value2;
Udo
  • 76
  • 1
  • 3
  • how can i cast it to multidimensional object? because i need to have a multidimensional object array for my codes.. – user3200634 Sep 24 '19 at 12:06
  • Without digging into the Interop.Excel namespace could you extract each cell value to your multi-dimensional array with nested looping? – Udo Sep 24 '19 at 12:15
  • I need to have a nested loop that's why I need to have multidimensional object array.. Because what I really want is to use the answer from https://stackoverflow.com/questions/17577184/importing-excel-into-a-datatable-quickly but I cannot do it because I am not able to succesfully have the object[,] data – user3200634 Sep 24 '19 at 12:21
  • I added an example of extracting the cell values to a multi-dimensional array. Hopefully that gets you moving towards a valid solution. – Udo Sep 24 '19 at 12:22
  • I need to have a nested loop that's why I need to have multidimensional object array.. Because what I really want is to use the answer from stackoverflow.com/questions/17577184/… but I cannot do it because I am not able to succesfully have the object[,] data – user3200634 Sep 24 '19 at 12:25
  • that same nested loops you added is the one I'm trying to solve that's why I need the multidimensional object array – user3200634 Sep 24 '19 at 12:28
  • @user3200634, I did some additional digging based upon your comments. The interface seems odd to me, but I was able to get it working with ```dynamic```. You'll need to add a reference to the Microsoft.CSharp built-in library if your application does not already have it. – Udo Sep 24 '19 at 14:00
0

The problem will occur when oWS.UsedRange references a single cell. In this case, the object returned is a single value and not an array. If you want an array, you will need to test for this case and explicitly create it.

object[,] data;
if (oWS.UsedRange.Cells.CountLarge == 1)
{
    // create a single cell two dimensional array with 1 based 
    // lower bounds to simular to an array returned by Excel
    data = (object[,])Array.CreateInstance(typeof(object), new[]{1, 1}, new[]{1, 1});
    data[1, 1] = oWS.UsedRange.Value2;
}
else
{
    data = oWS.UsedRange.Value2;
}
TnTinMn
  • 11,522
  • 3
  • 18
  • 39