How to validate Excel range that it has error/warning using .NET?

Question

I want to validate range of excel worksheet (e.g. "A10:B20") to check that is it has error or NA value or not? How to do that in C#?

P.S. I find similar topic (Excel range usage question (cell error checking)) but that topic is not thing I need.

Answer 1

This might help you.First read the data from the excel.Please see this stack over flow question. http://stackoverflow.com/questions/2458481/convert-excel-range-to-ado-net-dataset-or-datatable-etc. Then iterate each row from the data table like

    foreach (DataRow row in sheetTable.Rows)
    {
        foreach (DataColumn column in sheetTable.Columns)
        {
            // Check what ever you want to check
            if (row[column].ToString().Equals("Error") || row[column] ==null)
            {
                // do something
            }
        }
    }

Answer 2

The key is to check for the data type of the value held in the cell. If the data type is an Integer (Int32), then the value held is a CVErr value. To check for #N/A, the cell would be an Integer data type (not a Double!) holding the value -2146826246.

For more details, see the stack overflow question How to know if a cell has an error in the formula in C#.