Possible bug in Excel.Interop's Worksheet.UsedRange property???

Question

hi all...

Take a gander at the following code. Basically, this is a function that gets called in order to identify the used range of a worksheet, loop through that range, and hash social security numbers.

Here's the problem. If I create a spreadsheet and populate one cell, the function does not hash that cell. However, if I populate more than one, it works.

Could this be a bug in the UsedRange property? Or am I missing something?

thanks much.

Woody

try { foreach (Excel.Worksheet ws in _excelWorksheet) { // Grab the range of numbers that populates the XLS. Excel.Range range = ws.UsedRange;

                // In the following cases, Value2 returns different types:
                //
                // 1. The range variable points to a single cell:
                // Value2 returns a object
                //
                // 2. The range variable points to many cells:
                // Value2 returns object[,]

                object[,] values = (object[,])range.Value2;

                for (int row = 1; row <= values.GetUpperBound(0); row++)
                    for (int col = 1; col <= values.GetUpperBound(1); col++)
                    {
                        // Convert values to strings.
                        string value = Convert.ToString(values[row, col]);

                        // Mask the value that we retrieved and store it in a variable.
                        switch (_mdOptions.cbobxEncryption.Text)
                        {
                            case "MD5":
                                {
                                    replaceSSN = SHA2Hash.ComputeHash(value, "MD5", null);
                                    break;
                                }
                            case "SHA256":
                                {
                                    replaceSSN = SHA2Hash.ComputeHash(value, "SHA256", null);
                                    break;
                                }
                            default:
                                {
                                    replaceSSN = SHA2Hash.ComputeHash(value, "SHA256", null);
                                    break;
                                }
                        }

                        // Match expressions to sensitive data and replace with the hash
                        // value.
                        if (Regex.IsMatch(value, @"\b[0-9]{3}-[0-9]{2}-[0-9]{4}\b"))
                        {
                            range.Cells.set_Item(row, col, replaceSSN);
                        }
                        else if (Regex.IsMatch(value, @"\b[0-9]{3}[0-9]{2}[0-9]{4}\b"))
                        {
                            range.Cells.set_Item(row, col, replaceSSN);
                        }
                    }
            }
        }
        catch (Exception)
        {
            // This is here because of a non-fatal error that we're getting with
            // Windows 7 during the hashing phase. Everything still works,
            // it's just that this error keeps popping up.

            // REVIEW FOR WINDOWS 7 COMPATABILITY
            ;

            //MessageBox.Show("There was a major error. Please restart the program.");
            //MessageBox.Show(@"Exception: " + ee);
        }


        // Pull the hashed password from the registry and add it to the SaveAs
        //string saveAsPassword = Convert.ToString(Registry.GetValue(@"HKEY_CURRENT_USER\Software\Mask Data", "Password", ""));
        /*

        _excelWorkbook.SaveAs("Results.xls",
            Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, false,
            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
            Type.Missing, true, Type.Missing,
            Type.Missing, Type.Missing); 
        */
        // Report success.    
        MessageBox.Show("File masked successfully.",
            "Mask Data", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, 
            MessageBoxOptions.DefaultDesktopOnly);

        // Shutdown instance of Excel.
        //_excelApp.Quit();

        // Release memory.
        GC.Collect();
        GC.WaitForPendingFinalizers();
        Marshal.ReleaseComObject(_excelWorkbook);
        Marshal.ReleaseComObject(_excelApp);         
    }

Answer 1

As stated in the comment in your code, the Value2 property returns a single text / number / logical / error value for a single cell or object[,] for multiple cells.

Answer 2

You can't convert a double to a 2 dimensional array of objects, if only because a double has only 1 dimension. Apologies if I am telling you something you already know but the term 'double' means double precision, and has nothing to do with 2 dimensional arrays (as defined by object[,]).

You could simply add a check for this case so the first few lines would look like:

XLS.Excel.Range range = ws.UsedRange;
object[,] values;
if (range.Value2.GetType().IsArray)
{
    values = (object[,])range.Value2;
}
else
{
    values = new object[2,2];
    values[1,1] = range.Value2;
}

I don't have VS on this machine so the code is untested but should be pretty close

EDIT: Having knocked up a quick test app I can see what UsedRange.Value2 is doing - it returns an array of all of the sheet values which is why if there is more than 1 cell it is an array however for one cell it will just return that value (which could be any type). The code above will work however is a bit of a hack. The correct way to get the number of rows and columns is using:

range.Rows.Count

and

range.Columns.Count

If you change your for loops to use these 2 values rather than the array bounds it will solve your problem, and will work for both single and multiple rows