Class RangeImpl

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

Inheritance
System.Object
RangeImpl
MigrantRangeImpl
Implements
System.Collections.Generic.IEnumerable<IRange>
System.Collections.IEnumerable
System.IDisposable
Namespace: Syncfusion.XlsIO.Implementation
Assembly: Syncfusion.XlsIO.UWP.dll
Syntax
public class RangeImpl : Object, IReparse, ICombinedRange, IRange, IParentApplication, IEnumerable<IRange>, IEnumerable, ICellPositionFormat, INativePTG, IDisposable

Constructors

RangeImpl(IApplication, Object)

Creates an new instances of the RangeImpl class with specified IApplication and parent objects.

Declaration
public RangeImpl(IApplication application, object parent)
Parameters
Type Name Description
IApplication application

Represents application object for the Range.
System.Object parent

Represents parent object for the Range.
Exceptions
Type Condition
System.ApplicationException

The parent worksheet or workbook cannot be found.

RangeImpl(IApplication, Object, BiffRecordRaw, Boolean)

Creates an new instances of the RangeImpl class with specified IApplication ,Parent objects and ignore style flag from stream.

Declaration
public RangeImpl(IApplication application, object parent, BiffRecordRaw record, bool bIgnoreStyles)
Parameters
Type Name Description
IApplication application

Application object.
System.Object parent

Parent object.
Syncfusion.XlsIO.Parser.Biff_Records.BiffRecordRaw record

Range record.
System.Boolean bIgnoreStyles

Indicates whether to ignore styles.

RangeImpl(IApplication, Object, BiffRecordRaw[], Int32)

Creates an new instances of the RangeImpl class from Biff records from its specified position.

Declaration
public RangeImpl(IApplication application, object parent, BiffRecordRaw[] data, int position)
Parameters
Type Name Description
IApplication application

Application object for the Range.
System.Object parent

Parent object for the Range.
Syncfusion.XlsIO.Parser.Biff_Records.BiffRecordRaw[] data

Array of BiffRecordRaws which contains record for the Range.
System.Int32 position

Index of record for the Range.

RangeImpl(IApplication, Object, BiffRecordRaw[], ref Int32)

Creates an new instances of the RangeImpl class from Biff records from its specified position as a reference.

Declaration
public RangeImpl(IApplication application, object parent, BiffRecordRaw[] data, ref int position)
Parameters
Type Name Description
IApplication application

Application object for the Range.
System.Object parent

Parent object for the Range.
Syncfusion.XlsIO.Parser.Biff_Records.BiffRecordRaw[] data

Array of BiffRecordRaws which contains record for the Range.
System.Int32 position

Index of record for the Range.

RangeImpl(IApplication, Object, BiffRecordRaw[], ref Int32, Boolean)

Creates an new instances of the RangeImpl class from Biff records from its specified position with ignore style flag.

Declaration
public RangeImpl(IApplication application, object parent, BiffRecordRaw[] data, ref int position, bool ignoreStyles)
Parameters
Type Name Description
IApplication application

Application object for the Range.
System.Object parent

Parent object for the Range.
Syncfusion.XlsIO.Parser.Biff_Records.BiffRecordRaw[] data

Array of BiffRecordRaws which contains record for the Range.
System.Int32 position

Index of record for the Range.
System.Boolean ignoreStyles

Indicates whether to ignore styles.

RangeImpl(IApplication, Object, BiffReader)

Creates an new instances of the RangeImpl class with specified IApplication and Parent objects from stream.

Declaration
public RangeImpl(IApplication application, object parent, BiffReader reader)
Parameters
Type Name Description
IApplication application

Application object for the Range.
System.Object parent

Parent object for the Range.
BiffReader reader

Stream with Range data.

RangeImpl(IApplication, Object, List<BiffRecordRaw>, ref Int32, Boolean)

Creates an new instances of the RangeImpl class from collection of Biff records from its specified position with ignore style flag.

Declaration
public RangeImpl(IApplication application, object parent, List<BiffRecordRaw> data, ref int position, bool ignoreStyles)
Parameters
Type Name Description
IApplication application

Application object for the Range.
System.Object parent

Parent object for the Range.
System.Collections.Generic.List<Syncfusion.XlsIO.Parser.Biff_Records.BiffRecordRaw> data

Array of BiffRecordRaws which contains record for the Range.
System.Int32 position

Index of record for the Range.
System.Boolean ignoreStyles

Indicates whether to ignore styles.

RangeImpl(IApplication, Object, Int32, Int32)

Creates an new instances of the RangeImpl class with specified row and column.

Declaration
public RangeImpl(IApplication application, object parent, int column, int row)
Parameters
Type Name Description
IApplication application

Application object for the Range.
System.Object parent

Parent object for the Range.
System.Int32 column

Column index for the Range.
System.Int32 row

Row index for the Range.

RangeImpl(IApplication, Object, Int32, Int32, Int32, Int32)

Creates an new instances of the RangeImpl class with specified top-left and bottom-right corners.

Declaration
public RangeImpl(IApplication application, object parent, int firstCol, int firstRow, int lastCol, int lastRow)
Parameters
Type Name Description
IApplication application

Application object for the Range.
System.Object parent

Parent object for the Range.
System.Int32 firstCol

First column of the Range.
System.Int32 firstRow

First row of the Range.
System.Int32 lastCol

Last column of the Range.
System.Int32 lastRow

Last row of the Range.

Fields

DEF_DATE_FORMAT

Default format for date values.

Declaration
public const string DEF_DATE_FORMAT = "mm/dd/yyyy"
Field Value
Type
System.String

DEF_DATETIME_INDEX

Default date time index.

Declaration
public const int DEF_DATETIME_INDEX = 22
Field Value
Type
System.Int32

DEF_DEFAULT_STYLE

Default style.

Declaration
public const string DEF_DEFAULT_STYLE = "Normal"
Field Value
Type
System.String

DEF_GENERAL_FORMAT

General format.

Declaration
public const string DEF_GENERAL_FORMAT = "General"
Field Value
Type
System.String

DEF_NUMBER_FORMAT

Default format for number values.

Declaration
public const string DEF_NUMBER_FORMAT = "0.00"
Field Value
Type
System.String

DEF_OPTIONS

Default regular expressions options:

Declaration
protected const RegexOptions DEF_OPTIONS
Field Value
Type
System.Text.RegularExpressions.RegexOptions

DEF_TEXT_FORMAT

Default format for text values.

Declaration
public const string DEF_TEXT_FORMAT = "@"
Field Value
Type
System.String

DEF_TIME_FORMAT

Default format for time values.

Declaration
public const string DEF_TIME_FORMAT = "h:mm:ss"
Field Value
Type
System.String

DEF_UK_DATETIME_FORMAT

Default format for date time values.

Declaration
public const string DEF_UK_DATETIME_FORMAT = "dd/MM/yyyy HH:mm"
Field Value
Type
System.String

m_dataValidation

Represents data validation.

Declaration
protected DataValidationWrapper m_dataValidation
Field Value
Type
DataValidationWrapper

m_iBottomRow

Index of the bottom row.

Declaration
protected int m_iBottomRow
Field Value
Type
System.Int32

m_iLeftColumn

Index of the left column.

Declaration
protected int m_iLeftColumn
Field Value
Type
System.Int32

m_iRightColumn

Index of the right column.

Declaration
protected int m_iRightColumn
Field Value
Type
System.Int32

m_iTopRow

Index of the top row.

Declaration
protected int m_iTopRow
Field Value
Type
System.Int32

m_rtfString

Represents RTF string.

Declaration
protected IRTFWrapper m_rtfString
Field Value
Type
IRTFWrapper

m_style

Style wrapper for this Range.

Declaration
protected CellStyle m_style
Field Value
Type
CellStyle

Properties

Address

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

Declaration
public string Address { get; }
Property Value
Type
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
public string AddressGlobal { get; }
Property Value
Type
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();

AddressGlobal2007

Gets address global in the format required by Excel 2007.

Declaration
public string AddressGlobal2007 { get; }
Property Value
Type
System.String

AddressGlobalWithoutSheetName

Gets global address (with $ signs) without worksheet name.

Declaration
public string AddressGlobalWithoutSheetName { get; }
Property Value
Type
System.String

AddressLocal

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

Declaration
public string AddressLocal { get; }
Property Value
Type
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
public string AddressR1C1 { get; }
Property Value
Type
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
public string AddressR1C1Local { get; }
Property Value
Type
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();

Application

Gets the Application object.

Declaration
public IApplication Application { get; }
Property Value
Type
IApplication

AreFormulaArraysNotSeparated

Checks if all formula arrays partially contained by this Range are fully contained by this Range. Read-only.

Declaration
public bool AreFormulaArraysNotSeparated { get; }
Property Value
Type
System.Boolean

Boolean

Gets or sets boolean value in the Range.

Declaration
public bool Boolean { get; set; }
Property Value
Type
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
public IBorders Borders { get; }
Property Value
Type
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.

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

IWorksheet sheet = workbook.Worksheets[0];
IBorders borders = sheet["K3"].Borders;
 borders.LineStyle = ExcelLineStyle.Dashed;

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

BuiltInStyle

Gets or sets built in style in the Range.

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

CalculatedValue

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

Declaration
public string CalculatedValue { get; }
Property Value
Type
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();

CellIndex

If single cell, Gets its name; otherwise Gets -1. Read-only.

Declaration
protected long CellIndex { get; }
Property Value
Type
System.Int64

CellName

If it is a single cell, then it Gets its name; otherwise Gets NULL. Read-only.

Declaration
protected string CellName { get; }
Property Value
Type
System.String

Cells

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

Declaration
public IRange[] Cells { get; }
Property Value
Type
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();

CellsCount

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

Declaration
public int CellsCount { get; }
Property Value
Type
System.Int32

CellStyle

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

Declaration
public IStyle CellStyle { get; set; }
Property Value
Type
IStyle
Examples

The following code illustrates how to access CellStyle 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];
// Accessing cell style of the Range.
IStyle rangeStyle = sheet["K4"].CellStyle;
rangeStyle.Font.Bold = true;

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

CellStyleName

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

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

The following code illustrates how to access CellStyleName 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 new style with cell back color, fill pattern and font attribute
IStyle style = workbook.Styles.Add("GreenBoldFillDiagonal");
style.Color = System.Drawing.Color.LightGreen;
style.FillPattern = ExcelPattern.DarkUpwardDiagonal;
style.Font.Bold = true;

// Assigning the cell style name to the Range.
sheet["A1"].CellStyleName = "GreenBoldFillDiagonal";

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

CellType

Gets type of the cell. Read-only.

Declaration
protected RangeImpl.TCellType CellType { get; }
Property Value
Type
RangeImpl.TCellType

Column

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

Declaration
public int Column { get; }
Property Value
Type
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
public int ColumnGroupLevel { get; }
Property Value
Type
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
public IRange[] Columns { get; }
Property Value
Type
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
public double ColumnWidth { get; set; }
Property Value
Type
System.Double
Remarks

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

Comment

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

Declaration
public ICommentShape Comment { get; }
Property Value
Type
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
public IConditionalFormats ConditionalFormats { get; }
Property Value
Type
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();

ContainsNumber

Indicates whether Range contains number. Read-only.

Declaration
public bool ContainsNumber { get; }
Property Value
Type
System.Boolean

Count

Gets the number of objects in the collection. Read-only.

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

DataValidation

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

Declaration
public IDataValidation DataValidation { get; }
Property Value
Type
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
public DateTime DateTime { get; set; }
Property Value
Type
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
public string DisplayText { get; }
Property Value
Type
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
public IRange End { get; }
Property Value
Type
IRange

EntireColumn

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

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

EntireRow

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

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

Error

Gets or sets error value in the Range.

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

ExtendedFormatIndex

Sets or gets index of extended format.

Declaration
public ushort ExtendedFormatIndex { get; set; }
Property Value
Type
System.UInt16
Exceptions
Type Condition
System.ArgumentException

When method is applied for the Range that contains more than one cell.

FirstColumn

Gets or sets first column of the Range.

Declaration
protected int FirstColumn { get; set; }
Property Value
Type
System.Int32

FirstRow

Gets or sets first row of the Range.

Declaration
protected int FirstRow { get; set; }
Property Value
Type
System.Int32

Format

Read-only. Returns FormatRecord for this Range.

Declaration
protected FormatRecord Format { get; }
Property Value
Type
FormatRecord

Formula

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

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

FormulaArray

Represents array formula which can perform multiple calculations on one or more of the items in an array.

Declaration
public string FormulaArray { get; set; }
Property Value
Type
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
public string FormulaArrayR1C1 { get; set; }
Property Value
Type
System.String

FormulaArrays

Gets Dictionary where key is ArrayRecord that at least partially intersects with this Range. Read-only.

Declaration
public Dictionary<ArrayRecord, object> FormulaArrays { get; }
Property Value
Type
System.Collections.Generic.Dictionary<ArrayRecord, System.Object>

FormulaBoolValue

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

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

FormulaDateTime

Gets or sets formula DateTime value in the Range.

Declaration
public DateTime FormulaDateTime { get; set; }
Property Value
Type
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
public string FormulaErrorValue { get; set; }
Property Value
Type
System.String

FormulaHidden

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

Declaration
public bool FormulaHidden { get; set; }
Property Value
Type
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 the calculated value of the formula as a number.

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

FormulaR1C1

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

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

FormulaStringValue

Gets the Formula in the cell as a string.

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

FormulaValue

Gets formula value.

Declaration
public object FormulaValue { get; }
Property Value
Type
System.Object

HasBoolean

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

Declaration
public bool HasBoolean { get; }
Property Value
Type
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();

HasConditionFormats

Gets a Boolean value indicating whether each cell of the Range has some conditional formatting. Read-only.

Declaration
public bool HasConditionFormats { get; }
Property Value
Type
System.Boolean

HasDataValidation

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

Declaration
public bool HasDataValidation { get; }
Property Value
Type
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
public bool HasDateTime { get; }
Property Value
Type
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
public bool HasExternalFormula { get; }
Property Value
Type
System.Boolean

HasFormula

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

Declaration
public bool HasFormula { get; }
Property Value
Type
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
public bool HasFormulaArray { get; }
Property Value
Type
System.Boolean

HasFormulaBoolValue

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

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

HasFormulaDateTime

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

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

HasFormulaErrorValue

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

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

HasFormulaNumberValue

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

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

HasFormulaStringValue

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

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

HasNumber

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

Declaration
public bool HasNumber { get; }
Property Value
Type
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
public bool HasRichText { get; }
Property Value
Type
System.Boolean

HasString

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

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

HasStyle

Gets a Boolean value indicating whether Range's style differs from default style. Read-only.

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

HorizontalAlignment

Gets or sets the horizontal alignment for the specified object.

Declaration
public ExcelHAlign HorizontalAlignment { get; set; }
Property Value
Type
ExcelHAlign

Gets hyperlinks in the Range. Read-only.

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

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

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

IWorksheet sheet = workbook.Worksheets[0];

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

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

IgnoreErrorOptions

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

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

IndentLevel

Gets or sets the indent level for cell or Range.

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

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

InnerNumberFormat

Gets number format object corresponding to this Range. Read-only.

Declaration
public FormatImpl InnerNumberFormat { get; }
Property Value
Type
FormatImpl

InnerWorksheet

Gets parent worksheet. Read-only.

Declaration
protected WorksheetImpl InnerWorksheet { get; }
Property Value
Type
WorksheetImpl

IsBlank

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

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

IsBlankorHasStyle

Gets a Boolean value indicating whether the Range has value or style. Read-only.

Declaration
public bool IsBlankorHasStyle { get; }
Property Value
Type
System.Boolean

IsBoolean

Gets a Boolean value indicating whether cell type is boolean.

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

IsEntireColumn

True if it is Entire Column.

Declaration
public bool IsEntireColumn { get; set; }
Property Value
Type
System.Boolean

IsEntireRow

True if it is Entire row.

Declaration
public bool IsEntireRow { get; set; }
Property Value
Type
System.Boolean

IsError

Gets a Boolean value indicating whether Range contains error value.

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

IsGroupedByColumn

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

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

IsGroupedByRow

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

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

IsInitialized

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

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

IsMerged

Checks whether Range is a part of merged Range.

Declaration
public bool IsMerged { get; }
Property Value
Type
System.Boolean

IsSingleCell

Checks if the Range represents a single cell or Range of cells. Read-only.

Declaration
protected bool IsSingleCell { get; }
Property Value
Type
System.Boolean

IsStringsPreserved

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

Declaration
public Nullable<bool> IsStringsPreserved { get; set; }
Property Value
Type
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
public IRange this[int row, int column] { get; set; }
Parameters
Type Name Description
System.Int32 row
System.Int32 column
Property Value
Type
IRange

Item[Int32, Int32, Int32, Int32]

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

Declaration
public 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
IRange

Item[String]

Gets cell Range with R1C1Notation flag. Read-only.

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

Item[String, Boolean]

Gets cell Range with R1C1Notation flag. Read-only.

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

LastColumn

Gets or sets last column of the Range.

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

LastRow

Gets or sets last row of the Range.

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

MergeArea

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

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

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

Number

Gets or sets number value that is contained by Range.

Declaration
public double Number { get; set; }
Property Value
Type
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
public string NumberFormat { get; set; }
Property Value
Type
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 and access NumberFormat 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("NumberFormat.xlsx");
workbook.Close();
excelEngine.Dispose();

Parent

Gets the Parent object.

Declaration
public object Parent { get; }
Property Value
Type
System.Object

Record

Gets or sets Internal record.

Declaration
protected BiffRecordRaw Record { get; set; }
Property Value
Type
Syncfusion.XlsIO.Parser.Biff_Records.BiffRecordRaw

RichText

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

Declaration
public IRichTextString RichText { get; }
Property Value
Type
IRichTextString

RKSubRecord

Converts Range to rk subrecord. Read-only.

Declaration
protected MulRKRecord.RkRec RKSubRecord { get; }
Property Value
Type
MulRKRecord.RkRec
Exceptions
Type Condition
System.ArgumentException

When cell does not contain rk record.

Row

Gets row index of the first row in the Range. Read-only.One based index.

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

RowGroupLevel

Gets row group level. Read-only.

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

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

RowHeight

Gets the height of all the rows in the Range specified, measured in points.

Declaration
public double RowHeight { get; set; }
Property Value
Type
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.

Rows

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

Declaration
public IRange[] Rows { get; }
Property Value
Type
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();

StyleXFIndex

Gets index of extended format. Read-only.

Declaration
protected ushort StyleXFIndex { get; }
Property Value
Type
System.UInt16

Text

Gets or sets text contained by the cell.

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

ThreadedComment

Gets the threaded comment associated with the cell.

Declaration
public IThreadedComment ThreadedComment { get; }
Property Value
Type Description
IThreadedComment

The IThreadedComment object
Examples

The following code illustrates how to access ThreadedComment property of IRange

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

    //Access threaded comment from IRange
    IThreadedComment threadedComment = worksheet["C2"].ThreadedComment;            

    if(threadedComment != null)
        string text = threadedComment.Text;

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

TimeSpan

Gets or sets TimeSpan contained by the cell.

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

Value

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

Declaration
public string Value { get; set; }
Property Value
Type
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
public object Value2 { get; set; }
Property Value
Type
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
public ExcelVAlign VerticalAlignment { get; set; }
Property Value
Type
ExcelVAlign

Workbook

Gets parent workbook. Read-only.

Declaration
protected WorkbookImpl Workbook { get; }
Property Value
Type
WorkbookImpl

Worksheet

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

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

WorksheetName

Gets name of the parent worksheet.

Declaration
public string WorksheetName { get; }
Property Value
Type
System.String

WrapText

Gets or sets wrap text of the Range. Read/write Boolean.

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

Methods

Activate()

Activates the cell.

Declaration
public 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
public virtual 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. returns null if the cell has threaded comment

Declaration
public ICommentShape AddComment()
Returns
Type Description
ICommentShape

Returns a comment shape.
Examples

The following code illustrates how to insert Comments 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];
// 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);

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

AddComment(ICommentShape)

Adds copy of the comment.

Declaration
public void AddComment(ICommentShape comment)
Parameters
Type Name Description
ICommentShape comment

Comment to be added.

AddComment(Boolean)

Adds new comment or returns an old one if was present with parse options. Returns null if the cell has threaded comment.

Declaration
public ICommentShape AddComment(bool bIsParseOptions)
Parameters
Type Name Description
System.Boolean bIsParseOptions

Indicates whether to parse comment fill line options.
Returns
Type Description
ICommentShape

Returns a comment shape.

AddThreadedComment(String, DateTime)

Creates a threaded comment for the current cell with the specified text.

Declaration
public IThreadedComment AddThreadedComment(string text, DateTime creationTime)
Parameters
Type Name Description
System.String text

Threaded comment text
System.DateTime creationTime

Optional. The creation time of the threaded comment
Returns
Type Description
IThreadedComment

Returns the created instance of IThreadedComment. If the cell already contains comment(note) then threaded comment cannot be inserted and returns null
Remarks

The threaded comment created by mapping with author name from built-in document properties

Examples

The following code illustrates how to add threaded comment with text.

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

    //Add threaded comment with text
    IThreadedComment threadedComment = worksheet["C2"].AddThreadedComment("sample text");            

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

AddThreadedComment(String, String, DateTime)

Creates a threaded comment for the current cell with the specified text and author.

Declaration
public IThreadedComment AddThreadedComment(string text, string author, DateTime creationTime)
Parameters
Type Name Description
System.String text

Threaded comment text
System.String author

The author of the threaded comment text
System.DateTime creationTime

Optional. The creation time of the threaded comment
Returns
Type Description
IThreadedComment

Returns the created instance of IThreadedComment. If the cell already contains comment(note) then threaded comment cannot be inserted and returns null
Examples

The following code illustrates how to add threaded comment with text and author.

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

    //Add threaded comment with text and author
    IThreadedComment threadedComment = worksheet["C2"].AddThreadedComment("sample text", "User");            

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

AttachDetachNameIndexChangedEvent(WorkbookImpl, NameImpl.NameIndexChangedEventHandler, Ptg[], Int32, Int32, Boolean)

Attaches handler to NameIndexChanged event.

Declaration
public static void AttachDetachNameIndexChangedEvent(WorkbookImpl book, NameImpl.NameIndexChangedEventHandler handler, Ptg[] parsedFormula, int iBookIndex, int iNewIndex, bool bAdd)
Parameters
Type Name Description
WorkbookImpl book

Workbook with name.
NameImpl.NameIndexChangedEventHandler handler

Event handler.
Ptg[] parsedFormula

Parsed formula.
System.Int32 iBookIndex

Workbook index.
System.Int32 iNewIndex

New index.
System.Boolean bAdd

Indicates whether event handler should be added.

AutofitColumns()

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

Declaration
public void AutofitColumns()
Remarks

This method is supported on Windows Forms, WPF, ASP.NET, ASP.NET MVC, WinRT, Windows Phone, Universal, UWP, Xamarin and NetStandard platforms only.

Examples

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

ExcelEngine excelEngine = new ExcelEngine();
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();

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

AutofitRows()

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

Declaration
public void AutofitRows()
Remarks

This method is supported on Windows Forms, WPF, ASP.NET, ASP.NET MVC, WinRT, Windows Phone, Universal, UWP, Xamarin and NetStandard platforms only.

Examples

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

ExcelEngine excelEngine = new ExcelEngine();
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();

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

AutoFitToColumn(Int32, Int32)

Auto fits the column from specified first to last column.

Declaration
public void AutoFitToColumn(int firstColumn, int lastColumn)
Parameters
Type Name Description
System.Int32 firstColumn

The first column.
System.Int32 lastColumn

The last column.

Average()

Returns average of numeric cells in the range.

Declaration
public double Average()
Returns
Type
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
public double Average(bool considerDateAsNumber)
Parameters
Type Name Description
System.Boolean considerDateAsNumber

Considers numeric value of date formatted cells for calculation.
Returns
Type
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
public void BorderAround()
Examples

The following code illustrates how to apply border around 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];

// Applies border around the Range with default line style.
worksheet.Range["A2:F4"].BorderAround();

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

BorderAround(ExcelLineStyle)

Applies border around the Range with the specified ExcelLineStyle.

Declaration
public 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.

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

// Applies border around the Range with specified  line style.
worksheet.Range["A2:F4"].BorderAround(ExcelLineStyle.Double);

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

BorderAround(ExcelLineStyle, ExcelKnownColors)

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

Declaration
public 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.

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

// Applies border around the Range with specified  line style and ExcelKnownColors.
worksheet.Range["A2:F4"].BorderAround(ExcelLineStyle.Double,ExcelKnownColors.Blue);

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

BorderAround(ExcelLineStyle, Color)

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

Declaration
public 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.

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

// Applies border around the Range with specified  line style and color.
worksheet.Range["A2:F4"].BorderAround(ExcelLineStyle.Double,System.Drawing.Color.Blue);

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

BorderInside()

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

Declaration
public void BorderInside()
Examples

The following code illustrates how to apply border inside 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];

// Applies border inside the Range with default line style.
worksheet.Range["A2:F4"].BorderInside();

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

BorderInside(ExcelLineStyle)

Applies border inside the Range with the specified ExcelLineStyle.

Declaration
public 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.

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

// Applies border around the inside with specified  line style.
worksheet.Range["A2:F4"].BorderInside(ExcelLineStyle.Double);

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

BorderInside(ExcelLineStyle, ExcelKnownColors)

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

Declaration
public 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.

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

// Applies border around the Range with specified  line style and ExcelKnownColors.
worksheet.Range["A2:F4"].BorderAround(ExcelLineStyle.Double,ExcelKnownColors.Blue);

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

BorderInside(ExcelLineStyle, Color)

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

Declaration
public 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.

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

// Applies border inside the Range with specified  line style and color.
worksheet.Range["A2:F4"].BorderInside(ExcelLineStyle.Double,System.Drawing.Color.Blue);

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

BorderNone()

Applies no border in the Range.

Declaration
public void BorderNone()
Examples

The following code illustrates how to remove border 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];

// Applies border around the Range with default line style.
worksheet.Range["A2:F4"].BorderNone();

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

CellNameToIndex(String)

Converts cell name to cell index.

Declaration
public static long CellNameToIndex(string name)
Parameters
Type Name Description
System.String name

Name of the cell.
Returns
Type Description
System.Int64

Cell index of the specified cell.
Exceptions
Type Condition
System.ArgumentNullException

When specified cell name is null.
System.ArgumentException

When length of the specified cell name is less than 2.
System.ArgumentException

When length of the alpha part of the name is less than 1 or greater than 2.
System.ArgumentException

When length of the number part of the name is less than 1 or greater than 5.

CellNameToRowColumn(String, out Int32, out Int32)

Converts cell name to row and column index.

Declaration
public static void CellNameToRowColumn(string name, out int iRow, out int iColumn)
Parameters
Type Name Description
System.String name

Name of the cell.
System.Int32 iRow

Row index.
System.Int32 iColumn

Column index.

CheckFormulaArraysNotSeparated(ICollection<ArrayRecord>)

Checks if all formula arrays partially contained by this Range or fully contained by this Range. Read-only.

Declaration
protected bool CheckFormulaArraysNotSeparated(ICollection<ArrayRecord> colFormulas)
Parameters
Type Name Description
System.Collections.Generic.ICollection<ArrayRecord> colFormulas

Collection of array formula records.
Returns
Type Description
System.Boolean

Value indicating whether all formula arrays partially contained by this Range.

CheckRange(Int32, Int32)

Checks if specified cell has correct row and column index.

Declaration
protected void CheckRange(int row, int column)
Parameters
Type Name Description
System.Int32 row

Index of the row of the cell.
System.Int32 column

Index of the column of the cell.
Exceptions
Type Condition
System.ArgumentException

When row or column is less than 1 or column is greater than maximum possible column index (it is 256 for Excel 2003, and 16384 for Excel 2007).

Clear()

Clears content in the Range.

Declaration
public 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
public 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
public 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
public 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
public 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.

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

// Clears the Range �A4� and its formatting.
sheet.Range["A4"].Clear(true);

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

ClearConditionalFormats()

Clears conditional formats.

Declaration
public void ClearConditionalFormats()

ClearDataValidations()

Clears data validations.

Declaration
public void ClearDataValidations()

Clone(Object, Dictionary<String, String>, WorkbookImpl)

Creates the copy of current instance.

Declaration
public IRange Clone(object parent, Dictionary<string, string> hashNewNames, WorkbookImpl book)
Parameters
Type Name Description
System.Object parent

Represents a parent object.
System.Collections.Generic.Dictionary<System.String, System.String> hashNewNames

Hash table with new names.
WorkbookImpl book

Represents a parent workbook.
Returns
Type Description
IRange

Returns cloned Range.

CollapseGroup(ExcelGroupBy)

Collapses the group by rows/columns.

Declaration
public 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
public 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
public 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 skips copying blank cells in the source.

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

Represents the destination Range to copy.
ExcelCopyRangeOptions options

Represents the copy Range options.
System.Boolean skipBlank
Returns
Type
IRange

CopyTo(IRange, Boolean)

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

Declaration
public 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();

CreateRichTextString()

Creates rich text string.

Declaration
protected void CreateRichTextString()

CreateStyle()

Creates style.

Declaration
protected void CreateStyle()

CreateStyleWrapper(Int32)

Creates style wrapper.

Declaration
protected void CreateStyleWrapper(int value)
Parameters
Type Name Description
System.Int32 value

Extended format index.

CurrentStyleNumber(String)

Returns number from the style name, i.e. Normal_1 result is 1.

Declaration
protected int CurrentStyleNumber(string pre)
Parameters
Type Name Description
System.String pre

Style name.
Returns
Type Description
System.Int32

Parsed number.

Dispose()

Releases the unmanaged resources used by the XmlReader and optionally releases the managed resources.

Declaration
public void Dispose()

ExpandGroup(ExcelGroupBy)

Expands the group by rows/columns.

Declaration
public 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
public 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
public 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
public 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
public 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
public 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
public 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
public 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
public 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
public 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
public 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
public 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();

FindWorksheet(String)

Searches for specified worksheet in the parent workbook.

Declaration
protected IWorksheet FindWorksheet(string sheetName)
Parameters
Type Name Description
System.String sheetName

Name of the worksheet to search.
Returns
Type Description
IWorksheet

Found worksheet.
Exceptions
Type Condition
System.ArgumentOutOfRangeException

If there is no such worksheet in the parent workbook.

FreezePanes()

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

Declaration
public 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();

FullClear()

Clears Range completely.

Declaration
public void FullClear()

GetAddressLocal(Int32, Int32, Int32, Int32)

Returns the Range reference for the specified Range in the language of the user.

Declaration
public static string GetAddressLocal(int iFirstRow, int iFirstColumn, int iLastRow, int iLastColumn)
Parameters
Type Name Description
System.Int32 iFirstRow

First row of the Range.
System.Int32 iFirstColumn

First column of the Range.
System.Int32 iLastRow

Last row of the Range.
System.Int32 iLastColumn

Last column of the Range.
Returns
Type Description
System.String

The Range reference for the specified Range in the language of the user.

GetAddressLocal(Int32, Int32, Int32, Int32, Boolean)

Returns the Range reference for the specified Range in the language of the user.

Declaration
public static string GetAddressLocal(int iFirstRow, int iFirstColumn, int iLastRow, int iLastColumn, bool bR1C1)
Parameters
Type Name Description
System.Int32 iFirstRow

First row of the Range.
System.Int32 iFirstColumn

First column of the Range.
System.Int32 iLastRow

Last row of the Range.
System.Int32 iLastColumn

Last column of the Range.
System.Boolean bR1C1

Indicates whether to use R1C1 reference mode.
Returns
Type Description
System.String

The Range reference for the specified Range in the language of the user.

GetAreArrayFormulasNotSeparated(Dictionary<ArrayRecord, Object>)

Checks whether formula arrays inside this Range are separated or not.

Declaration
public bool GetAreArrayFormulasNotSeparated(Dictionary<ArrayRecord, object> hashToSkip)
Parameters
Type Name Description
System.Collections.Generic.Dictionary<ArrayRecord, System.Object> hashToSkip

Dictionary with records to skip.
Returns
Type Description
System.Boolean

True if records are not separated.

GetCellIndex(Int32, Int32)

Returns cell index by specified column and row index.

Declaration
public static long GetCellIndex(int firstColumn, int firstRow)
Parameters
Type Name Description
System.Int32 firstColumn

Column index of the cell.
System.Int32 firstRow

Row index of the cell.
Returns
Type Description
System.Int64

Cell index.
Exceptions
Type Condition
System.ArgumentOutOfRangeException

When firstRow or firstColumn is less than zero.

GetCellName(Int32, Int32)

Returns cell name by specified column and row index.

Declaration
public static string GetCellName(int firstColumn, int firstRow)
Parameters
Type Name Description
System.Int32 firstColumn

Column index of the cell.
System.Int32 firstRow

Row index of the cell.
Returns
Type Description
System.String

Cell name.
Exceptions
Type Condition
System.ArgumentOutOfRangeException

When firstColumn or firstRow is less than one.

GetCellName(Int32, Int32, Boolean)

Returns cell name by specified column and row index with R1C1-style notation flag.

Declaration
public static string GetCellName(int firstColumn, int firstRow, bool bR1C1)
Parameters
Type Name Description
System.Int32 firstColumn

Column index of the cell.
System.Int32 firstRow

Row index of the cell.
System.Boolean bR1C1

Indicates whether to use R1C1 reference mode.
Returns
Type Description
System.String

Cell name.
Exceptions
Type Condition
System.ArgumentOutOfRangeException

When firstColumn or firstRow is less than one.

GetCellName(Int32, Int32, Boolean, Boolean)

Returns cell name by specified column and row index with R1C1-style notation and separater flag.

Declaration
public static string GetCellName(int firstColumn, int firstRow, bool bR1C1, bool bUseSeparater)
Parameters
Type Name Description
System.Int32 firstColumn

Column index of the cell.
System.Int32 firstRow

Row index of the cell.
System.Boolean bR1C1

Indicates whether to use R1C1 reference mode.
System.Boolean bUseSeparater

If true adds '$' separator.
Returns
Type Description
System.String

Cell name.
Exceptions
Type Condition
System.ArgumentOutOfRangeException

When firstColumn or firstRow is less than one.

GetCellNameWithDollars(Int32, Int32)

Returns cell name by column and row index with dollar sign.

Declaration
public static string GetCellNameWithDollars(int firstColumn, int firstRow)
Parameters
Type Name Description
System.Int32 firstColumn

Column index of the cell.
System.Int32 firstRow

Row index of the cell.
Returns
Type Description
System.String

Cell name.
Exceptions
Type Condition
System.ArgumentOutOfRangeException

When firstColumn or firstRow is less than one.

GetCellStyleName(IList<IRange>)

Helper methods for CellStyleName Property.

Declaration
public static string GetCellStyleName(IList<IRange> rangeColection)
Parameters
Type Name Description
System.Collections.Generic.IList<IRange> rangeColection

List of IRange.
Returns
Type Description
System.String

Gets CellStyleName property value.

GetColumnFromCellIndex(Int64)

Returns column index from cell index.

Declaration
public static int GetColumnFromCellIndex(long index)
Parameters
Type Name Description
System.Int64 index

Cell index.
Returns
Type Description
System.Int32

Column index.

GetColumnIndex(String)

Converts column name into index.

Declaration
public static int GetColumnIndex(string columnName)
Parameters
Type Name Description
System.String columnName

Name to convert.
Returns
Type Description
System.Int32

Converted value.

GetColumnName(Int32)

Converts column index into string representation.

Declaration
public static string GetColumnName(int iColumn)
Parameters
Type Name Description
System.Int32 iColumn

Column to process.
Returns
Type Description
System.String

String name in excel of the column

GetColumnOutline(Int32)

Returns Column from collection.

Declaration
public IOutline GetColumnOutline(int iColumnIndex)
Parameters
Type Name Description
System.Int32 iColumnIndex

One-based column index.
Returns
Type Description
IOutline

Column information.

GetDateTime()

Returns DataTime value of the record.

Declaration
protected DateTime GetDateTime()
Returns
Type Description
System.DateTime

DataTime value of the record.

GetDependents()

Gets the dependent cells which refer to other cells.

Declaration
public 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
public 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
public 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 = "DirectDependents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

GetDirectDependents(Boolean)

Gets the direct dependent cells which refer to other cells.

Declaration
public 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 = "DirectDependents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

GetDirectPrecedents()

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

Declaration
public 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"].GetDirectPrecedents();

string fileName = "DirectPrecedents.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
public 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"].GetDirectPrecedents(true);

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

GetDisplayString()

Returns display text.

Declaration
protected string GetDisplayString()
Returns
Type Description
System.String

Value representing displayed string.

GetEnumerator()

Returns an enumerator that iterates through the Range.

Declaration
public IEnumerator<IRange> GetEnumerator()
Returns
Type
System.Collections.Generic.IEnumerator<IRange>

GetNativePtg()

Returns ptg of range.

Declaration
public Ptg[] GetNativePtg()
Returns
Type Description
Ptg[]

Returns native ptg.

GetNewAddress(Dictionary<String, String>, out String)

Returns new address of Range.

Declaration
public string GetNewAddress(Dictionary<string, string> names, out string strSheetName)
Parameters
Type Name Description
System.Collections.Generic.Dictionary<System.String, System.String> names

Dictionary with Worksheet names.
System.String strSheetName

String that sets as a worksheet name.
Returns
Type Description
System.String

Returns string with new name.

GetNumber()

Returns number value from the cell if possible.

Declaration
protected double GetNumber()
Returns
Type Description
System.Double

Stored number.

GetNumberFormat(IList)

Helper methods for WrapText Property.

Declaration
public static string GetNumberFormat(IList rangeColection)
Parameters
Type Name Description
System.Collections.IList rangeColection

List of IRange.
Returns
Type Description
System.String

Gets WrapText property value.

GetPrecedents()

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

Declaration
public 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
public 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();

GetR1C1AddresFromCellIndex(Int64)

Returns R1C1 address from cell index.

Declaration
public static string GetR1C1AddresFromCellIndex(long cellIndex)
Parameters
Type Name Description
System.Int64 cellIndex

Cell index.
Returns
Type Description
System.String

Returns R1C1 address.

GetR1C1AddressFromCellIndex(Int64)

Returns R1C1 address from cell index.

Declaration
public static string GetR1C1AddressFromCellIndex(long cellIndex)
Parameters
Type Name Description
System.Int64 cellIndex

Cell index.
Returns
Type Description
System.String

Returns R1C1 address.

GetRectangeOfRange(IRange, Boolean)

Returns rectangle object, that represents rectangle of Range.

Declaration
public static Rectangle GetRectangeOfRange(IRange range, bool bThrowExcONNullRange)
Parameters
Type Name Description
IRange range

Represents the Range.
System.Boolean bThrowExcONNullRange

If true than thrown an exception, if Range is null.
Returns
Type Description
Rectangle

Returns rectangle, that represents borders of Range.

GetRectangleOfRange(IRange, Boolean)

Returns rectangle object, that represents rectangle of Range.

Declaration
public static Rectangle GetRectangleOfRange(IRange range, bool bThrowExcONNullRange)
Parameters
Type Name Description
IRange range

Represents the Range.
System.Boolean bThrowExcONNullRange

If true than thrown an exception, if Range is null.
Returns
Type Description
Rectangle

Returns rectangle, that represents borders of Range.

GetRectangles()

Returns array that contains information about Range.

Declaration
public Rectangle[] GetRectangles()
Returns
Type Description
Rectangle[]

Rectangles that describes Range with zero-based coordinates.

GetRectanglesCount()

Returns number of rectangles returned by GetRectangles method.

Declaration
public int GetRectanglesCount()
Returns
Type Description
System.Int32

Number of rectangles returned by GetRectangles method.

GetRowFromCellIndex(Int64)

Returns row index from cell index.

Declaration
public static int GetRowFromCellIndex(long index)
Parameters
Type Name Description
System.Int64 index

Cell index.
Returns
Type Description
System.Int32

Row index.

GetRowOutline(Int32)

Returns Row outline from collection.

Declaration
public IOutline GetRowOutline(int iRowIndex)
Parameters
Type Name Description
System.Int32 iRowIndex

One-based row index.
Returns
Type Description
IOutline

Row information.

GetWorksheetName(ref String)

Extracts worksheet name from Range name.

Declaration
public static string GetWorksheetName(ref string rangeName)
Parameters
Type Name Description
System.String rangeName

Range name to extract from.
Returns
Type Description
System.String

Worksheet name.

GetWrapText(IList)

Helper methods for WrapText Property.

Declaration
public static bool GetWrapText(IList rangeColection)
Parameters
Type Name Description
System.Collections.IList rangeColection

List of IRange.
Returns
Type Description
System.Boolean

Gets WrapText property value.

Group(ExcelGroupBy)

Groups the Range by row/column.

Declaration
public 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
public 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();

InfillCells()

Fill internal collection by references on cells.

Declaration
protected void InfillCells()

IntersectWith(IRange)

Returns intersection of the Range with specified Range.

Declaration
public 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
public double Max()
Returns
Type
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
public double Max(bool considerDateAsNumber)
Parameters
Type Name Description
System.Boolean considerDateAsNumber

Considers numeric value of date formatted cells for calculation.
Returns
Type
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();

MeasureString(String)

Measures size of the string.

Declaration
public SizeF MeasureString(string strMeasure)
Parameters
Type Name Description
System.String strMeasure

String to measure.
Returns
Type Description
SizeF

Size of the string.

Merge()

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

Declaration
public void Merge()
Examples

The following code illustrates how to merge 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
worksheet.Range["A16:C16"].Merge();

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

Merge(Boolean)

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

Declaration
public 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.

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
worksheet.Range["A16:C16"].Merge(true);

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

MergeWith(IRange)

Merges the Range with specified Range and returns merged Range.

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

Range to be merged with.
Returns
Type Description
IRange

Merged Ranges or null if wasn't able to merge Ranges.
Examples

The following code illustrates how to perform MergeWith 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"];
worksheet.Range["D16"].MergeWith(range);
workbook.SaveAs("Merging.xlsx");
workbook.Close();
excelEngine.Dispose();

Min()

Returns minumum value from numeric cells in the range.

Declaration
public double Min()
Returns
Type
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
public double Min(bool considerDateAsNumber)
Parameters
Type Name Description
System.Boolean considerDateAsNumber

Considers numeric value of date formatted cells for calculation.
Returns
Type
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
public 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();

MoveTo(IRange, ExcelCopyRangeOptions)

Moves the cells to the specified Range.

Declaration
public void MoveTo(IRange destination, ExcelCopyRangeOptions options)
Parameters
Type Name Description
IRange destination

Represents a destination Range.
ExcelCopyRangeOptions options

Specifies the options to update formulas and merged Ranges during copy Range.

ObjectToDouble(Object)

Converts object to double if possible.

Declaration
protected double ObjectToDouble(object value)
Parameters
Type Name Description
System.Object value

Object to convert.
Returns
Type Description
System.Double

Converted value.

Offset(Int32, Int32)

Returns a IRange object that represents a range thats offset from the specified range.

Declaration
public IRange Offset(int row, int column)
Parameters
Type Name Description
System.Int32 row
System.Int32 column
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");
}

OnFirstColumnChanged()

Called after changing first column index.

Declaration
protected void OnFirstColumnChanged()

OnFirstRowChanged()

Called after changing first row index.

Declaration
protected void OnFirstRowChanged()

OnLastColumnChanged()

Called after changing last column index.

Declaration
protected void OnLastColumnChanged()

OnLastRowChanged()

Called after changing last row index.

Declaration
protected void OnLastRowChanged()

OnStyleChanged(RangeImpl.TCellType)

Called after changing style of the Range.

Declaration
protected void OnStyleChanged(RangeImpl.TCellType oldType)
Parameters
Type Name Description
RangeImpl.TCellType oldType

Cell type.

OnValueChanged(String, String)

Called after changing of value.

Declaration
protected void OnValueChanged(string old, string value)
Parameters
Type Name Description
System.String old

Old value.
System.String value

New value.

Parse(BiffRecordRaw[], ref Int32)

Recover region from array of Biff Records and position in it.

Declaration
public void Parse(BiffRecordRaw[] data, ref int position)
Parameters
Type Name Description
Syncfusion.XlsIO.Parser.Biff_Records.BiffRecordRaw[] data

Array of BiffRecordRaws that contains Range record.
System.Int32 position

Position of Range record.

Parse(BiffReader)

Recover Region from the stream.

Declaration
public void Parse(BiffReader reader)
Parameters
Type Name Description
BiffReader reader

BiffReader with Range record.

Parse(IList, ref Int32, Boolean)

Recover region from array of Biff Records and position in it with ignore style flag.

Declaration
public void Parse(IList data, ref int position, bool ignoreStyles)
Parameters
Type Name Description
System.Collections.IList data

Array of BiffRecordRaws that contains Range record.
System.Int32 position

Position of Range record.
System.Boolean ignoreStyles

Indicates whether to ignore style.

ParseBlank(BlankRecord)

Parses BlankRecord.

Declaration
protected string ParseBlank(BlankRecord blank)
Parameters
Type Name Description
BlankRecord blank

BlankRecord to parse.
Returns
Type Description
System.String

Blank string.

ParseBoolError(BoolErrRecord)

Parses BoolErrRecord.

Declaration
public static string ParseBoolError(BoolErrRecord error)
Parameters
Type Name Description
BoolErrRecord error

BoolErrRecord to parse.
Returns
Type Description
System.String

Extracted BoolError record.

ParseDouble(IDoubleValue)

Parses double value accordingly to format string.

Declaration
protected string ParseDouble(IDoubleValue value)
Parameters
Type Name Description
IDoubleValue value

Value to parse.
Returns
Type Description
System.String

Parsed value.

ParseFormula(FormulaRecord, IList, ref Int32)

Parses FormulaRecord.

Declaration
protected void ParseFormula(FormulaRecord formula, IList data, ref int pos)
Parameters
Type Name Description
FormulaRecord formula

FormulaRecord to parse.
System.Collections.IList data

List with Biff records.
System.Int32 pos

Position of formula in the list.

ParseRangeString(String, IWorkbook, out Int32, out Int32, out Int32, out Int32)

Parses string representation of the Range.

Declaration
public static int ParseRangeString(string range, IWorkbook book, out int iFirstRow, out int iFirstColumn, out int iLastRow, out int iLastColumn)
Parameters
Type Name Description
System.String range

Range to parse.
IWorkbook book

Parent workbook.
System.Int32 iFirstRow

First row.
System.Int32 iFirstColumn

First column.
System.Int32 iLastRow

Last row.
System.Int32 iLastColumn

Last column.
Returns
Type Description
System.Int32

Number of parts: 1 - one cell, 2 - range of cells.

ParseRString(RStringRecord)

Parses RStringRecord.

Declaration
protected string ParseRString(RStringRecord rstring)
Parameters
Type Name Description
RStringRecord rstring

RStringRecord to parse.
Returns
Type Description
System.String

Blank string.

PartialClear()

Partially clear Range.

Declaration
public void PartialClear()

Reparse()

Reparse cell if parsing wasn't successful when loading the workbook.

Declaration
public void Reparse()

ReParseFormula(FormulaRecord)

Reparses FormulaRecord.

Declaration
protected void ReParseFormula(FormulaRecord formula)
Parameters
Type Name Description
FormulaRecord formula

Record to reparse.

ReparseFormulaString()

Reparses formula.

Declaration
public void ReparseFormulaString()

Replace(String, DateTime)

Replaces the string with the specified DateTime value.

Declaration
public 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
public 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
public 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
public 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
public 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
public 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
public 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();

ResetCells()

Clears internal cells array.

Declaration
protected void ResetCells()

Resize(Int32, Int32)

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

Declaration
public IRange Resize(int row, int column)
Parameters
Type Name Description
System.Int32 row
System.Int32 column
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");
}

SetAutoFormat(ExcelAutoFormat)

Sets auto format with default auto format option for Range.

Declaration
public void SetAutoFormat(ExcelAutoFormat format)
Parameters
Type Name Description
ExcelAutoFormat format

Represents format to set.

SetAutoFormat(ExcelAutoFormat, ExcelAutoFormatOptions)

Sets auto format with specified auto format option for Range.

Declaration
public void SetAutoFormat(ExcelAutoFormat format, ExcelAutoFormatOptions options)
Parameters
Type Name Description
ExcelAutoFormat format

Represents auto format to set.
ExcelAutoFormatOptions options

Represents auto format options.

SetBoolean(Boolean)

Fills internal BiffRecord with data with specified boolean value.

Declaration
protected void SetBoolean(bool value)
Parameters
Type Name Description
System.Boolean value

Boolean with Range value.

SetBorderToSingleCell(ExcelBordersIndex, ExcelLineStyle, ExcelKnownColors)

Sets border to single cell.

Declaration
protected void SetBorderToSingleCell(ExcelBordersIndex borderIndex, ExcelLineStyle borderLine, ExcelKnownColors borderColor)
Parameters
Type Name Description
ExcelBordersIndex borderIndex

Represents border index.
ExcelLineStyle borderLine

Represents border line type.
ExcelKnownColors borderColor

Represents border line color.

SetChanged()

Called after any changes in the Range. Sets Saved property of the parent workbook to false.

Declaration
protected void SetChanged()

SetDataValidation(DataValidationImpl)

Sets data validation for the Range.

Declaration
public void SetDataValidation(DataValidationImpl dv)
Parameters
Type Name Description
DataValidationImpl dv

Data validation to set.

SetDateTime(DateTime)

Fills internal BiffRecord with data from specified DateTime.

Declaration
protected void SetDateTime(DateTime value)
Parameters
Type Name Description
System.DateTime value

DateTime with Range value.

SetError(String)

Fills internal BiffRecord with data with specified error value.

Declaration
protected void SetError(string strError)
Parameters
Type Name Description
System.String strError

String with error value.

SetFormula(FormulaRecord)

Copies formula record.

Declaration
protected void SetFormula(FormulaRecord record)
Parameters
Type Name Description
FormulaRecord record

Record to copy.

SetFormula(String)

Sets formula value to the current Range.

Declaration
protected void SetFormula(string value)
Parameters
Type Name Description
System.String value

Formula value.

SetFormula(String, Dictionary<String, String>, Boolean)

Sets formula value to the current Range.

Declaration
protected void SetFormula(string value, Dictionary<string, string> hashWorksheetNames, bool bR1C1)
Parameters
Type Name Description
System.String value

Formula value.
System.Collections.Generic.Dictionary<System.String, System.String> hashWorksheetNames

Dictionary with new worksheet names (to copy worksheet's into workbook's and merging workbooks).
System.Boolean bR1C1

Indicates whether R1C1-style notation is used.

SetFormulaArrayRecord(ArrayRecord)

Sets array formula record.

Declaration
public void SetFormulaArrayRecord(ArrayRecord record)
Parameters
Type Name Description
ArrayRecord record

Formula array record.

SetFormulaArrayRecord(ArrayRecord, Int32)

Sets array formula record with extended format index.

Declaration
public void SetFormulaArrayRecord(ArrayRecord record, int iXFIndex)
Parameters
Type Name Description
ArrayRecord record

Formula array record.
System.Int32 iXFIndex

Extended format index.

SetLabelSSTIndex(Int32)

Sets index in the LabelSST record.

Declaration
protected void SetLabelSSTIndex(int index)
Parameters
Type Name Description
System.Int32 index

New index value.

SetNumber(Double)

Fills internal BiffRecord with data from specified number.

Declaration
protected void SetNumber(double value)
Parameters
Type Name Description
System.Double value

Number with Range value.

SetParent(WorksheetImpl)

Sets new parent.

Declaration
protected void SetParent(WorksheetImpl parent)
Parameters
Type Name Description
WorksheetImpl parent

Parent to set.

SetRowHeight(Double, Boolean)

Sets row height.

Declaration
public void SetRowHeight(double value, bool bIsBadFontHeight)
Parameters
Type Name Description
System.Double value

Value to set.
System.Boolean bIsBadFontHeight

Indicates whether font and row height are not compatible.

SetTimeSpan(TimeSpan)

Fills internal BiffRecord with data from specified DateTime.

Declaration
protected void SetTimeSpan(TimeSpan time)
Parameters
Type Name Description
System.TimeSpan time

DateTime with Range value.

SetWrapText(IList, Boolean)

Helper methods for WrapText Property.

Declaration
public static void SetWrapText(IList rangeColection, bool wrapText)
Parameters
Type Name Description
System.Collections.IList rangeColection

List of IRange.
System.Boolean wrapText

Value to set.

SetXFormatIndex(Int32)

Sets index of extended format that defines style for this Range.

Declaration
public void SetXFormatIndex(int index)
Parameters
Type Name Description
System.Int32 index

Index to set.

SubTotal(Int32, ConsolidationFunction, Int32[])

Creates subtotals for the Range.

Declaration
public 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
public 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
public 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
public double Sum()
Returns
Type
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
public double Sum(bool considerDateAsNumber)
Parameters
Type Name Description
System.Boolean considerDateAsNumber

Considers numeric value of date formatted cells for calculation.
Returns
Type
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();

ToggleGroup(ExcelGroupBy, Boolean, Boolean)

Group or ungroup current Range.

Declaration
protected RangeImpl ToggleGroup(ExcelGroupBy groupBy, bool isGroup, bool bCollapsed)
Parameters
Type Name Description
ExcelGroupBy groupBy

Should we perform operation on rows or columns?
System.Boolean isGroup

If True then group, otherwise ungroup.
System.Boolean bCollapsed

Indicates whether created group should be collapsed.
Returns
Type Description
RangeImpl

This Range after grouping / ungrouping.

Trim()

Trim the empty rows at top and bottom of the range, the empty columns at left and right of the range.

Declaration
public 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
public 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
public void UnMerge()
Examples

The following code illustrates how to perform merging 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];

//Un-Merging merged cells
worksheet.Range["A16:C16"].UnMerge();

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

UpdateNamedRangeIndexes(Int32[])

Updates named Ranges indexes.

Declaration
public void UpdateNamedRangeIndexes(int[] arrNewIndex)
Parameters
Type Name Description
System.Int32[] arrNewIndex

New indexes.

UpdateRange(Int32, Int32, Int32, Int32)

Updates Range.

Declaration
public void UpdateRange(int iFirstRow, int iFirstColumn, int iLastRow, int iLastColumn)
Parameters
Type Name Description
System.Int32 iFirstRow

First row index.
System.Int32 iFirstColumn

First column index.
System.Int32 iLastRow

Last row index.
System.Int32 iLastColumn

Last column index.

UpdateRecord()

Updates Range information from record.

Declaration
public void UpdateRecord()

wrapStyle_OnNumberFormatChanged(Object, EventArgs)

Called when NumberFormat of the Range changes.

Declaration
protected void wrapStyle_OnNumberFormatChanged(object sender, EventArgs e)
Parameters
Type Name Description
System.Object sender

Event sender.
System.EventArgs e

Event arguments.

Explicit Interface Implementations

ICellPositionFormat.Column

Gets or sets cell column.

Declaration
int ICellPositionFormat.Column { get; set; }
Returns
Type
System.Int32

ICellPositionFormat.Row

Gets or sets cell row.

Declaration
int ICellPositionFormat.Row { get; set; }
Returns
Type
System.Int32

ICellPositionFormat.TypeCode

Returns type code of the underlying record. Read-only.

Declaration
TBIFFRecord ICellPositionFormat.TypeCode { get; }
Returns
Type
TBIFFRecord

Implements

System.Collections.Generic.IEnumerable<>
System.Collections.IEnumerable
System.IDisposable

Extension Methods