Document Solutions for Excel, Java Edition | Document Solutions
Features / Worksheet / Range Operations / Get Special Cell Ranges
In This Topic
    Get Special Cell Ranges
    In This Topic

    Special cell ranges refer to the ranges containing specified data type or values. For example, cells containing comments, text values, formulas, blanks, constants, numbers etc.

    DsExcel allows you to get special cell ranges by using specialCells method of IRange interface. It takes the following enumerations as parameters:

    The following table lists the types of cells or values that SpecialCellType and SpecialCellsValue enumerations allow you to find:

    Enumeration Type Description
    SpecialCellType AllFormatConditions Cells of any format condition in the specified range.
    AllValidation Cells having validation criteria in the specified range.
    Blanks Empty cells in the specified range.
    Comments Cells containing notes in the specified range.
    Constants Cells containing constants. Use the SpecialCellsValue to filter values by data types.
    Formulas Cells containing formulas. Use the SpecialCellsValue to filter formulas by return types.
    LastCell The last visible cell in the used range of the worksheet in the specified range.
    MergedCells Merged cells that intersect with the specified range.
    SameFormatConditions Cells having the same format as the top-left cell of the specified range.
    SameValidation Cells having the same validation criteria as the top-left cell of the specified range.
    Visible All visible cells in the specified range.
    Tags Cells containing tags in the specified range.
    SpecialCellsValue Errors Cells with errors.
    Logical Cells with logical values.
    Numbers Cells with numeric values.
    TextValues Cells with text.

    Find Special Cell Ranges by Type

    Refer to the following example code to find the range of special cells by specifying the type of cells.

    Java
    Copy Code 
    // Create a new workbook.
Workbook workbook = new Workbook();
        
// Get active sheet.
IWorksheet ws = workbook.getActiveSheet();

// Add data to the range.
Object[][] rngA1D2 = new Object[][] { { "Register", null, null, null },
        { "Field name", "Wildcard", "Validation error", "User input" } };
ws.getRange("$A$1:$D$2").setValue(rngA1D2);

Object[][] rngA3C6 = new Object[][] { { "User name", "??*", "At least 2 characters" },
        { "Captcha", "?????", "5 characters required" }, { "E-mail", "?*@?*.?*", "The format is incorrect" },
        { "Security code", "#######", "7 digits required" } };
ws.getRange("$A$3:$C$6").setValue(rngA3C6);

Object[][] rngA8D14 = new Object[][] { { "User table", null, null, null }, { "Id", "Name", "Email", "Banned" },
        { 1d, "User 1", "8zgnvlkp2@163.com", true }, { 2d, "User 2", "b9fvaswb@163.com", false },
        { 3d, "User", "md78b", false }, { 4d, "User 4", "1qasghjfg@163.com", false },
        { 5d, "U", "mncx23k8@163.com", false } };
ws.getRange("$A$8:$D$14").setValue(rngA8D14);

ws.getRange("A1:D1").merge();
ws.getRange("A1:D1").setHorizontalAlignment(HorizontalAlignment.Center);
ws.getRange("A8:D8").merge();
ws.getRange("A8:D8").setHorizontalAlignment(HorizontalAlignment.Center);

ws.getRange("A9").setTag("Number type");
ws.getRange("B9").setTag("Text type");
ws.getRange("C9").setTag("Text type");
ws.getRange("D9").setTag("Bool type");

ws.getRange("D3").addComment("Required");
ws.getRange("D4").addComment("Required");
ws.getRange("D5").addComment("Required");
ws.getRange("D6").addComment("Required");

ws.getRange("D10:D14").getValidation().add(ValidationType.List, ValidationAlertStyle.Stop,
        ValidationOperator.Between, "True,False", null);

IFormatCondition condition = (IFormatCondition) ws.getRange("C10:C14").getFormatConditions().add(
        FormatConditionType.Expression, FormatConditionOperator.Between, "=ISERROR(MATCH($B$5,C10,0))", null);
condition.getFont().setColor(Color.GetRed());

IFormatCondition condition2 = (IFormatCondition) ws.getRange("B10:B14").getFormatConditions()
        .add(FormatConditionType.Expression, FormatConditionOperator.Between, "=LEN(B10)<=2", null);
condition2.getFont().setColor(Color.GetRed());

ws.getRange("4:4").getEntireRow().setHidden(true);

IRange searchScope = ws.getRange("1:14");

// Find comments.
IRange comments = searchScope.specialCells(SpecialCellType.Comments);

// Find last cell.
IRange lastCell = searchScope.specialCells(SpecialCellType.LastCell);

// Find visible.
IRange visible = searchScope.specialCells(SpecialCellType.Visible);

// Find blanks.
IRange blanks = searchScope.specialCells(SpecialCellType.Blanks);

// Find all format conditions.
IRange allFormatConditions = searchScope.specialCells(SpecialCellType.AllFormatConditions);

// Find all validation.
IRange allValidation = searchScope.specialCells(SpecialCellType.AllValidation);

// Find same format condition as B10.
IRange sameFormatConditions = ws.getRange("B10").specialCells(SpecialCellType.SameFormatConditions);

// Find same validation as D10.
IRange sameValidation = ws.getRange("D10").specialCells(SpecialCellType.SameValidation);

// Find merged cells.
IRange merged = searchScope.specialCells(SpecialCellType.MergedCells);

// Find cells containing tags.
IRange tagCells = searchScope.specialCells(SpecialCellType.Tags);

// Add output of above search to the range.
ws.getRange("A16").setValue("Find result");
ws.getRange("A16:C16").merge();
ws.getRange("A16:C16").setHorizontalAlignment(HorizontalAlignment.Center);
ws.getRange("$A$17:$A$26").setValue(new Object[][] { { "Comments" }, { "LastCell" }, { "Visible" }, { "Blanks" },
                { "AllFormatConditions" }, { "AllValidation" }, { "SameFormatConditions B10" },
                { "SameValidation D10" }, { "MergedCells" }, { "TagCells" } });
ws.getRange("$C$17:$C$26").setValue(new Object[][] { { comments.getAddress() }, { lastCell.getAddress() },
                { visible.getAddress() }, { blanks.getAddress() }, { allFormatConditions.getAddress() },
                { allValidation.getAddress() }, { sameFormatConditions.getAddress() },
                { sameValidation.getAddress() }, { merged.getAddress() }, { tagCells.getAddress() } });

ws.getUsedRange().getEntireColumn().autoFit();
    
// Save the excel file.
workbook.save("SpecialCellsFindMiscellaneous.xlsx");

    Find Special Cells by Type in Existing File

    Refer to the following example code to load an existing file, find special cells containing formulas and constants and change their background color.

    Java
    Copy Code 
    // Create a new workbook
Workbook workbook = new Workbook();
workbook.open("FinancialReport.xlsx");
IRange cells = workbook.getActiveSheet().getCells();
// Find all formulas
IRange allFormulas = cells.specialCells(SpecialCellType.Formulas);
// Find all constants
IRange allConstants = cells.specialCells(SpecialCellType.Constants);
// Change background color of found cells
allFormulas.getInterior().setColor(Color.GetLightGray());
allConstants.getInterior().setColor(Color.GetDarkGray());
// Save to an excel file
workbook.save("SpecialCellsInExistingFiles.xlsx");

    Find Special Cells by Type and Values

    Refer to the following example code to find special cells by specifying cell type and values.

    Java
    Copy Code 
    // create a new workbook
Workbook workbook = new Workbook();
IWorksheet ws = workbook.getActiveSheet();

// Set data
ws.getRange("A1").setFormula("=\"Text \" & 1");
ws.getRange("B1").setFormula("=8*10^6");
ws.getRange("C1").setFormula("=SEARCH(A1,9)");
ws.getRange("A2").setValue("Text");
ws.getRange("B2").setValue(1);

// Find text formulas
IRange textFormula = ws.getCells().specialCells(SpecialCellType.Formulas, SpecialCellsValue.TextValues);
// Find number formulas
IRange numberFormula = ws.getCells().specialCells(SpecialCellType.Formulas, SpecialCellsValue.Numbers);
// Find error formulas
IRange errorFormula = ws.getCells().specialCells(SpecialCellType.Formulas, SpecialCellsValue.Errors);
// Find text values
IRange textValue = ws.getCells().specialCells(SpecialCellType.Constants, SpecialCellsValue.TextValues);
// Find number values
IRange numberValue = ws.getCells().specialCells(SpecialCellType.Constants, SpecialCellsValue.Numbers);

// Display search result
ws.getRange("A4:E5").setValue(new Object[][] {
                { "Text formula", "Number Formula", "Error Formula", "Text Value", "Number Value" },
                { textFormula.getAddress(), numberFormula.getAddress(), errorFormula.getAddress(),
                  textValue.getAddress(), numberValue.getAddress() } });

ws.getUsedRange().getEntireColumn().autoFit();

// save to an excel file
workbook.save("SpecialCellsQuickStart.xlsx");

    Refer to the following example code to find special cell ranges by cell type and values. The formatting of cells is defined to easily distinguish between different types of special cells.

    Java
    Copy Code 
    // Create a new workbook
Workbook workbook = new Workbook();
IWorksheet ws = workbook.getWorksheets().get(0);

ws.getRange("A1:F1").setValue(new Object[][] {
        { "Test id", "Group id", "Group item id", "New test id", "Test result", "Error code" } });

ws.getRange("B2:C2").setValue(1d);
ws.getRange("E2,E7,E12,E21,E27,E36,E40,E47:E48,E51,E59:E60,E70:E71,E80:E81,E88,E90:E91")
        .setValue("Error 80073cf9");
ws.getRange("G1:G2,I1:I7,H8:I8,A93:B93,E93:F93").setValue(null);

ws.getRange("H1:H7").setValue(new Object[][] { { "Constants" }, { "Formulas" }, { "String constants" },
        { "Number constants" }, { "String formulas" }, { "Number formulas" }, { "Error formulas" } });

ws.getRange("A2:A13").setValue("Test00001");
ws.getRange("A14:A67").setValue("Test00153");
ws.getRange("A68:A92").setValue("Test05789");
ws.getRange("E3:E5,E9:E11,E25:E26,E37:E38,E57,E75:E76,E86:E87").setValue("Runtime Error c0000005");
ws.getRange("E6,E13:E20,E28:E35,E41:E46,E52:E56,E61:E64,E72:E74,E77:E78,E82:E85,E89,E92").setValue("Passed");
ws.getRange("E8,E22:E24,E39,E49:E50,E58,E65:E69,E79").setValue("Deploy Error 80073cf9");

ws.getRange("D2:D92").setFormulaR1C1("=\"X-Test-G\" & RC[-2] & \"-I\" & RC[-1]");
ws.getRange("B3:B92").setFormulaR1C1("=IF(RC[-1]=R[-1]C[-1],R[-1]C,R[-1]C+1)");
ws.getRange("C3:C92").setFormulaR1C1("=IF(RC[-2]=R[-1]C[-2],R[-1]C+1,1)");
ws.getRange("F2:F92").setFormulaR1C1("=MID(RC[-1], SEARCH(\"Error \",RC[-1])+6,8)");

Color constantBgColor;
Color formulasBgColor;
Color stringForeColor;
Color errorForeColor;
{
    constantBgColor = Color.FromArgb((int) 0xFFDDEBF7);
    formulasBgColor = Color.FromArgb((int) 0xFFF2F2F2);
    stringForeColor = Color.FromArgb((int) 0xFF0000C0);
}
errorForeColor = Color.GetDarkRed();
IRange searchScope = ws.getRange("A:F");

// Find constant cells and change background color
IRange allConsts = searchScope.specialCells(SpecialCellType.Constants);
allConsts.getInterior().setColor(constantBgColor);

// Find formula cells and change background color
IRange allFormulas = searchScope.specialCells(SpecialCellType.Formulas);
allFormulas.getInterior().setColor(formulasBgColor);

// Find text constant cells and change foreground color
IRange textConsts = searchScope.specialCells(SpecialCellType.Constants, SpecialCellsValue.TextValues);
textConsts.getFont().setColor(stringForeColor);

// Find text formula cells and change foreground color
IRange textFormulas = searchScope.specialCells(SpecialCellType.Formulas, SpecialCellsValue.TextValues);
textFormulas.getFont().setColor(stringForeColor);

// Find number constant cells and change font weight
IRange numberConsts = searchScope.specialCells(SpecialCellType.Constants, SpecialCellsValue.Numbers);
numberConsts.getFont().setBold(true);

// Find number formula cells and change font weight
IRange numberFormulas = searchScope.specialCells(SpecialCellType.Formulas, SpecialCellsValue.Numbers);
numberFormulas.getFont().setBold(true);

// Find error formula cells and change foreground color and font style
IRange errorFormulas = searchScope.specialCells(SpecialCellType.Formulas, SpecialCellsValue.Errors);
errorFormulas.getFont().setColor(errorForeColor);
errorFormulas.getFont().setItalic(true);

// Set sample cell styles
ws.getRange("H1,H3,H4").getInterior().setColor(constantBgColor);
ws.getRange("H2,H5:H7").getInterior().setColor(formulasBgColor);
ws.getRange("H3,H5").getFont().setColor(stringForeColor);
ws.getRange("H4,H6").getFont().setBold(true);
ws.getRange("H7").getFont().setColor(errorForeColor);
ws.getRange("H7").getFont().setItalic(true);

ws.getUsedRange().getEntireColumn().autoFit();

// Save to an excel file
workbook.save("SpecialCellsFindValuesAndFormulas.xlsx");

    Limitations

    When the result contains cell ranges with multiple adjoining rectangles, the merging strategy in DsExcel is different from Excel.

    For example, if you find number constants with Excel, the result is $A$2:$C$3,$C$4:$D$4

    Whereas with DsExcel, the result is $A$2:$B$3,$C$2:$C$4,$D$4