Document Solutions for Excel, .NET Edition | Document Solutions
Features / Worksheet / Range Operations / Ignore Errors in Cell Range
In This Topic
    Ignore Errors in Cell Range
    In This Topic

    Sometimes, when working with numbers and calculating formulas, Excel evaluates for any errors and points out the errors by showing the green triangle at the top-left corner of the cell. To avoid error evaluation and showing errors with the green triangle, DsExcel provides IgnoredError property in IRange interface and IgnoredErrorType enumeration to enable you to ignore errors such as invalid formula results, numbers stored as text, inconsistent formulas in adjacent cells, and others, and not show the green triangle at the top-left corner of the cell in a specific cell range in Excel. 

    IgnoredError property will not change when copying or cutting rows, columns, or cells, whereas it will move or delete when inserting or deleting rows, columns, or cells. The property will be copied or moved when copying or moving the sheet. IgnoredError property of the top-left cell of the first cell rect will be returned when getting IgnoredError.

    DsExcel supports ignoring the following types of errors:

    Error Type Description
    None No errors are ignored.
    InconsistentListFormula Ignores the error of discrepancies in formulas within a calculated column.
    InconsistentFormula Ignores the error of discrepancies in formulas within a range.
    OmittedCells Ignores the error in cells containing formulas referring to a range that omits adjacent cells that could be included.
    TextDate Ignores the error when formulas contain text-formatted cells with years misinterpreted as the wrong century.
    EmptyCellReferences Ignores the error when a formula contains a reference to an empty cell.
    ListDataValidation Ignores the error of the cell value that does not comply with the Data Validation rule that restricts data to predefined items in a list.
    EvaluateToError Ignores the error of the formula result.
    NumberAsText Ignores the error in cells containing numbers stored as text or preceded by an apostrophe.
    UnlockedFormulaCells Ignores the error in unlocked cells containing formulas.
    All Ignores all types of errors.

    Refer to the following example code to ignore all types of errors in the specified range:

    C#
    Copy Code 
    // Create a new workbook.
var workbook = new Workbook();

// Add data object.
object[,] data = new object[,]{
    {"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
    {"Richard", "New York", new DateTime(1968, 6, 8), "Blue", "67", "165"},
    {"Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", "76", "176"},
    {"Angela", "Washington", new DateTime(1993, 2, 15), "Brown", "68", "145"}
};

// No errors are ignored in this range.
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A:F"].ColumnWidth = 15;
worksheet.Range["A1:F1"].Merge();
worksheet.Range["A1:F1"].Value = "Ignores No Range Errors";
worksheet.Range["A1:F1"].Font.Bold = true;
worksheet.Range["A1:F1"].HorizontalAlignment = HorizontalAlignment.Center;
worksheet.Range["A2:F5"].Value = data;
worksheet.Tables.Add(worksheet.Range["A2:F5"], true);

// Ignores all errors in this range.
worksheet.Range["A7:F7"].Merge();
worksheet.Range["A7:F7"].Value = "Ignores All Range Errors";
worksheet.Range["A7:F7"].Font.Bold = true;
worksheet.Range["A7:F7"].HorizontalAlignment = HorizontalAlignment.Center;
worksheet.Range["A8:F11"].Value = data;
worksheet.Tables.Add(worksheet.Range["A8:F11"], true);

// Ignore error in range A8:F11.
worksheet.Range["A8:F11"].IgnoredError = IgnoredErrorType.All;

// Save Excel file.
workbook.Save("IgnoreRangeError.xlsx");