Interface IRange
Represents a cell, a row, a column, collection of cells or a 3-D Range.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.NET.dll
Syntax
public interface IRange : IParentApplication, IEnumerable<IRange>, IEnumerable
Properties
Address
Gets the Range reference in macro language. Read-only.
Declaration
string Address { get; }
Property Value
Type |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
System.String |
Examples
The following code illustrates how to access CellStyleName
property of the Range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Add style
IStyle style = workbook.Styles.Add("CustomStyle");
//Set color
style.ColorIndex = ExcelKnownColors.Red;
//Set style
worksheet["C2"].CellStyle = style;
//Check Style name
Console.Write(worksheet["C2"].CellStyleName);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//CustomStyle
Column
Gets the column index of the first column in the Range which is a one based index. Read-only.
Declaration
int Column { get; }
Property Value
Type |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
IRange |
Error
Gets or sets error value in the Range.
Declaration
string Error { get; set; }
Property Value
Type |
---|
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 |
---|
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 |
---|
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 |
---|
System.String |
FormulaBoolValue
Gets or sets the calculated value of the formula as a boolean.
Declaration
bool FormulaBoolValue { get; set; }
Property Value
Type |
---|
System.Boolean |
FormulaDateTime
Gets or sets formula DateTime value in the Range.
Declaration
DateTime FormulaDateTime { get; set; }
Property Value
Type |
---|
System.DateTime |
Remarks
Gets DateTime.MinValue if not all cells in the Range have same FormulaDateTime value.
FormulaErrorValue
Gets or sets the error value of the formula as a string.
Declaration
string FormulaErrorValue { get; set; }
Property Value
Type |
---|
System.String |
FormulaHidden
Gets or sets a Boolean value indicating whether the formula will be hidden when the worksheet is protected.
Declaration
bool FormulaHidden { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
True if the formula will be hidden when the worksheet is protected. False if at least part of formula in the Range is not hidden.
FormulaNumberValue
Gets or sets number value evaluated by formula.
Declaration
double FormulaNumberValue { get; set; }
Property Value
Type |
---|
System.Double |
FormulaR1C1
Gets or sets the formula for the Range, using R1C1-style notation.
Declaration
string FormulaR1C1 { get; set; }
Property Value
Type |
---|
System.String |
FormulaStringValue
Gets or sets string value evaluated by formula.
Declaration
string FormulaStringValue { get; set; }
Property Value
Type |
---|
System.String |
HasBoolean
Gets a Boolean value indicating whether Range contains bool value. Read-only.
Declaration
bool HasBoolean { get; }
Property Value
Type |
---|
System.Boolean |
Examples
The following code illustrates how to set and access HasBoolean
property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assigning Value2 property of the Range
sheet["A3"].Value2 = false;
// Checking Range types.
bool isboolean = sheet["A3"].HasBoolean;
workbook.SaveAs("HasBoolean.xlsx");
workbook.Close();
excelEngine.Dispose();
HasDataValidation
Gets a Boolean value indicating whether Range has data validation. Read-only.
Declaration
bool HasDataValidation { get; }
Property Value
Type |
---|
System.Boolean |
Remarks
If Range is not single cell, then gets true only if all cells have data validation.
HasDateTime
Gets a Boolean value indicating whether Range contains DateTime value. Read-only.
Declaration
bool HasDateTime { get; }
Property Value
Type |
---|
System.Boolean |
Examples
The following code illustrates how to set and access HasDateTime
property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assigning Value2 property of the Range
sheet["A1"].Value2 = DateTime.Now;
// Checking Range types.
bool isDateTime = sheet["A1"].HasDateTime;
workbook.SaveAs("HasDateTime.xlsx");
workbook.Close();
excelEngine.Dispose();
HasExternalFormula
Gets a Boolean value indicating whether Range has external formula. Read-only.
Declaration
bool HasExternalFormula { get; }
Property Value
Type |
---|
System.Boolean |
HasFormula
Gets a Boolean value indicating whether Range contains formula. Read-only.
Declaration
bool HasFormula { get; }
Property Value
Type |
---|
System.Boolean |
Remarks
True if all cells in the Range contain formulas; False if at least one of the cells in the Range doesn't contain a formula.
HasFormulaArray
Gets a Boolean value indicating whether Range contains array-entered formula. Read-only.
Declaration
bool HasFormulaArray { get; }
Property Value
Type |
---|
System.Boolean |
HasFormulaBoolValue
Gets a Boolean value indicating whether Range has formula bool value. Read-only.
Declaration
bool HasFormulaBoolValue { get; }
Property Value
Type |
---|
System.Boolean |
HasFormulaDateTime
Gets a Boolean value indicating whether Range has formula value formatted as DateTime. Read-only.
Declaration
bool HasFormulaDateTime { get; }
Property Value
Type |
---|
System.Boolean |
HasFormulaErrorValue
Gets a Boolean value indicating whether Range has formula error value. Read-only.
Declaration
bool HasFormulaErrorValue { get; }
Property Value
Type |
---|
System.Boolean |
HasFormulaNumberValue
Gets a Boolean value indicating whether Range has formula number value. Read-only.
Declaration
bool HasFormulaNumberValue { get; }
Property Value
Type |
---|
System.Boolean |
HasFormulaStringValue
Gets a Boolean value indicating whether Range has formula string value. Read-only.
Declaration
bool HasFormulaStringValue { get; }
Property Value
Type |
---|
System.Boolean |
HasNumber
Gets a Boolean value indicating whether Range contains number. Read-only.
Declaration
bool HasNumber { get; }
Property Value
Type |
---|
System.Boolean |
Examples
The following code illustrates how to set and access Value2
property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assigning Value2 property of the Range
sheet["A2"].Value2 = 45;
// Checking Range types.
bool isNumber = sheet["A2"].HasNumber;
workbook.SaveAs("HasNumber.xlsx");
workbook.Close();
excelEngine.Dispose();
HasRichText
Gets a Boolean value indicating whether cell contains formatted rich text string.
Declaration
bool HasRichText { get; }
Property Value
Type |
---|
System.Boolean |
Examples
The following code illustrates how to access HasRichText
property.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Create style
IStyle style = workbook.Styles.Add("CustomStyle");
//Set rich text
IRichTextString richText = worksheet["C2"].RichText;
//Set text
richText.Text = "Sample";
//Set font
IFont font = style.Font;
//Set color
font.Color = ExcelKnownColors.Red;
//Set rich text font
richText.SetFont(0, 5, font);
//Check HasRichText
Console.Write(worksheet["C2"].HasRichText);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//True
HasString
Gets a Boolean value indicating whether Range contains string. Read-only.
Declaration
bool HasString { get; }
Property Value
Type |
---|
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 |
---|
System.Boolean |
Examples
The following code illustrates how to access HasStyle
property.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Add style
IStyle style = workbook.Styles.Add("CustomStyle");
//Set color
style.ColorIndex = ExcelKnownColors.Red;
//Set style
worksheet["C2"].CellStyle = style;
//Check HasStyle
Console.Write(worksheet["C2"].HasStyle);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//True
HorizontalAlignment
Gets or sets the horizontal alignment for the specified object.
Declaration
ExcelHAlign HorizontalAlignment { get; set; }
Property Value
Type |
---|
ExcelHAlign |
Examples
By default HAlignGeneral is set to cells. Here for example, we set HAlignRight to align the cell contents to right.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "Test";
//Set alignment
worksheet["A1"].HorizontalAlignment = ExcelHAlign.HAlignRight;
//Set row height
worksheet["A1"].RowHeight = 30;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}
HtmlString
Gets or sets HTML string.
Declaration
string HtmlString { get; set; }
Property Value
Type |
---|
System.String |
Hyperlinks
Gets hyperlinks in the Range. Read-only.
Declaration
IHyperLinks Hyperlinks { get; }
Property Value
Type |
---|
IHyperLinks |
Examples
The following code illustrates how to access Borders
property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
// Creating a Hyperlink for a Website.
IHyperLink hyperlink = sheet.HyperLinks.Add(sheet.Range["C5"]);
hyperlink.Type = ExcelHyperLinkType.Url;
hyperlink.Address = "http://www.syncfusion.com";
hyperlink.ScreenTip = "To know more About SYNCFUSION PRODUCTS go through this link";
workbook.SaveAs("Hyperlinks.xlsx");
workbook.Close();
excelEngine.Dispose();
IgnoreErrorOptions
Gets or sets ignore error options for formula auditing. If not single cell gets concatenated flags.
Declaration
ExcelIgnoreError IgnoreErrorOptions { get; set; }
Property Value
Type |
---|
ExcelIgnoreError |
IndentLevel
Gets or sets the indent level for cell or Range.
Declaration
int IndentLevel { get; set; }
Property Value
Type |
---|
System.Int32 |
Remarks
It can be from 0 to 15 for Excel 97-2003 and 250 for Excel 2007-2013.
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 |
---|
System.Boolean |
IsBoolean
Gets a Boolean value indicating whether cell type is boolean. Read-only.
Declaration
bool IsBoolean { get; }
Property Value
Type |
---|
System.Boolean |
IsError
Gets a Boolean value indicating whether Range contains error value. Read-only.
Declaration
bool IsError { get; }
Property Value
Type |
---|
System.Boolean |
IsGroupedByColumn
Gets a Boolean value indicating whether Range is grouped by column. Read-only.
Declaration
bool IsGroupedByColumn { get; }
Property Value
Type |
---|
System.Boolean |
IsGroupedByRow
Gets a Boolean value indicating whether Range is grouped by row. Read-only.
Declaration
bool IsGroupedByRow { get; }
Property Value
Type |
---|
System.Boolean |
IsInitialized
Gets a Boolean value indicating whether cell is initialized. Read-only.
Declaration
bool IsInitialized { get; }
Property Value
Type |
---|
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 |
---|
System.Boolean |
Examples
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["A1"].Text = "Sample text in cell";
//Set merge
worksheet["A1:B1"].Merge();
//Check merge
Console.Write(worksheet["A1:B1"].IsMerged);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//True
IsStringsPreserved
Gets a Boolean value indicating whether all values in the Range are preserved as strings.
Declaration
Nullable<bool> IsStringsPreserved { get; set; }
Property Value
Type |
---|
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 |
---|
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 |
---|
IRange |
Item[String]
Gets cell Range. Read-only.
Declaration
IRange this[string name] { get; }
Parameters
Type | Name | Description |
---|---|---|
System.String | name |
Property Value
Type |
---|
IRange |
Item[String, Boolean]
Gets cell Range with R1C1Notation flag. Read-only.
Declaration
IRange this[string name, bool IsR1C1Notation] { get; }
Parameters
Type | Name | Description |
---|---|---|
System.String | name | |
System.Boolean | IsR1C1Notation |
Property Value
Type |
---|
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 |
---|
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 |
---|
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 |
---|
IRange |
Remarks
If the specified cell isn�t in a merged Range, this property gets NULL.
Examples
The following code illustrates how to access MergeArea
property.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Sample";
worksheet["D2"].Text = "text";
worksheet["C3"].Text = "in";
worksheet["D3"].Text = "cell";
//Merge
worksheet["C2:D3"].Merge();
//Check merge area
Console.Write(worksheet["C2"].MergeArea.AddressLocal);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//C2:D3
Number
Gets or sets number value that is contained by Range.
Declaration
double Number { get; set; }
Property Value
Type |
---|
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 |
---|
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 |
---|
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 |
---|
System.Int32 |
RowGroupLevel
Gets row group level. Read-only.
Declaration
int RowGroupLevel { get; }
Property Value
Type |
---|
System.Int32 |
Remarks
-1 - Not all rows in the Range have same group level. 0 - No grouping, 1 - 7 - Group level.
RowHeight
Gets or sets the height of all the rows in the range, measured in points.
Declaration
double RowHeight { get; set; }
Property Value
Type |
---|
System.Double |
Remarks
Gets Double.MinValue if the rows in the specified Range aren't all the same height. Maximum Row height can be 409 value, minimum is zero.
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 |
---|
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 |
---|
System.String |
ThreadedComment
Gets the threaded comment associated with the cell.
Declaration
IThreadedComment ThreadedComment { get; }
Property Value
Type | Description |
---|---|
IThreadedComment | The IThreadedComment object |
Examples
The following code illustrates how to access ThreadedComment
property of IRange
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Access threaded comment from IRange
IThreadedComment threadedComment = worksheet["C2"].ThreadedComment;
if(threadedComment != null)
string text = threadedComment.Text;
//Save and dispose
workbook.SaveAs("ThreadedComments.xlsx");
workbook.Close();
}
TimeSpan
Gets or sets time value of the Range.
Declaration
TimeSpan TimeSpan { get; set; }
Property Value
Type |
---|
System.TimeSpan |
Value
Gets or sets the value of the Range. Does not support FormulaArray value.
Declaration
string Value { get; set; }
Property Value
Type |
---|
System.String |
Remarks
Sets different data types values as a string, Value property parses the input string to determine its type which leads performance delay.
The only difference between the Value2 property and the Value property is that the Value2 property does not use the Currency and Date data types. Also, it does not support FormulaArray value.
Examples
The following code illustrates how to set and access Value
property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
IRange range= sheet.Range[3, 1];
range.Value = "1/1/2015";
range.NumberFormat = "dd-MMM-yyyy";
string displayText = range.DisplayText;
workbook.SaveAs("Value.xlsx");
workbook.Close();
excelEngine.Dispose();
Value2
Gets or sets the cell value.
Declaration
object Value2 { get; set; }
Property Value
Type |
---|
System.Object |
Remarks
Sets different data types values as a object.
Value2
first checks whether the specified object has the type known for it (DateTime, TimeSpan, Double, Int).
If yes, then it uses the corresponding typed properties (DateTime, TimeSpan, and Number).
Otherwise, it calls Value property with String data type.
The only difference between the Value2 property and the Value property is that the Value2 property does not use the Currency and Date data types. Also, it does not support FormulaArray value.
Examples
The following code illustrates how to set and access Value2
property of the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assigning Value2 property of the Range
sheet["A1"].Value2 = DateTime.Now;
sheet["A2"].Value2 = 45;
sheet["A3"].Value2 = false;
// Checking Range types.
bool isDateTime = sheet["A1"].HasDateTime;
bool isNumber = sheet["A2"].HasNumber;
bool isboolean = sheet["A3"].HasBoolean;
workbook.SaveAs("Value2.xlsx");
workbook.Close();
excelEngine.Dispose();
VerticalAlignment
Gets or sets the vertical alignment of the Range.
Declaration
ExcelVAlign VerticalAlignment { get; set; }
Property Value
Type |
---|
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 |
---|
IWorksheet |
WrapText
Gets or sets wrap text of the Range.
Declaration
bool WrapText { get; set; }
Property Value
Type |
---|
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. If the cell has threaded comment, returns null.
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();
}
AddThreadedComment(String, DateTime)
Creates a threaded comment for the current cell with the specified text.
Declaration
IThreadedComment AddThreadedComment(string text, DateTime creationTime)
Parameters
Type | Name | Description |
---|---|---|
System.String | text | Threaded comment text |
System.DateTime | creationTime | Optional. The creation time of the threaded comment |
Returns
Type | Description |
---|---|
IThreadedComment | Returns the created instance of IThreadedComment. If the cell already contains comment(note) then threaded comment cannot be inserted and returns null |
Remarks
The threaded comment created by mapping with author name from built-in document properties
Examples
The following code illustrates how to add threaded comment with text.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Add threaded comment with text
IThreadedComment threadedComment = worksheet["C2"].AddThreadedComment("sample text");
//Add threaded comment with text and creation time
DateTime date = new DateTime(2020, 11, 22, 2, 22, 23);
IThreadedComment threadedComment = worksheet["C1"].AddThreadedComment("Text", date);
//Save and dispose
workbook.SaveAs("ThreadedComments.xlsx");
workbook.Close();
}
AddThreadedComment(String, String, DateTime)
Creates a threaded comment for the current cell with the specified text and author.
Declaration
IThreadedComment AddThreadedComment(string text, string author, DateTime creationTime)
Parameters
Type | Name | Description |
---|---|---|
System.String | text | Threaded comment text |
System.String | author | The author of the threaded comment text |
System.DateTime | creationTime | Optional. The creation time of the threaded comment |
Returns
Type | Description |
---|---|
IThreadedComment | Returns the created instance of IThreadedComment. If the cell already contains comment(note) then threaded comment cannot be inserted and returns null |
Examples
The following code illustrates how to add threaded comment with text and author.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Add threaded comment with text and author
IThreadedComment threadedComment = worksheet["C2"].AddThreadedComment("sample text", "User");
//Add threaded comment with text, author and creation time
DateTime date = new DateTime(2020, 11, 22, 2, 22, 23);
IThreadedComment threadedComment = worksheet["C1"].AddThreadedComment("Text", "User", date);
//Save and dispose
workbook.SaveAs("ThreadedComments.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 |
---|
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 |
---|
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, 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. |
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();
}
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();
}
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, 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. |
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();
}
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();
}
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 |
---|
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 |
---|
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 |
---|
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 |
---|
System.Double |
Examples
The following code illustrates how to find the minumum value from numeric cells in the range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set value to cells
worksheet.Range["A1"].Value = "10";
worksheet.Range["A2"].Value = "20";
worksheet.Range["A3"].Value = "30";
//Returns minumum value from Numeric cells in the range.
double minimum = worksheet.Range["A1:A3"].Min(true);
workbook.SaveAs("Min.xlsx");
workbook.Close();
excelEngine.Dispose();
MoveTo(IRange)
Moves cells to the specified Range (without updating formulas).
Declaration
void MoveTo(IRange destination)
Parameters
Type | Name | Description |
---|---|---|
IRange | destination | Represents the destination Range to move. |
Examples
The following code illustrates how to move the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
// Moving a Range �A1� to �A5�.
IRange source = sheet.Range["A1"];
IRange destination = sheet.Range["A5"];
source.MoveTo(destination);
workbook.Version = ExcelVersion.Excel2013;
workbook.SaveAs("MoveRange.xlsx");
workbook.Close();
excelEngine.Dispose();
Offset(Int32, Int32)
Returns an IRange object that represents a range or cell that is offset from the specified range or cell.
Declaration
IRange Offset(int rowOffset, int columnOffset)
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | rowOffset | The number of rows (positive, negative, or 0 (zero)) by which the range is to be offset |
System.Int32 | columnOffset | The number of columns (positive, negative, or 0 (zero)) by which the range is to be offset |
Returns
Type | Description |
---|---|
IRange | Returns a range which is shifted by the specified number of rows and columns. |
Remarks
Use the Offset method to access the cell range of the same size as the current range which is shifted by the specified number of rows and columns.
Examples
using(ExcelEngine engine = new ExcelEngine())
{
IApplication application = engine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IRange range = worksheet.Range[5,6];
var offset = range.Offset(-3,-1);
var address2 = offset.AddressLocal;
workbook.SaveAs("output.xlsx");
}
Replace(String, DataColumn, Boolean)
Replaces the string with the specified datacolumn.
Declaration
void Replace(string oldValue, DataColumn newValues, bool isFieldNamesShown)
Parameters
Type | Name | Description |
---|---|---|
System.String | oldValue | The string to be replaced. |
System.Data.DataColumn | newValues | DataColumn with new data. |
System.Boolean | isFieldNamesShown | Indicates whether field name must be shown. |
Examples
The following code snippet illustrates how to replace the string value with data column.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "AB2";
//Create the data table
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Rows.Add(1);
System.Data.DataColumn dataColumn = table.Columns[0];
// Replace the value with data column.
sheet.Range["A1:A3"].Replace("AB2", dataColumn, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
Replace(String, DataTable, Boolean)
Replaces the string with the specified datatable.
Declaration
void Replace(string oldValue, DataTable newValues, bool isFieldNamesShown)
Parameters
Type | Name | Description |
---|---|---|
System.String | oldValue | The string to be replaced. |
System.Data.DataTable | newValues | Data table with new data. |
System.Boolean | isFieldNamesShown | Indicates whether field name must be shown. |
Examples
The following code snippet illustrates how to replace the string value with data table.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "AB2";
//Create the data table
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Rows.Add(1);
// Replace the value with data table.
sheet.Range["A1:A3"].Replace("AB2", table, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
Replace(String, DateTime)
Replaces the string with the specified DateTime value.
Declaration
void Replace(string oldValue, DateTime newValue)
Parameters
Type | Name | Description |
---|---|---|
System.String | oldValue | The string to be replaced. |
System.DateTime | newValue | The datetime value to replace all occurrences of oldValue. |
Examples
The following code illustrates how to replace the string value with datetime.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Find";
string oldValue = "Find";
DateTime dateTime = DateTime.Now;
sheet.Range["A1:A3"].Replace(oldValue,dateTime);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
Replace(String, Double)
Replaces the string with the specified double value.
Declaration
void Replace(string oldValue, double newValue)
Parameters
Type | Name | Description |
---|---|---|
System.String | oldValue | The string to be replaced. |
System.Double | newValue | The double value to replace all occurrences of oldValue. |
Examples
The following code snippet illustrates how to replace the string value with double.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Ten";
string oldValue = "Ten";
sheet.Range["A1:A3"].Replace(oldValue,10.0);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
Replace(String, Double[], Boolean)
Replaces the string with the specified array of double values.
Declaration
void Replace(string oldValue, double[] newValues, bool isVertical)
Parameters
Type | Name | Description |
---|---|---|
System.String | oldValue | The string to be replaced. |
System.Double[] | newValues | Array of new values. |
System.Boolean | isVertical | Indicates whether array should be inserted vertically. |
Examples
The following code snippet illustrates how to replace the string with array of double values.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Find";
string oldValue = "Find";
double[] newValues = { 1.00, 3.00 };
sheet.Range["A1:A3"].Replace(oldValue, newValues, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
Replace(String, Int32[], Boolean)
Replaces the string with the specified array of int values.
Declaration
void Replace(string oldValue, int[] newValues, bool isVertical)
Parameters
Type | Name | Description |
---|---|---|
System.String | oldValue | The string to be replaced. |
System.Int32[] | newValues | Array of new values. |
System.Boolean | isVertical | Indicates whether array should be inserted vertically. |
Examples
The following code snippet illustrates how to replace the string with array of int values.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Find";
string oldValue = "Find";
int[] newValues = { 1, 2 };
sheet.Range["A1:A3"].Replace(oldValue, newValues, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
Replace(String, String)
Replaces the string with the specified string value.
Declaration
void Replace(string oldValue, string newValue)
Parameters
Type | Name | Description |
---|---|---|
System.String | oldValue | The string to be replaced. |
System.String | newValue | The string to replace all occurrences of oldValue. |
Examples
The following code snippet illustrates how to replace the string with another string.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Find";
string oldValue = "Find";
string newValue = "NewValue";
sheet.Range["A1:A3"].Replace(oldValue, newValue);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
Replace(String, String, ExcelFindOptions)
Replaces the string with the specified string value based on the given ExcelFindOptions.
Declaration
void Replace(string oldValue, string newValue, ExcelFindOptions findOptions)
Parameters
Type | Name | Description |
---|---|---|
System.String | oldValue | The string to be replaced. |
System.String | newValue | The string to replace all occurrences of oldValue. |
ExcelFindOptions | findOptions | Specifies the find options for the oldValue. |
Examples
The following code snippet illustrates how to replace the string with another string.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1:A3"].Value = "Find";
string oldValue = "Find";
string newValue = "NewValue";
sheet.Range["A1:A3"].Replace(oldValue, newValue, ExcelFindOptions.MatchCase);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
Replace(String, String[], Boolean)
Replaces the string with the specified array of string values.
Declaration
void Replace(string oldValue, string[] newValues, bool isVertical)
Parameters
Type | Name | Description |
---|---|---|
System.String | oldValue | The string to be replaced. |
System.String[] | newValues | Array of new values. |
System.Boolean | isVertical | Indicates whether array should be inserted vertically. |
Examples
The following code snippet illustrates how to replace the string with array of string values.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Find";
string oldValue = "Find";
string[] newValues = { "X values", "Y values" };
sheet.Range["A1:A3"].Replace(oldValue, newValues, true);
string fileName = "Replace.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
Resize(Int32, Int32)
Resizes the specified range a IRange object that represents the resized range.
Declaration
IRange Resize(int rowSize, int columnSize)
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | rowSize | The number of rows in the new range. |
System.Int32 | columnSize | The number of columns in the new range. |
Returns
Type | Description |
---|---|
IRange | Returns the resized range. |
Remarks
Use the Resize method to change the size of range by specified rows and columns from the current range.
Examples
using(ExcelEngine engine = new ExcelEngine())
{
IApplication application = engine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IRange range = worksheet.Range[5,6];
var resize = range.Resize(1000,2000);
var address = resize.AddressLocal;
workbook.SaveAs("output.xlsx");
}
SubTotal(Int32, ConsolidationFunction, Int32[])
Creates subtotals for the Range.
Declaration
void SubTotal(int groupBy, ConsolidationFunction function, int[] totalList)
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | groupBy | Column index based on which grouping should be done. |
ConsolidationFunction | function | Represents a consolidation function to be applied. |
System.Int32[] | totalList | List of column indexes on which subtotal is calculated. |
Examples
The following code illustrates how to subtotal the Range.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Set the range for subtotaling
IRange range = worksheet.Range["C3:G12"];
//Perform subtotals for the Range with every change in first column
//and subtotals to be included for specified list of columns
range.SubTotal(0, ConsolidationFunction.Sum, new int[] { 2, 3, 4 });
workbook.SaveAs("Subtotal.xlsx");
workbook.Close();
excelEngine.Dispose();
SubTotal(Int32, ConsolidationFunction, Int32[], Boolean, Boolean, Boolean)
Creates subtotals for the Range with the specified formatting flags.
Declaration
void SubTotal(int groupBy, ConsolidationFunction function, int[] totalList, bool replace, bool pageBreaks, bool summaryBelowData)
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | groupBy | Column index based on which grouping should be done |
ConsolidationFunction | function | Represents a consolidation function to be applied. |
System.Int32[] | totalList | List of column indexes on which subtotal is calculated. |
System.Boolean | replace | Indicates whether existing SubTotal must be replaced. |
System.Boolean | pageBreaks | Indicates whether page break must be inserted. |
System.Boolean | summaryBelowData | Indicates whether SummaryBelowData to be shown |
Examples
The following code illustrates how to subtotal the Range with formats.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Set the range for subtotaling
IRange range = worksheet.Range["C3:G12"];
//Perform subtotals for the range with every change in first column
//and subtotals to be included for specified list of columns
range.SubTotal(0, ConsolidationFunction.Sum, new int[] { 2, 3, 4 }, false, true, true);
workbook.SaveAs("Subtotal.xlsx");
workbook.Close();
excelEngine.Dispose();
SubTotal(Int32[], ConsolidationFunction, Int32[], Boolean, Boolean, Boolean)
Creates subtotals for the Range with multiple group by columns and the specified formatting flags.
Declaration
void SubTotal(int[] groupBy, ConsolidationFunction function, int[] totalList, bool replace, bool pageBreaks, bool summaryBelowData)
Parameters
Type | Name | Description |
---|---|---|
System.Int32[] | groupBy | Column indexes based on which grouping is done. Given in the order of subtotal levels |
ConsolidationFunction | function | Represents a consolidation function to be applied. |
System.Int32[] | totalList | List of columns on which subtotal is calculated |
System.Boolean | replace | Replaces Existing SubTotal |
System.Boolean | pageBreaks | Inserts PageBreaks |
System.Boolean | summaryBelowData | SummaryBelowData to be shown |
Examples
The following code illustrates how to subtotal the Range with formats.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Set the range for subtotaling
IRange range = worksheet.Range["C3:G12"];
//Perform subtotals for the range with every change in first column
//and subtotals to be included for specified list of columns
range.SubTotal( new int[] { 1, 2 }, ConsolidationFunction.Sum, new int[] { 2, 3, 4 }, false, true, true);
workbook.SaveAs("Subtotal.xlsx");
workbook.Close();
excelEngine.Dispose();
Sum()
Returns sum of numeric cells in the range.
Declaration
double Sum()
Returns
Type |
---|
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 |
---|
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();
}