CODEWITHSUNDEEP
c#visual-studio-2008.net-3.5vstoexcel-2010
ForeverWintr
ForeverWintr

Reputation: 6028

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

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 the if statement. However, when I call getValue repeatedly to read multiple cells, every so often the row and column of the range it returns are different than the row and column I called Excel.Worksheet.Cells with.

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

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

Why could this be?

Upvotes: 4

Views: 1657

Answers (1)

D Stanley
D Stanley

Reputation: 152521

This isn't an answer to your direct question, but may solve the problem.

Is getValue in a tight loop? If so then I would approach it a different way. Since Accessing Cells and Value2 are COM-calls they are slower than normal array accessors. If you have a particular range you're looping over I would get the array of values for that range in memory and access the array directly rather than looping through the cells.

e.g. instead of

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

do

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 may fix your problem by removing the COM access from tight loop and eliminating whatever problem is giving you the strange results.

Upvotes: 1

Related Questions