Interface IRange

Represents a cell, a row, a column, collection of cells or a 3-D Range.

Inherited Members
System.Collections.Generic.IEnumerable<Syncfusion.XlsIO.IRange>.GetEnumerator()
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.Base.dll
Syntax
public interface IRange : IParentApplication, IEnumerable<IRange>, IEnumerable

Properties

Address

Gets the Range reference in macro language. Read-only.

Declaration
string Address { get; }
Property Value
Type Description
System.String
Examples

The following code illustrates how to access Address property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
string address = sheet.Range[3, 4].Address;

workbook.SaveAs("Address.xlsx");
workbook.Close();
excelEngine.Dispose();

AddressGlobal

Gets the Range reference along with its sheet name in format "'Sheet1'!$A$1". Read-only.

Declaration
string AddressGlobal { get; }
Property Value
Type Description
System.String
Examples

The following code illustrates how to access AddressGlobal property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[2];
string address = sheet.Range[4, 3].AddressGlobal;

workbook.SaveAs("AddressGlobal.xlsx");
workbook.Close();
excelEngine.Dispose();

AddressLocal

Gets the Range reference in language of the user. Read-only.

Declaration
string AddressLocal { get; }
Property Value
Type Description
System.String
Examples

The following code illustrates how to access AddressLocal property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
string address = sheet.Range[1, 2].AddressLocal; 

workbook.SaveAs("AddressLocal.xlsx");
workbook.Close();
excelEngine.Dispose();

AddressR1C1

Gets the Range reference along with sheet name using R1C1-style notation. Read-only.

Declaration
string AddressR1C1 { get; }
Property Value
Type Description
System.String
Examples

The following code illustrates how to access AddressR1C1 property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
string address = sheet.Range[1, 1].AddressR1C1;

workbook.SaveAs("AddressR1C1.xlsx");
workbook.Close();
excelEngine.Dispose();

AddressR1C1Local

Gets the Range reference using R1C1-style notation. Read-only.

Declaration
string AddressR1C1Local { get; }
Property Value
Type Description
System.String
Examples

The following code illustrates how to access AddressR1C1Local property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
string address = sheet.Range[1, 1].AddressR1C1Local;

workbook.SaveAs("AddressR1C1Local.xlsx");
workbook.Close();
excelEngine.Dispose();

Boolean

Gets or sets boolean value in the Range.

Declaration
bool Boolean { get; set; }
Property Value
Type Description
System.Boolean
Examples

The following code illustrates how to set and access Boolean property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
sheet.Range[2, 4].Boolean = true;
bool boolean = sheet.Range[2, 4].Boolean;

workbook.SaveAs("Boolean.xlsx");
workbook.Close();
excelEngine.Dispose();

Borders

Gets a IBorders collection that represents the borders of a style in the Range. Read-only.

Declaration
IBorders Borders { get; }
Property Value
Type Description
IBorders
Remarks

Borders including a Range defined as part of a conditional format will be returned.

Examples

The following code illustrates how to access Borders property of the Range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";

            //Set borders
            IBorders borders = worksheet["C2"].Borders;

            //Set border color
            borders[ExcelBordersIndex.EdgeTop].Color = ExcelKnownColors.Red;
            borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Blue;

            //Set line style
            borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thick;
            borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thick;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

BuiltInStyle

Gets or sets built in style in the Range.

Declaration
BuiltInStyles? BuiltInStyle { get; set; }
Property Value
Type Description
System.Nullable<BuiltInStyles>
Examples

The following code illustrates how to set BuiltInStyle to a range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";

            //Set built in style
            worksheet["C2"].BuiltInStyle = BuiltInStyles.Accent3;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

CalculatedValue

Gets the calculated value of a formula in the Range. Read-only.

Declaration
string CalculatedValue { get; }
Property Value
Type Description
System.String
Remarks

To compute a formula, it is mandatory to enable calculate engine by invoking EnableSheetCalculations method of worksheet object. It is also recommend to disable calculate engine once all formula are computed by invoking DisableSheetCalculations method of worksheet object

Examples

Following code illustrates how to access a calculated value.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];

//Initializes Calculate Engine to perform calculation
sheet.EnableSheetCalculations();

//Returns the calculated value of a formula using the most current inputs
string calculatedValue = sheet["C1"].CalculatedValue;
//Formula calculation is disabled for the sheet.
sheet.DisableSheetCalculations();  

workbook.SaveAs("Formula.xlsx");
workbook.Close();
excelEngine.Dispose();

Cells

Gets a IRange object that represents the cells in the Range. Read-only.

Declaration
IRange[] Cells { get; }
Property Value
Type Description
IRange[]
Examples

The following code illustrates how to access Cells property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
IRange[] cells = sheet["A1:E8"].Cells;
foreach (IRange Range in cells)
{
    // Do some manipulations
}

workbook.SaveAs("Cells.xlsx");
workbook.Close();
excelEngine.Dispose();

CellStyle

Gets a IStyle object that represents the style of the Range.

Declaration
IStyle CellStyle { get; set; }
Property Value
Type Description
IStyle
Remarks

To know more about setting styles refer Set Default Style for row/column.

Examples

The following code illustrates how to access IStyle using CellStyle property of the Range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("BorderStyle");

            //Set color
            style.ColorIndex = ExcelKnownColors.Red;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

CellStyleName

Gets name of the IStyle object that represents style of the Range.

Declaration
string CellStyleName { get; set; }
Property Value
Type Description
System.String
Examples

The following code illustrates how to access CellStyleName property of the Range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set color
            style.ColorIndex = ExcelKnownColors.Red;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Check Style name
            Console.Write(worksheet["C2"].CellStyleName);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//CustomStyle

Column

Gets the column index of the first column in the Range which is a one based index. Read-only.

Declaration
int Column { get; }
Property Value
Type Description
System.Int32
Examples

The following code illustrates how to access Column property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
int firstColumn = sheet["E1:R3"].Column;

workbook.SaveAs("Column.xlsx");
workbook.Close();
excelEngine.Dispose();

ColumnGroupLevel

Gets column group level of the Range. Read-only.

Declaration
int ColumnGroupLevel { get; }
Property Value
Type Description
System.Int32
Remarks

-1 - not all column in the Range have same group level. 0 - No grouping, 1 - 7 - group level.

Columns

Gets an array of IRange objects that represent the columns in the Range. Read only.

Declaration
IRange[] Columns { get; }
Property Value
Type Description
IRange[]
Examples

The following code illustrates how to access Columns property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
IRange[] columns = sheet["A1:E8"].Columns;
foreach (IRange column in columns)
{
    // Do some manipulations
}

workbook.SaveAs("Columns.xlsx");
workbook.Close();
excelEngine.Dispose();

ColumnWidth

Gets or sets the width of all columns in the specified range, measured in points.

Declaration
double ColumnWidth { get; set; }
Property Value
Type Description
System.Double
Remarks

Gets Double.MinValue if the rows in the specified Range aren't all the same width.

Examples
        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["A1"].Text = "This cell contains sample text";

            //Set column width
            worksheet["A1"].ColumnWidth = 25;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close(); 
        }

Comment

Gets a ICommentShape object that represents the comment associated with the range. Read-only.

Declaration
ICommentShape Comment { get; }
Property Value
Type Description
ICommentShape
Examples

The following code illustrates how to insert Comments property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
// Adding comments to a cell.
sheet.Range["A1"].AddComment().Text = "Comments";

// Add Rich Text Comments.
IRange range = sheet.Range["A6"];
range.AddComment().RichText.Text = "RichText";
IRichTextString rtf = range.Comment.RichText;

// Formatting first 4 characters.
IFont redFont = workbook.CreateFont();
redFont.Bold = true;
redFont.Color = ExcelKnownColors.Red;
rtf.SetFont(0, 3, redFont);

workbook.SaveAs("Comments.xlsx");
workbook.Close();
excelEngine.Dispose();

ConditionalFormats

Gets the collection of conditional formats in the Range. Read-only.

Declaration
IConditionalFormats ConditionalFormats { get; }
Property Value
Type Description
IConditionalFormats
Examples

The following code illustrates how to set and access Value2 property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];

// Applying conditional formatting to "A1"
IConditionalFormats condition = sheet.Range["A2"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();

// Represents conditional format rule that the value in target Range should be between 10 and 20
condition1.FormatType = ExcelCFType.CellValue;
condition1.Operator = ExcelComparisonOperator.Between;
condition1.FirstFormula = "10";
condition1.SecondFormula = "20";
sheet.Range["A1"].Text = "Enter a number between 10 and 20";

workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
excelEngine.Dispose();

Count

Gets the number of cells in the Range. Read-only.

Declaration
int Count { get; }
Property Value
Type Description
System.Int32

DataValidation

Gets a IDataValidation object that represents the data validation associated with the cell. Read-only.

Declaration
IDataValidation DataValidation { get; }
Property Value
Type Description
IDataValidation
Examples

The following code illustrates how to access DataValidation property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];

//Data validation for number
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.Integer;

//Value between 0 to 10
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstFormula = "0";
validation.SecondFormula = "10";

workbook.SaveAs("DataValidation.xlsx");
workbook.Close();
excelEngine.Dispose();

DateTime

Gets or sets DateTime value in the Range.

Declaration
DateTime DateTime { get; set; }
Property Value
Type Description
System.DateTime
Remarks

Gets DateTime.MinValue if not all cells in the Range have same DateTime value.

Examples

The following code illustrates how to set and access DateTime property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
sheet.Range[2, 4].DateTime = DateTime.Now;
DateTime dateTime = sheet.Range[2, 4].DateTime;

workbook.SaveAs("DateTime.xlsx");
workbook.Close();
excelEngine.Dispose();

DisplayText

Gets cell value with its number format. Read-only.

Declaration
string DisplayText { get; }
Property Value
Type Description
System.String
Remarks

While accessing DisplayText for Range of cells, it gets DisplayText of the first cell

If a range contains a formula, EnableSheetCalculations() must be invoked to get the calculated value of the formula

Examples

The following code illustrates how to access DisplayText property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
IRange range= sheet.Range[3, 1];
range.Value = "1/1/2015";
range.NumberFormat = "dd-MMM-yyyy";
string displayText = range.DisplayText;

workbook.SaveAs("DisplayText.xlsx");
workbook.Close();
excelEngine.Dispose();

End

Gets a IRange object that represents the cell at the end of the Range.

Declaration
IRange End { get; }
Property Value
Type Description
IRange

EntireColumn

Gets a IRange object that represents the entire column (or columns) in the Range. Read-only.

Declaration
IRange EntireColumn { get; }
Property Value
Type Description
IRange

EntireRow

Gets a IRange object that represents the entire row (or rows) in the Range. Read-only.

Declaration
IRange EntireRow { get; }
Property Value
Type Description
IRange

Error

Gets or sets error value in the Range.

Declaration
string Error { get; set; }
Property Value
Type Description
System.String

Formula

Gets or sets the formula in A1-style notation and in macro language for the Range.

Declaration
string Formula { get; set; }
Property Value
Type Description
System.String

FormulaArray

Gets or sets sets the array formula of a range which can perform multiple calculations on one or more of the items in an array.

Declaration
string FormulaArray { get; set; }
Property Value
Type Description
System.String
Examples

The following code illustrates how to set and access FormulaArray property of the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];

//Assign array formula
sheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}";

//Adding a named range for the range A1 to D1
sheet.Names.Add("ArrayRange", sheet.Range["A1:D1"]);

//Assign formula array with named range
sheet.Range["A2:D2"].FormulaArray = "ArrayRange+100";

string fileName = "FormulaArray.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FormulaArrayR1C1

Gets or sets the formula array for the Range, using R1C1-style notation.

Declaration
string FormulaArrayR1C1 { get; set; }
Property Value
Type Description
System.String

FormulaBoolValue

Gets or sets the calculated value of the formula as a boolean.

Declaration
bool FormulaBoolValue { get; set; }
Property Value
Type Description
System.Boolean

FormulaDateTime

Gets or sets formula DateTime value in the Range.

Declaration
DateTime FormulaDateTime { get; set; }
Property Value
Type Description
System.DateTime
Remarks

Gets DateTime.MinValue if not all cells in the Range have same FormulaDateTime value.

FormulaErrorValue

Gets or sets the error value of the formula as a string.

Declaration
string FormulaErrorValue { get; set; }
Property Value
Type Description
System.String

FormulaHidden

Gets or sets a Boolean value indicating whether the formula will be hidden when the worksheet is protected.

Declaration
bool FormulaHidden { get; set; }
Property Value
Type Description
System.Boolean
Remarks

True if the formula will be hidden when the worksheet is protected. False if at least part of formula in the Range is not hidden.

FormulaNumberValue

Gets or sets number value evaluated by formula.

Declaration
double FormulaNumberValue { get; set; }
Property Value
Type Description
System.Double

FormulaR1C1

Gets or sets the formula for the Range, using R1C1-style notation.

Declaration
string FormulaR1C1 { get; set; }
Property Value
Type Description
System.String

FormulaStringValue

Gets or sets string value evaluated by formula.

Declaration
string FormulaStringValue { get; set; }
Property Value
Type Description
System.String

HasBoolean

Gets a Boolean value indicating whether Range contains bool value. Read-only.

Declaration
bool HasBoolean { get; }
Property Value
Type Description
System.Boolean
Examples

The following code illustrates how to set and access HasBoolean property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];

//Assigning Value2 property of the Range
sheet["A3"].Value2 = false;

// Checking Range types.
bool isboolean = sheet["A3"].HasBoolean;

workbook.SaveAs("HasBoolean.xlsx");
workbook.Close();
excelEngine.Dispose();

HasDataValidation

Gets a Boolean value indicating whether Range has data validation. Read-only.

Declaration
bool HasDataValidation { get; }
Property Value
Type Description
System.Boolean
Remarks

If Range is not single cell, then gets true only if all cells have data validation.

HasDateTime

Gets a Boolean value indicating whether Range contains DateTime value. Read-only.

Declaration
bool HasDateTime { get; }
Property Value
Type Description
System.Boolean
Examples

The following code illustrates how to set and access HasDateTime property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];

//Assigning Value2 property of the Range
sheet["A1"].Value2 = DateTime.Now;

// Checking Range types.
bool isDateTime =  sheet["A1"].HasDateTime;

workbook.SaveAs("HasDateTime.xlsx");
workbook.Close();
excelEngine.Dispose();

HasExternalFormula

Gets a Boolean value indicating whether Range has external formula. Read-only.

Declaration
bool HasExternalFormula { get; }
Property Value
Type Description
System.Boolean

HasFormula

Gets a Boolean value indicating whether Range contains formula. Read-only.

Declaration
bool HasFormula { get; }
Property Value
Type Description
System.Boolean
Remarks

True if all cells in the Range contain formulas; False if at least one of the cells in the Range doesn't contain a formula.

HasFormulaArray

Gets a Boolean value indicating whether Range contains array-entered formula. Read-only.

Declaration
bool HasFormulaArray { get; }
Property Value
Type Description
System.Boolean

HasFormulaBoolValue

Gets a Boolean value indicating whether Range has formula bool value. Read-only.

Declaration
bool HasFormulaBoolValue { get; }
Property Value
Type Description
System.Boolean

HasFormulaDateTime

Gets a Boolean value indicating whether Range has formula value formatted as DateTime. Read-only.

Declaration
bool HasFormulaDateTime { get; }
Property Value
Type Description
System.Boolean

HasFormulaErrorValue

Gets a Boolean value indicating whether Range has formula error value. Read-only.

Declaration
bool HasFormulaErrorValue { get; }
Property Value
Type Description
System.Boolean

HasFormulaNumberValue

Gets a Boolean value indicating whether Range has formula number value. Read-only.

Declaration
bool HasFormulaNumberValue { get; }
Property Value
Type Description
System.Boolean

HasFormulaStringValue

Gets a Boolean value indicating whether Range has formula string value. Read-only.

Declaration
bool HasFormulaStringValue { get; }
Property Value
Type Description
System.Boolean

HasNumber

Gets a Boolean value indicating whether Range contains number. Read-only.

Declaration
bool HasNumber { get; }
Property Value
Type Description
System.Boolean
Examples

The following code illustrates how to set and access Value2 property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];

//Assigning Value2 property of the Range
sheet["A2"].Value2 = 45;

// Checking Range types.
bool isNumber =  sheet["A2"].HasNumber;

workbook.SaveAs("HasNumber.xlsx");
workbook.Close();
excelEngine.Dispose();

HasRichText

Gets a Boolean value indicating whether cell contains formatted rich text string.

Declaration
bool HasRichText { get; }
Property Value
Type Description
System.Boolean
Examples

The following code illustrates how to access HasRichText property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Create style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set rich text
            IRichTextString richText = worksheet["C2"].RichText;

            //Set text
            richText.Text = "Sample";

            //Set font
            IFont font = style.Font;

            //Set color
            font.Color = ExcelKnownColors.Red;

            //Set rich text font
            richText.SetFont(0, 5, font);

            //Check HasRichText
            Console.Write(worksheet["C2"].HasRichText);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//True

HasString

Gets a Boolean value indicating whether Range contains string. Read-only.

Declaration
bool HasString { get; }
Property Value
Type Description
System.Boolean

HasStyle

Gets a Boolean value indicating whether Range has default style. False means default style. Read-only.

Declaration
bool HasStyle { get; }
Property Value
Type Description
System.Boolean
Examples

The following code illustrates how to access HasStyle property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set color
            style.ColorIndex = ExcelKnownColors.Red;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Check HasStyle
            Console.Write(worksheet["C2"].HasStyle);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//True

HorizontalAlignment

Gets or sets the horizontal alignment for the specified object.

Declaration
ExcelHAlign HorizontalAlignment { get; set; }
Property Value
Type Description
ExcelHAlign
Examples

By default HAlignGeneral is set to cells. Here for example, we set HAlignRight to align the cell contents to right.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["A1"].Text = "Test";

            //Set alignment
            worksheet["A1"].HorizontalAlignment = ExcelHAlign.HAlignRight;

            //Set row height
            worksheet["A1"].RowHeight = 30;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close(); 
        }

HtmlString

Gets or sets HTML string.

Declaration
string HtmlString { get; set; }
Property Value
Type Description
System.String

Gets hyperlinks in the Range. Read-only.

Declaration
IHyperLinks Hyperlinks { get; }
Property Value
Type Description
IHyperLinks

The following code illustrates how to access Borders property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];

// Creating a Hyperlink for a Website.
IHyperLink hyperlink = sheet.HyperLinks.Add(sheet.Range["C5"]);
hyperlink.Type = ExcelHyperLinkType.Url;
hyperlink.Address = "http://www.syncfusion.com";
hyperlink.ScreenTip = "To know more About SYNCFUSION PRODUCTS go through this link";

workbook.SaveAs("Hyperlinks.xlsx");
workbook.Close();
excelEngine.Dispose();

IgnoreErrorOptions

Gets or sets ignore error options for formula auditing. If not single cell gets concatenated flags.

Declaration
ExcelIgnoreError IgnoreErrorOptions { get; set; }
Property Value
Type Description
ExcelIgnoreError

IndentLevel

Gets or sets the indent level for cell or Range.

Declaration
int IndentLevel { get; set; }
Property Value
Type Description
System.Int32
Remarks

It can be from 0 to 15 for Excel 97-2003 and 250 for Excel 2007-2013.

Examples

The following code illustrates how to set indent level for a IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";

            //Set indent level
            worksheet["C2"].IndentLevel = 2;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

IsBlank

Gets a Boolean value indicating whether the Range is blank. Read-only.

Declaration
bool IsBlank { get; }
Property Value
Type Description
System.Boolean

IsBoolean

Gets a Boolean value indicating whether cell type is boolean. Read-only.

Declaration
bool IsBoolean { get; }
Property Value
Type Description
System.Boolean

IsError

Gets a Boolean value indicating whether Range contains error value. Read-only.

Declaration
bool IsError { get; }
Property Value
Type Description
System.Boolean

IsGroupedByColumn

Gets a Boolean value indicating whether Range is grouped by column. Read-only.

Declaration
bool IsGroupedByColumn { get; }
Property Value
Type Description
System.Boolean

IsGroupedByRow

Gets a Boolean value indicating whether Range is grouped by row. Read-only.

Declaration
bool IsGroupedByRow { get; }
Property Value
Type Description
System.Boolean

IsInitialized

Gets a Boolean value indicating whether cell is initialized. Read-only.

Declaration
bool IsInitialized { get; }
Property Value
Type Description
System.Boolean

IsMerged

Gets a Boolean value indicating whether Range is a part of merged Range. Read-only.

Declaration
bool IsMerged { get; }
Property Value
Type Description
System.Boolean
Examples
        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["A1"].Text = "Sample text in cell";

            //Set merge
            worksheet["A1:B1"].Merge();

            //Check merge
            Console.Write(worksheet["A1:B1"].IsMerged);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//True

IsStringsPreserved

Gets a Boolean value indicating whether all values in the Range are preserved as strings.

Declaration
bool? IsStringsPreserved { get; set; }
Property Value
Type Description
System.Nullable<System.Boolean>

Item[Int32, Int32]

Gets or sets cell range by row and column index. Row and column indexes are one-based.

Declaration
IRange this[int row, int column] { get; set; }
Parameters
Type Name Description
System.Int32 row
System.Int32 column
Property Value
Type Description
IRange

Item[Int32, Int32, Int32, Int32]

Get cell Range. Row and column indexes are one-based. Read-only.

Declaration
IRange this[int row, int column, int lastRow, int lastColumn] { get; }
Parameters
Type Name Description
System.Int32 row
System.Int32 column
System.Int32 lastRow
System.Int32 lastColumn
Property Value
Type Description
IRange

Item[String]

Gets cell Range. Read-only.

Declaration
IRange this[string name] { get; }
Parameters
Type Name Description
System.String name
Property Value
Type Description
IRange

Item[String, Boolean]

Gets cell Range with R1C1Notation flag. Read-only.

Declaration
IRange this[string name, bool IsR1C1Notation] { get; }
Parameters
Type Name Description
System.String name
System.Boolean IsR1C1Notation
Property Value
Type Description
IRange

LastColumn

Gets the column index of the last column in the Range which is a one based index. Read-only.

Declaration
int LastColumn { get; }
Property Value
Type Description
System.Int32

LastRow

Gets the row index of the last row in the Range which is a one based index. Read-only.

Declaration
int LastRow { get; }
Property Value
Type Description
System.Int32

MergeArea

Gets a IRange object that represents the merged Range to which the cell or Range belongs. Read-only.

Declaration
IRange MergeArea { get; }
Property Value
Type Description
IRange
Remarks

If the specified cell isn�t in a merged Range, this property gets NULL.

Examples

The following code illustrates how to access MergeArea property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["D2"].Text = "text";
            worksheet["C3"].Text = "in";
            worksheet["D3"].Text = "cell";

            //Merge
            worksheet["C2:D3"].Merge();

            //Check merge area
            Console.Write(worksheet["C2"].MergeArea.AddressLocal);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//C2:D3

Number

Gets or sets number value that is contained by Range.

Declaration
double Number { get; set; }
Property Value
Type Description
System.Double
Exceptions
Type Condition
System.FormatException

When Range value is not a number.

NumberFormat

Gets or sets format of cell which is similar to Style.NumberFormat property.

Declaration
string NumberFormat { get; set; }
Property Value
Type Description
System.String
Remarks

Gets NULL if all cells in the specified Range don't have the same number format.

Examples

The following code illustrates how to set NumberFormat property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Load data
            worksheet["C2"].Value = "3100.23";

            //Set number format
            worksheet["C2"].NumberFormat = "#,##0.##";

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

RichText

Gets string with rich text formatting in the Range. Read-only.

Declaration
IRichTextString RichText { get; }
Property Value
Type Description
IRichTextString
Examples

The following code illustrates how to set rich text formatting in the range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Create style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set rich text
            IRichTextString richText = worksheet["C2"].RichText;

            //Set text
            richText.Text = "Sample text";

            //Set font
            IFont font = style.Font;

            //Set color
            font.Color = ExcelKnownColors.Red;

            //Set rich text font
            richText.SetFont(0, 5, font);

            //Set color
            font.Color = ExcelKnownColors.Blue;

            //Set rich text font
            richText.SetFont(6, 10, font);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

Row

Gets the row index of the first row in the Range which is a one based index. Read-only.

Declaration
int Row { get; }
Property Value
Type Description
System.Int32

RowGroupLevel

Gets row group level. Read-only.

Declaration
int RowGroupLevel { get; }
Property Value
Type Description
System.Int32
Remarks

-1 - Not all rows in the Range have same group level. 0 - No grouping, 1 - 7 - Group level.

RowHeight

Gets or sets the height of all the rows in the range, measured in points.

Declaration
double RowHeight { get; set; }
Property Value
Type Description
System.Double
Remarks

Gets Double.MinValue if the rows in the specified Range aren't all the same height. Maximum Row height can be 409 value, minimum is zero.

Examples
        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["A1"].Text = "Test";

            //Set alignment
            worksheet["A1"].VerticalAlignment = ExcelVAlign.VAlignTop;

            //Set row height
            worksheet["A1"].RowHeight = 30;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close(); 
        }

Rows

Gets an array of IRange objects that represent the rows in the Range. Read only.

Declaration
IRange[] Rows { get; }
Property Value
Type Description
IRange[]
Examples

The following code illustrates how to access Columns property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
IRange[] rows = sheet["A1:E8"].Rows;
foreach (IRange row in rows)
{
    // Do some manipulations
}

workbook.SaveAs("Rows.xlsx");
workbook.Close();
excelEngine.Dispose();

Text

Gets or sets string value of the Range.

Declaration
string Text { get; set; }
Property Value
Type Description
System.String

TimeSpan

Gets or sets time value of the Range.

Declaration
TimeSpan TimeSpan { get; set; }
Property Value
Type Description
System.TimeSpan

Value

Gets or sets the value of the Range. Does not support FormulaArray value.

Declaration
string Value { get; set; }
Property Value
Type Description
System.String
Remarks

Sets different data types values as a string, Value property parses the input string to determine its type which leads performance delay.

The only difference between the Value2 property and the Value property is that the Value2 property does not use the Currency and Date data types. Also, it does not support FormulaArray value.

Examples

The following code illustrates how to set and access Value property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
IRange range= sheet.Range[3, 1];
range.Value = "1/1/2015";
range.NumberFormat = "dd-MMM-yyyy";
string displayText = range.DisplayText;

workbook.SaveAs("Value.xlsx");
workbook.Close();
excelEngine.Dispose();

Value2

Gets or sets the cell value.

Declaration
object Value2 { get; set; }
Property Value
Type Description
System.Object
Remarks

Sets different data types values as a object. Value2 first checks whether the specified object has the type known for it (DateTime, TimeSpan, Double, Int). If yes, then it uses the corresponding typed properties (DateTime, TimeSpan, and Number). Otherwise, it calls Value property with String data type.

The only difference between the Value2 property and the Value property is that the Value2 property does not use the Currency and Date data types. Also, it does not support FormulaArray value.

Examples

The following code illustrates how to set and access Value2 property of the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];

//Assigning Value2 property of the Range
sheet["A1"].Value2 = DateTime.Now;
sheet["A2"].Value2 = 45;
sheet["A3"].Value2 = false;

// Checking Range types.
bool isDateTime =  sheet["A1"].HasDateTime;
bool isNumber =  sheet["A2"].HasNumber;
bool isboolean = sheet["A3"].HasBoolean;

workbook.SaveAs("Value2.xlsx");
workbook.Close();
excelEngine.Dispose();

VerticalAlignment

Gets or sets the vertical alignment of the Range.

Declaration
ExcelVAlign VerticalAlignment { get; set; }
Property Value
Type Description
ExcelVAlign
Examples
        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["A1"].Text = "Test";

            //Set alignment
            worksheet["A1"].VerticalAlignment = ExcelVAlign.VAlignTop;

            //Set row height
            worksheet["A1"].RowHeight = 30;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close(); 
        }

Worksheet

Gets the IWorksheet object in which belongs to the Range. Read-only.

Declaration
IWorksheet Worksheet { get; }
Property Value
Type Description
IWorksheet

WrapText

Gets or sets wrap text of the Range.

Declaration
bool WrapText { get; set; }
Property Value
Type Description
System.Boolean
Remarks

While applying wraptext to rows/columns in Excel, autofit is done for rows/columns everytime when data is added. But in XlsIO, autofit of rows/columns are not done like Excel because of performance considerations. To achieve Excel behavior, the autofit can be invoked manually.

Examples

The following code illustrates how to set WrapText property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["A1"].Text = "This cell contains sample text";

            //Set wrap text
            worksheet["A1"].WrapText = true;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close(); 
        }

Methods

Activate()

Activates the cell.

Declaration
IRange Activate()
Returns
Type Description
IRange

Returns the active cell.
Examples

The following code illustrates how to activate a Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

// Activates 'F1' cell.
worksheet.Range["F1"].Activate();

workbook.SaveAs("Activate.xlsx");
workbook.Close();
excelEngine.Dispose();

Activate(Boolean)

Activates the cell and its worksheet.

Declaration
IRange Activate(bool scroll)
Parameters
Type Name Description
System.Boolean scroll

True to set as top left cell.
Returns
Type Description
IRange

Returns the active cell.
Examples

The following code illustrates how to activate a Range with scroll flag.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

// Activates 'F1' cell.
worksheet.Range["F1"].Activate(true);

workbook.SaveAs("Activate.xlsx");
workbook.Close();
excelEngine.Dispose();

AddComment()

Adds a comment.

Declaration
ICommentShape AddComment()
Returns
Type Description
ICommentShape

Returns a comment shape.
Examples

The following code illustrates how to insert Comments in the Range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet sheet = workbook.Worksheets[0];

            // Adding comments to a cell.
            ICommentShape comment = sheet.Range["A1"].AddComment();
            comment.Text= "Comments";

            // Add Rich Text Comments.
            IRange range = sheet.Range["A6"];
            range.AddComment().RichText.Text = "RichText";
            IRichTextString rtf = range.Comment.RichText;

            // Formatting first 4 characters.
            IFont redFont = workbook.CreateFont();
            redFont.Bold = true;
            redFont.Color = ExcelKnownColors.Red;
            rtf.SetFont(0, 3, redFont);

            //Save and dispose
            workbook.SaveAs("Comments.xlsx");
            workbook.Close();
        }

AutofitColumns()

Changes the width of the columns in the range to achieve the best fit.

Declaration
void AutofitColumns()
Remarks

To know more about resizing rows and columns refer Auto-Fit Rows or Columns. This method is supported on Windows Forms, WPF, ASP.NET, ASP.NET MVC, WinRT, WP, Universal and UWP,Xamarin and NetStandard platforms only.

Examples

The following code shows how to auto-size row height to its cell content.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Auto-fit columns
            worksheet.Range["B4"].Text = "Fit the content to column";
            worksheet.Range["B4"].AutofitColumns();

            //Save and dispose
            workbook.SaveAs("AutoFit.xlsx");
            workbook.Close();
            excelEngine.Dispose();
        }

AutofitRows()

Changes the height of the rows in the range to achieve the best fit.

Declaration
void AutofitRows()
Remarks

To know more about resizing rows and columns refer Auto-Fit Rows or Columns. This method is supported on Windows Forms, WPF, ASP.NET, ASP.NET MVC, WinRT, WP, Universal and UWP,Xamarin and NetStandard platforms only.

Examples

The following code shows how to auto-size row height to its cell content.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Auto-fit rows
            worksheet.Range["A2"].Text = "Fit the content to row";
            worksheet.Range["A2"].WrapText = true;
            worksheet.Range["A2"].AutofitRows();

            //Save and dispose
            workbook.SaveAs("AutoFit.xlsx");
            workbook.Close();
            excelEngine.Dispose();
        }

Average()

Returns average of numeric cells in the range.

Declaration
double Average()
Returns
Type Description
System.Double
Examples

The following code illustrates how to find the average of numeric cells in the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

//Set value to cells
worksheet.Range["A1:A3"].Value = "10";

//Returns average of numeric cells in the range.
double average = worksheet.Range["A1:A3"].Average();

workbook.SaveAs("Average.xlsx");
workbook.Close();
excelEngine.Dispose();

Average(Boolean)

Returns average of numeric cells in the range.

Declaration
double Average(bool considerDateAsNumber)
Parameters
Type Name Description
System.Boolean considerDateAsNumber

Considers numeric value of date formatted cells for calculation.
Returns
Type Description
System.Double
Examples

The following code illustrates how to find the average of numeric cells in the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

//Set value to cells
worksheet.Range["A1:A3"].Value = "10";

//Returns average of numeric cells in the range.
double average = worksheet.Range["A1:A3"].Average(true);

workbook.SaveAs("Average.xlsx");
workbook.Close();
excelEngine.Dispose();

BorderAround()

Applies border around the Range. The default line style is Thin.

Declaration
void BorderAround()
Examples

The following code illustrates how to apply border around the Range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);                                     
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["D2"].Text = "text";
            worksheet["C3"].Text = "in";
            worksheet["D3"].Text = "cell";

            //Set border
            worksheet["C2:D3"].BorderAround();

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

BorderAround(ExcelLineStyle)

Applies border around the Range with the specified ExcelLineStyle.

Declaration
void BorderAround(ExcelLineStyle borderLine)
Parameters
Type Name Description
ExcelLineStyle borderLine

Represents border line style.
Examples

The following code illustrates how to apply border around the Range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["D2"].Text = "text";
            worksheet["C3"].Text = "in";
            worksheet["D3"].Text = "cell";

            //Set border
            worksheet["C2:D3"].BorderAround(ExcelLineStyle.Thick);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

BorderAround(ExcelLineStyle, ExcelKnownColors)

Applies border around the Range with the specified ExcelLineStyle and ExcelKnownColors.

Declaration
void BorderAround(ExcelLineStyle borderLine, ExcelKnownColors borderColor)
Parameters
Type Name Description
ExcelLineStyle borderLine

Represents border line style.
ExcelKnownColors borderColor

Represents border color.
Examples

The following code illustrates how to apply border around the Range with ExcelKnownColors.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["D2"].Text = "text";
            worksheet["C3"].Text = "in";
            worksheet["D3"].Text = "cell";

            //Set border
            worksheet["C2:D3"].BorderAround(ExcelLineStyle.Thick, ExcelKnownColors.Red);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

BorderAround(ExcelLineStyle, Color)

Applies border around the Range with the specified ExcelLineStyle and System.Drawing.Color.

Declaration
void BorderAround(ExcelLineStyle borderLine, Color borderColor)
Parameters
Type Name Description
ExcelLineStyle borderLine

Represents border line style.
System.Drawing.Color borderColor

Represents border color.
Examples

The following code illustrates how to apply border around the Range with color from System.Drawing.Color structure.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["D2"].Text = "text";
            worksheet["C3"].Text = "in";
            worksheet["D3"].Text = "cell";

            //Set border
            worksheet["C2:D3"].BorderAround(ExcelLineStyle.Thick, System.Drawing.Color.Red);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

BorderInside()

Applies border inside the Range. The default line style is Thin.

Declaration
void BorderInside()
Examples

The following code illustrates how to apply border inside the Range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["D2"].Text = "text";
            worksheet["C3"].Text = "in";
            worksheet["D3"].Text = "cell";

            //Set border
            worksheet["C2:D3"].BorderInside();

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

BorderInside(ExcelLineStyle)

Applies border inside the Range with the specified ExcelLineStyle.

Declaration
void BorderInside(ExcelLineStyle borderLine)
Parameters
Type Name Description
ExcelLineStyle borderLine

Represents border line style.
Examples

The following code illustrates how to apply border inside the Range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["D2"].Text = "text";
            worksheet["C3"].Text = "in";
            worksheet["D3"].Text = "cell";

            //Set border
            worksheet["C2:D3"].BorderInside(ExcelLineStyle.Thick);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

BorderInside(ExcelLineStyle, ExcelKnownColors)

Applies border inside the Range with the specified ExcelLineStyle and ExcelKnownColors.

Declaration
void BorderInside(ExcelLineStyle borderLine, ExcelKnownColors borderColor)
Parameters
Type Name Description
ExcelLineStyle borderLine

Represents border line style.
ExcelKnownColors borderColor

Represents border color.
Examples

The following code illustrates how to apply border around the Range with ExcelKnownColors.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["D2"].Text = "text";
            worksheet["C3"].Text = "in";
            worksheet["D3"].Text = "cell";

            //Set border
            worksheet["C2:D3"].BorderInside(ExcelLineStyle.Thick, ExcelKnownColors.Red);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

BorderInside(ExcelLineStyle, Color)

Applies border inside the Range with the specified ExcelLineStyle and System.Drawing.Color.

Declaration
void BorderInside(ExcelLineStyle borderLine, Color borderColor)
Parameters
Type Name Description
ExcelLineStyle borderLine

Represents border line style.
System.Drawing.Color borderColor

Represents border color.
Examples

The following code illustrates how to apply border inside the Range with color from System.Drawing.Color structure.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["D2"].Text = "text";
            worksheet["C3"].Text = "in";
            worksheet["D3"].Text = "cell";

            //Set border
            worksheet["C2:D3"].BorderInside(ExcelLineStyle.Thick, System.Drawing.Color.Red);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

BorderNone()

Applies no border in the Range.

Declaration
void BorderNone()
Examples

The following code illustrates how to remove borders in the Range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";

            //Set borders
            IBorders borders = worksheet["C2"].Borders;

            //Set border color
            borders[ExcelBordersIndex.EdgeTop].Color = ExcelKnownColors.Red;
            borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Blue;

            //Set line style
            borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thick;
            borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thick;

            //Remove borders
            worksheet["C2"].BorderNone();

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

Clear()

Clears content in the Range.

Declaration
void Clear()
Examples

The following code illustrates how to clear the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

// Clears the Range �A4� .
sheet.Range["A4"].Clear();

workbook.SaveAs("ClearRange.xlsx");
workbook.Version = ExcelVersion.Excel2013;
workbook.Close();
excelEngine.Dispose();

Clear(ExcelClearOptions)

Clears the Range with the specified ExcelClearOptions.

Declaration
void Clear(ExcelClearOptions option)
Parameters
Type Name Description
ExcelClearOptions option

Represents the clear options.
Examples

The following code illustrates how to clear the Range with clear options.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

// Clears the Range �A4� with its clear options.
sheet.Range["A4"].Clear(ExcelClearOptions.ClearAll);

workbook.SaveAs("ClearRange.xlsx");
workbook.Version = ExcelVersion.Excel2013;
workbook.Close();
excelEngine.Dispose();

Clear(ExcelMoveDirection)

Clears the Range and shifts the cells Up or Left.

Declaration
void Clear(ExcelMoveDirection direction)
Parameters
Type Name Description
ExcelMoveDirection direction

Shifts the cells to the specified direction.
Examples

The following code illustrates how to clear the Range with specified direction.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

// Clearing a Range �A4� with move options.
sheet.Range["A4"].Clear(ExcelMoveDirection.MoveLeft);

workbook.SaveAs("ClearRange.xlsx");
workbook.Version = ExcelVersion.Excel2013;
workbook.Close();
excelEngine.Dispose();

Clear(ExcelMoveDirection, ExcelCopyRangeOptions)

Clears the Range with the specified ExcelMoveDirection and ExcelCopyRangeOptions.

Declaration
void Clear(ExcelMoveDirection direction, ExcelCopyRangeOptions options)
Parameters
Type Name Description
ExcelMoveDirection direction

Shifts the cells to the specified direction.
ExcelCopyRangeOptions options

Copies the cells with the specified copy Range options.
Examples

The following code illustrates how to clear the Range with specified direction and copy Range options.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

// Clearing a Range �A4� with copy Range and move options.
sheet.Range["A4"].Clear(ExcelMoveDirection.MoveLeft,ExcelCopyRangeOptions.All);

workbook.SaveAs("ClearRange.xlsx");
workbook.Version = ExcelVersion.Excel2013;
workbook.Close();
excelEngine.Dispose();

Clear(Boolean)

Clears content and formats in the Range.

Declaration
void Clear(bool isClearFormat)
Parameters
Type Name Description
System.Boolean isClearFormat

True to clear format.
Examples

The following code illustrates how to clear the Range with formats.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["D2"].Text = "text";
            worksheet["C3"].Text = "in";
            worksheet["D3"].Text = "cell";

            //Set border
            worksheet["C2:D3"].BorderInside();

            //Clear format
            worksheet["C2:D3"].Clear(true);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

CollapseGroup(ExcelGroupBy)

Collapses the group by rows/columns.

Declaration
void CollapseGroup(ExcelGroupBy groupBy)
Parameters
Type Name Description
ExcelGroupBy groupBy

Represents to collapse the group by rows/columns.
Examples

The following code illustrates how to collapse the group in the Range.

 
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;

IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

//Collapse group
worksheet.Range["A5:A15"].CollapseGroup(ExcelGroupBy.ByRows);

workbook.SaveAs("CollapseGroup.xlsx");
workbook.Close();
excelEngine.Dispose();

CopyTo(IRange)

Copies the Range to the specified Range (without updating formulas).

Declaration
IRange CopyTo(IRange destination)
Parameters
Type Name Description
IRange destination

Represents the destination Range to copy.
Returns
Type Description
IRange

Returns the copied Range.
Examples

The following code illustrates how to copy the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

// Copying a Range �A1� to �A5�.
IRange source = sheet.Range["A1"];
IRange destination = sheet.Range["A5"];
source.CopyTo(destination);

workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("CopyRange.xlsx");
workbook.Close();
excelEngine.Dispose();

CopyTo(IRange, ExcelCopyRangeOptions)

Copies the Range to the specified destination Range with copy options.

Declaration
IRange CopyTo(IRange destination, ExcelCopyRangeOptions options)
Parameters
Type Name Description
IRange destination

Represents the destination Range to copy.
ExcelCopyRangeOptions options

Represents the copy Range options.
Returns
Type Description
IRange

Returns the copied Range.
Examples

The following code illustrates how to copy the Range with copy options.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

// Copying a Range �A1� to �A5�.
IRange source = sheet.Range["A1"];
IRange destination = sheet.Range["A5"];
source.CopyTo(destination, ExcelCopyRangeOptions.All);
workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("CopyRange.xlsx");
workbook.Close();
excelEngine.Dispose();

CopyTo(IRange, ExcelCopyRangeOptions, Boolean)

Copies the Range to the specified destination Range with copy options and skip blanks option.

Declaration
IRange CopyTo(IRange destination, ExcelCopyRangeOptions options, bool skipBlanks)
Parameters
Type Name Description
IRange destination

Represents the destination Range to copy.
ExcelCopyRangeOptions options

Represents the copy Range options.
System.Boolean skipBlanks

Skip blank cells while copying the range from source to destination.
Returns
Type Description
IRange

Returns the copied Range.
Remarks

This method can be used to prevent copying blank cells in the destination range. If "true", then blank cells are not copied from source range.

Examples

The following code illustrates how to copy the Range with skip blanks option.

        using(ExcelEngine engine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = engine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
            IWorksheet worksheet = workbook.Worksheets[0];

            //Load Data
            worksheet["A1"].Value = "A";
            worksheet["A3"].Value = "B";
            worksheet["A5"].Value = "C";
            worksheet["A7"].Value = "D";
            worksheet["B1"].Value = "E";
            worksheet["B2"].Value = "F";
            worksheet["B4"].Value = "G";
            worksheet["B6"].Value = "H";

            //Apply styles
            worksheet["A1:A7"].CellStyle.ColorIndex = ExcelKnownColors.Yellow;

            //Skip blanks while copying
            worksheet["A1:A7"].CopyTo(worksheet["B1"], ExcelCopyRangeOptions.All, true);

            workbook.SaveAs("SkipBlanks.xlsx");
            workbook.Close();
        }

CopyTo(IRange, Boolean)

Copies the Range to the specified destination Range with copy options.

Declaration
IRange CopyTo(IRange destination, bool pasteLink)
Parameters
Type Name Description
IRange destination

Represents the destination Range to copy.
System.Boolean pasteLink

Represents if the pasteLinkOptions implement or not .
Returns
Type Description
IRange

Returns the copied Range.
Examples

The following code illustrates how to copy the Range with pasteLink options.

ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IRange source = worksheet["A1:D5"];
IRange destination = worksheet["E10"];
// Copy range as link from source to destination
source.CopyTo(destination, true);
workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("CopyRange.xlsx");
workbook.Close();
engine.Dispose();

ExpandGroup(ExcelGroupBy)

Expands the group by rows/columns.

Declaration
void ExpandGroup(ExcelGroupBy groupBy)
Parameters
Type Name Description
ExcelGroupBy groupBy

Represents to expand the group by rows/columns.
Examples

The following code illustrates how to expand the group in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;

IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

//Expand group with flag set to expand parent
worksheet.Range["A5:A15"].ExpandGroup(ExcelGroupBy.ByRows);

workbook.SaveAs("ExpandGroup.xlsx");
workbook.Close();
excelEngine.Dispose();

ExpandGroup(ExcelGroupBy, ExpandCollapseFlags)

Expands the group by row/column with collapse option.

Declaration
void ExpandGroup(ExcelGroupBy groupBy, ExpandCollapseFlags flags)
Parameters
Type Name Description
ExcelGroupBy groupBy

Represents to expand the group by rows/columns.
ExpandCollapseFlags flags

Represent the collapses options.
Examples

The following code illustrates how to perform ExpandGroup in the Range with collapse option.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;

IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

//Expand group with flag set to expand parent
worksheet.Range["A5:A15"].ExpandGroup(ExcelGroupBy.ByRows, ExpandCollapseFlags.ExpandParent);

workbook.SaveAs("ExpandGroup.xlsx");
workbook.Close();
excelEngine.Dispose();

FindAll(Boolean)

Returns the cells with a specified bool value.

Declaration
IRange[] FindAll(bool findValue)
Parameters
Type Name Description
System.Boolean findValue

Value to be found.
Returns
Type Description
IRange[]

Returns the cells with a specified bool value, or null if value was not found.
Examples

The following code illustrates how to find the cells with specified bool value in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];

//Find all with bool
IRange[] results = sheet["A2:K100"].FindAll(true);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindAll(DateTime)

Returns the cells with a specified DateTime value.

Declaration
IRange[] FindAll(DateTime findValue)
Parameters
Type Name Description
System.DateTime findValue

Value to be found.
Returns
Type Description
IRange[]

Returns the cells with a specified DateTime value, or null if value was not found.
Examples

The following code illustrates how to find the cells with specified DateTime value in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];

//Find all with DateTime
IRange[] results = sheet["A2:K100"].FindAll(DateTime.Now);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindAll(Double, ExcelFindType)

Returns the cells with a specified double value and ExcelFindType.

Declaration
IRange[] FindAll(double findValue, ExcelFindType flags)
Parameters
Type Name Description
System.Double findValue

Value to be found.
ExcelFindType flags

Represents type of the value to be found.
Returns
Type Description
IRange[]

Returns the cells with a specified double value, or null if value was not found.
Examples

The following code illustrates how to find the cells with specified double value in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];

//Find All with number
IRange[] results = sheet["A2:K100"].FindAll(100.32, ExcelFindType.Number);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindAll(String, ExcelFindType)

Returns the cells with a specified string value and ExcelFindType.

Declaration
IRange[] FindAll(string findValue, ExcelFindType flags)
Parameters
Type Name Description
System.String findValue

Value to be found.
ExcelFindType flags

Represents type of the value to be found.
Returns
Type Description
IRange[]

Returns the cells with a specified string value, or null if value was not found.
Examples

The following code illustrates how to find the cells with specified string value in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];

//Find all with string
IRange[] results = sheet["A2:K100"].FindAll("Simple text", ExcelFindType.Text);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindAll(TimeSpan)

Returns the cells with a specified TimeSpan value.

Declaration
IRange[] FindAll(TimeSpan findValue)
Parameters
Type Name Description
System.TimeSpan findValue

Value to be found.
Returns
Type Description
IRange[]

Returns the cells with a specified TimeSpan value, or null if value was not found.
Examples

The following code illustrates how to find the cells with specified TimeSpan value in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];

//Find All with Timespan
IRange[] results = sheet["A2:K100"].FindAll(TimeSpan.MinValue);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindFirst(Boolean)

Returns the first occurrence cell with the specified bool value.

Declaration
IRange FindFirst(bool findValue)
Parameters
Type Name Description
System.Boolean findValue

Value to be found.
Returns
Type Description
IRange

Returns the first cell with a specified bool value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified bool value in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];

//Find First with bool
IRange result = sheet["A2:K100"].FindFirst(true);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindFirst(DateTime)

Returns the first occurrence cell with the specified DateTime value.

Declaration
IRange FindFirst(DateTime findValue)
Parameters
Type Name Description
System.DateTime findValue

Value to be found.
Returns
Type Description
IRange

Returns the first cell with a specified DateTime value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified DateTime value in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];

//Find First with DateTime
IRange result = sheet["A2:K100"].FindFirst(DateTime.Now);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindFirst(Double, ExcelFindType)

Returns the first occurrence cell with the specified double value and ExcelFindType.

Declaration
IRange FindFirst(double findValue, ExcelFindType flags)
Parameters
Type Name Description
System.Double findValue

Value to found.
ExcelFindType flags

Flag that represent type of search.
Returns
Type Description
IRange

Returns the first cell with a specified double value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified double value in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];

//Find First with number
IRange result = sheet["A2:K100"].FindFirst(100.32, ExcelFindType.Number);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindFirst(String, ExcelFindType)

Returns the first occurrence cell with the specified string value and ExcelFindType.

Declaration
IRange FindFirst(string findValue, ExcelFindType flags)
Parameters
Type Name Description
System.String findValue

Value to found.
ExcelFindType flags

Flag that represent type of search.
Returns
Type Description
IRange

Returns the first cell with a specified string value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified string value in the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];

//Find First with string
IRange result = sheet["A2:K100"].FindFirst("Simple text", ExcelFindType.Text);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindFirst(TimeSpan)

Returns the first occurrence cell with the specified TimeSpan value.

Declaration
IRange FindFirst(TimeSpan findValue)
Parameters
Type Name Description
System.TimeSpan findValue

Value to be found.
Returns
Type Description
IRange

Returns the first cell with a specified TimeSpan value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified TimeSpan value in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];

//Find First with TimeSpan
IRange result = sheet["A2:K100"].FindFirst(TimeSpan.MinValue);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FreezePanes()

Keep rows and columns visible in the Range while the rest of the worksheet scrolls.

Declaration
void FreezePanes()
Examples

The following code illustrates how to freeze a pane in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];

//Applying Freeze Pane to the sheet by specifying a cell.
sheet.Range["B2"].FreezePanes();

workbook.SaveAs("Freeze.xlsx");
workbook.Close();
excelEngine.Dispose();

GetDependents()

Gets the dependent cells which refer to other cells.

Declaration
IRange[] GetDependents()
Returns
Type Description
IRange[]

Returns the dependent cells or null if value was not found.
Examples

The following code illustrates how to get the dependent cells from the worksheet.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

IRange[] results = sheet["A1"].GetDependents();

string fileName = "Dependents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

GetDependents(Boolean)

Gets the dependent cells which refer to other cells.

Declaration
IRange[] GetDependents(bool isEntireWorkbook)
Parameters
Type Name Description
System.Boolean isEntireWorkbook

Indicates whether to check from other worksheets.
Returns
Type Description
IRange[]

Returns the dependent cells with a specified bool value or null if value was not found.
Examples

The following code illustrates how to get the dependents cells from all the worksheets.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

IRange[] results = sheet["A1"].GetDependents(true);

string fileName = "Dependents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

GetDirectDependents()

Gets the direct dependent cells which refer to other cells.

Declaration
IRange[] GetDirectDependents()
Returns
Type Description
IRange[]

Returns the direct dependent cells or null if value was not found.
Examples

The following code illustrates how to get the direct dependent cells from the worksheet.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

IRange[] results = sheet["A1"].GetDirectDependents();

string fileName = "Dependents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

GetDirectDependents(Boolean)

Gets the direct dependent cells which refer to other cells.

Declaration
IRange[] GetDirectDependents(bool isEntireWorkbook)
Parameters
Type Name Description
System.Boolean isEntireWorkbook

Indicates whether to check from other worksheets.
Returns
Type Description
IRange[]

Returns the direct dependent cells with a specified bool value or null if value was not found.
Examples

The following code illustrates how to get the direct dependents cells from all the worksheets.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

IRange[] results = sheet["A1"].GetDirectDependents(true);

string fileName = "Dependents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

GetDirectPrecedents()

Gets the direct precedent cells which are referred by a formula in another cell.

Declaration
IRange[] GetDirectPrecedents()
Returns
Type Description
IRange[]

Returns the direct precedent cells or null if value was not found.
Examples

The following code illustrates how to get the direct precedent cells from the worksheet.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

IRange[] results = sheet["A1"].GetPrecedents();

string fileName = "Precedents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

GetDirectPrecedents(Boolean)

Gets the direct precedent cells which are referred by a formula in another cell.

Declaration
IRange[] GetDirectPrecedents(bool isEntireWorkbook)
Parameters
Type Name Description
System.Boolean isEntireWorkbook

Indicates whether to check from other worksheets.
Returns
Type Description
IRange[]

Returns the direct precedent cells with a specified bool value or null if value was not found.
Examples

The following code illustrates how to get the direct precedent cells from all the worksheets.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

IRange[] results = sheet["A1"].GetPrecedents(true);

string fileName = "Precedents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

GetPrecedents()

Gets the precedent cells which are referred by a formula in another cell.

Declaration
IRange[] GetPrecedents()
Returns
Type Description
IRange[]

Returns the precedent cells or null if value was not found.
Examples

The following code illustrates how to get the precedent cells from the worksheet.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

IRange[] results = sheet["A1"].GetPrecedents();

string fileName = "Precedents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

GetPrecedents(Boolean)

Gets the precedent cells which are referred by a formula in another cell.

Declaration
IRange[] GetPrecedents(bool isEntireWorkbook)
Parameters
Type Name Description
System.Boolean isEntireWorkbook

Indicates whether to check from other worksheets.
Returns
Type Description
IRange[]

Returns the precedent cells with a specified bool value or null if value was not found.
Examples

The following code illustrates how to get the precedent cells from all the worksheets.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

IRange[] results = sheet["A1"].GetPrecedents(true);

string fileName = "Precedents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

Group(ExcelGroupBy)

Groups the Range by row/column.

Declaration
IRange Group(ExcelGroupBy groupBy)
Parameters
Type Name Description
ExcelGroupBy groupBy

Represents to expand the group by rows/columns.
Returns
Type Description
IRange

Returns the Range after grouping.
Examples

The following code illustrates how to group the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

//Group Rows
worksheet.Range["A4:A6"].Group(ExcelGroupBy.ByRows);

//Group Columns
worksheet.Range["C1:F1"].Group(ExcelGroupBy.ByColumns);

workbook.SaveAs("Grouping.xlsx");
workbook.Close();
excelEngine.Dispose();

Group(ExcelGroupBy, Boolean)

Groups the Range by row/column with collapse flag.

Declaration
IRange Group(ExcelGroupBy groupBy, bool bCollapsed)
Parameters
Type Name Description
ExcelGroupBy groupBy

Represents to expand the group by rows/columns.
System.Boolean bCollapsed

Indicates whether group should be collapsed.
Returns
Type Description
IRange

Returns the Range after grouping.
Examples

The following code illustrates how to group the Range with collapse flag.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

//Group Rows
worksheet.Range["A1:A3"].Group(ExcelGroupBy.ByRows, true);

//Group Columns
worksheet.Range["A1:B1"].Group(ExcelGroupBy.ByColumns, false);

workbook.SaveAs("Grouping.xlsx");
workbook.Close();
excelEngine.Dispose();

IntersectWith(IRange)

Returns intersection of the Range with specified Range.

Declaration
IRange IntersectWith(IRange range)
Parameters
Type Name Description
IRange range

Range to be intersected.
Returns
Type Description
IRange

Returns the intersection Range;if there is no intersection, NULL is returned.
Examples

The following code illustrates how to perform intersectwith in the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

//Merging cells
IRange range = worksheet.Range["A16:C16"];
IRange commonRange = worksheet.Range["B16:D16"].IntersectWith(range);
workbook.SaveAs("Intersection.xlsx");
workbook.Close();
excelEngine.Dispose();

Max()

Returns maximum value from numeric cells in the range.

Declaration
double Max()
Returns
Type Description
System.Double
Examples

The following code illustrates how to find the maximum value from numeric cells in the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

//Set value to cells
worksheet.Range["A1"].Value = "10";
worksheet.Range["A2"].Value = "20";
worksheet.Range["A3"].Value = "30";

//Returns maximum value from numeric cells in the range.
double maximum = worksheet.Range["A1:A3"].Max();

workbook.SaveAs("Max.xlsx");
workbook.Close();
excelEngine.Dispose();

Max(Boolean)

Returns maximum value from numeric cells in the range.

Declaration
double Max(bool considerDateAsNumber)
Parameters
Type Name Description
System.Boolean considerDateAsNumber

Considers numeric value of date formatted cells for calculation.
Returns
Type Description
System.Double
Examples

The following code illustrates how to find the maximum value from numeric cells in the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

//Set value to cells
worksheet.Range["A1"].Value = "10";
worksheet.Range["A2"].Value = "20";
worksheet.Range["A3"].Value = "30";

//Returns maximum value from Numeric cells in the given range.
double maximum = worksheet.Range["A1:A3"].Max();

workbook.SaveAs("Max.xlsx");
workbook.Close();
excelEngine.Dispose();

Merge()

Combines the contents of the selected cells in a new larger cell.

Declaration
void Merge()
Remarks

To know more about merging refer Merging and Un-Merging Cells.

Examples

The following code illustrates how to merge the Range.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["A1"].Text = "Merged cell";

            //Merge cells
            worksheet["A1:B1"].Merge();

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close(); 
        }

Merge(Boolean)

Combines the contents of the selected cells in a new larger cell with clear option flag.

Declaration
void Merge(bool clearCells)
Parameters
Type Name Description
System.Boolean clearCells

Clears the Merged cell.
Examples

The following code illustrates how to merge the Range with clear option.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);                               
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["A1"].Text = "Merged cell";
            worksheet["B1"].Text = "sample";

            //Merge cells
            worksheet["A1:B1"].Merge(true);

            //Unmerge cells
            worksheet["A1:B1"].UnMerge();

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close(); 
        }

MergeWith(IRange)

Merges the range with specified range and returns merged range.

Declaration
IRange MergeWith(IRange range)
Parameters
Type Name Description
IRange range

Range to be merged with.
Returns
Type Description
IRange

Returns a merged range; if the Range is unable to merge, null is returned.
Examples

The following code illustrates how to check whether two ranges are mergable or not.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;                                
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Merge
            worksheet["A2:B2"].Merge();

            //Get mergable range
            IRange mergableRange = worksheet["A2"].MergeArea.MergeWith(worksheet["C2"]);

            //Check mergable Area
            if (mergableRange != null)
                Console.Write("Mergable!");
            else
                Console.Write("Not Mergable!");

            //Save and Dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//Mergable!

Min()

Returns minumum value from numeric cells in the range.

Declaration
double Min()
Returns
Type Description
System.Double
Examples

The following code illustrates how to find the minumum value from numeric cells in the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

//Set value to cells
worksheet.Range["A1"].Value = "10";
worksheet.Range["A2"].Value = "20";
worksheet.Range["A3"].Value = "30";

//Returns minumum value from Numeric cells in the given range.
double minimum = worksheet.Range["A1:A3"].Min();

workbook.SaveAs("Min.xlsx");
workbook.Close();
excelEngine.Dispose();

Min(Boolean)

Returns minumum value from numeric cells in the range.

Declaration
double Min(bool considerDateAsNumber)
Parameters
Type Name Description
System.Boolean considerDateAsNumber

Considers numeric value of date formatted cells for calculation.
Returns
Type Description
System.Double
Examples

The following code illustrates how to find the minumum value from numeric cells in the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

//Set value to cells
worksheet.Range["A1"].Value = "10";
worksheet.Range["A2"].Value = "20";
worksheet.Range["A3"].Value = "30";

//Returns minumum value from Numeric cells in the range.
double minimum = worksheet.Range["A1:A3"].Min(true);

workbook.SaveAs("Min.xlsx");
workbook.Close();
excelEngine.Dispose();

MoveTo(IRange)

Moves cells to the specified Range (without updating formulas).

Declaration
void MoveTo(IRange destination)
Parameters
Type Name Description
IRange destination

Represents the destination Range to move.
Examples

The following code illustrates how to move the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];

// Moving a Range �A1� to �A5�.
IRange source = sheet.Range["A1"];
IRange destination = sheet.Range["A5"];
source.MoveTo(destination);

workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("MoveRange.xlsx");
workbook.Close();
excelEngine.Dispose();

Offset(Int32, Int32)

Returns an IRange object that represents a range or cell that is offset from the specified range or cell.

Declaration
IRange Offset(int rowOffset, int columnOffset)
Parameters
Type Name Description
System.Int32 rowOffset

The number of rows (positive, negative, or 0 (zero)) by which the range is to be offset
System.Int32 columnOffset

The number of columns (positive, negative, or 0 (zero)) by which the range is to be offset
Returns
Type Description
IRange

Returns a range which is shifted by the specified number of rows and columns.
Remarks

Use the Offset method to access the cell range of the same size as the current range which is shifted by the specified number of rows and columns.

Examples
 
   using(ExcelEngine engine = new ExcelEngine())
   {
      IApplication application = engine.Excel;
      application.DefaultVersion = ExcelVersion.Excel2013;
      IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
      IWorksheet worksheet = workbook.Worksheets[0];
      IRange range = worksheet.Range[5,6];
      var offset = range.Offset(-3,-1);
      var address2 = offset.AddressLocal;
      workbook.SaveAs("output.xlsx");
   }

Replace(String, DataColumn, Boolean)

Replaces the string with the specified datacolumn.

Declaration
void Replace(string oldValue, DataColumn newValues, bool isFieldNamesShown)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.Data.DataColumn newValues

DataColumn with new data.
System.Boolean isFieldNamesShown

Indicates whether field name must be shown.
Examples

The following code snippet illustrates how to replace the string value with data column.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "AB2";
//Create the data table
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Rows.Add(1); 
System.Data.DataColumn dataColumn = table.Columns[0];
// Replace the value with data column.
sheet.Range["A1:A3"].Replace("AB2", dataColumn, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

Replace(String, DataTable, Boolean)

Replaces the string with the specified datatable.

Declaration
void Replace(string oldValue, DataTable newValues, bool isFieldNamesShown)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.Data.DataTable newValues

Data table with new data.
System.Boolean isFieldNamesShown

Indicates whether field name must be shown.
Examples

The following code snippet illustrates how to replace the string value with data table.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "AB2";
//Create the data table
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Rows.Add(1);
// Replace the value with data table.
sheet.Range["A1:A3"].Replace("AB2", table, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

Replace(String, DateTime)

Replaces the string with the specified DateTime value.

Declaration
void Replace(string oldValue, DateTime newValue)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.DateTime newValue

The datetime value to replace all occurrences of oldValue.
Examples

The following code illustrates how to replace the string value with datetime.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Find";
string oldValue = "Find";
DateTime dateTime = DateTime.Now;
sheet.Range["A1:A3"].Replace(oldValue,dateTime);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

Replace(String, Double)

Replaces the string with the specified double value.

Declaration
void Replace(string oldValue, double newValue)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.Double newValue

The double value to replace all occurrences of oldValue.
Examples

The following code snippet illustrates how to replace the string value with double.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Ten";
string oldValue = "Ten";
sheet.Range["A1:A3"].Replace(oldValue,10.0);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

Replace(String, Double[], Boolean)

Replaces the string with the specified array of double values.

Declaration
void Replace(string oldValue, double[] newValues, bool isVertical)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.Double[] newValues

Array of new values.
System.Boolean isVertical

Indicates whether array should be inserted vertically.
Examples

The following code snippet illustrates how to replace the string with array of double values.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Find";
string oldValue = "Find";
double[] newValues = { 1.00, 3.00 };
sheet.Range["A1:A3"].Replace(oldValue, newValues, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

Replace(String, Int32[], Boolean)

Replaces the string with the specified array of int values.

Declaration
void Replace(string oldValue, int[] newValues, bool isVertical)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.Int32[] newValues

Array of new values.
System.Boolean isVertical

Indicates whether array should be inserted vertically.
Examples

The following code snippet illustrates how to replace the string with array of int values.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Find";
string oldValue = "Find";
int[] newValues = { 1, 2 };
sheet.Range["A1:A3"].Replace(oldValue, newValues, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

Replace(String, String)

Replaces the string with the specified string value.

Declaration
void Replace(string oldValue, string newValue)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.String newValue

The string to replace all occurrences of oldValue.
Examples

The following code snippet illustrates how to replace the string with another string.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Find";
string oldValue = "Find";
string newValue = "NewValue";
sheet.Range["A1:A3"].Replace(oldValue, newValue);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

Replace(String, String, ExcelFindOptions)

Replaces the string with the specified string value based on the given ExcelFindOptions.

Declaration
void Replace(string oldValue, string newValue, ExcelFindOptions findOptions)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.String newValue

The string to replace all occurrences of oldValue.
ExcelFindOptions findOptions

Specifies the find options for the oldValue.
Examples

The following code snippet illustrates how to replace the string with another string.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Find";
string oldValue = "Find";
string newValue = "NewValue";
sheet.Range["A1:A3"].Replace(oldValue, newValue, ExcelFindOptions.MatchCase);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

Replace(String, String[], Boolean)

Replaces the string with the specified array of string values.

Declaration
void Replace(string oldValue, string[] newValues, bool isVertical)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.String[] newValues

Array of new values.
System.Boolean isVertical

Indicates whether array should be inserted vertically.
Examples

The following code snippet illustrates how to replace the string with array of string values.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Find";
string oldValue = "Find";
string[] newValues = { "X values", "Y values" };
sheet.Range["A1:A3"].Replace(oldValue, newValues, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

Resize(Int32, Int32)

Resizes the specified range a IRange object that represents the resized range.

Declaration
IRange Resize(int rowSize, int columnSize)
Parameters
Type Name Description
System.Int32 rowSize

The number of rows in the new range.
System.Int32 columnSize

The number of columns in the new range.
Returns
Type Description
IRange

Returns the resized range.
Remarks

Use the Resize method to change the size of range by specified rows and columns from the current range.

Examples
 
   using(ExcelEngine engine = new ExcelEngine())
   {
      IApplication application = engine.Excel;
      application.DefaultVersion = ExcelVersion.Excel2013;
      IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
      IWorksheet worksheet = workbook.Worksheets[0];
      IRange range = worksheet.Range[5,6];
      var resize = range.Resize(1000,2000);
      var address = resize.AddressLocal;
      workbook.SaveAs("output.xlsx");
   }

SubTotal(Int32, ConsolidationFunction, Int32[])

Creates subtotals for the Range.

Declaration
void SubTotal(int groupBy, ConsolidationFunction function, int[] totalList)
Parameters
Type Name Description
System.Int32 groupBy

Column index based on which grouping should be done.
ConsolidationFunction function

Represents a consolidation function to be applied.
System.Int32[] totalList

List of column indexes on which subtotal is calculated.
Examples

The following code illustrates how to subtotal the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;

IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

//Set the range for subtotaling
IRange range = worksheet.Range["C3:G12"];

//Perform subtotals for the Range with every change in first column
//and subtotals to be included for specified list of columns
range.SubTotal(0, ConsolidationFunction.Sum, new int[] { 2, 3, 4 });

workbook.SaveAs("Subtotal.xlsx");
workbook.Close();
excelEngine.Dispose();

SubTotal(Int32, ConsolidationFunction, Int32[], Boolean, Boolean, Boolean)

Creates subtotals for the Range with the specified formatting flags.

Declaration
void SubTotal(int groupBy, ConsolidationFunction function, int[] totalList, bool replace, bool pageBreaks, bool summaryBelowData)
Parameters
Type Name Description
System.Int32 groupBy

Column index based on which grouping should be done
ConsolidationFunction function

Represents a consolidation function to be applied.
System.Int32[] totalList

List of column indexes on which subtotal is calculated.
System.Boolean replace

Indicates whether existing SubTotal must be replaced.
System.Boolean pageBreaks

Indicates whether page break must be inserted.
System.Boolean summaryBelowData

Indicates whether SummaryBelowData to be shown
Examples

The following code illustrates how to subtotal the Range with formats.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;

IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

//Set the range for subtotaling
IRange range = worksheet.Range["C3:G12"];

//Perform subtotals for the range with every change in first column
//and subtotals to be included for specified list of columns
range.SubTotal(0, ConsolidationFunction.Sum, new int[] { 2, 3, 4 }, false, true, true);

workbook.SaveAs("Subtotal.xlsx");
workbook.Close();
excelEngine.Dispose();

SubTotal(Int32[], ConsolidationFunction, Int32[], Boolean, Boolean, Boolean)

Creates subtotals for the Range with multiple group by columns and the specified formatting flags.

Declaration
void SubTotal(int[] groupBy, ConsolidationFunction function, int[] totalList, bool replace, bool pageBreaks, bool summaryBelowData)
Parameters
Type Name Description
System.Int32[] groupBy

Column indexes based on which grouping is done. Given in the order of subtotal levels
ConsolidationFunction function

Represents a consolidation function to be applied.
System.Int32[] totalList

List of columns on which subtotal is calculated
System.Boolean replace

Replaces Existing SubTotal
System.Boolean pageBreaks

Inserts PageBreaks
System.Boolean summaryBelowData

SummaryBelowData to be shown
Examples

The following code illustrates how to subtotal the Range with formats.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;

IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

//Set the range for subtotaling
IRange range = worksheet.Range["C3:G12"];

//Perform subtotals for the range with every change in first column
//and subtotals to be included for specified list of columns
range.SubTotal( new int[] { 1, 2 }, ConsolidationFunction.Sum, new int[] { 2, 3, 4 }, false, true, true);

workbook.SaveAs("Subtotal.xlsx");
workbook.Close();
excelEngine.Dispose();

Sum()

Returns sum of numeric cells in the range.

Declaration
double Sum()
Returns
Type Description
System.Double
Examples

The following code illustrates how to find the sum of numeric cells in the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

//Set value to cells
worksheet.Range["A1:A3"].Value = "10";

//Returns Sum of numeric cells in the range.
double sum = worksheet.Range["A1:A3"].Sum();

workbook.SaveAs("Sum.xlsx");
workbook.Close();
excelEngine.Dispose();

Sum(Boolean)

Returns sum of numeric cells in the range.

Declaration
double Sum(bool considerDateAsNumber)
Parameters
Type Name Description
System.Boolean considerDateAsNumber

Considers numeric value of date formatted cells for calculation.
Returns
Type Description
System.Double
Examples

The following code illustrates how to find the sum of numeric cells in the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

//Set value to cells
worksheet.Range["A1:A3"].Value = "10";

//Returns Sum of numeric cells in the range.
double sum = worksheet.Range["A1:A3"].Sum(true);

workbook.SaveAs("Sum.xlsx");
workbook.Close();
excelEngine.Dispose();

Trim()

Trim the empty rows at top and bottom of the range, the empty columns at left and right of the range.

Declaration
IRange Trim()
Returns
Type Description
IRange

Returns the range after trim.
Examples

The following code illustrates how to Trim the empty rows at top and bottom of the range, the empty columns at left and right of the range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];

//Set value to cells
worksheet.Range["B1"].Value = "10";
worksheet.Range["B2"].Value = "20";
worksheet.Range["B3"].Value = "30";

//Returns the range after trim the given range.
IRange trim = worksheet.Range["A1:B3"].Trim();

workbook.SaveAs("Trim.xlsx");
workbook.Close();
excelEngine.Dispose();

Ungroup(ExcelGroupBy)

Ungroups the Range.

Declaration
IRange Ungroup(ExcelGroupBy groupBy)
Parameters
Type Name Description
ExcelGroupBy groupBy

Represents to expand the group by rows/columns.
Returns
Type Description
IRange

Returns the Range after ungrouping.
Examples

The following code illustrates how to ungroup the Range.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

//Ungroup Rows
worksheet.Range["A1:A3"].Ungroup(ExcelGroupBy.ByRows);

//Ungroup Columns
worksheet.Range["C1:F1"].Ungroup(ExcelGroupBy.ByColumns);

workbook.SaveAs("Ungrouping.xlsx");
workbook.Close();
excelEngine.Dispose();

UnMerge()

Separates merged cells into individual cells.

Declaration
void UnMerge()
Remarks

To know more about merging refer Merging and Un-Merging Cells.

Examples

The following code illustrates how to UnMerge the merged cells.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);                                
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["A1"].Text = "Merged cell";

            //Merge cells
            worksheet["A1:B1"].Merge();

            //Unmerge cells
            worksheet["A1:B1"].UnMerge();

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close(); 
        }