Get Special Cell Ranges | Documents for Excel .NET Edition

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.

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

SpecialCellType: Specifies the type of cells like formula, constant, blank etc.
SpecialCellsValue: Specifies cells with a particular type of value like numbers, text values etc.

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.

C#	Copy Code
//create a new workbook var workbook = new Workbook(); IWorksheet ws = workbook.ActiveSheet; //prepare data var rngA1D2 = new object[,] { { "Register", null, null, null}, { "Field name", "Wildcard", "Validation error", "User input"} }; ws.Range["$A$1:$D$2"].Value = rngA1D2; var 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.Range["$A$3:$C$6"].Value = rngA3C6; var 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.Range["$A$8:$D$14"].Value = rngA8D14; ws.Range["A1:D1"].Merge(); ws.Range["A1:D1"].HorizontalAlignment = HorizontalAlignment.Center; ws.Range["A8:D8"].Merge(); ws.Range["A8:D8"].HorizontalAlignment = HorizontalAlignment.Center; ws.Range["D3"].AddComment("Required"); ws.Range["D4"].AddComment("Required"); ws.Range["D5"].AddComment("Required"); ws.Range["D6"].AddComment("Required"); ws.Range["D10:D14"].Validation.Add( ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "True,False"); var condition = (IFormatCondition)ws.Range["C10:C14"].FormatConditions.Add( FormatConditionType.Expression, formula1: "=ISERROR(MATCH($B$5,C10,0))"); condition.Font.Color = Color.Red; var condition2 = (IFormatCondition)ws.Range["B10:B14"].FormatConditions.Add( FormatConditionType.Expression, formula1: "=LEN(B10)<=2"); condition2.Font.Color = Color.Red; ws.Range["4:4"].EntireRow.Hidden = true; IRange searchScope = ws.Range["1:14"]; // Find comments var comments = searchScope.SpecialCells(SpecialCellType.Comments); // Find last cell var lastCell = searchScope.SpecialCells(SpecialCellType.LastCell); // Find visible var visible = searchScope.SpecialCells(SpecialCellType.Visible); // Find blanks var blanks = searchScope.SpecialCells(SpecialCellType.Blanks); // Find all format conditions var allFormatConditions = searchScope.SpecialCells(SpecialCellType.AllFormatConditions); // Find all validation var allValidation = searchScope.SpecialCells(SpecialCellType.AllValidation); // Find same format condition as B10 var sameFormatConditions = ws.Range["B10"].SpecialCells(SpecialCellType.SameFormatConditions); // Find same validation as D10 var sameValidation = ws.Range["D10"].SpecialCells(SpecialCellType.SameValidation); // Find merged cells var merged = searchScope.SpecialCells(SpecialCellType.MergedCells); // Output ws.Range["A16"].Value = "Find result"; ws.Range["A16:C16"].Merge(); ws.Range["A16:C16"].HorizontalAlignment = HorizontalAlignment.Center; ws.Range["$A$17:$A$25"].Value = new object[,] { {"Comments"}, {"LastCell"}, {"Visible"}, {"Blanks"}, {"AllFormatConditions"}, {"AllValidation"}, {"SameFormatConditions B10"}, {"SameValidation D10"}, {"MergedCells"} }; ws.Range["$C$17:$C$25"].Value = new object[,] { {comments.Address}, {lastCell.Address}, {visible.Address}, {blanks.Address}, {allFormatConditions.Address}, {allValidation.Address}, {sameFormatConditions.Address}, {sameValidation.Address}, {merged.Address} }; ws.UsedRange.EntireColumn.AutoFit(); //save to an excel file workbook.Save("specialcellsfindmiscellaneous.xlsx");

Copy Code

//create a new workbook
var workbook = new Workbook();

IWorksheet ws = workbook.ActiveSheet;

//prepare data
var rngA1D2 = new object[,] {
{ "Register", null, null, null},
{ "Field name", "Wildcard", "Validation error", "User input"}
};
ws.Range["$A$1:$D$2"].Value = rngA1D2;

var 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.Range["$A$3:$C$6"].Value = rngA3C6;

var 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.Range["$A$8:$D$14"].Value = rngA8D14;

ws.Range["A1:D1"].Merge();
ws.Range["A1:D1"].HorizontalAlignment = HorizontalAlignment.Center;
ws.Range["A8:D8"].Merge();
ws.Range["A8:D8"].HorizontalAlignment = HorizontalAlignment.Center;

ws.Range["D3"].AddComment("Required");
ws.Range["D4"].AddComment("Required");
ws.Range["D5"].AddComment("Required");
ws.Range["D6"].AddComment("Required");

ws.Range["D10:D14"].Validation.Add(
    ValidationType.List, ValidationAlertStyle.Stop,
    ValidationOperator.Between, "True,False");

var condition = (IFormatCondition)ws.Range["C10:C14"].FormatConditions.Add(
    FormatConditionType.Expression, formula1: "=ISERROR(MATCH($B$5,C10,0))");
condition.Font.Color = Color.Red;

var condition2 = (IFormatCondition)ws.Range["B10:B14"].FormatConditions.Add(
    FormatConditionType.Expression, formula1: "=LEN(B10)<=2");
condition2.Font.Color = Color.Red;

ws.Range["4:4"].EntireRow.Hidden = true;

IRange searchScope = ws.Range["1:14"];

// Find comments
var comments = searchScope.SpecialCells(SpecialCellType.Comments);

// Find last cell
var lastCell = searchScope.SpecialCells(SpecialCellType.LastCell);

// Find visible
var visible = searchScope.SpecialCells(SpecialCellType.Visible);

// Find blanks
var blanks = searchScope.SpecialCells(SpecialCellType.Blanks);

// Find all format conditions
var allFormatConditions = searchScope.SpecialCells(SpecialCellType.AllFormatConditions);

// Find all validation
var allValidation = searchScope.SpecialCells(SpecialCellType.AllValidation);

// Find same format condition as B10
var sameFormatConditions = ws.Range["B10"].SpecialCells(SpecialCellType.SameFormatConditions);

// Find same validation as D10
var sameValidation = ws.Range["D10"].SpecialCells(SpecialCellType.SameValidation);

// Find merged cells
var merged = searchScope.SpecialCells(SpecialCellType.MergedCells);

// Output
ws.Range["A16"].Value = "Find result";
ws.Range["A16:C16"].Merge();
ws.Range["A16:C16"].HorizontalAlignment = HorizontalAlignment.Center;
ws.Range["$A$17:$A$25"].Value = new object[,] {
{"Comments"},
{"LastCell"},
{"Visible"},
{"Blanks"},
{"AllFormatConditions"},
{"AllValidation"},
{"SameFormatConditions B10"},
{"SameValidation D10"},
{"MergedCells"}
};
ws.Range["$C$17:$C$25"].Value = new object[,] {
{comments.Address},
{lastCell.Address},
{visible.Address},
{blanks.Address},
{allFormatConditions.Address},
{allValidation.Address},
{sameFormatConditions.Address},
{sameValidation.Address},
{merged.Address}
};

ws.UsedRange.EntireColumn.AutoFit();

//save to an 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.

C#	Copy Code
//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); workbook.Open("FinancialReport.xlsx"); IRange cells = workbook.ActiveSheet.Cells; // Find all formulas var allFormulas = cells.SpecialCells(SpecialCellType.Formulas); // Find all constants var allConstants = cells.SpecialCells(SpecialCellType.Constants); // Change background color of found cells allFormulas.Interior.Color = Color.LightGray; allConstants.Interior.Color = Color.DarkGray; //save to an excel file workbook.Save("specialcellsinexistingfiles.xlsx");

Copy Code

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

workbook.Open("FinancialReport.xlsx");
        
IRange cells = workbook.ActiveSheet.Cells;

// Find all formulas
var allFormulas = cells.SpecialCells(SpecialCellType.Formulas);
// Find all constants
var allConstants = cells.SpecialCells(SpecialCellType.Constants);

// Change background color of found cells
allFormulas.Interior.Color = Color.LightGray;
allConstants.Interior.Color = Color.DarkGray;

//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.

C#	Copy Code
//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); IWorksheet ws = workbook.ActiveSheet; // Set data ws.Range["A1"].Formula = "=\"Text \" & 1"; ws.Range["B1"].Formula = "=8*10^6"; ws.Range["C1"].Formula = "=SEARCH(A1,9)"; ws.Range["A2"].Value = "Text"; ws.Range["B2"].Value = 1; // Find text formulas var textFormula = ws.Cells.SpecialCells(SpecialCellType.Formulas, SpecialCellsValue.TextValues); // Find number formulas var numberFormula = ws.Cells.SpecialCells(SpecialCellType.Formulas, SpecialCellsValue.Numbers); // Find error formulas var errorFormula = ws.Cells.SpecialCells(SpecialCellType.Formulas, SpecialCellsValue.Errors); // Find text values var textValue = ws.Cells.SpecialCells(SpecialCellType.Constants, SpecialCellsValue.TextValues); // Find number values var numberValue = ws.Cells.SpecialCells(SpecialCellType.Constants, SpecialCellsValue.Numbers); // Display search result ws.Range["A4:E5"].Value = new object[,] { { "Text formula", "Number Formula", "Error Formula", "Text Value", "Number Value"}, { textFormula.Address, numberFormula.Address, errorFormula.Address, textValue.Address, numberValue.Address} }; ws.UsedRange.EntireColumn.AutoFit(); //save to an excel file workbook.Save("specialcellsquickstart.xlsx");

Copy Code

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet ws = workbook.ActiveSheet;

// Set data
ws.Range["A1"].Formula = "=\"Text \" & 1";
ws.Range["B1"].Formula = "=8*10^6";
ws.Range["C1"].Formula = "=SEARCH(A1,9)";
ws.Range["A2"].Value = "Text";
ws.Range["B2"].Value = 1;

// Find text formulas
var textFormula = ws.Cells.SpecialCells(SpecialCellType.Formulas, SpecialCellsValue.TextValues);

// Find number formulas
var numberFormula = ws.Cells.SpecialCells(SpecialCellType.Formulas, SpecialCellsValue.Numbers);

// Find error formulas
var errorFormula = ws.Cells.SpecialCells(SpecialCellType.Formulas, SpecialCellsValue.Errors);

// Find text values
var textValue = ws.Cells.SpecialCells(SpecialCellType.Constants, SpecialCellsValue.TextValues);

// Find number values
var numberValue = ws.Cells.SpecialCells(SpecialCellType.Constants, SpecialCellsValue.Numbers);

// Display search result
ws.Range["A4:E5"].Value = new object[,] {
{ "Text formula", "Number Formula", "Error Formula", "Text Value", "Number Value"},
{ textFormula.Address, numberFormula.Address, errorFormula.Address, textValue.Address, numberValue.Address}
};

ws.UsedRange.EntireColumn.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.

C#	Copy Code
//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); IWorksheet ws = workbook.ActiveSheet; //prepare data ws.Range["$A$1:$F$1"].Value = new object[,]{ { "Test id", "Group id", "Group item id", "New test id", "Test result", "Error code"} }; ws.Range["$B$2:$C$2"].Value = 1d; ws.Range["$E$2,$E$7,$E$12,$E$21,$E$27,$E$36,$E$40,$E$47:$E$48,$E$51,$E$59:$E$60,$E$70:$E$71,$E$80:$E$81,$E$88,$E$90:$E$91"].Value = "Error 80073cf9"; ws.Range["$G$1:$G$2,$I$1:$I$7,$H$8:$I$8,$A$93:$B$93,$E$93:$F$93"].Value = null; ws.Range["$H$1:$H$7"].Value = new object[,]{ { "Constants"}, { "Formulas"}, { "String constants"}, { "Number constants"}, { "String formulas"}, { "Number formulas"}, { "Error formulas"} }; ws.Range["$A$2:$A$13"].Value = "Test00001"; ws.Range["$A$14:$A$67"].Value = "Test00153"; ws.Range["$A$68:$A$92"].Value = "Test05789"; ws.Range["$E$3:$E$5,$E$9:$E$11,$E$25:$E$26,$E$37:$E$38,$E$57,$E$75:$E$76,$E$86:$E$87"].Value = "Runtime Error c0000005"; ws.Range["$E$6,$E$13:$E$20,$E$28:$E$35,$E$41:$E$46,$E$52:$E$56,$E$61:$E$64,$E$72:$E$74,$E$77:$E$78,$E$82:$E$85,$E$89,$E$92"].Value = "Passed"; ws.Range["$E$8,$E$22:$E$24,$E$39,$E$49:$E$50,$E$58,$E$65:$E$69,$E$79"].Value = "Deploy Error 80073cf9"; ws.Range["$D$2:$D$92"].FormulaR1C1 = "=\"X-Test-G\" & RC[-2] & \"-I\" & RC[-1]"; ws.Range["$B$3:$B$92"].FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],R[-1]C,R[-1]C+1)"; ws.Range["$C$3:$C$92"].FormulaR1C1 = "=IF(RC[-2]=R[-1]C[-2],R[-1]C+1,1)"; ws.Range["$F$2:$F$92"].FormulaR1C1 = "=MID(RC[-1], SEARCH(\"Error \",RC[-1])+6,8)"; Color constantBgColor; Color formulasBgColor; Color stringForeColor; Color errorForeColor; unchecked { constantBgColor = Color.FromArgb((int)0xFFDDEBF7); formulasBgColor = Color.FromArgb((int)0xFFF2F2F2); stringForeColor = Color.FromArgb((int)0xFF0000C0); } errorForeColor = Color.DarkRed; var searchScope = ws.Range["$A:$F"]; // Find constant cells and change background color IRange allConsts = searchScope.SpecialCells(SpecialCellType.Constants); allConsts.Interior.Color = constantBgColor; // Find formula cells and change background color IRange allFormulas = searchScope.SpecialCells(SpecialCellType.Formulas); allFormulas.Interior.Color = formulasBgColor; // Find text constant cells and change foreground color IRange textConsts = searchScope.SpecialCells( SpecialCellType.Constants, SpecialCellsValue.TextValues); textConsts.Font.Color = stringForeColor; // Find text formula cells and change foreground color IRange textFormulas = searchScope.SpecialCells( SpecialCellType.Formulas, SpecialCellsValue.TextValues); textFormulas.Font.Color = stringForeColor; // Find number constant cells and change font weight IRange numberConsts = searchScope.SpecialCells( SpecialCellType.Constants, SpecialCellsValue.Numbers); numberConsts.Font.Bold = true; // Find number formula cells and change font weight IRange numberFormulas = searchScope.SpecialCells( SpecialCellType.Formulas, SpecialCellsValue.Numbers); numberFormulas.Font.Bold = true; // Find error formula cells and change foreground color and font style IRange errorFormulas = searchScope.SpecialCells( SpecialCellType.Formulas, SpecialCellsValue.Errors); errorFormulas.Font.Color = errorForeColor; errorFormulas.Font.Italic = true; // Set sample cell styles ws.Range["$H$1,$H$3,$H$4"].Interior.Color = constantBgColor; ws.Range["$H$2,$H$5:$H$7"].Interior.Color = formulasBgColor; ws.Range["$H$3,$H$5"].Font.Color = stringForeColor; ws.Range["$H$4,$H$6"].Font.Bold = true; ws.Range["$H$7"].Font.Color = errorForeColor; ws.Range["$H$7"].Font.Italic = true; ws.UsedRange.EntireColumn.AutoFit(); //save to an excel file workbook.Save("specialcellsfindvaluesandformulas.xlsx");

Copy Code

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

IWorksheet ws = workbook.ActiveSheet;

//prepare data
ws.Range["$A$1:$F$1"].Value = new object[,]{
{ "Test id", "Group id", "Group item id", "New test id", "Test result", "Error code"}
};

ws.Range["$B$2:$C$2"].Value = 1d;
ws.Range["$E$2,$E$7,$E$12,$E$21,$E$27,$E$36,$E$40,$E$47:$E$48,$E$51,$E$59:$E$60,$E$70:$E$71,$E$80:$E$81,$E$88,$E$90:$E$91"].Value = "Error 80073cf9";
ws.Range["$G$1:$G$2,$I$1:$I$7,$H$8:$I$8,$A$93:$B$93,$E$93:$F$93"].Value = null;

ws.Range["$H$1:$H$7"].Value = new object[,]{
{ "Constants"}, { "Formulas"}, { "String constants"}, { "Number constants"}, { "String formulas"}, { "Number formulas"}, { "Error formulas"}
};

ws.Range["$A$2:$A$13"].Value = "Test00001";
ws.Range["$A$14:$A$67"].Value = "Test00153";
ws.Range["$A$68:$A$92"].Value = "Test05789";
ws.Range["$E$3:$E$5,$E$9:$E$11,$E$25:$E$26,$E$37:$E$38,$E$57,$E$75:$E$76,$E$86:$E$87"].Value = "Runtime Error c0000005";
ws.Range["$E$6,$E$13:$E$20,$E$28:$E$35,$E$41:$E$46,$E$52:$E$56,$E$61:$E$64,$E$72:$E$74,$E$77:$E$78,$E$82:$E$85,$E$89,$E$92"].Value = "Passed";
ws.Range["$E$8,$E$22:$E$24,$E$39,$E$49:$E$50,$E$58,$E$65:$E$69,$E$79"].Value = "Deploy Error 80073cf9";

ws.Range["$D$2:$D$92"].FormulaR1C1 = "=\"X-Test-G\" & RC[-2] & \"-I\" & RC[-1]";
ws.Range["$B$3:$B$92"].FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],R[-1]C,R[-1]C+1)";
ws.Range["$C$3:$C$92"].FormulaR1C1 = "=IF(RC[-2]=R[-1]C[-2],R[-1]C+1,1)";
ws.Range["$F$2:$F$92"].FormulaR1C1 = "=MID(RC[-1], SEARCH(\"Error \",RC[-1])+6,8)";

Color constantBgColor;
Color formulasBgColor;
Color stringForeColor;
Color errorForeColor;
unchecked
{
    constantBgColor = Color.FromArgb((int)0xFFDDEBF7);
    formulasBgColor = Color.FromArgb((int)0xFFF2F2F2);
    stringForeColor = Color.FromArgb((int)0xFF0000C0);
}
errorForeColor = Color.DarkRed;
        

var searchScope = ws.Range["$A:$F"];

// Find constant cells and change background color
IRange allConsts = searchScope.SpecialCells(SpecialCellType.Constants);
allConsts.Interior.Color = constantBgColor;

// Find formula cells and change background color
IRange allFormulas = searchScope.SpecialCells(SpecialCellType.Formulas);
allFormulas.Interior.Color = formulasBgColor;

// Find text constant cells and change foreground color
IRange textConsts = searchScope.SpecialCells(
    SpecialCellType.Constants, SpecialCellsValue.TextValues);
textConsts.Font.Color = stringForeColor;

// Find text formula cells and change foreground color
IRange textFormulas = searchScope.SpecialCells(
    SpecialCellType.Formulas, SpecialCellsValue.TextValues);
textFormulas.Font.Color = stringForeColor;

// Find number constant cells and change font weight
IRange numberConsts = searchScope.SpecialCells(
    SpecialCellType.Constants, SpecialCellsValue.Numbers);
numberConsts.Font.Bold = true;

// Find number formula cells and change font weight
IRange numberFormulas = searchScope.SpecialCells(
    SpecialCellType.Formulas, SpecialCellsValue.Numbers);
numberFormulas.Font.Bold = true;

// Find error formula cells and change foreground color and font style
IRange errorFormulas = searchScope.SpecialCells(
    SpecialCellType.Formulas, SpecialCellsValue.Errors);
errorFormulas.Font.Color = errorForeColor;
errorFormulas.Font.Italic = true;

// Set sample cell styles
ws.Range["$H$1,$H$3,$H$4"].Interior.Color = constantBgColor;
ws.Range["$H$2,$H$5:$H$7"].Interior.Color = formulasBgColor;
ws.Range["$H$3,$H$5"].Font.Color = stringForeColor;
ws.Range["$H$4,$H$6"].Font.Bold = true;
ws.Range["$H$7"].Font.Color = errorForeColor;
ws.Range["$H$7"].Font.Italic = true;

ws.UsedRange.EntireColumn.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 GcExcel 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 GcExcel, the result is $A$2:$B$3,$C$2:$C$4,$D$4

Product Support Forum | Print this page