Search Results for

    Show / Hide Table of Contents

    Class WorkSheet

    Class that stands for single sheet within Excel file.

    Inheritance
    System.Object
    Range
    WorkSheet
    Implements
    System.Collections.Generic.IEnumerable<Cell>
    System.Collections.IEnumerable
    Inherited Members
    Range.ClearContents()
    Range.GetEnumerator()
    Range.ToArray()
    Range.Sum()
    Range.Max()
    Range.Min()
    Range.Avg()
    Range.AddRange(Range)
    Range.AddRange(RangeAddress)
    Range.SubtractRange(Range)
    Range.SubtractRange(RangeAddress)
    Range.RemoveRow(Int32)
    Range.RemoveRow(RangeRow)
    Range.RemoveColumn(String)
    Range.RemoveColumn(Int32)
    Range.RemoveColumn(RangeColumn)
    Range.InsertRow(Int32)
    Range.InsertRows(Int32, Int32)
    Range.InsertColumn(Int32)
    Range.InsertColumn(String)
    Range.InsertColumns(String, Int32)
    Range.InsertColumns(Int32, Int32)
    Range.SortAscending()
    Range.SortDescending()
    Range.SortByColumn(String, SortOrder)
    Range.SortByColumn(Int32, SortOrder)
    Range.Trim()
    Range.ToDataTable(Boolean)
    Range.SetCellValue(Int32, Int32, Object)
    Range.GetValue<TType>()
    Range.TryGetValue<TType>(TType)
    Range.ToString()
    Range.Copy(WorkSheet, String)
    Range.Replace(String, String)
    Range.SaveAsNamedTable(String, Boolean, TableStyle)
    Range.SaveAsNamedRange(String, Boolean)
    Range.Row(Int32)
    Range.Column(Int32)
    Range.RangeAddressAsString
    Range.Count
    Range.ColumnCount
    Range.RowCount
    Range.Style
    Range.WorkBook
    Range.IsEmpty
    Range.Rows
    Range.Columns
    Range.Value
    Range.FormatString
    Range.Formula
    Range.StringValue
    Range.DateTimeValue
    Range.BoolValue
    Range.IntValue
    Range.DoubleValue
    Range.DecimalValue
    Range.FloatValue
    Range.Int64Value
    Range.LongValue
    Range.Int32Value
    Range.AllRowsInRange
    Range.AllColumnsInRange
    Namespace: IronXL
    Assembly: IronXL.dll
    Syntax
    public class WorkSheet : Range, IEnumerable<Cell>, IEnumerable

    Properties

    Charts

    A list of IChart contained within the sheet.

    Declaration
    public List<IChart> Charts { get; }
    Property Value
    Type Description
    System.Collections.Generic.List<IChart>

    ConditionalFormatting

    Gets the conditional formatting facet of the worksheet.

    Declaration
    public ISheetConditionalFormatting ConditionalFormatting { get; }
    Property Value
    Type Description
    ISheetConditionalFormatting

    DataValidations

    Gets the list of data validation rules on the worksheet.

    Declaration
    public DataValidationsCollection DataValidations { get; }
    Property Value
    Type Description
    DataValidationsCollection

    DisplayGridlines

    Gets or sets a value indicating whether to display grid-lines.

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

    true if the sheet displays grid-lines; otherwise, false.

    FilledCells

    Gets the collection of filled (physically existing) cells.

    Declaration
    public ICollection<Cell> FilledCells { get; }
    Property Value
    Type Description
    System.Collections.Generic.ICollection<Cell>

    FirstFilledCell

    Gets the first filled (physically existing) cell, returns null if the worksheet does not contain a filled (physically existing) cell.

    Declaration
    public Cell FirstFilledCell { get; }
    Property Value
    Type Description
    Cell

    FitToPage

    Flag indicating whether the Fit to Page print option is enabled.

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

    Footer

    Gets the print footer of the worksheet.

    Declaration
    public IFooter Footer { get; }
    Property Value
    Type Description
    IFooter

    The Footer.

    Header

    Gets the print header of the worksheet.

    Declaration
    public IHeader Header { get; }
    Property Value
    Type Description
    IHeader

    The Header.

    Hidden

    Indicates whether this worksheet visible, hidden or very hidden.

    Declaration
    public HiddenState Hidden { get; set; }
    Property Value
    Type Description
    HiddenState

    HorizontallyCenter

    Determine whether printed output for this sheet will be horizontally centered.

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

    Images

    A list of IImage contained within the sheet.

    Declaration
    public List<IImage> Images { get; }
    Property Value
    Type Description
    System.Collections.Generic.List<IImage>

    Index

    Gets the index of the worksheet within workbook.

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

    IsPrintGridlines

    Gets or sets a value indicating whether to display grid-lines when printing.

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

    true if this sheet displays print grid-lines; otherwise, false.

    IsRightToLeft

    Gets or Sets the Worksheet's RightToLeft flag

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

    Item[String]

    Indexer for getting range with specified range address. <para.>Range MyRange = workSheet["A12:B18"]</para.>

    Declaration
    public Range this[string rangeAddress] { get; }
    Parameters
    Type Name Description
    System.String rangeAddress

    E.g. A12:B18

    Property Value
    Type Description
    Range

    The Range.

    LastFilledCell

    Gets the last filled (physically existing) cell, returns null if the worksheet does not contain a filled (physically existing) cell.

    Declaration
    public Cell LastFilledCell { get; }
    Property Value
    Type Description
    Cell

    Name

    Worksheet name

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

    PhysicalColumnCount

    The number of physical columns that contain cells or style information in them.

    If used range of this WorkSheet is, for exeample, B1:K1, but there is only one cell on it at address B1 and column K was hidden, then PhysicalColumnCount will return 2

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

    PhysicalRowCount

    The number of physical rows that contain cells or style information in them.

    If used range of this WorkSheet is, for exeample, A11:A20, but there is only one cell on it at address A11 and row 20 was hidden, then PhysicalRowCount will return 2

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

    PrintSetup

    Gets the print setup object.

    Declaration
    public IPrintSetup PrintSetup { get; }
    Property Value
    Type Description
    IPrintSetup

    The PrintSetup for this WorkSheet.

    RangeAddress

    Address of the working range of this WorkSheet.

    Declaration
    public override RangeAddress RangeAddress { get; }
    Property Value
    Type Description
    RangeAddress
    Overrides
    Range.RangeAddress

    TabColor

    Get the Tab Color of this worksheet if set. Returns null if tab color wasn't set

    Declaration
    public byte[] TabColor { get; }
    Property Value
    Type Description
    System.Byte[]

    VerticallyCenter

    Determine whether printed output for this sheet will be vertically centered.

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

    true if the sheet is vertically centered; otherwise, false.

    Methods

    AddNamedRange(String, Range, Boolean)

    Adds a new named range with specified range of cells.

    Declaration
    public void AddNamedRange(string nameOfRange, Range range, bool globalName = true)
    Parameters
    Type Name Description
    System.String nameOfRange

    The user-defined name.

    Range range

    The range to be referred by name.

    System.Boolean globalName

    Sets the range as global if set to true. Default value is true.

    AddNamedRange(String, String, Boolean)

    Adds a new named range with specified formula.

    Declaration
    public void AddNamedRange(string nameOfRange, string formula, bool globalName = true)
    Parameters
    Type Name Description
    System.String nameOfRange

    The user-defined name.

    System.String formula

    The formula to be referred by name.

    System.Boolean globalName

    Sets the range as global if set to true. Default value is true.

    AddNamedTable(String, Range, Boolean, TableStyle)

    Adds named table with the specified name at the data range.

    Declaration
    public void AddNamedTable(string tableName, Range range, bool showFilter = true, TableStyle tableStyle = null)
    Parameters
    Type Name Description
    System.String tableName

    the name of the table

    Range range

    the data range

    System.Boolean showFilter

    show filter on the header. Default value is true.

    TableStyle tableStyle

    table style properties

    Exceptions
    Type Condition
    System.NotSupportedException

    Only XLSX supports named tables.

    AutoSizeColumn(Int32)

    Changes width of the column to fit the content. NOTE: This method measures the text in the cell based on the font that is used for the cell. If the exact font is not found on the machine it will try to measure it with a defalt font. If the default could not be found, it will try to use any font available. Finally if there are no fonts found the exception will be thrown.

    Declaration
    public void AutoSizeColumn(int columnIndex)
    Parameters
    Type Name Description
    System.Int32 columnIndex

    0-based index of the column. Cannot be outside of the bounds set by 0 and ColumnCount - 1

    Exceptions
    Type Condition
    System.ArgumentException
    SixLabors.Fonts.FontException

    Trown when no fonts found istalled on the machine.

    AutoSizeColumn(Int32, Boolean)

    Changes width of the column to fit the content. NOTE: This method measures the text in the cell based on the font that is used for the cell. If the exact font is not found on the machine it will try to measure it with a defalt font. If the default could not be found, it will try to use any font available. Finally if there are no fonts found the exception will be thrown.

    Declaration
    public void AutoSizeColumn(int columnIndex, bool useMergedCells)
    Parameters
    Type Name Description
    System.Int32 columnIndex

    0-based index of the column. Cannot be outside of the bounds set by 0 and ColumnCount - 1

    System.Boolean useMergedCells

    If true, the width of merged cells will be taken into account.

    Exceptions
    Type Condition
    System.ArgumentException
    SixLabors.Fonts.FontException

    Trown when no fonts found istalled on the machine.

    AutoSizeRow(Int32)

    Changes height of the row to fit the content. NOTE: This method measures the text in the cell based on the font that is used for the cell. If the exact font is not found on the machine it will try to measure it with a defalt font. If the default could not be found, it will try to use any font available. Finally if there are no fonts found the exception will be thrown.

    Declaration
    public void AutoSizeRow(int rowIndex)
    Parameters
    Type Name Description
    System.Int32 rowIndex

    0-based index of the row. Cannot be outside of the bounds set by 0 and RowCount - 1

    Exceptions
    Type Condition
    System.ArgumentException
    SixLabors.Fonts.FontException

    Trown when no fonts found istalled on the machine.

    AutoSizeRow(Int32, Boolean)

    Changes height of the row to fit the content. NOTE: This method measures the text in the cell based on the font that is used for the cell. If the exact font is not found on the machine it will try to measure it with a defalt font. If the default could not be found, it will try to use any font available. Finally if there are no fonts found the exception will be thrown.

    Declaration
    public void AutoSizeRow(int rowIndex, bool useMergedCells)
    Parameters
    Type Name Description
    System.Int32 rowIndex

    0-based index of the row. Cannot be outside of the bounds set by 0 and RowCount - 1

    System.Boolean useMergedCells

    If true, the height of merged cells will be considered.

    Exceptions
    Type Condition
    System.ArgumentException
    SixLabors.Fonts.FontException

    Trown when no fonts found istalled on the machine.

    CopySheet(String)

    Duplicates this WorkSheet within the same excel WorkBook.

    Declaration
    public WorkSheet CopySheet(string name)
    Parameters
    Type Name Description
    System.String name

    The name of the new WorkSheet

    Returns
    Type Description
    WorkSheet

    This WorkSheet allowing for a fluent, in-line coding style.

    Exceptions
    Type Condition
    System.ArgumentException

    Duplicate sheet names are not allowed.

    CopyTo(WorkBook, String)

    Copies worksheet to another workbook with the specified name.

    Declaration
    public void CopyTo(WorkBook targetWorkbook, string name)
    Parameters
    Type Name Description
    WorkBook targetWorkbook

    The WorkBook where this worksheet will be copied to.

    System.String name

    The new name of the copied worksheet.

    Exceptions
    Type Condition
    System.ArgumentException

    Duplicate sheet names are not allowed.

    CreateChart(ChartType, Int32, Int32, Int32, Int32)

    Creates the chart of the specified type with the specified sizes.

    Integer parameters are 0-based row and column coordinates. E.g. if col1 equals 1, Excel column coordinate will equal "B"

    Declaration
    public IChart CreateChart(ChartType type, int row1, int col1, int row2, int col2)
    Parameters
    Type Name Description
    ChartType type

    The type of the chart.

    System.Int32 row1

    The upper row coordinate.

    System.Int32 col1

    The upper column coordinate

    System.Int32 row2

    The lower row coordinate.

    System.Int32 col2

    The lower column coordinate.

    Returns
    Type Description
    IChart

    Created chart object.

    Exceptions
    Type Condition
    System.Exception

    Charts for xls format are not currently supported. Try to use xlsx format as default.

    CreateChart(ChartType, Int32, String, Int32, String)

    Creates the chart of the specified type with the specified sizes.

    Integer parameters are 0-based row coordinates. E.g. if row1 equals 1, Excel row coordinate will equal 2

    Declaration
    public IChart CreateChart(ChartType type, int row1, string col1, int row2, string col2)
    Parameters
    Type Name Description
    ChartType type

    The type of the chart.

    System.Int32 row1

    The upper row coordinate.

    System.String col1

    The left column coordinate, e.g. "A"

    System.Int32 row2

    The lower row coordinate.

    System.String col2

    The right column coordinate, e.g. "C".

    Returns
    Type Description
    IChart

    Created chart object.

    Exceptions
    Type Condition
    System.Exception

    Charts for xls format are not currently supported. Try to use xlsx format as default.

    CreateFreezePane(Int32, Int32)

    Creates a freeze pane. Any existing freeze pane or split pane is overwritten.

    All parameters are 0-based row and column coordinates. E.g. if colSplit equals 1, Excel column coordinate will equal "B"

    Declaration
    public void CreateFreezePane(int colSplit, int rowSplit)
    Parameters
    Type Name Description
    System.Int32 colSplit

    Horizontal position of split.

    System.Int32 rowSplit

    Vertical position of split.

    CreateFreezePane(Int32, Int32, Int32, Int32)

    Creates a freeze pane. Any existing freeze pane or split pane is overwritten.

    All parameters are 0-based row and column coordinates. E.g. if colSplit equals 1, Excel column coordinate will equal "B"

    Declaration
    public void CreateFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow)
    Parameters
    Type Name Description
    System.Int32 colSplit

    Horizontal position of split.

    System.Int32 rowSplit

    Vertical position of split.

    System.Int32 leftmostColumn

    Top row visible in bottom pane

    System.Int32 topRow

    Left column visible in right pane.

    CreateSplitPane(Int32, Int32, Int32, Int32, PanePosition)

    Creates a split pane. Any existing freeze pane or split pane is overwritten.

    Integer parameters are 0-based row and column coordinates. E.g. if ySplitPos equals 1, Excel column coordinate will equal "B"

    Declaration
    public void CreateSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, PanePosition activePane)
    Parameters
    Type Name Description
    System.Int32 xSplitPos

    Horizontal position of split in px.

    System.Int32 ySplitPos

    Vertical position of split in px.

    System.Int32 leftmostColumn

    Left column visible in right pane.

    System.Int32 topRow

    Top row visible in bottom pane.

    PanePosition activePane

    Active pane.

    FindNamedRange(String)

    Finds the named range with the specified name.

    Declaration
    public string FindNamedRange(string nameOfRange)
    Parameters
    Type Name Description
    System.String nameOfRange

    The user-defined name.

    Returns
    Type Description
    System.String

    The formula of the named range.

    GetCellAt(Int32, Int32)

    Returns the cell at a given row/column number within a worksheet. Useful for iterating a worksheet;

    Declaration
    public Cell GetCellAt(int rowIndex, int columnIndex)
    Parameters
    Type Name Description
    System.Int32 rowIndex

    Row index within this worksheet.

    The first row is numbered 0.

    System.Int32 columnIndex

    Column index within this worksheet.

    The first column is numbered 0.

    Returns
    Type Description
    Cell

    A Cell object. Use Value to get the value. null if cell is not filled.

    GetColumn(Int32)

    Returns the Column at columnIndex. The first column "A" is counted as 0;

    Synonym of Column(Int32)

    Declaration
    public RangeColumn GetColumn(int columnIndex)
    Parameters
    Type Name Description
    System.Int32 columnIndex

    The index (zero-based number) of the column to return

    Returns
    Type Description
    RangeColumn

    A Column within the WorkSheet.

    GetColumn(String)

    Returns a Column by name

    E.g. "A" or "B"

    Declaration
    public RangeColumn GetColumn(string columnName)
    Parameters
    Type Name Description
    System.String columnName

    Column heading letter. For Example "A" or "B"

    Returns
    Type Description
    RangeColumn

    A Column within the WorkSheet.

    GetDataTableFromNamedTable(String, Boolean)

    Get System.Data.DataTable representation of named table.

    Useful when working with DataGrids, System.Data, SQL or Entity Framework

    Declaration
    public DataTable GetDataTableFromNamedTable(string tableName, bool useFirstRowAsColumnNames = false)
    Parameters
    Type Name Description
    System.String tableName

    The name of the table.

    System.Boolean useFirstRowAsColumnNames

    Indicates whether to use first row as column names or not

    Returns
    Type Description
    System.Data.DataTable

    GetMergedRegions()

    Retrieve a list of merged regions.

    Declaration
    public List<Range> GetMergedRegions()
    Returns
    Type Description
    System.Collections.Generic.List<Range>

    GetNamedRanges()

    Gets names of the named ranges.

    Declaration
    public ICollection<string> GetNamedRanges()
    Returns
    Type Description
    System.Collections.Generic.ICollection<System.String>

    Collection for the names of the named ranges.

    GetNamedTable(String)

    Get Range representation of named table.

    Declaration
    public Range GetNamedTable(string tableName)
    Parameters
    Type Name Description
    System.String tableName

    The name of the table.

    Returns
    Type Description
    Range

    GetNamedTableNames()

    Gets names of named tables.

    Declaration
    public ICollection<string> GetNamedTableNames()
    Returns
    Type Description
    System.Collections.Generic.ICollection<System.String>
    Exceptions
    Type Condition
    System.NotSupportedException

    Only XLSX supports named tables.

    GetRange(String)

    Get range with specified range address

    E.g. Range myRange = mySheet.GetRange("A1:C62")

    Declaration
    public Range GetRange(string range)
    Parameters
    Type Name Description
    System.String range

    The range address within the sheet. E.g. "B12:B29"

    Returns
    Type Description
    Range

    The specified Range

    GetRow(Int32)

    Returns the Row at rowIndex. The first row is counted as 0;

    Synonym of Row(Int32)

    Declaration
    public RangeRow GetRow(int rowIndex)
    Parameters
    Type Name Description
    System.Int32 rowIndex

    The index (zero-based number) of the row to return

    Returns
    Type Description
    RangeRow

    A Row within the WorkSheet.

    GroupColumns(Int32, Int32)

    Group multiple columns within the specified range together.

    Grouping and ungrouping is layered and calling GroupColumns(Int32, Int32) twice requires two calls of UngroupColumns(Int32, Int32) with appropriate ranges to remove all groupings.

    All parameters are 0-based coordinates. E.g. if startColumn equals 2, Excel column coordinate will equal "C"

    Declaration
    public void GroupColumns(int startColumn, int endColumn)
    Parameters
    Type Name Description
    System.Int32 startColumn

    The start column index.

    System.Int32 endColumn

    The end column index.

    GroupColumns(String, String)

    Group multiple columns within the specified range together.

    Grouping and ungrouping is layered and calling GroupColumns(String, String) twice requires two calls of UngroupColumns(Int32, Int32) with appropriate ranges to remove all groupings.

    Declaration
    public void GroupColumns(string startColumn, string endColumn)
    Parameters
    Type Name Description
    System.String startColumn

    The start column value, e.g. "A".

    System.String endColumn

    The end column value, e.g. "C"

    GroupRows(Int32, Int32)

    Group multiple rows within the specified range together.

    Grouping and ungrouping is layered and calling GroupRows(Int32, Int32) twice requires two calls of UngroupRows(Int32, Int32) with appropriate ranges to remove all groupings.

    All parameters are 0-based coordinates. E.g. if startRow equals 2, Excel row coordinate will equal 3

    Declaration
    public void GroupRows(int startRow, int endRow)
    Parameters
    Type Name Description
    System.Int32 startRow

    The start row index.

    System.Int32 endRow

    The end row index.

    InsertImage(Byte[], ImageFormat, Int32, Int32, Int32, Int32)

    Inserts image from the specified path to the worksheet. The size of the picture is defined by col/row coordinates.

    Integer parameters are 0-based row and column coordinates. E.g. if col1 equals 1, Excel column coordinate will equal "B"

    Declaration
    public IImage InsertImage(byte[] bytes, ImageFormat pictureType, int col1, int row1, int col2, int row2)
    Parameters
    Type Name Description
    System.Byte[] bytes

    The byte array of the image being imported

    ImageFormat pictureType

    The type of a picture contained in bytes. Supported types are listed in ImageFormat enumeration

    System.Int32 col1

    The left column to place the image.

    System.Int32 row1

    The top row to place the image.

    System.Int32 col2

    The right column to place the image.

    System.Int32 row2

    The bottom row to place the image.

    Returns
    Type Description
    IImage

    The inserted IronXL.Drawing.Images.Image object.

    InsertImage(String, Int32, Int32, Int32, Int32)

    Inserts image from the specified path to the worksheet. The size of the picture is defined by col/row coordinates.

    Integer parameters are 0-based row and column coordinates. E.g. if col1 equals 1, Excel column coordinate will equal "B"

    Declaration
    public IImage InsertImage(string path, int col1, int row1, int col2, int row2)
    Parameters
    Type Name Description
    System.String path

    The path to the image file.

    System.Int32 col1

    The left column to place the image.

    System.Int32 row1

    The top row to place the image.

    System.Int32 col2

    The right column to place the image.

    System.Int32 row2

    The bottom row to place the image.

    Returns
    Type Description
    IImage

    The inserted IronXL.Drawing.Images.Image object.

    Exceptions
    Type Condition
    System.ArgumentException

    Unsupported image format. Supported formats are: JPG/JPEG, BMP, PNG, GIF, TIFF

    InsertImage(String, String, Int32, String, Int32)

    Inserts image from the specified path to the worksheet. The size of the picture is defined by col/row coordinates.

    Integer parameters are 0-based row coordinates. E.g. if row1 equals 1, Excel row coordinate will equal 2

    Declaration
    public IImage InsertImage(string path, string col1, int row1, string col2, int row2)
    Parameters
    Type Name Description
    System.String path

    The path to the image file.

    System.String col1

    The left column to place the image, e.g. "A".

    System.Int32 row1

    The top row to place the image.

    System.String col2

    The right column to place the image, e.g. "C".

    System.Int32 row2

    The bottom row to place the image.

    Returns
    Type Description
    IImage

    The inserted IronXL.Drawing.Images.Image object.

    Exceptions
    Type Condition
    System.ArgumentException

    Unsupported image format. Supported formats are: JPG/JPEG, BMP, PNG, GIF, TIFF

    Merge(String)

    Merges the specified range into a single cell.

    Declaration
    public int Merge(string range)
    Parameters
    Type Name Description
    System.String range

    The Excel range selector as a string. E.g. "B1:C1"

    Returns
    Type Description
    System.Int32

    Index of the created merged region. See Unmerge(Int32).

    ProtectSheet(String)

    Protects the sheet.

    Declaration
    public void ProtectSheet(string password)
    Parameters
    Type Name Description
    System.String password

    The password.

    RemoveChart(IChart)

    Removes the specified chart.

    Declaration
    public void RemoveChart(IChart chart)
    Parameters
    Type Name Description
    IChart chart

    The chart to remove.

    RemoveColumnBreak(Int32)

    Removes a page break at the indicated column.

    Declaration
    public void RemoveColumnBreak(int column)
    Parameters
    Type Name Description
    System.Int32 column

    The column index.

    RemoveImage(Int32)

    Removes the image by its id. Id can be retrieved from the IImage object

    Declaration
    public void RemoveImage(int id)
    Parameters
    Type Name Description
    System.Int32 id

    The image id [identifier].

    RemoveNamedRange(String)

    Removes the named range with the specified name.

    Declaration
    public void RemoveNamedRange(string nameOfRange)
    Parameters
    Type Name Description
    System.String nameOfRange

    The user-defined name.

    RemovePane()

    Removes the freeze or the split pane.

    Declaration
    public void RemovePane()

    RemoveRepeatingColumns()

    Reset repeating columns for the print title of the worksheet.

    Declaration
    public void RemoveRepeatingColumns()

    RemoveRepeatingRows()

    Reset repeating rows for the print title of the worksheet.

    Declaration
    public void RemoveRepeatingRows()

    RemoveRowBreak(Int32)

    Removes the page break at the indicated row.

    Declaration
    public void RemoveRowBreak(int row)
    Parameters
    Type Name Description
    System.Int32 row

    The row index.

    SaveAs(String)

    Exports the worksheet with as a new worksheet to: CSV, TSV, JSON, Excel or XML

    Declaration
    public WorkSheet SaveAs(string filename)
    Parameters
    Type Name Description
    System.String filename

    The new filename. Export format will be detected by the file extension specified.

    Returns
    Type Description
    WorkSheet

    This WorkSheet, allowing for an in-line fluent coding style.

    Exceptions
    Type Condition
    System.FormatException

    Unsupported extension. Supported formats for worksheet are: CSV, TSV, JSON, XML

    SaveAsCsv(String, String)

    Saves the worksheet into a new CSV file.

    Declaration
    public void SaveAsCsv(string filename, string delimiter = null)
    Parameters
    Type Name Description
    System.String filename

    The new CSV filename.

    System.String delimiter

    The CSV column delimiter. Leave this null for default system behavior.

    SaveAsJson(String)

    Saves the worksheet into a new JSON file.

    Declaration
    public void SaveAsJson(string filename)
    Parameters
    Type Name Description
    System.String filename

    The new JSON filename.

    SaveAsNewExcelFile(String, ExcelFileFormat)

    Saves the worksheet into a new excel file on disk.

    Declaration
    public void SaveAsNewExcelFile(string filename, ExcelFileFormat excelFormat)
    Parameters
    Type Name Description
    System.String filename

    The new Excel filename.

    ExcelFileFormat excelFormat

    The Excel format. XLSX is preferred and default.

    SaveAsXml(String)

    Saves the worksheet into a new XML file.

    Declaration
    public void SaveAsXml(string filename)
    Parameters
    Type Name Description
    System.String filename

    The new XML filename.

    SetAutoFilter(Int32, Int32, Int32, Int32)

    Enable filtering for a range of cells defined with the row and column coordinates.

    e.g. if firstColumn equals 0, Excel column coordinate will equal "A"

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

    First row of the range to filter.

    System.Int32 lastRow

    Last of the range to filter.

    System.Int32 firstColumn

    First column of the range to filter.

    System.Int32 lastColumn

    Last column of the range to filter.

    SetAutoFilter(String)

    Enable filtering for a range of cells.

    Declaration
    public void SetAutoFilter(string range)
    Parameters
    Type Name Description
    System.String range

    The range of cells to filter.

    SetColumnBreak(Int32)

    Sets the column break.

    Declaration
    public void SetColumnBreak(int column)
    Parameters
    Type Name Description
    System.Int32 column

    The column index.

    SetPrintArea(String)

    Sets the print area for this worksheet.

    E.g. "A1:X82"

    Declaration
    public void SetPrintArea(string range)
    Parameters
    Type Name Description
    System.String range

    The cell range to specify as the print area. E.g. "A1:X82"

    SetRepeatingColumns(Int32, Int32)

    Set repeating columns for the print title of the worksheet.

    All parameters are 0-based coordinates. E.g. if startColumn equals 2, Excel column coordinate will equal "C"

    Declaration
    public void SetRepeatingColumns(int startColumn, int endColumn)
    Parameters
    Type Name Description
    System.Int32 startColumn

    The start 0-based column index.

    System.Int32 endColumn

    The end 0-based column index.

    SetRepeatingColumns(String, String)

    Set repeating columns for the print title of the worksheet.

    Declaration
    public void SetRepeatingColumns(string startColumn, string endColumn)
    Parameters
    Type Name Description
    System.String startColumn

    The start column value, e.g. "A".

    System.String endColumn

    The end column value, e.g. "C".

    SetRepeatingRows(Int32, Int32)

    Set repeating rows for the print title of the worksheet.

    All parameters are 0-based coordinates. E.g. if startRow equals 1, Excel row coordinate will equal 2

    Declaration
    public void SetRepeatingRows(int startRow, int endRow)
    Parameters
    Type Name Description
    System.Int32 startRow

    The start 0-based row index.

    System.Int32 endRow

    The end 0-based row index.

    SetRowBreak(Int32)

    Sets the row break.

    Declaration
    public void SetRowBreak(int row)
    Parameters
    Type Name Description
    System.Int32 row

    The row.

    SetWorksheetTabColor(Byte[])

    Sets this worksheet's tab color accepting RGB array (0-255), works only for XLSX files. For old XLS files, set tab color using SetWorksheetTabColorIndex(Int16)

    Declaration
    public void SetWorksheetTabColor(byte[] rgb)
    Parameters
    Type Name Description
    System.Byte[] rgb

    The Red, Green and Blue color values between 0 and 255

    Exceptions
    Type Condition
    System.ArgumentException
    See Also
    SetWorksheetTabColorIndex(Int16)

    SetWorksheetTabColorIndex(Int16)

    Sets this worksheet's tab color accepting Excel ColorIndex. For new XSLX files, prefer using using SetWorksheetTabColor(Byte[])

    Declaration
    public void SetWorksheetTabColorIndex(short colorIndex)
    Parameters
    Type Name Description
    System.Int16 colorIndex

    The Excel XLS Color Index Number

    See Also
    SetWorksheetTabColor(Byte[])

    SetZoom(Int32)

    Sets the zoom scale for this worksheet.

    100 is normal zoom.

    Declaration
    public void SetZoom(int scale)
    Parameters
    Type Name Description
    System.Int32 scale

    The scale percentage. 100 is normal zoom.

    ToCsvStream(String)

    Converts the worksheet data to CSV format and saves it to a System.IO.MemoryStream.

    Declaration
    public MemoryStream ToCsvStream(string delimiter = null)
    Parameters
    Type Name Description
    System.String delimiter

    The delimiter for separating CSV columns. If null, the default system delimiter is used.

    Returns
    Type Description
    System.IO.MemoryStream

    A System.IO.MemoryStream containing the CSV data.

    ToJsonStream()

    Converts the worksheet data to JSON format and saves it to a System.IO.MemoryStream.

    Declaration
    public MemoryStream ToJsonStream()
    Returns
    Type Description
    System.IO.MemoryStream

    A System.IO.MemoryStream containing the JSON data.

    ToMultiDimensionalArray()

    Convert worksheet to two-dimensional array of cells

    Declaration
    public Cell[][] ToMultiDimensionalArray()
    Returns
    Type Description
    Cell[][]

    An 2 dimensional array of Cell objects.

    ToNewExcelStream(ExcelFileFormat)

    Creates a new workbook with the current worksheet's data and saves it as an Excel file format stream.

    Declaration
    public MemoryStream ToNewExcelStream(ExcelFileFormat excelFormat)
    Parameters
    Type Name Description
    ExcelFileFormat excelFormat

    The Excel format (e.g., XLSX or XLS). The default is XLSX.

    Returns
    Type Description
    System.IO.MemoryStream

    A System.IO.MemoryStream containing the worksheet data in the specified Excel format.

    ToTsvStream()

    Converts the worksheet data to TSV format and saves it to a System.IO.MemoryStream.

    Declaration
    public MemoryStream ToTsvStream()
    Returns
    Type Description
    System.IO.MemoryStream

    A System.IO.MemoryStream containing the TSV data.

    ToXmlStream()

    Converts the worksheet data to XML format and saves it to a System.IO.MemoryStream.

    Declaration
    public MemoryStream ToXmlStream()
    Returns
    Type Description
    System.IO.MemoryStream

    A System.IO.MemoryStream containing the XML data.

    UngroupColumn(String, String)

    Ungroup all columns within the specified range.

    Grouping and ungrouping is layered and calling GroupColumns(String, String) twice requires two calls of UngroupColumns(Int32, Int32) with appropriate ranges to remove all groupings.

    Declaration
    public void UngroupColumn(string startColumn, string endColumn)
    Parameters
    Type Name Description
    System.String startColumn

    The start column value, e.g. "A".

    System.String endColumn

    The end column index, e.g. "C"

    UngroupColumns(Int32, Int32)

    Ungroup all columns within the specified range.

    Grouping and ungrouping is layered and calling GroupColumns(Int32, Int32) twice requires two calls of UngroupColumns(Int32, Int32) with appropriate ranges to remove all groupings.

    All parameters are 0-based coordinates. E.g. if startColumn equals 2, Excel column coordinate will equal "C"

    Declaration
    public void UngroupColumns(int startColumn, int endColumn)
    Parameters
    Type Name Description
    System.Int32 startColumn

    The start column index.

    System.Int32 endColumn

    The end column index.

    UngroupRows(Int32, Int32)

    Ungroup all rows within the specified range.

    Grouping and ungrouping is layered and calling GroupRows(Int32, Int32) twice requires two calls of UngroupRows(Int32, Int32) with appropriate ranges to remove all groupings.

    All parameters are 0-based coordinates. E.g. if startRow equals 2, Excel row coordinate will equal 3

    Declaration
    public void UngroupRows(int startRow, int endRow)
    Parameters
    Type Name Description
    System.Int32 startRow

    The start row index.

    System.Int32 endRow

    The end row index.

    Unmerge(Int32)

    Un-Merges the specified merge region by index.

    To learn about indexes, see Merge(String).

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

    The index. To learn about indexes see Merge(String)

    Unmerge(String)

    Un-Merges a specified range of cells.

    Declaration
    public void Unmerge(string range)
    Parameters
    Type Name Description
    System.String range

    The Excel range selector for the merged region as a string. E.g. "B1:C1"

    Exceptions
    Type Condition
    System.ArgumentException

    Merged region was not found.

    UnprotectSheet()

    Unprotects the sheet.

    Declaration
    public void UnprotectSheet()

    UnsetAutoFilter()

    Unset filtering for a current worksheet.

    Declaration
    public void UnsetAutoFilter()

    Implements

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

    Inherited members

    ClearContents()
    GetEnumerator()
    ToArray()
    Sum()
    Max()
    Min()
    Avg()
    AddRange(Range)
    AddRange(RangeAddress)
    SubtractRange(Range)
    SubtractRange(RangeAddress)
    RemoveRow(Int32)
    RemoveRow(RangeRow)
    RemoveColumn(String)
    RemoveColumn(Int32)
    RemoveColumn(RangeColumn)
    InsertRow(Int32)
    InsertRows(Int32, Int32)
    InsertColumn(Int32)
    InsertColumn(String)
    InsertColumns(String, Int32)
    InsertColumns(Int32, Int32)
    SortAscending()
    SortDescending()
    SortByColumn(String, SortOrder)
    SortByColumn(Int32, SortOrder)
    Trim()
    ToDataTable(Boolean)
    SetCellValue(Int32, Int32, Object)
    GetValue<TType>()
    TryGetValue<TType>(TType)
    ToString()
    Copy(WorkSheet, String)
    Replace(String, String)
    SaveAsNamedTable(String, Boolean, TableStyle)
    SaveAsNamedRange(String, Boolean)
    Row(Int32)
    Column(Int32)
    RangeAddressAsString
    Count
    ColumnCount
    RowCount
    Style
    WorkBook
    IsEmpty
    Rows
    Columns
    Value
    FormatString
    Formula
    StringValue
    DateTimeValue
    BoolValue
    IntValue
    DoubleValue
    DecimalValue
    FloatValue
    Int64Value
    LongValue
    Int32Value
    AllRowsInRange
    AllColumnsInRange
    ☀
    ☾
    Downloads
    • Download with Nuget
    • Start for Free
    In This Article
    Back to top
    Install with Nuget
    Want to deploy IronXL to a live project for FREE?
    What’s included?
    30 days of fully-functional product
    Test and share in a live environment
    No watermarks in production
    Get your free 30-day Trial Key instantly.
    No credit card or account creation required
    Your Trial License Key has been emailed to you.
    Download IronXL free to apply
    your Trial Licenses Key
    Install with NuGet View Licenses
    Licenses from $499. Have a question? Get in touch.