UWP

  • Code Examples
  • Upgrade Guide
  • User Guide
  • Demos
  • Support
  • Forums
  • Download
Interface IRange

    Show / Hide Table of Contents

    Interface IRange

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

    Inherited Members
    IParentApplication.Application
    IParentApplication.Parent
    Namespace: Syncfusion.XlsIO
    Assembly: Syncfusion.XlsIO.UWP.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
    Nullable<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(); 
            }

    Hyperlinks

    Gets hyperlinks in the Range. Read-only.

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

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

    Windows.UI.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.

    Windows.UI.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, 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(); 
            }

    Extension Methods

    DateTimeExtension.ToDateTime(Object)
    GridExcelHelper.ConvertExcelRangeToGridRange(IRange)
    GridExcelHelper.ConvertExcelRangeToGridRange(IRange, SpreadsheetGrid)
    Back to top Generated by DocFX
    Copyright © 2001 - 2023 Syncfusion Inc. All Rights Reserved