Document Solutions for Excel, Java 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 setIgnoredError method 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. 

    setIgnoredError method 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 method will be copied or moved when copying or moving the sheet. setIgnoredError method 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
    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.

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

    Java
    Copy Code 
    // Create a new workbook.
Workbook workbook = new Workbook();
        
// Add data object.
Object[][] data = new Object[][]{
        {"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
        {"Richard", "New York", LocalDateTime.of(1968, 6, 8, 0, 0, 0), "Blue", "67", "165"},
        {"Damon", "Washington", LocalDateTime.of(1986, 2, 2, 0, 0, 0), "Hazel", "76", "176"},
        {"Angela", "Washington", LocalDateTime.of(1993, 2, 15, 0, 0, 0), "Brown", "68", "145"}
};

// No errors are ignored in this range.
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("A:F").setColumnWidth(15);
worksheet.getRange("A1:F1").merge();
worksheet.getRange("A1:F1").setValue("Range errors not ignored");
worksheet.getRange("A1:F1").getFont().setBold(true);
worksheet.getRange("A1:F1").setHorizontalAlignment(HorizontalAlignment.Center);
worksheet.getRange("A2:F5").setValue(data);
worksheet.getTables().add(worksheet.getRange("A2:F5"), true);

// Ignores all errors in this range.
worksheet.getRange("A7:F7").merge();
worksheet.getRange("A7:F7").setValue("Range errors ignored");
worksheet.getRange("A7:F7").getFont().setBold(true);
worksheet.getRange("A7:F7").setHorizontalAlignment(HorizontalAlignment.Center);
worksheet.getRange("A8:F11").setValue(data);
worksheet.getTables().add(worksheet.getRange("A8:F11"), true);
        
// Ignore error in range A8:F11.
worksheet.getRange("A8:F11").setIgnoredError(EnumSet.allOf(IgnoredErrorType.class));
    
// Save Excel file.
workbook.save("IgnoreRangeError.xlsx");