Get Special Cell Ranges

// Create a new workbook.
var workbook = new Workbook();

// Get active sheet.
IWorksheet ws = workbook.ActiveSheet;

// Add data to the range.
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["A9"].Tag = "Number type";
ws.Range["B9"].Tag = "Text type";
ws.Range["C9"].Tag = "Text type";
ws.Range["D9"].Tag = "Bool type";

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);

// Find cells containing tags.
var tagCells = searchScope.SpecialCells(SpecialCellType.Tags);

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

ws.UsedRange.EntireColumn.AutoFit();

// Save the excel file.
workbook.Save("SpecialCellsFindMiscellaneous.xlsx");

//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");

//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");

//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");