Class ExcelRangeBase

public class ExcelRangeBase : ExcelAddress, IExcelCell, IEnumerable<ExcelRangeBase>, IEnumerable, IEnumerator<ExcelRangeBase>, IEnumerator, IDisposable

Fields

_rtc

The richtext collection

Declaration

protected ExcelRichTextCollection _rtc

Field Value

Type	Description
ExcelRichTextCollection

_worksheet

Reference to the worksheet

Declaration

protected ExcelWorksheet _worksheet

Field Value

Type	Description
ExcelWorksheet

Properties

AutoFilter

Set an autofilter for the range

Declaration

public bool AutoFilter { get; set; }

Property Value

Type	Description
System.Boolean

Comment

Returns the comment object of the first cell in the range

Declaration

public ExcelComment Comment { get; }

Property Value

Type	Description
ExcelComment

ConditionalFormatting

Conditional Formatting for this range.

Declaration

public IRangeConditionalFormatting ConditionalFormatting { get; }

Property Value

Type	Description
IRangeConditionalFormatting

Current

The current range when enumerating

Declaration

public ExcelRangeBase Current { get; }

Property Value

Type	Description
ExcelRangeBase

DataValidation

Data validation for this range.

Declaration

public IRangeDataValidation DataValidation { get; }

Property Value

Type	Description
IRangeDataValidation

Formula

Gets or sets a formula for a range.

Declaration

public string Formula { get; set; }

Property Value

Type	Description
System.String

FormulaR1C1

Gets or Set a formula in R1C1 format.

Declaration

public string FormulaR1C1 { get; set; }

Property Value

Type	Description
System.String

FullAddress

Address including sheet name

Declaration

public string FullAddress { get; }

Property Value

Type	Description
System.String

FullAddressAbsolute

Address including sheetname

Declaration

public string FullAddressAbsolute { get; }

Property Value

Type	Description
System.String

Hyperlink

Set the Hyperlink property for a range of cells

Declaration

public Uri Hyperlink { get; set; }

Property Value

Type	Description
System.Uri

IsArrayFormula

Is the range a part of an Arrayformula

Declaration

public bool IsArrayFormula { get; }

Property Value

Type	Description
System.Boolean

IsRichText

If the value is in richtext format.

Declaration

public bool IsRichText { get; set; }

Property Value

Type	Description
System.Boolean

Merge

If the cells in the range are merged.

Declaration

public bool Merge { get; set; }

Property Value

Type	Description
System.Boolean

RichText

The cell value is rich text formatted. The RichText-property only apply to the left-top cell of the range.

Declaration

public ExcelRichTextCollection RichText { get; }

Property Value

Type	Description
ExcelRichTextCollection

Style

The style object for the range.

Declaration

public ExcelStyle Style { get; }

Property Value

Type	Description
ExcelStyle

StyleID

The style ID. It is not recomended to use this one. Use Named styles as an alternative. If you do, make sure that you use the Style.UpdateXml() method to update any new styles added to the workbook.

Declaration

public int StyleID { get; set; }

Property Value

Type	Description
System.Int32

StyleName

The named style

Declaration

public string StyleName { get; set; }

Property Value

Type	Description
System.String

Text

Returns the formatted value.

Declaration

public string Text { get; }

Property Value

Type	Description
System.String

ThreadedComment

Returns the threaded comment object of the first cell in the range

Declaration

public ExcelThreadedCommentThread ThreadedComment { get; }

Property Value

Type	Description
ExcelThreadedCommentThread

Value

Set the range to a specific value

Declaration

public object Value { get; set; }

Property Value

Type	Description
System.Object

Worksheet

WorkSheet object

Declaration

public ExcelWorksheet Worksheet { get; }

Property Value

Type	Description
ExcelWorksheet

Methods

AddComment(String, String)

Adds a new comment for the range. If this range contains more than one cell, the top left comment is returned by the method.

Declaration

public ExcelComment AddComment(string Text, string Author)

Parameters

Type	Name	Description
System.String	Text
System.String	Author

Returns

Type	Description
ExcelComment	A reference comment of the top left cell

AddThreadedComment()

Adds a new threaded comment for the range. If this range contains more than one cell, the top left comment is returned by the method.

Declaration

public ExcelThreadedCommentThread AddThreadedComment()

Returns

Type	Description
ExcelThreadedCommentThread	A reference comment of the top left cell

AutoFitColumns()

Set the column width from the content of the range. Columns outside of the worksheets dimension are ignored. The minimum width is the value of the ExcelWorksheet.defaultColumnWidth property.

Declaration

public void AutoFitColumns()

Remarks

Cells containing formulas must be calculated before autofit is called. Wrapped and merged cells are also ignored.

AutoFitColumns(Double)

Set the column width from the content of the range. Columns outside of the worksheets dimension are ignored.

Declaration

public void AutoFitColumns(double MinimumWidth)

Parameters

Type	Name	Description
System.Double	MinimumWidth	Minimum column width

Remarks

This method will not work if you run in an environment that does not support GDI. Cells containing formulas are ignored if no calculation is made. Wrapped and merged cells are also ignored.

AutoFitColumns(Double, Double)

Set the column width from the content of the range. Columns outside of the worksheets dimension are ignored.

Declaration

public void AutoFitColumns(double MinimumWidth, double MaximumWidth)

Parameters

Type	Name	Description
System.Double	MinimumWidth	Minimum column width
System.Double	MaximumWidth	Maximum column width

Remarks

This method will not work if you run in an environment that does not support GDI. Cells containing formulas are ignored if no calculation is made. Wrapped and merged cells are also ignored.

ChangeAddress()

On change address handler

Declaration

protected override void ChangeAddress()

Overrides

ExcelAddressBase.ChangeAddress()

Clear()

Clear all cells

Declaration

public void Clear()

ClearFormulas()

Removes all formulas within the range, but keeps the calculated values.

Declaration

public void ClearFormulas()

ClearFormulaValues()

Removes all values of cells with formulas, but keeps the formulas.

Declaration

public void ClearFormulaValues()

Copy(ExcelRangeBase)

Copies the range of cells to an other range

Declaration

public void Copy(ExcelRangeBase Destination)

Parameters

Type	Name	Description
ExcelRangeBase	Destination	The start cell where the range will be copied.

Copy(ExcelRangeBase, Nullable<ExcelRangeCopyOptionFlags>)

Copies the range of cells to an other range

Declaration

public void Copy(ExcelRangeBase Destination, ExcelRangeCopyOptionFlags? excelRangeCopyOptionFlags)

Parameters

Type	Name	Description
ExcelRangeBase	Destination	The start cell where the range will be copied.
System.Nullable<ExcelRangeCopyOptionFlags>	excelRangeCopyOptionFlags	Cell parts that will not be copied. If Formulas are specified, the formulas will NOT be copied.

CreateArrayFormula(String)

Creates an array-formula.

Declaration

public void CreateArrayFormula(string ArrayFormula)

Parameters

Type	Name	Description
System.String	ArrayFormula	The formula

Delete(eShiftTypeDelete)

Delete the range from the worksheet and shift affected cells in the selected direction.

Declaration

public void Delete(eShiftTypeDelete shift)

Parameters

Type	Name	Description
eShiftTypeDelete	shift	The direction that the cells will shift.

Dispose()

Disposes the object

Declaration

public void Dispose()

GetEnumerator()

Gets the enumerator for the collection

Declaration

public IEnumerator<ExcelRangeBase> GetEnumerator()

Returns

Type	Description
System.Collections.Generic.IEnumerator<ExcelRangeBase>	The enumerator

GetValue<T>()

Convert cell value to desired type, including nullable structs. When converting blank string to nullable struct (e.g. ' ' to int?) null is returned. When attempted conversion fails exception is passed through.

Declaration

public T GetValue<T>()

Returns

Type	Description
T	The Value converted to `T`.

Type Parameters

Name	Description
T	The type to convert to.

Remarks

If Value is string, parsing is performed for output types of DateTime and TimeSpan, which if fails throws System.FormatException. Another special case for output types of DateTime and TimeSpan is when input is double, in which case System.DateTime.FromOADate(System.Double) is used for conversion. This special case does not work through other types convertible to double (e.g. integer or string with number). In all other cases 'direct' conversion System.Convert.ChangeType(System.Object,System.Type) is performed.

Exceptions

Type	Condition
System.FormatException	Value is string and its format is invalid for conversion (parsing fails)
System.InvalidCastException	Value is not string and direct conversion fails

Insert(eShiftTypeInsert)

Insert cells into the worksheet and shift the cells to the selected direction.

Declaration

public void Insert(eShiftTypeInsert shift)

Parameters

Type	Name	Description
eShiftTypeInsert	shift	The direction that the cells will shift.

LoadFromArrays(IEnumerable<Object[]>)

Loads data from the collection of arrays of objects into the range, starting from the top-left cell.

Declaration

public ExcelRangeBase LoadFromArrays(IEnumerable<object[]> Data)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<System.Object[]>	Data	The data.

Returns

Type	Description
ExcelRangeBase

LoadFromCollection<T>(IEnumerable<T>)

Load a collection into a the worksheet starting from the top left row of the range.

Declaration

public ExcelRangeBase LoadFromCollection<T>(IEnumerable<T> Collection)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<T>	Collection	The collection to load

Returns

Type	Description
ExcelRangeBase	The filled range

Type Parameters

Name	Description
T	The datatype in the collection

LoadFromCollection<T>(IEnumerable<T>, Action<LoadFromCollectionParams>)

Load a collection into the worksheet starting from the top left row of the range.

Declaration

public ExcelRangeBase LoadFromCollection<T>(IEnumerable<T> collection, Action<LoadFromCollectionParams> paramsConfig)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<T>	collection	The collection to load
System.Action<LoadFromCollectionParams>	paramsConfig	System.Action<T> to provide parameters to the function

Returns

Type	Description
ExcelRangeBase	The filled range

Type Parameters

Name	Description
T	The datatype in the collection

Examples

sheet.Cells["C1"].LoadFromCollection(items, c =>
{
    c.PrintHeaders = true;
    c.TableStyle = TableStyles.Dark1;
});

LoadFromCollection<T>(IEnumerable<T>, Boolean)

Load a collection of T into the worksheet starting from the top left row of the range. Default option will load all public instance properties of T

Declaration

public ExcelRangeBase LoadFromCollection<T>(IEnumerable<T> Collection, bool PrintHeaders)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<T>	Collection	The collection to load
System.Boolean	PrintHeaders	Print the property names on the first row. If the property is decorated with a System.ComponentModel.DisplayNameAttribute or a System.ComponentModel.DescriptionAttribute that attribute will be used instead of the reflected member name.

Returns

Type	Description
ExcelRangeBase	The filled range

Type Parameters

Name	Description
T	The datatype in the collection

LoadFromCollection<T>(IEnumerable<T>, Boolean, TableStyles)

Load a collection of T into the worksheet starting from the top left row of the range. Default option will load all public instance properties of T

Declaration

public ExcelRangeBase LoadFromCollection<T>(IEnumerable<T> Collection, bool PrintHeaders, TableStyles TableStyle)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<T>	Collection	The collection to load
System.Boolean	PrintHeaders	Print the property names on the first row. If the property is decorated with a System.ComponentModel.DisplayNameAttribute or a System.ComponentModel.DescriptionAttribute that attribute will be used instead of the reflected member name.
TableStyles	TableStyle	Will create a table with this style. If set to TableStyles.None no table will be created

Returns

Type	Description
ExcelRangeBase	The filled range

Type Parameters

Name	Description
T	The datatype in the collection

LoadFromCollection<T>(IEnumerable<T>, Boolean, TableStyles, BindingFlags, MemberInfo[])

Load a collection into the worksheet starting from the top left row of the range.

Declaration

public ExcelRangeBase LoadFromCollection<T>(IEnumerable<T> Collection, bool PrintHeaders, TableStyles TableStyle, BindingFlags memberFlags, MemberInfo[] Members)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<T>	Collection	The collection to load
System.Boolean	PrintHeaders	Print the property names on the first row. Any underscore in the property name will be converted to a space. If the property is decorated with a System.ComponentModel.DisplayNameAttribute or a System.ComponentModel.DescriptionAttribute that attribute will be used instead of the reflected member name.
TableStyles	TableStyle	Will create a table with this style. If set to TableStyles.None no table will be created
System.Reflection.BindingFlags	memberFlags	Property flags to use
System.Reflection.MemberInfo[]	Members	The properties to output. Must be of type T

Returns

Type	Description
ExcelRangeBase	The filled range

Type Parameters

Name	Description
T	The datatype in the collection

LoadFromDataReader(IDataReader, Boolean)

Load the data from the datareader starting from the top left cell of the range

Declaration

public ExcelRangeBase LoadFromDataReader(IDataReader Reader, bool PrintHeaders)

Parameters

Type	Name	Description
System.Data.IDataReader	Reader	The datareader to load from
System.Boolean	PrintHeaders	Print the caption property (if set) or the columnname property if not, on first row

Returns

Type	Description
ExcelRangeBase	The filled range

LoadFromDataReader(IDataReader, Boolean, String, TableStyles)

Load the data from the datareader starting from the top left cell of the range

Declaration

public ExcelRangeBase LoadFromDataReader(IDataReader Reader, bool PrintHeaders, string TableName, TableStyles TableStyle = TableStyles.None)

Parameters

Type	Name	Description
System.Data.IDataReader	Reader	The datareader to loadfrom
System.Boolean	PrintHeaders	Print the column caption property (if set) or the columnname property if not, on first row
System.String	TableName	The name of the table
TableStyles	TableStyle	The table style to apply to the data

Returns

Type	Description
ExcelRangeBase	The filled range

LoadFromDataReaderAsync(DbDataReader, Boolean)

Load the data from the datareader starting from the top left cell of the range

Declaration

public Task<ExcelRangeBase> LoadFromDataReaderAsync(DbDataReader Reader, bool PrintHeaders)

Parameters

Type	Name	Description
System.Data.Common.DbDataReader	Reader	The datareader to load from
System.Boolean	PrintHeaders	Print the caption property (if set) or the columnname property if not, on first row

Returns

Type	Description
System.Threading.Tasks.Task<ExcelRangeBase>	The filled range

LoadFromDataReaderAsync(DbDataReader, Boolean, String, TableStyles, Nullable<CancellationToken>)

Load the data from the datareader starting from the top left cell of the range

Declaration

public Task<ExcelRangeBase> LoadFromDataReaderAsync(DbDataReader Reader, bool PrintHeaders, string TableName, TableStyles TableStyle = TableStyles.None, CancellationToken? cancellationToken = default(CancellationToken? ))

Parameters

Type	Name	Description
System.Data.Common.DbDataReader	Reader	The datareader to loadfrom
System.Boolean	PrintHeaders	Print the column caption property (if set) or the columnname property if not, on first row
System.String	TableName	The name of the table
TableStyles	TableStyle	The table style to apply to the data
System.Nullable<System.Threading.CancellationToken>	cancellationToken	The cancellation token to use

Returns

Type	Description
System.Threading.Tasks.Task<ExcelRangeBase>	The filled range

LoadFromDataReaderAsync(DbDataReader, Boolean, CancellationToken)

Load the data from the datareader starting from the top left cell of the range

Declaration

public Task<ExcelRangeBase> LoadFromDataReaderAsync(DbDataReader Reader, bool PrintHeaders, CancellationToken cancellationToken)

Parameters

Type	Name	Description
System.Data.Common.DbDataReader	Reader	The datareader to load from
System.Boolean	PrintHeaders	Print the caption property (if set) or the columnname property if not, on first row
System.Threading.CancellationToken	cancellationToken	The cancellation token to use

Returns

Type	Description
System.Threading.Tasks.Task<ExcelRangeBase>	The filled range

LoadFromDataTable(DataTable)

Load the data from the datatable starting from the top left cell of the range

Declaration

public ExcelRangeBase LoadFromDataTable(DataTable table)

Parameters

Type	Name	Description
System.Data.DataTable	table	The datatable to load

Returns

Type	Description
ExcelRangeBase	The filled range

LoadFromDataTable(DataTable, Action<LoadFromDataTableParams>)

Load the data from the System.Data.DataTable starting from the top left cell of the range

Declaration

public ExcelRangeBase LoadFromDataTable(DataTable table, Action<LoadFromDataTableParams> paramsConfig)

Parameters

Type	Name	Description
System.Data.DataTable	table
System.Action<LoadFromDataTableParams>	paramsConfig	System.Action<T> to provide parameters to the function

Returns

Type	Description
ExcelRangeBase	The filled range

Examples

sheet.Cells["C1"].LoadFromDataTable(dataTable, c =>
{
    c.PrintHeaders = true;
    c.TableStyle = TableStyles.Dark1;
});

LoadFromDataTable(DataTable, Boolean)

Load the data from the datatable starting from the top left cell of the range

Declaration

public ExcelRangeBase LoadFromDataTable(DataTable Table, bool PrintHeaders)

Parameters

Type	Name	Description
System.Data.DataTable	Table	The datatable to load
System.Boolean	PrintHeaders	Print the caption property (if set) or the columnname property if not, on first row

Returns

Type	Description
ExcelRangeBase	The filled range

LoadFromDataTable(DataTable, Boolean, TableStyles)

Load the data from the datatable starting from the top left cell of the range

Declaration

public ExcelRangeBase LoadFromDataTable(DataTable Table, bool PrintHeaders, TableStyles TableStyle)

Parameters

Type	Name	Description
System.Data.DataTable	Table	The datatable to load
System.Boolean	PrintHeaders	Print the column caption property (if set) or the columnname property if not, on first row
TableStyles	TableStyle	The table style to apply to the data

Returns

Type	Description
ExcelRangeBase	The filled range

LoadFromDictionaries(IEnumerable<IDictionary<String, Object>>)

Load a collection of dictionaries (or dynamic/ExpandoObjects) into the worksheet starting from the top left row of the range. These dictionaries should have the same set of keys.

Declaration

public ExcelRangeBase LoadFromDictionaries(IEnumerable<IDictionary<string, object>> items)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<System.Collections.Generic.IDictionary<System.String, System.Object>>	items	A list of dictionaries/>

Returns

Type	Description
ExcelRangeBase	The filled range

Examples

 var items = new List<IDictionary<string, object>>()
   {
       new Dictionary<string, object>()
       { 
           { "Id", 1 },
           { "Name", "TestName 1" }
       },
       new Dictionary<string, object>()
       {
           { "Id", 2 },
           { "Name", "TestName 2" }
       }
   };
   using(var package = new ExcelPackage())
   {
       var sheet = package.Workbook.Worksheets.Add("test");
       var r = sheet.Cells["A1"].LoadFromDictionaries(items);
   }

LoadFromDictionaries(IEnumerable<IDictionary<String, Object>>, Action<LoadFromDictionariesParams>)

Load a collection of dictionaries (or dynamic/ExpandoObjects) into the worksheet starting from the top left row of the range. These dictionaries should have the same set of keys.

Declaration

public ExcelRangeBase LoadFromDictionaries(IEnumerable<IDictionary<string, object>> items, Action<LoadFromDictionariesParams> paramsConfig)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<System.Collections.Generic.IDictionary<System.String, System.Object>>	items	A list of dictionaries/ExpandoObjects
System.Action<LoadFromDictionariesParams>	paramsConfig	System.Action<T> to provide parameters to the function

Returns

Type	Description
ExcelRangeBase

Examples

sheet.Cells["C1"].LoadFromDictionaries(items, c => { c.PrintHeaders = true; c.TableStyle = TableStyles.Dark1; });

LoadFromDictionaries(IEnumerable<IDictionary<String, Object>>, Boolean)

Load a collection of dictionaries (or dynamic/ExpandoObjects) into the worksheet starting from the top left row of the range. These dictionaries should have the same set of keys.

Declaration

public ExcelRangeBase LoadFromDictionaries(IEnumerable<IDictionary<string, object>> items, bool printHeaders)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<System.Collections.Generic.IDictionary<System.String, System.Object>>	items	A list of dictionaries/>
System.Boolean	printHeaders	If true the key names from the first instance will be used as headers

Returns

Type	Description
ExcelRangeBase	The filled range

Examples

 var items = new List<IDictionary<string, object>>()
   {
       new Dictionary<string, object>()
       { 
           { "Id", 1 },
           { "Name", "TestName 1" }
       },
       new Dictionary<string, object>()
       {
           { "Id", 2 },
           { "Name", "TestName 2" }
       }
   };
   using(var package = new ExcelPackage())
   {
       var sheet = package.Workbook.Worksheets.Add("test");
       var r = sheet.Cells["A1"].LoadFromDictionaries(items, true);
   }

LoadFromDictionaries(IEnumerable<IDictionary<String, Object>>, Boolean, TableStyles)

Load a collection of dictionaries (or dynamic/ExpandoObjects) into the worksheet starting from the top left row of the range. These dictionaries should have the same set of keys.

Declaration

public ExcelRangeBase LoadFromDictionaries(IEnumerable<IDictionary<string, object>> items, bool printHeaders, TableStyles tableStyle)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<System.Collections.Generic.IDictionary<System.String, System.Object>>	items	A list of dictionaries/>
System.Boolean	printHeaders	If true the key names from the first instance will be used as headers
TableStyles	tableStyle	Will create a table with this style. If set to TableStyles.None no table will be created

Returns

Type	Description
ExcelRangeBase	The filled range

Examples

 var items = new List<IDictionary<string, object>>()
   {
       new Dictionary<string, object>()
       { 
           { "Id", 1 },
           { "Name", "TestName 1" }
       },
       new Dictionary<string, object>()
       {
           { "Id", 2 },
           { "Name", "TestName 2" }
       }
   };
   using(var package = new ExcelPackage())
   {
       var sheet = package.Workbook.Worksheets.Add("test");
       var r = sheet.Cells["A1"].LoadFromDictionaries(items, true, TableStyles.None);
   }

LoadFromDictionaries(IEnumerable<IDictionary<String, Object>>, Boolean, TableStyles, IEnumerable<String>)

Load a collection of dictionaries (or dynamic/ExpandoObjects) into the worksheet starting from the top left row of the range. These dictionaries should have the same set of keys.

Declaration

public ExcelRangeBase LoadFromDictionaries(IEnumerable<IDictionary<string, object>> items, bool printHeaders, TableStyles tableStyle, IEnumerable<string> keys)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<System.Collections.Generic.IDictionary<System.String, System.Object>>	items	A list of dictionaries
System.Boolean	printHeaders	If true the key names from the first instance will be used as headers
TableStyles	tableStyle	Will create a table with this style. If set to TableStyles.None no table will be created
System.Collections.Generic.IEnumerable<System.String>	keys	Keys that should be used, keys omitted will not be included

Returns

Type	Description
ExcelRangeBase	The filled range

Examples

 var items = new List<IDictionary<string, object>>()
   {
       new Dictionary<string, object>()
       { 
           { "Id", 1 },
           { "Name", "TestName 1" }
       },
       new Dictionary<string, object>()
       {
           { "Id", 2 },
           { "Name", "TestName 2" }
       }
   };
   using(var package = new ExcelPackage())
   {
       var sheet = package.Workbook.Worksheets.Add("test");
       var r = sheet.Cells["A1"].LoadFromDictionaries(items, true, TableStyles.None, null);
   }

LoadFromText(FileInfo)

Loads a CSV file into a range starting from the top left cell.

Declaration

public ExcelRangeBase LoadFromText(FileInfo TextFile)

Parameters

Type	Name	Description
System.IO.FileInfo	TextFile	The Textfile

Returns

Type	Description
ExcelRangeBase

LoadFromText(FileInfo, ExcelTextFormat)

Loads a CSV file into a range starting from the top left cell.

Declaration

public ExcelRangeBase LoadFromText(FileInfo TextFile, ExcelTextFormat Format)

Parameters

Type	Name	Description
System.IO.FileInfo	TextFile	The Textfile
ExcelTextFormat	Format	Information how to load the text

Returns

Type	Description
ExcelRangeBase

LoadFromText(FileInfo, ExcelTextFormat, TableStyles, Boolean)

Loads a CSV file into a range starting from the top left cell.

Declaration

public ExcelRangeBase LoadFromText(FileInfo TextFile, ExcelTextFormat Format, TableStyles TableStyle, bool FirstRowIsHeader)

Parameters

Type	Name	Description
System.IO.FileInfo	TextFile	The Textfile
ExcelTextFormat	Format	Information how to load the text
TableStyles	TableStyle	Create a table with this style
System.Boolean	FirstRowIsHeader	Use the first row as header

Returns

Type	Description
ExcelRangeBase

LoadFromText(String)

Loads a CSV text into a range starting from the top left cell. Default settings is Comma separation

Declaration

public ExcelRangeBase LoadFromText(string Text)

Parameters

Type	Name	Description
System.String	Text	The Text

Returns

Type	Description
ExcelRangeBase	The range containing the data

LoadFromText(String, ExcelTextFormat)

Loads a CSV text into a range starting from the top left cell.

Declaration

public ExcelRangeBase LoadFromText(string Text, ExcelTextFormat Format)

Parameters

Type	Name	Description
System.String	Text	The Text
ExcelTextFormat	Format	Information how to load the text

Returns

Type	Description
ExcelRangeBase	The range containing the data

LoadFromText(String, ExcelTextFormat, TableStyles, Boolean)

Loads a CSV text into a range starting from the top left cell.

Declaration

public ExcelRangeBase LoadFromText(string Text, ExcelTextFormat Format, TableStyles TableStyle, bool FirstRowIsHeader)

Parameters

Type	Name	Description
System.String	Text	The Text
ExcelTextFormat	Format	Information how to load the text
TableStyles	TableStyle	Create a table with this style
System.Boolean	FirstRowIsHeader	Use the first row as header

Returns

Type	Description
ExcelRangeBase

LoadFromTextAsync(FileInfo)

Loads a CSV file into a range starting from the top left cell.

Declaration

public Task<ExcelRangeBase> LoadFromTextAsync(FileInfo TextFile)

Parameters

Type	Name	Description
System.IO.FileInfo	TextFile	The Textfile

Returns

Type	Description
System.Threading.Tasks.Task<ExcelRangeBase>

LoadFromTextAsync(FileInfo, ExcelTextFormat)

Loads a CSV file into a range starting from the top left cell.

Declaration

public Task<ExcelRangeBase> LoadFromTextAsync(FileInfo TextFile, ExcelTextFormat Format)

Parameters

Type	Name	Description
System.IO.FileInfo	TextFile	The Textfile
ExcelTextFormat	Format	Information how to load the text

Returns

Type	Description
System.Threading.Tasks.Task<ExcelRangeBase>

LoadFromTextAsync(FileInfo, ExcelTextFormat, TableStyles, Boolean)

Loads a CSV file into a range starting from the top left cell.

Declaration

public Task<ExcelRangeBase> LoadFromTextAsync(FileInfo TextFile, ExcelTextFormat Format, TableStyles TableStyle, bool FirstRowIsHeader)

Parameters

Type	Name	Description
System.IO.FileInfo	TextFile	The Textfile
ExcelTextFormat	Format	Information how to load the text
TableStyles	TableStyle	Create a table with this style
System.Boolean	FirstRowIsHeader	Use the first row as header

Returns

Type	Description
System.Threading.Tasks.Task<ExcelRangeBase>

MoveNext()

Iterate to the next cell

Declaration

public bool MoveNext()

Returns

Type	Description
System.Boolean

Offset(Int32, Int32)

Get a range with an offset from the top left cell. The new range has the same dimensions as the current range

Declaration

public ExcelRangeBase Offset(int RowOffset, int ColumnOffset)

Parameters

Type	Name	Description
System.Int32	RowOffset	Row Offset
System.Int32	ColumnOffset	Column Offset

Returns

Type	Description
ExcelRangeBase

Offset(Int32, Int32, Int32, Int32)

Get a range with an offset from the top left cell.

Declaration

public ExcelRangeBase Offset(int RowOffset, int ColumnOffset, int NumberOfRows, int NumberOfColumns)

Parameters

Type	Name	Description
System.Int32	RowOffset	Row Offset
System.Int32	ColumnOffset	Column Offset
System.Int32	NumberOfRows	Number of rows. Minimum 1
System.Int32	NumberOfColumns	Number of colums. Minimum 1

Returns

Type	Description
ExcelRangeBase

Reset()

Reset the enumerator

Declaration

public void Reset()

SaveToText(FileInfo, ExcelOutputTextFormat)

Converts a range to text in CSV format. Invariant culture is used by default.

Declaration

public void SaveToText(FileInfo file, ExcelOutputTextFormat Format)

Parameters

Type	Name	Description
System.IO.FileInfo	file	The file to write to
ExcelOutputTextFormat	Format	Information how to create the csv text

SaveToText(Stream, ExcelOutputTextFormat)

Converts a range to text in CSV format. Invariant culture is used by default.

Declaration

public void SaveToText(Stream stream, ExcelOutputTextFormat Format)

Parameters

Type	Name	Description
System.IO.Stream	stream	The strem to write to
ExcelOutputTextFormat	Format	Information how to create the csv text

SaveToTextAsync(FileInfo, ExcelOutputTextFormat)

Converts a range to text in CSV format. Invariant culture is used by default.

Declaration

public Task SaveToTextAsync(FileInfo file, ExcelOutputTextFormat Format)

Parameters

Type	Name	Description
System.IO.FileInfo	file	The file to write to
ExcelOutputTextFormat	Format	Information how to create the csv text

Returns

Type	Description
System.Threading.Tasks.Task

SaveToTextAsync(Stream, ExcelOutputTextFormat)

Converts a range to text in CSV format. Invariant culture is used by default.

Declaration

public Task SaveToTextAsync(Stream stream, ExcelOutputTextFormat Format)

Parameters

Type	Name	Description
System.IO.Stream	stream	The strem to write to
ExcelOutputTextFormat	Format	Information how to create the csv text

Returns

Type	Description
System.Threading.Tasks.Task

SetHyperlink(ExcelHyperLink)

Sets the Hyperlink property using the ExcelHyperLink class.

Declaration

public void SetHyperlink(ExcelHyperLink uri)

Parameters

Type	Name	Description
ExcelHyperLink	uri	The ExcelHyperLink uri to set

SetHyperlink(ExcelRange, String)

Sets the Hyperlink property to an url within the workbook.

Declaration

public void SetHyperlink(ExcelRange range, string display)

Parameters

Type	Name	Description
ExcelRange	range	A reference within the same workbook
System.String	display	The displayed text in the cell

SetHyperlink(Uri)

Sets the hyperlink property

Declaration

public void SetHyperlink(Uri uri)

Parameters

Type	Name	Description
System.Uri	uri	The URI to set

Sort()

Sort the range by value of the first column, Ascending.

Declaration

public void Sort()

Sort(RangeSortOptions)

Sort the range by value. Use RangeSortOptions.Create() to create an instance of the sort options, then use the SortBy or SortLeftToRightBy properties to build up your sort parameters.

Declaration

public void Sort(RangeSortOptions options)

Parameters

Type	Name	Description
RangeSortOptions	options	RangeSortOptions for the sort

Examples

var options = RangeSortOptions.Create();
var builder = options.SortBy.Column(0);
builder.ThenSortBy.Column(2).UsingCustomList("S", "M", "L", "XL");
builder.ThenSortBy.Column(3);
worksheet.Cells["A1:D15"].Sort(options);

Sort(Action<RangeSortOptions>)

Sort the range by value. Supports top-down and left to right sort.

Declaration

public void Sort(Action<RangeSortOptions> configuration)

Parameters

Type	Name	Description
System.Action<RangeSortOptions>	configuration	An action of RangeSortOptions where sort parameters can be set.

Examples

// 1. Sort rows (top-down)

// The Column function takes the zero based column index in the range
worksheet.Cells["A1:D15"].Sort(x => x.SortBy.Column(0).ThenSortBy.Column(1, eSortOrder.Descending));

// 2. Sort columns(left to right)
// The Row function takes the zero based row index in the range
worksheet.Cells["A1:D15"].Sort(x => x.SortLeftToRightBy.Row(0));

// 3. Sort using a custom list
worksheet.Cells["A1:D15"].Sort(x => x.SortBy.Column(0).UsingCustomList("S", "M", "L", "XL"));
worksheet.Cells["A1:D15"].Sort(x => x.SortLeftToRightBy.Row(0).UsingCustomList("S", "M", "L", "XL"));

Sort(Int32, Boolean)

Sort the range by value of the supplied column, Ascending.

The column to sort by within the range. Zerobased Descending if true, otherwise Ascending. Default Ascending. Zerobased

Declaration

public void Sort(int column, bool descending = false)

Parameters

Type	Name	Description
System.Int32	column
System.Boolean	descending

Sort(Int32[], Boolean[], CultureInfo, CompareOptions)

Sort the range by value

Declaration

public void Sort(int[] columns, bool[] descending = null, CultureInfo culture = null, CompareOptions compareOptions = CompareOptions.None)

Parameters

Type	Name	Description
System.Int32[]	columns	The column(s) to sort by within the range. Zerobased
System.Boolean[]	descending	Descending if true, otherwise Ascending. Default Ascending. Zerobased
System.Globalization.CultureInfo	culture	The CultureInfo used to compare values. A null value means CurrentCulture
System.Globalization.CompareOptions	compareOptions	String compare option

ToDataTable()

Declaration

public DataTable ToDataTable()

Returns

Type	Description
System.Data.DataTable

ToDataTable(ToDataTableOptions)

Declaration

public DataTable ToDataTable(ToDataTableOptions options)

Parameters

Type	Name	Description
ToDataTableOptions	options

Returns

Type	Description
System.Data.DataTable

ToDataTable(ToDataTableOptions, DataTable)

Declaration

public DataTable ToDataTable(ToDataTableOptions options, DataTable dataTable)

Parameters

Type	Name	Description
ToDataTableOptions	options
System.Data.DataTable	dataTable

Returns

Type	Description
System.Data.DataTable

ToDataTable(Action<ToDataTableOptions>)

Declaration

public DataTable ToDataTable(Action<ToDataTableOptions> configHandler)

Parameters

Type	Name	Description
System.Action<ToDataTableOptions>	configHandler

Returns

Type	Description
System.Data.DataTable

ToDataTable(Action<ToDataTableOptions>, DataTable)

Declaration

public DataTable ToDataTable(Action<ToDataTableOptions> configHandler, DataTable dataTable)

Parameters

Type	Name	Description
System.Action<ToDataTableOptions>	configHandler
System.Data.DataTable	dataTable

Returns

Type	Description
System.Data.DataTable

ToDataTable(DataTable)

Declaration

public DataTable ToDataTable(DataTable dataTable)

Parameters

Type	Name	Description
System.Data.DataTable	dataTable

Returns

Type	Description
System.Data.DataTable

ToText()

Converts a range to text in CSV format.

Declaration

public string ToText()

Returns

Type	Description
System.String	A string containing the text

ToText(ExcelOutputTextFormat)

Converts a range to text in CSV format. Invariant culture is used by default.

Declaration

public string ToText(ExcelOutputTextFormat Format)

Parameters

Type	Name	Description
ExcelOutputTextFormat	Format	Information how to create the csv text

Returns

Type	Description
System.String	A string containing the text

ToTextAsync()

Converts a range to text in CSV format.

Declaration

public Task<string> ToTextAsync()

Returns

Type	Description
System.Threading.Tasks.Task<System.String>	A string containing the text

ToTextAsync(ExcelOutputTextFormat)

Converts a range to text in CSV format. Invariant culture is used by default.

Declaration

public Task<string> ToTextAsync(ExcelOutputTextFormat Format)

Parameters

Type	Name	Description
ExcelOutputTextFormat	Format	Information how to create the csv text

Returns

Type	Description
System.Threading.Tasks.Task<System.String>	A string containing the text

Explicit Interface Implementations

IEnumerable.GetEnumerator()

Declaration

IEnumerator IEnumerable.GetEnumerator()

Returns

Type	Description
System.Collections.IEnumerator

IEnumerator.Current

The current range when enumerating

Declaration

object IEnumerator.Current { get; }

Returns

Type	Description
System.Object

Implements

System.Collections.Generic.IEnumerable<T>

System.Collections.IEnumerable

System.Collections.Generic.IEnumerator<T>

System.Collections.IEnumerator

System.IDisposable

Extension Methods

CalculationExtension.Calculate(ExcelRangeBase)

CalculationExtension.Calculate(ExcelRangeBase, Action<ExcelCalculationOption>)

CalculationExtension.Calculate(ExcelRangeBase, ExcelCalculationOption)