Class RangeImpl

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

Inheritance

System.Object

RangeImpl

MigrantRangeImpl

Implements

ICombinedRange

IRange

IParentApplication

System.Collections.Generic.IEnumerable<IRange>

System.Collections.IEnumerable

ICellPositionFormat

INativePTG

System.IDisposable

Namespace: Syncfusion.XlsIO.Implementation

Assembly: Syncfusion.XlsIO.Base.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

HtmlString

Gets or sets HTML string.

Declaration

public string HtmlString { get; set; }

Property Value

Type
System.String

Hyperlinks

Gets hyperlinks in the Range. Read-only.

Declaration

public IHyperLinks Hyperlinks { get; }

Property Value

Type
IHyperLinks

Examples

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

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

IWorksheet sheet = workbook.Worksheets[0];

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

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

IgnoreErrorOptions

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

Declaration

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.
System.Drawing.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.
System.Drawing.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
System.Drawing.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
System.Drawing.Rectangle	Returns rectangle, that represents borders of Range.

GetRectangles()

Returns array that contains information about Range.

Declaration

public Rectangle[] GetRectangles()

Returns

Type	Description
System.Drawing.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
System.Drawing.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, DataColumn, Boolean)

Replaces the string with the specified datacolumn.

Declaration

public void Replace(string oldValue, DataColumn newValues, bool isFieldNamesShown)

Parameters

Type	Name	Description
System.String	oldValue	The string to be replaced.
System.Data.DataColumn	newValues	DataColumn with new data.
System.Boolean	isFieldNamesShown	Indicates whether field name must be shown.

Examples

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

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

Replace(String, DataTable, Boolean)

Replaces the string with the specified datatable.

Declaration

public void Replace(string oldValue, DataTable newValues, bool isFieldNamesShown)

Parameters

Type	Name	Description
System.String	oldValue	The string to be replaced.
System.Data.DataTable	newValues	Data table with new data.
System.Boolean	isFieldNamesShown	Indicates whether field name must be shown.

Examples

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

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

Replace(String, DateTime)

Replaces the string with the specified DateTime value.

Declaration

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

ICombinedRange

IRange

IParentApplication

System.Collections.Generic.IEnumerable<T>

System.Collections.IEnumerable

ICellPositionFormat

INativePTG

System.IDisposable