Possible error in Excel.Interop property Worksheet.UsedRange?

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

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

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

Many thanks.

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 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);         
}

      

+2


source to share


2 answers


You cannot convert a double to a 2-dimensional array of objects, if only because double is only 1 size. Apologies if I tell you what you already know, but the term "double" means double precision and has nothing to do with two-dimensional arrays (as defined by the [,] object).

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

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: Choosing a quick test app, I can see what UsedRange.Value2 does - it returns an array of all sheet values, so if there is more than one cell, it is an array, but for one cell, it will just return that value (which can be of any type ). The above code will work, but this is a bit of a hack. The correct way to get the number of rows and columns is:



range.Rows.Count

      

and

range.Columns.Count

      

If you change your for loops to use these 2 values ​​and not the bounds of the array, this will solve your problem and will work for both single and multiple lines.

+4


source


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



+2


source







All Articles