Interface IRange
Represents a cell, a row, a column, collection of cells or a 3-D Range.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.dll
Syntax
public interface IRange : IParentApplication, IEnumerable<IRange>, IEnumerableProperties
Address
Gets the Range reference in macro language. Read-only.
Declaration
string Address { get; }Property Value
| Type | Description |
|---|---|
| System.String |
Examples
The following code illustrates how to access Address property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
string address = sheet.Range[3, 4].Address;
workbook.SaveAs("Address.xlsx");
workbook.Close();
excelEngine.Dispose();AddressGlobal
Gets the Range reference along with its sheet name in format "'Sheet1'!$A$1". Read-only.
Declaration
string AddressGlobal { get; }Property Value
| Type | Description |
|---|---|
| System.String |
Examples
The following code illustrates how to access AddressGlobal property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[2];
string address = sheet.Range[4, 3].AddressGlobal;
workbook.SaveAs("AddressGlobal.xlsx");
workbook.Close();
excelEngine.Dispose();AddressLocal
Gets the Range reference in language of the user. Read-only.
Declaration
string AddressLocal { get; }Property Value
| Type | Description |
|---|---|
| System.String |
Examples
The following code illustrates how to access AddressLocal property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
string address = sheet.Range[1, 2].AddressLocal;
workbook.SaveAs("AddressLocal.xlsx");
workbook.Close();
excelEngine.Dispose();AddressR1C1
Gets the Range reference along with sheet name using R1C1-style notation. Read-only.
Declaration
string AddressR1C1 { get; }Property Value
| Type | Description |
|---|---|
| System.String |
Examples
The following code illustrates how to access AddressR1C1 property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
string address = sheet.Range[1, 1].AddressR1C1;
workbook.SaveAs("AddressR1C1.xlsx");
workbook.Close();
excelEngine.Dispose();AddressR1C1Local
Gets the Range reference using R1C1-style notation. Read-only.
Declaration
string AddressR1C1Local { get; }Property Value
| Type | Description |
|---|---|
| System.String |
Examples
The following code illustrates how to access AddressR1C1Local property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
string address = sheet.Range[1, 1].AddressR1C1Local;
workbook.SaveAs("AddressR1C1Local.xlsx");
workbook.Close();
excelEngine.Dispose();Boolean
Gets or sets boolean value in the Range.
Declaration
bool Boolean { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
The following code illustrates how to set and access Boolean property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range[2, 4].Boolean = true;
bool boolean = sheet.Range[2, 4].Boolean;
workbook.SaveAs("Boolean.xlsx");
workbook.Close();
excelEngine.Dispose();Borders
Gets a IBorders collection that represents the borders of a style in the Range. Read-only.
Declaration
IBorders Borders { get; }Property Value
| Type | Description |
|---|---|
| IBorders |
Remarks
Borders including a Range defined as part of a conditional format will be returned.
Examples
The following code illustrates how to access Borders property of the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
//Set borders
IBorders borders = worksheet["C2"].Borders;
//Set border color
borders[ExcelBordersIndex.EdgeTop].Color = ExcelKnownColors.Red;
borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Blue;
//Set line style
borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thick;
borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thick;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}BuiltInStyle
Gets or sets built in style in the Range.
Declaration
Nullable<BuiltInStyles> BuiltInStyle { get; set; }Property Value
| Type | Description |
|---|---|
| System.Nullable<BuiltInStyles> |
Examples
The following code illustrates how to set BuiltInStyle to a range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
//Set built in style
worksheet["C2"].BuiltInStyle = BuiltInStyles.Accent3;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}CalculatedValue
Gets the calculated value of a formula in the Range. Read-only.
Declaration
string CalculatedValue { get; }Property Value
| Type | Description |
|---|---|
| System.String |
Remarks
To compute a formula, it is mandatory to enable calculate engine by
invoking EnableSheetCalculations method of worksheet object. It is also recommend to disable
calculate engine once all formula are computed by invoking DisableSheetCalculations method of worksheet object
Examples
Following code illustrates how to access a calculated value.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
//Initializes Calculate Engine to perform calculation
sheet.EnableSheetCalculations();
//Returns the calculated value of a formula using the most current inputs
string calculatedValue = sheet["C1"].CalculatedValue;
//Formula calculation is disabled for the sheet.
sheet.DisableSheetCalculations();
workbook.SaveAs("Formula.xlsx");
workbook.Close();
excelEngine.Dispose(); Cells
Gets a IRange object that represents the cells in the Range. Read-only.
Declaration
IRange[] Cells { get; }Property Value
| Type | Description |
|---|---|
| IRange[] |
Examples
The following code illustrates how to access Cells property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
IRange[] cells = sheet["A1:E8"].Cells;
foreach (IRange Range in cells)
{
// Do some manipulations
}
workbook.SaveAs("Cells.xlsx");
workbook.Close();
excelEngine.Dispose();CellStyle
Gets a IStyle object that represents the style of the Range.
Declaration
IStyle CellStyle { get; set; }Property Value
| Type | Description |
|---|---|
| IStyle |
Remarks
To know more about setting styles refer Set Default Style for row/column.
Examples
The following code illustrates how to access IStyle using CellStyle property of the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
//Add style
IStyle style = workbook.Styles.Add("BorderStyle");
//Set color
style.ColorIndex = ExcelKnownColors.Red;
//Set style
worksheet["C2"].CellStyle = style;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}CellStyleName
Gets name of the IStyle object that represents style of the Range.
Declaration
string CellStyleName { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Examples
The following code illustrates how to access CellStyleName property of the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Add style
IStyle style = workbook.Styles.Add("CustomStyle");
//Set color
style.ColorIndex = ExcelKnownColors.Red;
//Set style
worksheet["C2"].CellStyle = style;
//Check Style name
Console.Write(worksheet["C2"].CellStyleName);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//CustomStyleColumn
Gets the column index of the first column in the Range which is a one based index. Read-only.
Declaration
int Column { get; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
Examples
The following code illustrates how to access Column property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
int firstColumn = sheet["E1:R3"].Column;
workbook.SaveAs("Column.xlsx");
workbook.Close();
excelEngine.Dispose();ColumnGroupLevel
Gets column group level of the Range. Read-only.
Declaration
int ColumnGroupLevel { get; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
Remarks
-1 - not all column in the Range have same group level. 0 - No grouping, 1 - 7 - group level.
Columns
Gets an array of IRange objects that represent the columns in the Range. Read only.
Declaration
IRange[] Columns { get; }Property Value
| Type | Description |
|---|---|
| IRange[] |
Examples
The following code illustrates how to access Columns property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
IRange[] columns = sheet["A1:E8"].Columns;
foreach (IRange column in columns)
{
// Do some manipulations
}
workbook.SaveAs("Columns.xlsx");
workbook.Close();
excelEngine.Dispose();ColumnWidth
Gets or sets the width of all columns in the specified range, measured in points.
Declaration
double ColumnWidth { get; set; }Property Value
| Type | Description |
|---|---|
| System.Double |
Remarks
Gets Double.MinValue if the rows in the specified Range aren't all the same width.
Examples
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "This cell contains sample text";
//Set column width
worksheet["A1"].ColumnWidth = 25;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}Comment
Gets a ICommentShape object that represents the comment associated with the range. Read-only.
Declaration
ICommentShape Comment { get; }Property Value
| Type | Description |
|---|---|
| ICommentShape |
Examples
The following code illustrates how to insert Comments property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
// Adding comments to a cell.
sheet.Range["A1"].AddComment().Text = "Comments";
// Add Rich Text Comments.
IRange range = sheet.Range["A6"];
range.AddComment().RichText.Text = "RichText";
IRichTextString rtf = range.Comment.RichText;
// Formatting first 4 characters.
IFont redFont = workbook.CreateFont();
redFont.Bold = true;
redFont.Color = ExcelKnownColors.Red;
rtf.SetFont(0, 3, redFont);
workbook.SaveAs("Comments.xlsx");
workbook.Close();
excelEngine.Dispose(); ConditionalFormats
Gets the collection of conditional formats in the Range. Read-only.
Declaration
IConditionalFormats ConditionalFormats { get; }Property Value
| Type | Description |
|---|---|
| IConditionalFormats |
Examples
The following code illustrates how to set and access Value2 property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
// Applying conditional formatting to "A1"
IConditionalFormats condition = sheet.Range["A2"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
// Represents conditional format rule that the value in target Range should be between 10 and 20
condition1.FormatType = ExcelCFType.CellValue;
condition1.Operator = ExcelComparisonOperator.Between;
condition1.FirstFormula = "10";
condition1.SecondFormula = "20";
sheet.Range["A1"].Text = "Enter a number between 10 and 20";
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
excelEngine.Dispose();Count
Gets the number of cells in the Range. Read-only.
Declaration
int Count { get; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
DataValidation
Gets a IDataValidation object that represents the data validation associated with the cell. Read-only.
Declaration
IDataValidation DataValidation { get; }Property Value
| Type | Description |
|---|---|
| IDataValidation |
Examples
The following code illustrates how to access DataValidation property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Data validation for number
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.Integer;
//Value between 0 to 10
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstFormula = "0";
validation.SecondFormula = "10";
workbook.SaveAs("DataValidation.xlsx");
workbook.Close();
excelEngine.Dispose();DateTime
Gets or sets DateTime value in the Range.
Declaration
DateTime DateTime { get; set; }Property Value
| Type | Description |
|---|---|
| System.DateTime |
Remarks
Gets DateTime.MinValue if not all cells in the Range have same DateTime value.
Examples
The following code illustrates how to set and access DateTime property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range[2, 4].DateTime = DateTime.Now;
DateTime dateTime = sheet.Range[2, 4].DateTime;
workbook.SaveAs("DateTime.xlsx");
workbook.Close();
excelEngine.Dispose();DisplayText
Gets cell value with its number format. Read-only.
Declaration
string DisplayText { get; }Property Value
| Type | Description |
|---|---|
| System.String |
Remarks
While accessing DisplayText for Range of cells, it gets DisplayText of the first cell
If a range contains a formula, EnableSheetCalculations() must be invoked to get the calculated value of the formula
Examples
The following code illustrates how to access DisplayText property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
IRange range= sheet.Range[3, 1];
range.Value = "1/1/2015";
range.NumberFormat = "dd-MMM-yyyy";
string displayText = range.DisplayText;
workbook.SaveAs("DisplayText.xlsx");
workbook.Close();
excelEngine.Dispose();End
Gets a IRange object that represents the cell at the end of the Range.
Declaration
IRange End { get; }Property Value
| Type | Description |
|---|---|
| IRange |
EntireColumn
Gets a IRange object that represents the entire column (or columns) in the Range. Read-only.
Declaration
IRange EntireColumn { get; }Property Value
| Type | Description |
|---|---|
| IRange |
EntireRow
Gets a IRange object that represents the entire row (or rows) in the Range. Read-only.
Declaration
IRange EntireRow { get; }Property Value
| Type | Description |
|---|---|
| IRange |
Error
Gets or sets error value in the Range.
Declaration
string Error { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Formula
Gets or sets the formula in A1-style notation and in macro language for the Range.
Declaration
string Formula { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
FormulaArray
Gets or sets sets the array formula of a range which can perform multiple calculations on one or more of the items in an array.
Declaration
string FormulaArray { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Examples
The following code illustrates how to set and access FormulaArray property of the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assign array formula
sheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}";
//Adding a named range for the range A1 to D1
sheet.Names.Add("ArrayRange", sheet.Range["A1:D1"]);
//Assign formula array with named range
sheet.Range["A2:D2"].FormulaArray = "ArrayRange+100";
string fileName = "FormulaArray.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose(); FormulaArrayR1C1
Gets or sets the formula array for the Range, using R1C1-style notation.
Declaration
string FormulaArrayR1C1 { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
FormulaBoolValue
Gets or sets the calculated value of the formula as a boolean.
Declaration
bool FormulaBoolValue { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
FormulaDateTime
Gets or sets formula DateTime value in the Range.
Declaration
DateTime FormulaDateTime { get; set; }Property Value
| Type | Description |
|---|---|
| System.DateTime |
Remarks
Gets DateTime.MinValue if not all cells in the Range have same FormulaDateTime value.
FormulaErrorValue
Gets or sets the error value of the formula as a string.
Declaration
string FormulaErrorValue { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
FormulaHidden
Gets or sets a Boolean value indicating whether the formula will be hidden when the worksheet is protected.
Declaration
bool FormulaHidden { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
True if the formula will be hidden when the worksheet is protected. False if at least part of formula in the Range is not hidden.
FormulaNumberValue
Gets or sets number value evaluated by formula.
Declaration
double FormulaNumberValue { get; set; }Property Value
| Type | Description |
|---|---|
| System.Double |
FormulaR1C1
Gets or sets the formula for the Range, using R1C1-style notation.
Declaration
string FormulaR1C1 { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
FormulaStringValue
Gets or sets string value evaluated by formula.
Declaration
string FormulaStringValue { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
HasBoolean
Gets a Boolean value indicating whether Range contains bool value. Read-only.
Declaration
bool HasBoolean { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
The following code illustrates how to set and access HasBoolean property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assigning Value2 property of the Range
sheet["A3"].Value2 = false;
// Checking Range types.
bool isboolean = sheet["A3"].HasBoolean;
workbook.SaveAs("HasBoolean.xlsx");
workbook.Close();
excelEngine.Dispose();HasDataValidation
Gets a Boolean value indicating whether Range has data validation. Read-only.
Declaration
bool HasDataValidation { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
If Range is not single cell, then gets true only if all cells have data validation.
HasDateTime
Gets a Boolean value indicating whether Range contains DateTime value. Read-only.
Declaration
bool HasDateTime { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
The following code illustrates how to set and access HasDateTime property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assigning Value2 property of the Range
sheet["A1"].Value2 = DateTime.Now;
// Checking Range types.
bool isDateTime = sheet["A1"].HasDateTime;
workbook.SaveAs("HasDateTime.xlsx");
workbook.Close();
excelEngine.Dispose();HasExternalFormula
Gets a Boolean value indicating whether Range has external formula. Read-only.
Declaration
bool HasExternalFormula { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
HasFormula
Gets a Boolean value indicating whether Range contains formula. Read-only.
Declaration
bool HasFormula { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
True if all cells in the Range contain formulas; False if at least one of the cells in the Range doesn't contain a formula.
HasFormulaArray
Gets a Boolean value indicating whether Range contains array-entered formula. Read-only.
Declaration
bool HasFormulaArray { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
HasFormulaBoolValue
Gets a Boolean value indicating whether Range has formula bool value. Read-only.
Declaration
bool HasFormulaBoolValue { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
HasFormulaDateTime
Gets a Boolean value indicating whether Range has formula value formatted as DateTime. Read-only.
Declaration
bool HasFormulaDateTime { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
HasFormulaErrorValue
Gets a Boolean value indicating whether Range has formula error value. Read-only.
Declaration
bool HasFormulaErrorValue { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
HasFormulaNumberValue
Gets a Boolean value indicating whether Range has formula number value. Read-only.
Declaration
bool HasFormulaNumberValue { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
HasFormulaStringValue
Gets a Boolean value indicating whether Range has formula string value. Read-only.
Declaration
bool HasFormulaStringValue { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
HasNumber
Gets a Boolean value indicating whether Range contains number. Read-only.
Declaration
bool HasNumber { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
The following code illustrates how to set and access Value2 property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assigning Value2 property of the Range
sheet["A2"].Value2 = 45;
// Checking Range types.
bool isNumber = sheet["A2"].HasNumber;
workbook.SaveAs("HasNumber.xlsx");
workbook.Close();
excelEngine.Dispose();HasRichText
Gets a Boolean value indicating whether cell contains formatted rich text string.
Declaration
bool HasRichText { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
The following code illustrates how to access HasRichText property.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Create style
IStyle style = workbook.Styles.Add("CustomStyle");
//Set rich text
IRichTextString richText = worksheet["C2"].RichText;
//Set text
richText.Text = "Sample";
//Set font
IFont font = style.Font;
//Set color
font.Color = ExcelKnownColors.Red;
//Set rich text font
richText.SetFont(0, 5, font);
//Check HasRichText
Console.Write(worksheet["C2"].HasRichText);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//TrueHasString
Gets a Boolean value indicating whether Range contains string. Read-only.
Declaration
bool HasString { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
HasStyle
Gets a Boolean value indicating whether Range has default style. False means default style. Read-only.
Declaration
bool HasStyle { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
The following code illustrates how to access HasStyle property.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Add style
IStyle style = workbook.Styles.Add("CustomStyle");
//Set color
style.ColorIndex = ExcelKnownColors.Red;
//Set style
worksheet["C2"].CellStyle = style;
//Check HasStyle
Console.Write(worksheet["C2"].HasStyle);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//TrueHorizontalAlignment
Gets or sets the horizontal alignment for the specified object.
Declaration
ExcelHAlign HorizontalAlignment { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelHAlign |
Examples
By default HAlignGeneral is set to cells. Here for example, we set HAlignRight to align the cell contents to right.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "Test";
//Set alignment
worksheet["A1"].HorizontalAlignment = ExcelHAlign.HAlignRight;
//Set row height
worksheet["A1"].RowHeight = 30;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}Hyperlinks
Gets hyperlinks in the Range. Read-only.
Declaration
IHyperLinks Hyperlinks { get; }Property Value
| Type | Description |
|---|---|
| IHyperLinks |
Examples
The following code illustrates how to access Borders property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
// Creating a Hyperlink for a Website.
IHyperLink hyperlink = sheet.HyperLinks.Add(sheet.Range["C5"]);
hyperlink.Type = ExcelHyperLinkType.Url;
hyperlink.Address = "http://www.syncfusion.com";
hyperlink.ScreenTip = "To know more About SYNCFUSION PRODUCTS go through this link";
workbook.SaveAs("Hyperlinks.xlsx");
workbook.Close();
excelEngine.Dispose();IgnoreErrorOptions
Gets or sets ignore error options for formula auditing. If not single cell gets concatenated flags.
Declaration
ExcelIgnoreError IgnoreErrorOptions { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelIgnoreError |
IndentLevel
Gets or sets the indent level for cell or Range.
Declaration
int IndentLevel { get; set; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
Remarks
It can be from 0 to 15 for Excel 97-2003 and 250 for Excel 2007-2013.
Examples
The following code illustrates how to set indent level for a IStyle.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
//Set indent level
worksheet["C2"].IndentLevel = 2;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}IsBlank
Gets a Boolean value indicating whether the Range is blank. Read-only.
Declaration
bool IsBlank { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
IsBoolean
Gets a Boolean value indicating whether cell type is boolean. Read-only.
Declaration
bool IsBoolean { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
IsError
Gets a Boolean value indicating whether Range contains error value. Read-only.
Declaration
bool IsError { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
IsGroupedByColumn
Gets a Boolean value indicating whether Range is grouped by column. Read-only.
Declaration
bool IsGroupedByColumn { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
IsGroupedByRow
Gets a Boolean value indicating whether Range is grouped by row. Read-only.
Declaration
bool IsGroupedByRow { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
IsInitialized
Gets a Boolean value indicating whether cell is initialized. Read-only.
Declaration
bool IsInitialized { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
IsMerged
Gets a Boolean value indicating whether Range is a part of merged Range. Read-only.
Declaration
bool IsMerged { get; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "Sample text in cell";
//Set merge
worksheet["A1:B1"].Merge();
//Check merge
Console.Write(worksheet["A1:B1"].IsMerged);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//TrueIsStringsPreserved
Gets a Boolean value indicating whether all values in the Range are preserved as strings.
Declaration
Nullable<bool> IsStringsPreserved { get; set; }Property Value
| Type | Description |
|---|---|
| System.Nullable<System.Boolean> |
Item[Int32, Int32]
Gets or sets cell range by row and column index. Row and column indexes are one-based.
Declaration
IRange this[int row, int column] { get; set; }Parameters
| Type | Name | Description |
|---|---|---|
| System.Int32 | row | |
| System.Int32 | column |
Property Value
| Type | Description |
|---|---|
| IRange |
Item[Int32, Int32, Int32, Int32]
Get cell Range. Row and column indexes are one-based. Read-only.
Declaration
IRange this[int row, int column, int lastRow, int lastColumn] { get; }Parameters
| Type | Name | Description |
|---|---|---|
| System.Int32 | row | |
| System.Int32 | column | |
| System.Int32 | lastRow | |
| System.Int32 | lastColumn |
Property Value
| Type | Description |
|---|---|
| IRange |
Item[String]
Gets cell Range. Read-only.
Declaration
IRange this[string name] { get; }Parameters
| Type | Name | Description |
|---|---|---|
| System.String | name |
Property Value
| Type | Description |
|---|---|
| IRange |
Item[String, Boolean]
Gets cell Range with R1C1Notation flag. Read-only.
Declaration
IRange this[string name, bool IsR1C1Notation] { get; }Parameters
| Type | Name | Description |
|---|---|---|
| System.String | name | |
| System.Boolean | IsR1C1Notation |
Property Value
| Type | Description |
|---|---|
| IRange |
LastColumn
Gets the column index of the last column in the Range which is a one based index. Read-only.
Declaration
int LastColumn { get; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
LastRow
Gets the row index of the last row in the Range which is a one based index. Read-only.
Declaration
int LastRow { get; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
MergeArea
Gets a IRange object that represents the merged Range to which the cell or Range belongs. Read-only.
Declaration
IRange MergeArea { get; }Property Value
| Type | Description |
|---|---|
| IRange |
Remarks
If the specified cell isn�t in a merged Range, this property gets NULL.
Examples
The following code illustrates how to access MergeArea property.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Merge
worksheet["C2:D3"].Merge();
//Check merge area
Console.Write(worksheet["C2"].MergeArea.AddressLocal);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//C2:D3Number
Gets or sets number value that is contained by Range.
Declaration
double Number { get; set; }Property Value
| Type | Description |
|---|---|
| System.Double |
Exceptions
| Type | Condition |
|---|---|
| System.FormatException | When Range value is not a number. |
NumberFormat
Gets or sets format of cell which is similar to Style.NumberFormat property.
Declaration
string NumberFormat { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Remarks
Gets NULL if all cells in the specified Range don't have the same number format.
Examples
The following code illustrates how to set NumberFormat property.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Load data
worksheet["C2"].Value = "3100.23";
//Set number format
worksheet["C2"].NumberFormat = "#,##0.##";
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}RichText
Gets string with rich text formatting in the Range. Read-only.
Declaration
IRichTextString RichText { get; }Property Value
| Type | Description |
|---|---|
| IRichTextString |
Examples
The following code illustrates how to set rich text formatting in the range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Create style
IStyle style = workbook.Styles.Add("CustomStyle");
//Set rich text
IRichTextString richText = worksheet["C2"].RichText;
//Set text
richText.Text = "Sample text";
//Set font
IFont font = style.Font;
//Set color
font.Color = ExcelKnownColors.Red;
//Set rich text font
richText.SetFont(0, 5, font);
//Set color
font.Color = ExcelKnownColors.Blue;
//Set rich text font
richText.SetFont(6, 10, font);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}Row
Gets the row index of the first row in the Range which is a one based index. Read-only.
Declaration
int Row { get; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
RowGroupLevel
Gets row group level. Read-only.
Declaration
int RowGroupLevel { get; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
Remarks
-1 - Not all rows in the Range have same group level. 0 - No grouping, 1 - 7 - Group level.
RowHeight
Gets or sets the height of all the rows in the range, measured in points.
Declaration
double RowHeight { get; set; }Property Value
| Type | Description |
|---|---|
| System.Double |
Remarks
Gets Double.MinValue if the rows in the specified Range aren't all the same height. Maximum Row height can be 409 value, minimum is zero.
Examples
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "Test";
//Set alignment
worksheet["A1"].VerticalAlignment = ExcelVAlign.VAlignTop;
//Set row height
worksheet["A1"].RowHeight = 30;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}Rows
Gets an array of IRange objects that represent the rows in the Range. Read only.
Declaration
IRange[] Rows { get; }Property Value
| Type | Description |
|---|---|
| IRange[] |
Examples
The following code illustrates how to access Columns property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
IRange[] rows = sheet["A1:E8"].Rows;
foreach (IRange row in rows)
{
// Do some manipulations
}
workbook.SaveAs("Rows.xlsx");
workbook.Close();
excelEngine.Dispose();Text
Gets or sets string value of the Range.
Declaration
string Text { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
TimeSpan
Gets or sets time value of the Range.
Declaration
TimeSpan TimeSpan { get; set; }Property Value
| Type | Description |
|---|---|
| System.TimeSpan |
Value
Gets or sets the value of the Range. Does not support FormulaArray value.
Declaration
string Value { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Remarks
Sets different data types values as a string, Value property parses the input string to determine its type which leads performance delay.
The only difference between the Value2 property and the Value property is that the Value2 property does not use the Currency and Date data types. Also, it does not support FormulaArray value.
Examples
The following code illustrates how to set and access Value property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
IRange range= sheet.Range[3, 1];
range.Value = "1/1/2015";
range.NumberFormat = "dd-MMM-yyyy";
string displayText = range.DisplayText;
workbook.SaveAs("Value.xlsx");
workbook.Close();
excelEngine.Dispose();Value2
Gets or sets the cell value.
Declaration
object Value2 { get; set; }Property Value
| Type | Description |
|---|---|
| System.Object |
Remarks
Sets different data types values as a object.
Value2 first checks whether the specified object has the type known for it (DateTime, TimeSpan, Double, Int).
If yes, then it uses the corresponding typed properties (DateTime, TimeSpan, and Number).
Otherwise, it calls Value property with String data type.
The only difference between the Value2 property and the Value property is that the Value2 property does not use the Currency and Date data types. Also, it does not support FormulaArray value.
Examples
The following code illustrates how to set and access Value2 property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assigning Value2 property of the Range
sheet["A1"].Value2 = DateTime.Now;
sheet["A2"].Value2 = 45;
sheet["A3"].Value2 = false;
// Checking Range types.
bool isDateTime = sheet["A1"].HasDateTime;
bool isNumber = sheet["A2"].HasNumber;
bool isboolean = sheet["A3"].HasBoolean;
workbook.SaveAs("Value2.xlsx");
workbook.Close();
excelEngine.Dispose();VerticalAlignment
Gets or sets the vertical alignment of the Range.
Declaration
ExcelVAlign VerticalAlignment { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelVAlign |
Examples
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "Test";
//Set alignment
worksheet["A1"].VerticalAlignment = ExcelVAlign.VAlignTop;
//Set row height
worksheet["A1"].RowHeight = 30;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}Worksheet
Gets the IWorksheet object in which belongs to the Range. Read-only.
Declaration
IWorksheet Worksheet { get; }Property Value
| Type | Description |
|---|---|
| IWorksheet |
WrapText
Gets or sets wrap text of the Range.
Declaration
bool WrapText { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
While applying wraptext to rows/columns in Excel, autofit is done for rows/columns everytime when data is added. But in XlsIO, autofit of rows/columns are not done like Excel because of performance considerations. To achieve Excel behavior, the autofit can be invoked manually.
Examples
The following code illustrates how to set WrapText property.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "This cell contains sample text";
//Set wrap text
worksheet["A1"].WrapText = true;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}Methods
Activate()
Activates the cell.
Declaration
IRange Activate()Returns
| Type | Description |
|---|---|
| IRange | Returns the active cell. |
Examples
The following code illustrates how to activate a Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
// Activates 'F1' cell.
worksheet.Range["F1"].Activate();
workbook.SaveAs("Activate.xlsx");
workbook.Close();
excelEngine.Dispose();Activate(Boolean)
Activates the cell and its worksheet.
Declaration
IRange Activate(bool scroll)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | scroll | True to set as top left cell. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the active cell. |
Examples
The following code illustrates how to activate a Range with scroll flag.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
// Activates 'F1' cell.
worksheet.Range["F1"].Activate(true);
workbook.SaveAs("Activate.xlsx");
workbook.Close();
excelEngine.Dispose();AddComment()
Adds a comment.
Declaration
ICommentShape AddComment()Returns
| Type | Description |
|---|---|
| ICommentShape | Returns a comment shape. |
Examples
The following code illustrates how to insert Comments in the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
// Adding comments to a cell.
ICommentShape comment = sheet.Range["A1"].AddComment();
comment.Text= "Comments";
// Add Rich Text Comments.
IRange range = sheet.Range["A6"];
range.AddComment().RichText.Text = "RichText";
IRichTextString rtf = range.Comment.RichText;
// Formatting first 4 characters.
IFont redFont = workbook.CreateFont();
redFont.Bold = true;
redFont.Color = ExcelKnownColors.Red;
rtf.SetFont(0, 3, redFont);
//Save and dispose
workbook.SaveAs("Comments.xlsx");
workbook.Close();
}AutofitColumns()
Changes the width of the columns in the range to achieve the best fit.
Declaration
void AutofitColumns()Remarks
To know more about resizing rows and columns refer Auto-Fit Rows or Columns. This method is supported on Windows Forms, WPF, ASP.NET, ASP.NET MVC, WinRT, WP, Universal and UWP,Xamarin and NetStandard platforms only.
Examples
The following code shows how to auto-size row height to its cell content.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Auto-fit columns
worksheet.Range["B4"].Text = "Fit the content to column";
worksheet.Range["B4"].AutofitColumns();
//Save and dispose
workbook.SaveAs("AutoFit.xlsx");
workbook.Close();
excelEngine.Dispose();
}AutofitRows()
Changes the height of the rows in the range to achieve the best fit.
Declaration
void AutofitRows()Remarks
To know more about resizing rows and columns refer Auto-Fit Rows or Columns. This method is supported on Windows Forms, WPF, ASP.NET, ASP.NET MVC, WinRT, WP, Universal and UWP,Xamarin and NetStandard platforms only.
Examples
The following code shows how to auto-size row height to its cell content.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Auto-fit rows
worksheet.Range["A2"].Text = "Fit the content to row";
worksheet.Range["A2"].WrapText = true;
worksheet.Range["A2"].AutofitRows();
//Save and dispose
workbook.SaveAs("AutoFit.xlsx");
workbook.Close();
excelEngine.Dispose();
}Average()
Returns average of numeric cells in the range.
Declaration
double Average()Returns
| Type | Description |
|---|---|
| System.Double |
Examples
The following code illustrates how to find the average of numeric cells in the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set value to cells
worksheet.Range["A1:A3"].Value = "10";
//Returns average of numeric cells in the range.
double average = worksheet.Range["A1:A3"].Average();
workbook.SaveAs("Average.xlsx");
workbook.Close();
excelEngine.Dispose();Average(Boolean)
Returns average of numeric cells in the range.
Declaration
double Average(bool considerDateAsNumber)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | considerDateAsNumber | Considers numeric value of date formatted cells for calculation. |
Returns
| Type | Description |
|---|---|
| System.Double |
Examples
The following code illustrates how to find the average of numeric cells in the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set value to cells
worksheet.Range["A1:A3"].Value = "10";
//Returns average of numeric cells in the range.
double average = worksheet.Range["A1:A3"].Average(true);
workbook.SaveAs("Average.xlsx");
workbook.Close();
excelEngine.Dispose();BorderAround()
Applies border around the Range. The default line style is Thin.
Declaration
void BorderAround()Examples
The following code illustrates how to apply border around the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Set border
worksheet["C2:D3"].BorderAround();
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}BorderAround(ExcelLineStyle)
Applies border around the Range with the specified ExcelLineStyle.
Declaration
void BorderAround(ExcelLineStyle borderLine)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelLineStyle | borderLine | Represents border line style. |
Examples
The following code illustrates how to apply border around the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Set border
worksheet["C2:D3"].BorderAround(ExcelLineStyle.Thick);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}BorderAround(ExcelLineStyle, ExcelKnownColors)
Applies border around the Range with the specified ExcelLineStyle and ExcelKnownColors.
Declaration
void BorderAround(ExcelLineStyle borderLine, ExcelKnownColors borderColor)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelLineStyle | borderLine | Represents border line style. |
| ExcelKnownColors | borderColor | Represents border color. |
Examples
The following code illustrates how to apply border around the Range with ExcelKnownColors.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Set border
worksheet["C2:D3"].BorderAround(ExcelLineStyle.Thick, ExcelKnownColors.Red);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}BorderAround(ExcelLineStyle, Color)
Applies border around the Range with the specified ExcelLineStyle and System.Drawing.Color.
Declaration
void BorderAround(ExcelLineStyle borderLine, Color borderColor)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelLineStyle | borderLine | Represents border line style. |
| Windows.UI.Color | borderColor | Represents border color. |
Examples
The following code illustrates how to apply border around the Range with color from System.Drawing.Color structure.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Set border
worksheet["C2:D3"].BorderAround(ExcelLineStyle.Thick, System.Drawing.Color.Red);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}BorderInside()
Applies border inside the Range. The default line style is Thin.
Declaration
void BorderInside()Examples
The following code illustrates how to apply border inside the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Set border
worksheet["C2:D3"].BorderInside();
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}BorderInside(ExcelLineStyle)
Applies border inside the Range with the specified ExcelLineStyle.
Declaration
void BorderInside(ExcelLineStyle borderLine)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelLineStyle | borderLine | Represents border line style. |
Examples
The following code illustrates how to apply border inside the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Set border
worksheet["C2:D3"].BorderInside(ExcelLineStyle.Thick);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}BorderInside(ExcelLineStyle, ExcelKnownColors)
Applies border inside the Range with the specified ExcelLineStyle and ExcelKnownColors.
Declaration
void BorderInside(ExcelLineStyle borderLine, ExcelKnownColors borderColor)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelLineStyle | borderLine | Represents border line style. |
| ExcelKnownColors | borderColor | Represents border color. |
Examples
The following code illustrates how to apply border around the Range with ExcelKnownColors.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Set border
worksheet["C2:D3"].BorderInside(ExcelLineStyle.Thick, ExcelKnownColors.Red);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}BorderInside(ExcelLineStyle, Color)
Applies border inside the Range with the specified ExcelLineStyle and System.Drawing.Color.
Declaration
void BorderInside(ExcelLineStyle borderLine, Color borderColor)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelLineStyle | borderLine | Represents border line style. |
| Windows.UI.Color | borderColor | Represents border color. |
Examples
The following code illustrates how to apply border inside the Range with color from System.Drawing.Color structure.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Set border
worksheet["C2:D3"].BorderInside(ExcelLineStyle.Thick, System.Drawing.Color.Red);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}BorderNone()
Applies no border in the Range.
Declaration
void BorderNone()Examples
The following code illustrates how to remove borders in the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
//Set borders
IBorders borders = worksheet["C2"].Borders;
//Set border color
borders[ExcelBordersIndex.EdgeTop].Color = ExcelKnownColors.Red;
borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Blue;
//Set line style
borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thick;
borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thick;
//Remove borders
worksheet["C2"].BorderNone();
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}Clear()
Clears content in the Range.
Declaration
void Clear()Examples
The following code illustrates how to clear the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
// Clears the Range �A4� .
sheet.Range["A4"].Clear();
workbook.SaveAs("ClearRange.xlsx");
workbook.Version = ExcelVersion.Excel2013;
workbook.Close();
excelEngine.Dispose();Clear(ExcelClearOptions)
Clears the Range with the specified ExcelClearOptions.
Declaration
void Clear(ExcelClearOptions option)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelClearOptions | option | Represents the clear options. |
Examples
The following code illustrates how to clear the Range with clear options.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
// Clears the Range �A4� with its clear options.
sheet.Range["A4"].Clear(ExcelClearOptions.ClearAll);
workbook.SaveAs("ClearRange.xlsx");
workbook.Version = ExcelVersion.Excel2013;
workbook.Close();
excelEngine.Dispose();Clear(ExcelMoveDirection)
Clears the Range and shifts the cells Up or Left.
Declaration
void Clear(ExcelMoveDirection direction)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelMoveDirection | direction | Shifts the cells to the specified direction. |
Examples
The following code illustrates how to clear the Range with specified direction.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
// Clearing a Range �A4� with move options.
sheet.Range["A4"].Clear(ExcelMoveDirection.MoveLeft);
workbook.SaveAs("ClearRange.xlsx");
workbook.Version = ExcelVersion.Excel2013;
workbook.Close();
excelEngine.Dispose();Clear(ExcelMoveDirection, ExcelCopyRangeOptions)
Clears the Range with the specified ExcelMoveDirection and ExcelCopyRangeOptions.
Declaration
void Clear(ExcelMoveDirection direction, ExcelCopyRangeOptions options)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelMoveDirection | direction | Shifts the cells to the specified direction. |
| ExcelCopyRangeOptions | options | Copies the cells with the specified copy Range options. |
Examples
The following code illustrates how to clear the Range with specified direction and copy Range options.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
// Clearing a Range �A4� with copy Range and move options.
sheet.Range["A4"].Clear(ExcelMoveDirection.MoveLeft,ExcelCopyRangeOptions.All);
workbook.SaveAs("ClearRange.xlsx");
workbook.Version = ExcelVersion.Excel2013;
workbook.Close();
excelEngine.Dispose();Clear(Boolean)
Clears content and formats in the Range.
Declaration
void Clear(bool isClearFormat)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | isClearFormat | True to clear format. |
Examples
The following code illustrates how to clear the Range with formats.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Set border
worksheet["C2:D3"].BorderInside();
//Clear format
worksheet["C2:D3"].Clear(true);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}CollapseGroup(ExcelGroupBy)
Collapses the group by rows/columns.
Declaration
void CollapseGroup(ExcelGroupBy groupBy)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelGroupBy | groupBy | Represents to collapse the group by rows/columns. |
Examples
The following code illustrates how to collapse the group in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Collapse group
worksheet.Range["A5:A15"].CollapseGroup(ExcelGroupBy.ByRows);
workbook.SaveAs("CollapseGroup.xlsx");
workbook.Close();
excelEngine.Dispose();CopyTo(IRange)
Copies the Range to the specified Range (without updating formulas).
Declaration
IRange CopyTo(IRange destination)Parameters
| Type | Name | Description |
|---|---|---|
| IRange | destination | Represents the destination Range to copy. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the copied Range. |
Examples
The following code illustrates how to copy the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
// Copying a Range �A1� to �A5�.
IRange source = sheet.Range["A1"];
IRange destination = sheet.Range["A5"];
source.CopyTo(destination);
workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("CopyRange.xlsx");
workbook.Close();
excelEngine.Dispose();CopyTo(IRange, ExcelCopyRangeOptions)
Copies the Range to the specified destination Range with copy options.
Declaration
IRange CopyTo(IRange destination, ExcelCopyRangeOptions options)Parameters
| Type | Name | Description |
|---|---|---|
| IRange | destination | Represents the destination Range to copy. |
| ExcelCopyRangeOptions | options | Represents the copy Range options. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the copied Range. |
Examples
The following code illustrates how to copy the Range with copy options.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
// Copying a Range �A1� to �A5�.
IRange source = sheet.Range["A1"];
IRange destination = sheet.Range["A5"];
source.CopyTo(destination, ExcelCopyRangeOptions.All);
workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("CopyRange.xlsx");
workbook.Close();
excelEngine.Dispose();CopyTo(IRange, ExcelCopyRangeOptions, Boolean)
Copies the Range to the specified destination Range with copy options and skip blanks option.
Declaration
IRange CopyTo(IRange destination, ExcelCopyRangeOptions options, bool skipBlanks)Parameters
| Type | Name | Description |
|---|---|---|
| IRange | destination | Represents the destination Range to copy. |
| ExcelCopyRangeOptions | options | Represents the copy Range options. |
| System.Boolean | skipBlanks | Skip blank cells while copying the range from source to destination. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the copied Range. |
Remarks
This method can be used to prevent copying blank cells in the destination range. If "true", then blank cells are not copied from source range.
Examples
The following code illustrates how to copy the Range with skip blanks option.
using(ExcelEngine engine = new ExcelEngine())
{
//Create worksheet
IApplication application = engine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Load Data
worksheet["A1"].Value = "A";
worksheet["A3"].Value = "B";
worksheet["A5"].Value = "C";
worksheet["A7"].Value = "D";
worksheet["B1"].Value = "E";
worksheet["B2"].Value = "F";
worksheet["B4"].Value = "G";
worksheet["B6"].Value = "H";
//Apply styles
worksheet["A1:A7"].CellStyle.ColorIndex = ExcelKnownColors.Yellow;
//Skip blanks while copying
worksheet["A1:A7"].CopyTo(worksheet["B1"], ExcelCopyRangeOptions.All, true);
workbook.SaveAs("SkipBlanks.xlsx");
workbook.Close();
}CopyTo(IRange, Boolean)
Copies the Range to the specified destination Range with copy options.
Declaration
IRange CopyTo(IRange destination, bool pasteLink)Parameters
| Type | Name | Description |
|---|---|---|
| IRange | destination | Represents the destination Range to copy. |
| System.Boolean | pasteLink | Represents if the pasteLinkOptions implement or not . |
Returns
| Type | Description |
|---|---|
| IRange | Returns the copied Range. |
Examples
The following code illustrates how to copy the Range with pasteLink options.
ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IRange source = worksheet["A1:D5"];
IRange destination = worksheet["E10"];
// Copy range as link from source to destination
source.CopyTo(destination, true);
workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("CopyRange.xlsx");
workbook.Close();
engine.Dispose();ExpandGroup(ExcelGroupBy)
Expands the group by rows/columns.
Declaration
void ExpandGroup(ExcelGroupBy groupBy)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelGroupBy | groupBy | Represents to expand the group by rows/columns. |
Examples
The following code illustrates how to expand the group in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Expand group with flag set to expand parent
worksheet.Range["A5:A15"].ExpandGroup(ExcelGroupBy.ByRows);
workbook.SaveAs("ExpandGroup.xlsx");
workbook.Close();
excelEngine.Dispose();ExpandGroup(ExcelGroupBy, ExpandCollapseFlags)
Expands the group by row/column with collapse option.
Declaration
void ExpandGroup(ExcelGroupBy groupBy, ExpandCollapseFlags flags)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelGroupBy | groupBy | Represents to expand the group by rows/columns. |
| ExpandCollapseFlags | flags | Represent the collapses options. |
Examples
The following code illustrates how to perform ExpandGroup in the Range with collapse option.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Expand group with flag set to expand parent
worksheet.Range["A5:A15"].ExpandGroup(ExcelGroupBy.ByRows, ExpandCollapseFlags.ExpandParent);
workbook.SaveAs("ExpandGroup.xlsx");
workbook.Close();
excelEngine.Dispose();FindAll(Boolean)
Returns the cells with a specified bool value.
Declaration
IRange[] FindAll(bool findValue)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | findValue | Value to be found. |
Returns
| Type | Description |
|---|---|
| IRange[] | Returns the cells with a specified bool value, or null if value was not found. |
Examples
The following code illustrates how to find the cells with specified bool value in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
//Find all with bool
IRange[] results = sheet["A2:K100"].FindAll(true);
string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();FindAll(DateTime)
Returns the cells with a specified DateTime value.
Declaration
IRange[] FindAll(DateTime findValue)Parameters
| Type | Name | Description |
|---|---|---|
| System.DateTime | findValue | Value to be found. |
Returns
| Type | Description |
|---|---|
| IRange[] | Returns the cells with a specified DateTime value, or null if value was not found. |
Examples
The following code illustrates how to find the cells with specified DateTime value in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
//Find all with DateTime
IRange[] results = sheet["A2:K100"].FindAll(DateTime.Now);
string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();FindAll(Double, ExcelFindType)
Returns the cells with a specified double value and ExcelFindType.
Declaration
IRange[] FindAll(double findValue, ExcelFindType flags)Parameters
| Type | Name | Description |
|---|---|---|
| System.Double | findValue | Value to be found. |
| ExcelFindType | flags | Represents type of the value to be found. |
Returns
| Type | Description |
|---|---|
| IRange[] | Returns the cells with a specified double value, or null if value was not found. |
Examples
The following code illustrates how to find the cells with specified double value in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
//Find All with number
IRange[] results = sheet["A2:K100"].FindAll(100.32, ExcelFindType.Number);
string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();FindAll(String, ExcelFindType)
Returns the cells with a specified string value and ExcelFindType.
Declaration
IRange[] FindAll(string findValue, ExcelFindType flags)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | findValue | Value to be found. |
| ExcelFindType | flags | Represents type of the value to be found. |
Returns
| Type | Description |
|---|---|
| IRange[] | Returns the cells with a specified string value, or null if value was not found. |
Examples
The following code illustrates how to find the cells with specified string value in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
//Find all with string
IRange[] results = sheet["A2:K100"].FindAll("Simple text", ExcelFindType.Text);
string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();FindAll(TimeSpan)
Returns the cells with a specified TimeSpan value.
Declaration
IRange[] FindAll(TimeSpan findValue)Parameters
| Type | Name | Description |
|---|---|---|
| System.TimeSpan | findValue | Value to be found. |
Returns
| Type | Description |
|---|---|
| IRange[] | Returns the cells with a specified TimeSpan value, or null if value was not found. |
Examples
The following code illustrates how to find the cells with specified TimeSpan value in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
//Find All with Timespan
IRange[] results = sheet["A2:K100"].FindAll(TimeSpan.MinValue);
string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();FindFirst(Boolean)
Returns the first occurrence cell with the specified bool value.
Declaration
IRange FindFirst(bool findValue)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | findValue | Value to be found. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the first cell with a specified bool value, or null if value was not found. |
Examples
The following code illustrates how to find the first occurrence of the specified bool value in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
//Find First with bool
IRange result = sheet["A2:K100"].FindFirst(true);
string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();FindFirst(DateTime)
Returns the first occurrence cell with the specified DateTime value.
Declaration
IRange FindFirst(DateTime findValue)Parameters
| Type | Name | Description |
|---|---|---|
| System.DateTime | findValue | Value to be found. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the first cell with a specified DateTime value, or null if value was not found. |
Examples
The following code illustrates how to find the first occurrence of the specified DateTime value in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
//Find First with DateTime
IRange result = sheet["A2:K100"].FindFirst(DateTime.Now);
string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();FindFirst(Double, ExcelFindType)
Returns the first occurrence cell with the specified double value and ExcelFindType.
Declaration
IRange FindFirst(double findValue, ExcelFindType flags)Parameters
| Type | Name | Description |
|---|---|---|
| System.Double | findValue | Value to found. |
| ExcelFindType | flags | Flag that represent type of search. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the first cell with a specified double value, or null if value was not found. |
Examples
The following code illustrates how to find the first occurrence of the specified double value in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
//Find First with number
IRange result = sheet["A2:K100"].FindFirst(100.32, ExcelFindType.Number);
string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();FindFirst(String, ExcelFindType)
Returns the first occurrence cell with the specified string value and ExcelFindType.
Declaration
IRange FindFirst(string findValue, ExcelFindType flags)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | findValue | Value to found. |
| ExcelFindType | flags | Flag that represent type of search. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the first cell with a specified string value, or null if value was not found. |
Examples
The following code illustrates how to find the first occurrence of the specified string value in the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
//Find First with string
IRange result = sheet["A2:K100"].FindFirst("Simple text", ExcelFindType.Text);
string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();FindFirst(TimeSpan)
Returns the first occurrence cell with the specified TimeSpan value.
Declaration
IRange FindFirst(TimeSpan findValue)Parameters
| Type | Name | Description |
|---|---|---|
| System.TimeSpan | findValue | Value to be found. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the first cell with a specified TimeSpan value, or null if value was not found. |
Examples
The following code illustrates how to find the first occurrence of the specified TimeSpan value in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
//Find First with TimeSpan
IRange result = sheet["A2:K100"].FindFirst(TimeSpan.MinValue);
string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();FreezePanes()
Keep rows and columns visible in the Range while the rest of the worksheet scrolls.
Declaration
void FreezePanes()Examples
The following code illustrates how to freeze a pane in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Applying Freeze Pane to the sheet by specifying a cell.
sheet.Range["B2"].FreezePanes();
workbook.SaveAs("Freeze.xlsx");
workbook.Close();
excelEngine.Dispose();GetDependents()
Gets the dependent cells which refer to other cells.
Declaration
IRange[] GetDependents()Returns
| Type | Description |
|---|---|
| IRange[] | Returns the dependent cells or null if value was not found. |
Examples
The following code illustrates how to get the dependent cells from the worksheet.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IRange[] results = sheet["A1"].GetDependents();
string fileName = "Dependents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();GetDependents(Boolean)
Gets the dependent cells which refer to other cells.
Declaration
IRange[] GetDependents(bool isEntireWorkbook)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | isEntireWorkbook | Indicates whether to check from other worksheets. |
Returns
| Type | Description |
|---|---|
| IRange[] | Returns the dependent cells with a specified bool value or null if value was not found. |
Examples
The following code illustrates how to get the dependents cells from all the worksheets.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IRange[] results = sheet["A1"].GetDependents(true);
string fileName = "Dependents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();GetDirectDependents()
Gets the direct dependent cells which refer to other cells.
Declaration
IRange[] GetDirectDependents()Returns
| Type | Description |
|---|---|
| IRange[] | Returns the direct dependent cells or null if value was not found. |
Examples
The following code illustrates how to get the direct dependent cells from the worksheet.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IRange[] results = sheet["A1"].GetDirectDependents();
string fileName = "Dependents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();GetDirectDependents(Boolean)
Gets the direct dependent cells which refer to other cells.
Declaration
IRange[] GetDirectDependents(bool isEntireWorkbook)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | isEntireWorkbook | Indicates whether to check from other worksheets. |
Returns
| Type | Description |
|---|---|
| IRange[] | Returns the direct dependent cells with a specified bool value or null if value was not found. |
Examples
The following code illustrates how to get the direct dependents cells from all the worksheets.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IRange[] results = sheet["A1"].GetDirectDependents(true);
string fileName = "Dependents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();GetDirectPrecedents()
Gets the direct precedent cells which are referred by a formula in another cell.
Declaration
IRange[] GetDirectPrecedents()Returns
| Type | Description |
|---|---|
| IRange[] | Returns the direct precedent cells or null if value was not found. |
Examples
The following code illustrates how to get the direct precedent cells from the worksheet.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IRange[] results = sheet["A1"].GetPrecedents();
string fileName = "Precedents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();GetDirectPrecedents(Boolean)
Gets the direct precedent cells which are referred by a formula in another cell.
Declaration
IRange[] GetDirectPrecedents(bool isEntireWorkbook)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | isEntireWorkbook | Indicates whether to check from other worksheets. |
Returns
| Type | Description |
|---|---|
| IRange[] | Returns the direct precedent cells with a specified bool value or null if value was not found. |
Examples
The following code illustrates how to get the direct precedent cells from all the worksheets.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IRange[] results = sheet["A1"].GetPrecedents(true);
string fileName = "Precedents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();GetPrecedents()
Gets the precedent cells which are referred by a formula in another cell.
Declaration
IRange[] GetPrecedents()Returns
| Type | Description |
|---|---|
| IRange[] | Returns the precedent cells or null if value was not found. |
Examples
The following code illustrates how to get the precedent cells from the worksheet.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IRange[] results = sheet["A1"].GetPrecedents();
string fileName = "Precedents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();GetPrecedents(Boolean)
Gets the precedent cells which are referred by a formula in another cell.
Declaration
IRange[] GetPrecedents(bool isEntireWorkbook)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | isEntireWorkbook | Indicates whether to check from other worksheets. |
Returns
| Type | Description |
|---|---|
| IRange[] | Returns the precedent cells with a specified bool value or null if value was not found. |
Examples
The following code illustrates how to get the precedent cells from all the worksheets.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IRange[] results = sheet["A1"].GetPrecedents(true);
string fileName = "Precedents.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();Group(ExcelGroupBy)
Groups the Range by row/column.
Declaration
IRange Group(ExcelGroupBy groupBy)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelGroupBy | groupBy | Represents to expand the group by rows/columns. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the Range after grouping. |
Examples
The following code illustrates how to group the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Group Rows
worksheet.Range["A4:A6"].Group(ExcelGroupBy.ByRows);
//Group Columns
worksheet.Range["C1:F1"].Group(ExcelGroupBy.ByColumns);
workbook.SaveAs("Grouping.xlsx");
workbook.Close();
excelEngine.Dispose();Group(ExcelGroupBy, Boolean)
Groups the Range by row/column with collapse flag.
Declaration
IRange Group(ExcelGroupBy groupBy, bool bCollapsed)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelGroupBy | groupBy | Represents to expand the group by rows/columns. |
| System.Boolean | bCollapsed | Indicates whether group should be collapsed. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the Range after grouping. |
Examples
The following code illustrates how to group the Range with collapse flag.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Group Rows
worksheet.Range["A1:A3"].Group(ExcelGroupBy.ByRows, true);
//Group Columns
worksheet.Range["A1:B1"].Group(ExcelGroupBy.ByColumns, false);
workbook.SaveAs("Grouping.xlsx");
workbook.Close();
excelEngine.Dispose();IntersectWith(IRange)
Returns intersection of the Range with specified Range.
Declaration
IRange IntersectWith(IRange range)Parameters
| Type | Name | Description |
|---|---|---|
| IRange | range | Range to be intersected. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the intersection Range;if there is no intersection, NULL is returned. |
Examples
The following code illustrates how to perform intersectwith in the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Merging cells
IRange range = worksheet.Range["A16:C16"];
IRange commonRange = worksheet.Range["B16:D16"].IntersectWith(range);
workbook.SaveAs("Intersection.xlsx");
workbook.Close();
excelEngine.Dispose();Max()
Returns maximum value from numeric cells in the range.
Declaration
double Max()Returns
| Type | Description |
|---|---|
| System.Double |
Examples
The following code illustrates how to find the maximum value from numeric cells in the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set value to cells
worksheet.Range["A1"].Value = "10";
worksheet.Range["A2"].Value = "20";
worksheet.Range["A3"].Value = "30";
//Returns maximum value from numeric cells in the range.
double maximum = worksheet.Range["A1:A3"].Max();
workbook.SaveAs("Max.xlsx");
workbook.Close();
excelEngine.Dispose();Max(Boolean)
Returns maximum value from numeric cells in the range.
Declaration
double Max(bool considerDateAsNumber)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | considerDateAsNumber | Considers numeric value of date formatted cells for calculation. |
Returns
| Type | Description |
|---|---|
| System.Double |
Examples
The following code illustrates how to find the maximum value from numeric cells in the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set value to cells
worksheet.Range["A1"].Value = "10";
worksheet.Range["A2"].Value = "20";
worksheet.Range["A3"].Value = "30";
//Returns maximum value from Numeric cells in the given range.
double maximum = worksheet.Range["A1:A3"].Max();
workbook.SaveAs("Max.xlsx");
workbook.Close();
excelEngine.Dispose();Merge()
Combines the contents of the selected cells in a new larger cell.
Declaration
void Merge()Remarks
To know more about merging refer Merging and Un-Merging Cells.
Examples
The following code illustrates how to merge the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "Merged cell";
//Merge cells
worksheet["A1:B1"].Merge();
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}Merge(Boolean)
Combines the contents of the selected cells in a new larger cell with clear option flag.
Declaration
void Merge(bool clearCells)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | clearCells | Clears the Merged cell. |
Examples
The following code illustrates how to merge the Range with clear option.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "Merged cell";
worksheet["B1"].Text = "sample";
//Merge cells
worksheet["A1:B1"].Merge(true);
//Unmerge cells
worksheet["A1:B1"].UnMerge();
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}MergeWith(IRange)
Merges the range with specified range and returns merged range.
Declaration
IRange MergeWith(IRange range)Parameters
| Type | Name | Description |
|---|---|---|
| IRange | range | Range to be merged with. |
Returns
| Type | Description |
|---|---|
| IRange | Returns a merged range; if the Range is unable to merge, null is returned. |
Examples
The following code illustrates how to check whether two ranges are mergable or not.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Merge
worksheet["A2:B2"].Merge();
//Get mergable range
IRange mergableRange = worksheet["A2"].MergeArea.MergeWith(worksheet["C2"]);
//Check mergable Area
if (mergableRange != null)
Console.Write("Mergable!");
else
Console.Write("Not Mergable!");
//Save and Dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//Mergable!Min()
Returns minumum value from numeric cells in the range.
Declaration
double Min()Returns
| Type | Description |
|---|---|
| System.Double |
Examples
The following code illustrates how to find the minumum value from numeric cells in the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set value to cells
worksheet.Range["A1"].Value = "10";
worksheet.Range["A2"].Value = "20";
worksheet.Range["A3"].Value = "30";
//Returns minumum value from Numeric cells in the given range.
double minimum = worksheet.Range["A1:A3"].Min();
workbook.SaveAs("Min.xlsx");
workbook.Close();
excelEngine.Dispose();Min(Boolean)
Returns minumum value from numeric cells in the range.
Declaration
double Min(bool considerDateAsNumber)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | considerDateAsNumber | Considers numeric value of date formatted cells for calculation. |
Returns
| Type | Description |
|---|---|
| System.Double |
Examples
The following code illustrates how to find the minumum value from numeric cells in the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set value to cells
worksheet.Range["A1"].Value = "10";
worksheet.Range["A2"].Value = "20";
worksheet.Range["A3"].Value = "30";
//Returns minumum value from Numeric cells in the range.
double minimum = worksheet.Range["A1:A3"].Min(true);
workbook.SaveAs("Min.xlsx");
workbook.Close();
excelEngine.Dispose();MoveTo(IRange)
Moves cells to the specified Range (without updating formulas).
Declaration
void MoveTo(IRange destination)Parameters
| Type | Name | Description |
|---|---|---|
| IRange | destination | Represents the destination Range to move. |
Examples
The following code illustrates how to move the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
// Moving a Range �A1� to �A5�.
IRange source = sheet.Range["A1"];
IRange destination = sheet.Range["A5"];
source.MoveTo(destination);
workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("MoveRange.xlsx");
workbook.Close();
excelEngine.Dispose();Offset(Int32, Int32)
Returns an IRange object that represents a range or cell that is offset from the specified range or cell.
Declaration
IRange Offset(int rowOffset, int columnOffset)Parameters
| Type | Name | Description |
|---|---|---|
| System.Int32 | rowOffset | The number of rows (positive, negative, or 0 (zero)) by which the range is to be offset |
| System.Int32 | columnOffset | The number of columns (positive, negative, or 0 (zero)) by which the range is to be offset |
Returns
| Type | Description |
|---|---|
| IRange | Returns a range which is shifted by the specified number of rows and columns. |
Remarks
Use the Offset method to access the cell range of the same size as the current range which is shifted by the specified number of rows and columns.
Examples
using(ExcelEngine engine = new ExcelEngine())
{
IApplication application = engine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IRange range = worksheet.Range[5,6];
var offset = range.Offset(-3,-1);
var address2 = offset.AddressLocal;
workbook.SaveAs("output.xlsx");
}Replace(String, DateTime)
Replaces the string with the specified DateTime value.
Declaration
void Replace(string oldValue, DateTime newValue)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | oldValue | The string to be replaced. |
| System.DateTime | newValue | The datetime value to replace all occurrences of oldValue. |
Examples
The following code illustrates how to replace the string value with datetime.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Find";
string oldValue = "Find";
DateTime dateTime = DateTime.Now;
sheet.Range["A1:A3"].Replace(oldValue,dateTime);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();Replace(String, Double)
Replaces the string with the specified double value.
Declaration
void Replace(string oldValue, double newValue)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | oldValue | The string to be replaced. |
| System.Double | newValue | The double value to replace all occurrences of oldValue. |
Examples
The following code snippet illustrates how to replace the string value with double.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Ten";
string oldValue = "Ten";
sheet.Range["A1:A3"].Replace(oldValue,10.0);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();Replace(String, Double[], Boolean)
Replaces the string with the specified array of double values.
Declaration
void Replace(string oldValue, double[] newValues, bool isVertical)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | oldValue | The string to be replaced. |
| System.Double[] | newValues | Array of new values. |
| System.Boolean | isVertical | Indicates whether array should be inserted vertically. |
Examples
The following code snippet illustrates how to replace the string with array of double values.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Find";
string oldValue = "Find";
double[] newValues = { 1.00, 3.00 };
sheet.Range["A1:A3"].Replace(oldValue, newValues, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();Replace(String, Int32[], Boolean)
Replaces the string with the specified array of int values.
Declaration
void Replace(string oldValue, int[] newValues, bool isVertical)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | oldValue | The string to be replaced. |
| System.Int32[] | newValues | Array of new values. |
| System.Boolean | isVertical | Indicates whether array should be inserted vertically. |
Examples
The following code snippet illustrates how to replace the string with array of int values.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Find";
string oldValue = "Find";
int[] newValues = { 1, 2 };
sheet.Range["A1:A3"].Replace(oldValue, newValues, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();Replace(String, String)
Replaces the string with the specified string value.
Declaration
void Replace(string oldValue, string newValue)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | oldValue | The string to be replaced. |
| System.String | newValue | The string to replace all occurrences of oldValue. |
Examples
The following code snippet illustrates how to replace the string with another string.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Find";
string oldValue = "Find";
string newValue = "NewValue";
sheet.Range["A1:A3"].Replace(oldValue, newValue);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();Replace(String, String, ExcelFindOptions)
Replaces the string with the specified string value based on the given ExcelFindOptions.
Declaration
void Replace(string oldValue, string newValue, ExcelFindOptions findOptions)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | oldValue | The string to be replaced. |
| System.String | newValue | The string to replace all occurrences of oldValue. |
| ExcelFindOptions | findOptions | Specifies the find options for the oldValue. |
Examples
The following code snippet illustrates how to replace the string with another string.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Find";
string oldValue = "Find";
string newValue = "NewValue";
sheet.Range["A1:A3"].Replace(oldValue, newValue, ExcelFindOptions.MatchCase);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();Replace(String, String[], Boolean)
Replaces the string with the specified array of string values.
Declaration
void Replace(string oldValue, string[] newValues, bool isVertical)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | oldValue | The string to be replaced. |
| System.String[] | newValues | Array of new values. |
| System.Boolean | isVertical | Indicates whether array should be inserted vertically. |
Examples
The following code snippet illustrates how to replace the string with array of string values.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Find";
string oldValue = "Find";
string[] newValues = { "X values", "Y values" };
sheet.Range["A1:A3"].Replace(oldValue, newValues, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();Resize(Int32, Int32)
Resizes the specified range a IRange object that represents the resized range.
Declaration
IRange Resize(int rowSize, int columnSize)Parameters
| Type | Name | Description |
|---|---|---|
| System.Int32 | rowSize | The number of rows in the new range. |
| System.Int32 | columnSize | The number of columns in the new range. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the resized range. |
Remarks
Use the Resize method to change the size of range by specified rows and columns from the current range.
Examples
using(ExcelEngine engine = new ExcelEngine())
{
IApplication application = engine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IRange range = worksheet.Range[5,6];
var resize = range.Resize(1000,2000);
var address = resize.AddressLocal;
workbook.SaveAs("output.xlsx");
}SubTotal(Int32, ConsolidationFunction, Int32[])
Creates subtotals for the Range.
Declaration
void SubTotal(int groupBy, ConsolidationFunction function, int[] totalList)Parameters
| Type | Name | Description |
|---|---|---|
| System.Int32 | groupBy | Column index based on which grouping should be done. |
| ConsolidationFunction | function | Represents a consolidation function to be applied. |
| System.Int32[] | totalList | List of column indexes on which subtotal is calculated. |
Examples
The following code illustrates how to subtotal the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Set the range for subtotaling
IRange range = worksheet.Range["C3:G12"];
//Perform subtotals for the Range with every change in first column
//and subtotals to be included for specified list of columns
range.SubTotal(0, ConsolidationFunction.Sum, new int[] { 2, 3, 4 });
workbook.SaveAs("Subtotal.xlsx");
workbook.Close();
excelEngine.Dispose(); SubTotal(Int32, ConsolidationFunction, Int32[], Boolean, Boolean, Boolean)
Creates subtotals for the Range with the specified formatting flags.
Declaration
void SubTotal(int groupBy, ConsolidationFunction function, int[] totalList, bool replace, bool pageBreaks, bool summaryBelowData)Parameters
| Type | Name | Description |
|---|---|---|
| System.Int32 | groupBy | Column index based on which grouping should be done |
| ConsolidationFunction | function | Represents a consolidation function to be applied. |
| System.Int32[] | totalList | List of column indexes on which subtotal is calculated. |
| System.Boolean | replace | Indicates whether existing SubTotal must be replaced. |
| System.Boolean | pageBreaks | Indicates whether page break must be inserted. |
| System.Boolean | summaryBelowData | Indicates whether SummaryBelowData to be shown |
Examples
The following code illustrates how to subtotal the Range with formats.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Set the range for subtotaling
IRange range = worksheet.Range["C3:G12"];
//Perform subtotals for the range with every change in first column
//and subtotals to be included for specified list of columns
range.SubTotal(0, ConsolidationFunction.Sum, new int[] { 2, 3, 4 }, false, true, true);
workbook.SaveAs("Subtotal.xlsx");
workbook.Close();
excelEngine.Dispose(); SubTotal(Int32[], ConsolidationFunction, Int32[], Boolean, Boolean, Boolean)
Creates subtotals for the Range with multiple group by columns and the specified formatting flags.
Declaration
void SubTotal(int[] groupBy, ConsolidationFunction function, int[] totalList, bool replace, bool pageBreaks, bool summaryBelowData)Parameters
| Type | Name | Description |
|---|---|---|
| System.Int32[] | groupBy | Column indexes based on which grouping is done. Given in the order of subtotal levels |
| ConsolidationFunction | function | Represents a consolidation function to be applied. |
| System.Int32[] | totalList | List of columns on which subtotal is calculated |
| System.Boolean | replace | Replaces Existing SubTotal |
| System.Boolean | pageBreaks | Inserts PageBreaks |
| System.Boolean | summaryBelowData | SummaryBelowData to be shown |
Examples
The following code illustrates how to subtotal the Range with formats.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Set the range for subtotaling
IRange range = worksheet.Range["C3:G12"];
//Perform subtotals for the range with every change in first column
//and subtotals to be included for specified list of columns
range.SubTotal( new int[] { 1, 2 }, ConsolidationFunction.Sum, new int[] { 2, 3, 4 }, false, true, true);
workbook.SaveAs("Subtotal.xlsx");
workbook.Close();
excelEngine.Dispose(); Sum()
Returns sum of numeric cells in the range.
Declaration
double Sum()Returns
| Type | Description |
|---|---|
| System.Double |
Examples
The following code illustrates how to find the sum of numeric cells in the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set value to cells
worksheet.Range["A1:A3"].Value = "10";
//Returns Sum of numeric cells in the range.
double sum = worksheet.Range["A1:A3"].Sum();
workbook.SaveAs("Sum.xlsx");
workbook.Close();
excelEngine.Dispose();Sum(Boolean)
Returns sum of numeric cells in the range.
Declaration
double Sum(bool considerDateAsNumber)Parameters
| Type | Name | Description |
|---|---|---|
| System.Boolean | considerDateAsNumber | Considers numeric value of date formatted cells for calculation. |
Returns
| Type | Description |
|---|---|
| System.Double |
Examples
The following code illustrates how to find the sum of numeric cells in the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set value to cells
worksheet.Range["A1:A3"].Value = "10";
//Returns Sum of numeric cells in the range.
double sum = worksheet.Range["A1:A3"].Sum(true);
workbook.SaveAs("Sum.xlsx");
workbook.Close();
excelEngine.Dispose();Trim()
Trim the empty rows at top and bottom of the range, the empty columns at left and right of the range.
Declaration
IRange Trim()Returns
| Type | Description |
|---|---|
| IRange | Returns the range after trim. |
Examples
The following code illustrates how to Trim the empty rows at top and bottom of the range, the empty columns at left and right of the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set value to cells
worksheet.Range["B1"].Value = "10";
worksheet.Range["B2"].Value = "20";
worksheet.Range["B3"].Value = "30";
//Returns the range after trim the given range.
IRange trim = worksheet.Range["A1:B3"].Trim();
workbook.SaveAs("Trim.xlsx");
workbook.Close();
excelEngine.Dispose();Ungroup(ExcelGroupBy)
Ungroups the Range.
Declaration
IRange Ungroup(ExcelGroupBy groupBy)Parameters
| Type | Name | Description |
|---|---|---|
| ExcelGroupBy | groupBy | Represents to expand the group by rows/columns. |
Returns
| Type | Description |
|---|---|
| IRange | Returns the Range after ungrouping. |
Examples
The following code illustrates how to ungroup the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Ungroup Rows
worksheet.Range["A1:A3"].Ungroup(ExcelGroupBy.ByRows);
//Ungroup Columns
worksheet.Range["C1:F1"].Ungroup(ExcelGroupBy.ByColumns);
workbook.SaveAs("Ungrouping.xlsx");
workbook.Close();
excelEngine.Dispose();UnMerge()
Separates merged cells into individual cells.
Declaration
void UnMerge()Remarks
To know more about merging refer Merging and Un-Merging Cells.
Examples
The following code illustrates how to UnMerge the merged cells.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "Merged cell";
//Merge cells
worksheet["A1:B1"].Merge();
//Unmerge cells
worksheet["A1:B1"].UnMerge();
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}