Strange behavior from Excel.Worksheet.Cells [row, column]

Question

I have the following method:

public static object getValue(Excel.Worksheet sheet, int row, int col) { Excel.Range r = sheet.Cells[row, col] as Excel.Range; //I've also tried using sheet.get_Range(cell, cell); here, with the same result if (r.Row != row || r.Column != col) { //Why does this debug statement sometimes print? Debug.Print("Tried to read (" + row.ToString() + ", " + col.ToString() + ") but got (" + r.Row.ToString() + ", " + r.Column.ToString() + ")"); } return r.Value2; } 

Based on my understanding of Excel.Worksheet.Cells [row, column], my code should never enter an if statement. However, when I repeatedly call getValue to read multiple cells, each so often the row and column of the range that it returns are different from the row and column that I called Excel.Worksheet.Cells.

Example output: "I tried to read (19, 1), but got (56, 5)"

Also, if I break the if statement, then rewind my execution point and run Excel.Worksheet.Cells again, I get the correct range.

Why could this be?

Answer 1

This is not an answer to your direct question, but it can solve the problem.

Is getValue in a closed loop? If so, then I will approach him differently. Because Access Cells and Value2 are COM calls, they are slower than regular accessors. If you have a specific range that you are looping, I get an array of the values ​​of that range in memory and access the array directly, and not the loop through the cells.

eg. instead

 for(int r = 1; r <= 10; r++) for(int c = 1; c <= 20; c++) double val = sheet.Cells[r,c]; 

make

 object[,] values = sheet.Range("A1:J20").Value for(int r = 1; r <= 10; r++) for(int c = 1; c <= 20; c++) double val = values[r,c]; 

This can fix your problem by removing COM access from the hard loop and fixing any problem giving you weird results.