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
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

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

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, Boolean)

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, bool useFirstRowAsHeader = false)
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
System.Boolean useFirstRowAsHeader

Specifies how the first row of the range should be interpreted.

- true (Default): The first row of the data is used as the header row for the new table. The data for the table will begin on the second row.

- false: The entire data range is treated as the body of the table. A new row will be inserted at the top of the range where default headers (e.g., 'Column1', 'Column2', etc.) will be automatically generated.

Exceptions
Type Condition
System.NotSupportedException

Only XLSX supports named tables.

AutoSizeColumn(Int32)

Changes width of the column to fit the content based on font measurements.

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 with a default font (Arial).

------------------------------------------------

Usage: 

// Auto-size column B to fit its content
var workbook = WorkBook.Load("example.xlsx");
var sheet = workbook.DefaultWorkSheet;
sheet.AutoSizeColumn(1);  // Column B (0-indexed)
workbook.SaveAs("autosized.xlsx");

------------------------------------------------

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
Remarks

Important Considerations:

⚠️ Font Availability: The exact font must be installed on the system for accurate measurements.

📝 Note: This overload does not consider merged cells. Use AutoSizeColumn(1, true) to include merged cells.

Related Documentation:

📖 How-To Guide:AutoSize Rows and Columns Guide

📚 API Reference:Worksheet API Documentation

Exceptions
Type Condition
System.ArgumentException

Thrown when the column cannot be auto-sized.
SixLabors.Fonts.FontException

Thrown when no fonts are found installed on the machine.

AutoSizeColumn(Int32, Boolean)

Changes width of the column to fit the content based on font measurements.

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 with a default font (Arial).

------------------------------------------------

Usage: 

// Auto-size column B to fit its content
var workbook = WorkBook.Load("example.xlsx");
var sheet = workbook.DefaultWorkSheet;
sheet.AutoSizeColumn(1, true);  // Column B (0-indexed)
workbook.SaveAs("autosized.xlsx");

------------------------------------------------

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.
Remarks

Important Considerations:

⚠️ Font Availability: The exact font must be installed on the system for accurate measurements.

📝 Merged Cells: When useMergedCells is true, the width calculation considers cells that span multiple columns.

🎯 Long Text: Ensures column width accommodates the longest text content in the column.

Related Documentation:

📖 How-To Guide:AutoSize Rows and Columns Guide

📚 API Reference:Worksheet API Documentation

Exceptions
Type Condition
System.ArgumentException

Thrown when the column cannot be auto-sized.
SixLabors.Fonts.FontException

Thrown when no fonts are found installed on the machine.

AutoSizeRow(Int32)

Changes height of the row to fit the content based on font measurements.

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 with a default font (Arial).

------------------------------------------------

Usage: 

// Auto-size row 5 to fit its content
var workbook = WorkBook.Load("example.xlsx");
var sheet = workbook.DefaultWorkSheet;
sheet.AutoSizeRow(5);
workbook.SaveAs("autosized.xlsx");

------------------------------------------------

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
Remarks

Important Considerations:

⚠️ Font Availability: The exact font must be installed on the system for accurate measurements.

📝 Note: This overload does not consider merged cells. Use AutoSizeRow(1, true) to include merged cells.

Related Documentation:

📖 How-To Guide:AutoSize Rows and Columns Guide

📚 API Reference:Worksheet API Documentation

Exceptions
Type Condition
System.ArgumentException

Thrown when the row cannot be auto-sized.
SixLabors.Fonts.FontException

Thrown when no fonts are found installed on the machine.

AutoSizeRow(Int32, Boolean)

Changes height of the row to fit the content based on font measurements.

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 with a default font (Arial).

------------------------------------------------

Usage: 

// Auto-size row 5 to fit its content
var workbook = WorkBook.Load("example.xlsx");
var sheet = workbook.DefaultWorkSheet;
sheet.AutoSizeRow(5, true);
workbook.SaveAs("autosized.xlsx");

------------------------------------------------

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.
Remarks

Important Considerations:

⚠️ Font Availability: The exact font must be installed on the system for accurate measurements.

📝 Merged Cells: When useMergedCells is true, the height calculation considers cells that span multiple rows.

🎯 Wrap Text: If cells have wrap text enabled, the height will be adjusted to show all wrapped content.

Related Documentation:

📖 How-To Guide:AutoSize Rows and Columns Guide

📚 API Reference:Worksheet API Documentation

Exceptions
Type Condition
System.ArgumentException

Thrown when the row cannot be auto-sized.
SixLabors.Fonts.FontException

Thrown when no fonts are found installed 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)

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

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, Boolean)
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