Table of Contents

Class Worksheet

Namespace
Spire.Xls
Assembly
Spire.XLS.dll
public class Worksheet : XlsWorksheet, IDisposable, INamedObject, ICloneParent, IInternalWorksheet, IWorksheet, ITabSheet, IExcelApplication
Inheritance
Worksheet
Implements
Inherited Members

Properties

AllocatedRange

Returns a Range object that represents the used range on the specified worksheet. Read-only.

public CellRange AllocatedRange { get; }

Property Value

CellRange

AutoFilters 

public AutoFiltersCollection AutoFilters { get; }

Property Value

AutoFiltersCollection

CellImages

Cell image array. Read-only.

public ExcelPicture[] CellImages { get; }

Property Value

ExcelPicture[]

Cells

Returns all used cells in the worksheet. Read-only.

public CellRange[] Cells { get; }

Property Value

CellRange[]

Charts

Returns charts collection. Read-only.

public WorksheetChartsCollection Charts { get; }

Property Value

WorksheetChartsCollection

Columns

Rrepresents all used columns on the specified worksheet. Read-only Range object.

public CellRange[] Columns { get; }

Property Value

CellRange[]

Comments

Returns comments collection for this worksheet. Read-only. The following code illustrates how to access the comments collection in the worksheet:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Adding comments to a cell.
ICommentShape comment1 = worksheet.Range["A1"].AddComment();
ICommentShape comment2 = worksheet.Range["B1"].AddComment();

//Set comment text
comment1.Text = "Comment1";
comment2.Text = "Comment2";

//Check count
Console.Write(worksheet.Comments.Count);

//Save to file
workbook.SaveToFile("Comments.xlsx");
public CommentsCollection Comments { get; }

Property Value

CommentsCollection

HPageBreaks

Returns an HPageBreaks collection that represents the horizontal page breaks on the sheet.

public HPageBreaksCollection HPageBreaks { get; }

Property Value

HPageBreaksCollection

Collection of all worksheet's hyperlinks.

public HyperLinksCollection HyperLinks { get; }

Property Value

HyperLinksCollection

this[int, int]

Get cell range.

public CellRange this[int row, int column] { get; }

Parameters

row int
column int

Property Value

CellRange

this[int, int, int, int]

Get cell range.

public CellRange this[int row, int column, int lastRow, int lastColumn] { get; }

Parameters

row int
column int
lastRow int
lastColumn int

Property Value

CellRange

this[string]

Get cell range.

public CellRange this[string name] { get; }

Parameters

name string

Property Value

CellRange

MergedCells

Returns all merged ranges. Read-only. The following code illustrates how to get the merged ranges:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Merge cells
worksheet["C2:D2"].Merge();
worksheet["F3:G3"].Merge();

//Get merged ranges
IXLSRange[] mergedRanges = worksheet.MergedCells;

//Get merged range count . Output will be 2
Console.Write(mergedRanges.Length);

//Save to file
workbook.SaveToFile("MergedCells.xlsx");
public CellRange[] MergedCells { get; }

Property Value

CellRange[]

PageSetup

Returns a PageSetup object that contains all the page setup settings for the specified object. Read-only.

public PageSetup PageSetup { get; }

Property Value

PageSetup

ParentWorkbook 

public Workbook ParentWorkbook { get; }

Property Value

Workbook

Pictures

Pictures collection. Read-only.

public PicturesCollection Pictures { get; }

Property Value

PicturesCollection

PrintRange

Print area of worksheet.

public CellRange PrintRange { get; }

Property Value

CellRange

QueryTables 

public QueryTableCollection QueryTables { get; }

Property Value

QueryTableCollection

Range

Returns a Range object that represents the used range on the specified worksheet. Read-only.
The following code illustrates how to get used range on the specified worksheet:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["B2"].Text = "Text";

//Set Color
worksheet["J3"].Style.Color = Color.Red;

//Get used range . Output will be B2:J3
Console.Write(worksheet.Range.RangeAddressLocal);

//Save to file
workbook.SaveToFile("UsedRange.xlsx");
public CellRange Range { get; }

Property Value

CellRange

Rows

Represents all the rows on the specified worksheet. Read-only Range object.

public CellRange[] Rows { get; }

Property Value

CellRange[]

VPageBreaks

Returns a VPageBreaks collection that represents the vertical page breaks on the sheet. Read-only.

public VPageBreaksCollection VPageBreaks { get; }

Property Value

VPageBreaksCollection

Workbook 

public Workbook Workbook { get; }

Property Value

Workbook

Methods

ApplyStyle(CellStyle)

Apply style to whole sheet.

public void ApplyStyle(CellStyle style)

Parameters

style CellStyle

style to apply

CalculateAndGetRowHeight(int) 

public double CalculateAndGetRowHeight(int rowIndex)

Parameters

rowIndex int

Returns

double

Copy(CellRange, CellRange)

Copys data from a source range to a destination range.

public void Copy(CellRange sourceRange, CellRange destRange)

Parameters

sourceRange CellRange

Source range.

destRange CellRange

Destination range.

Copy(CellRange, CellRange, CopyRangeOptions)

Copys data from a source range to a destination range.

public void Copy(CellRange sourceRange, CellRange destRange, CopyRangeOptions copyOptions)

Parameters

sourceRange CellRange

Source range.

destRange CellRange

Destination range

copyOptions CopyRangeOptions

Copy options.

Copy(CellRange, CellRange, bool)

Copys data from a source range to a destination range.

public void Copy(CellRange sourceRange, CellRange destRange, bool copyStyle)

Parameters

sourceRange CellRange

Source range.

destRange CellRange

Destination range.

copyStyle bool

Indicates whether copys styles.

Copy(CellRange, CellRange, bool, bool, bool)

Copys data from a source range to a destination range.

public void Copy(CellRange sourceRange, CellRange destRange, bool copyStyle, bool updateReference, bool ignoreSize)

Parameters

sourceRange CellRange

Source range.

destRange CellRange

Destination range

copyStyle bool

Indicates whether copy styles.

updateReference bool

Indicates whether update reference ranges.

ignoreSize bool

Indicates whether check range sizes.

Copy(CellRange, CellRange, bool, bool, bool, bool)

Copys data from a source range to a destination range.

public void Copy(CellRange sourceRange, CellRange destRange, bool copyStyle, bool updateReference, bool ignoreSize, bool copyShape)

Parameters

sourceRange CellRange

Source range.

destRange CellRange

Destination range

copyStyle bool

Indicates whether copy styles.

updateReference bool

Indicates whether update reference ranges.

ignoreSize bool

Indicates whether check range sizes.

copyShape bool

Indicates whether copy shape.

Copy(CellRange, Worksheet, int, int)

Copy data from source range to destination worksheet.

public void Copy(CellRange sourceRange, Worksheet worksheet, int destRow, int destColumn)

Parameters

sourceRange CellRange

Source range.

worksheet Worksheet

Destination worksheet

destRow int

Row index of destination worksheet.

destColumn int

Column index of destination worksheet.

Copy(CellRange, Worksheet, int, int, bool)

Copy data from source range to destination worksheet.

public void Copy(CellRange sourceRange, Worksheet worksheet, int destRow, int destColumn, bool copyStyle)

Parameters

sourceRange CellRange

Source range

worksheet Worksheet

Destination worksheet.

destRow int

Row index of destination worksheet.

destColumn int

Column index of destination worksheet.

copyStyle bool

Indicates whehter copy styles.

Copy(CellRange, Worksheet, int, int, bool, bool)

Copy data from source range to destination worksheet.

public void Copy(CellRange sourceRange, Worksheet worksheet, int destRow, int destColumn, bool copyStyle, bool updateRerence)

Parameters

sourceRange CellRange

Source range

worksheet Worksheet

Destination worksheet.

destRow int

Row index of destination worksheet.

destColumn int

Column index of destination worksheet.

copyStyle bool

Indicates whehter copy styles.

updateRerence bool

Indicates whether update reference range.

CopyColumn(CellRange, Worksheet, int, CopyRangeOptions)

Copys data from a source column to a destination column.

public void CopyColumn(CellRange sourceColumn, Worksheet destSheet, int destColIndex, CopyRangeOptions copyOptions)

Parameters

sourceColumn CellRange

Source column.

destSheet Worksheet

Destination sheet

destColIndex int

Destination column index

copyOptions CopyRangeOptions

Copy options.

CopyFrom(Worksheet)

Copy data from specified worksheet.

public void CopyFrom(Worksheet worksheet)

Parameters

worksheet Worksheet

worksheet object

CopyRow(CellRange, Worksheet, int, CopyRangeOptions)

Copys data from a source row to a destination row.

public void CopyRow(CellRange sourceRow, Worksheet destSheet, int destRowIndex, CopyRangeOptions copyOptions)

Parameters

sourceRow CellRange

Source row.

destSheet Worksheet

Destination sheet

destRowIndex int

Destination row index

copyOptions CopyRangeOptions

Copy options.

ExportDataTable(CellRange, ExportTableOptions) 

public DataTable ExportDataTable(CellRange range, ExportTableOptions options)

Parameters

range CellRange
options ExportTableOptions

Returns

DataTable

ExportDataTable(CellRange, bool)

Exports worksheet data into a DataTable.

public DataTable ExportDataTable(CellRange range, bool exportColumnNames)

Parameters

range CellRange

Range to export.

exportColumnNames bool

Indicates if export column name.

Returns

DataTable

exported datatable

ExportDataTable(CellRange, bool, bool)

Exports worksheet data into a DataTable.

public DataTable ExportDataTable(CellRange range, bool exportColumnNames, bool computedFormulaValue)

Parameters

range CellRange

Range to export.

exportColumnNames bool

Indicates if export column name.

computedFormulaValue bool

Indicates wheter compute formula value.

Returns

DataTable

exported datatable

ExportDataTable(int, int, int, int, ExportTableOptions) 

public DataTable ExportDataTable(int firstRow, int firstColumn, int maxRows, int maxColumns, ExportTableOptions options)

Parameters

firstRow int
firstColumn int
maxRows int
maxColumns int
options ExportTableOptions

Returns

DataTable

ExportDataTable(int, int, int, int, bool)

Exports worksheet data into a DataTable

public DataTable ExportDataTable(int firstRow, int firstColumn, int maxRows, int maxColumns, bool exportColumnNames)

Parameters

firstRow int

Row of first cell.

firstColumn int

Column of first cell.

maxRows int

Maximun rows to export.

maxColumns int

Maximun columns to export.

exportColumnNames bool

Indicates if export column name.

Returns

DataTable

Exported datatable.

FindAll(string, FindType, ExcelFindOptions)

Finds the all cells with specified string value.

public CellRange[] FindAll(string findValue, FindType flags, ExcelFindOptions findOptions)

Parameters

findValue string

Value to search.

flags FindType

Type of value to search.

findOptions ExcelFindOptions

Way to search.

Returns

CellRange[]

All found cells, or Null if value was not found.

FindAllBool(bool)

Finds the cell with the input bool.

public CellRange[] FindAllBool(bool boolValue)

Parameters

boolValue bool

Bool value to search for.

Returns

CellRange[]

Found ranges.

FindAllDateTime(DateTime)

Finds the cell with the input date time.

public CellRange[] FindAllDateTime(DateTime dateTimeValue)

Parameters

dateTimeValue DateTime

Datetime value to search for.

Returns

CellRange[]

Found ranges.

FindAllNumber(double, bool)

Finds the cell with the input number.

public CellRange[] FindAllNumber(double doubleValue, bool formulaValue)

Parameters

doubleValue double

Double value to search for.

formulaValue bool

Indicates if includes formula value.

Returns

CellRange[]

Found ranges.

FindAllString(string, bool, bool)

Finds the cell with the input string.

public CellRange[] FindAllString(string stringValue, bool formula, bool formulaValue)

Parameters

stringValue string

String value to search for.

formula bool

Indicates if includes formula.

formulaValue bool

Indicates if includes formula value.

Returns

CellRange[]

Found ranges.

FindAllTimeSpan(TimeSpan)

Finds the cell with the input time span.

public CellRange[] FindAllTimeSpan(TimeSpan timeSpanValue)

Parameters

timeSpanValue TimeSpan

Time span value to search for.

Returns

CellRange[]

Found ranges.

FindBool(bool)

Finds the cell with the input bool.

public CellRange FindBool(bool boolValue)

Parameters

boolValue bool

Bool value to search for.

Returns

CellRange

Found range.

FindDateTime(DateTime)

Finds the cell with the input date time.

public CellRange FindDateTime(DateTime dateTimeValue)

Parameters

dateTimeValue DateTime

DateTime value to search for.

Returns

CellRange

Found range.

FindNumber(double, bool)

Finds the cell with the input double.

public CellRange FindNumber(double doubleValue, bool formulaValue)

Parameters

doubleValue double

Double value to search for.

formulaValue bool

Indicates if includes formula value.

Returns

CellRange

Found range.

FindString(string, bool, bool)

Finds the cell with the input string.

public CellRange FindString(string stringValue, bool formula, bool formulaValue)

Parameters

stringValue string

String value to search for.

formula bool

Indicates whether includes formula.

formulaValue bool

Indicates whether includes formula value.

Returns

CellRange

Found range.

FindTimeSpan(TimeSpan)

Finds the cell with the input time span.

public CellRange FindTimeSpan(TimeSpan timeSpanValue)

Parameters

timeSpanValue TimeSpan

Time span value to search for.

Returns

CellRange

Found range.

FreezePanes(int, int)

Freezes panes at the specified cell in the worksheet.

public void FreezePanes(int rowIndex, int columnIndex)

Parameters

rowIndex int

Row index.

columnIndex int

Column index.

GetDefaultColumnStyle(int)

Returns default column style. The following code illustrates how to get default column style:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create style
CellStyle style = workbook.Styles.Add("CustomStyle");

//Set Color
style.Color = Color.Red;

//Set default style
worksheet.SetDefaultColumnStyle(2,style);

//Get default style
CellStyle defaultStyle = worksheet.GetDefaultColumnStyle(2);

//Set color
defaultStyle.Color = Color.Blue;
worksheet.SetDefaultColumnStyle(3, defaultStyle);

//Save to file
workbook.SaveToFile("GetDefaultColumnStyle.xlsx");
public CellStyle GetDefaultColumnStyle(int columnIndex)

Parameters

columnIndex int

Column index.

Returns

CellStyle

Default column style or null if default style is not exists.

GetDefaultRowStyle(int)

Returns default row style. The following code illustrates how to get default row style:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create style
CellStyle style = workbook.Styles.Add("CustomStyle");

//Set Color
style.Color = Color.Red;

//Set default style
worksheet.SetDefaultRowStyle(2,style);

//Get default style
CellStyle defaultStyle = worksheet.GetDefaultRowStyle(2);

//Set color
defaultStyle.Color = Color.Blue;
worksheet.SetDefaultRowStyle(3, defaultStyle);

//Save to file
workbook.SaveToFile("GetDefaultColumnStyle.xlsx");
public CellStyle GetDefaultRowStyle(int rowIndex)

Parameters

rowIndex int

Row index.

Returns

CellStyle

Default row style or null if default style is not set.

GetFreezePanes(out int, out int)

Get Freezes panes cell's index in the worksheet.

public void GetFreezePanes(out int rowIndex, out int columnIndex)

Parameters

rowIndex int

Row index. 0 means no freeze row

columnIndex int

Column index. 0 means no freeze column

GetIntersectRanges(CellRange, CellRange)

Intersects two ranges.

public CellRange GetIntersectRanges(CellRange range1, CellRange range2)

Parameters

range1 CellRange

First range.

range2 CellRange

Second range.

Returns

CellRange

Intersection of two ranges

Merge(CellRange, CellRange)

Combines a range of cells into a single cell.

public CellRange Merge(CellRange range1, CellRange range2)

Parameters

range1 CellRange

First range.

range2 CellRange

Second range.

Returns

CellRange

Merged ranges

Move(CellRange, CellRange)

Move data from source range to destination range.

public void Move(CellRange sourceRange, CellRange destRange)

Parameters

sourceRange CellRange

Source range.

destRange CellRange

Destination range.

Move(CellRange, CellRange, bool, bool) 

public void Move(CellRange sourceRange, CellRange destRange, bool updateReference, bool copyStyle)

Parameters

sourceRange CellRange
destRange CellRange
updateReference bool
copyStyle bool

OnDispose()

Method which can be overriden by users to take any specific actions when object is disposed.

protected override void OnDispose()

RemoveMergedCells(CellRange) 

public void RemoveMergedCells(CellRange range)

Parameters

range CellRange

RemovePicture(IPictureShape)

Remove picture from this worksheet.

public void RemovePicture(IPictureShape picture)

Parameters

picture IPictureShape

A pictureshape

RemovePicture(int)

Remove picture from this worksheet.

public void RemovePicture(int index)

Parameters

index int

Picture ID

RemovePicture(string)

Remove picture from this worksheet.

public void RemovePicture(string picturename)

Parameters

picturename string

Picture name

RemoveRange(CellRange)

Removes range from list.

public void RemoveRange(CellRange range)

Parameters

range CellRange

Specified range.

RemoveRange(int, int)

Removes range from list.

public void RemoveRange(int rowIndex, int columnIndex)

Parameters

rowIndex int

Row index.

columnIndex int

Column index.

SetActiveCell(CellRange)

Sets active cell

public void SetActiveCell(CellRange range)

Parameters

range CellRange

Cell to activate.

SetDefaultColumnStyle(int, CellStyle)

Sets default style for column. The following code illustrates how to set the default style for a column:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create style
CellStyle style = workbook.Styles.Add("CustomStyle");

//Set Color
style.Color = Color.Red;

//Set default style
worksheet.SetDefaultColumnStyle(2, style);

//Save to file
workbook.SaveToFile("SetDefaultColumnStyle.xlsx");
public void SetDefaultColumnStyle(int columnIndex, CellStyle defaultStyle)

Parameters

columnIndex int

Column index.

defaultStyle CellStyle

Default style.

SetDefaultColumnStyle(int, int, CellStyle)

Sets default style for column. The following code illustrates how to set the default style for columns:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create style
CellStyle style = workbook.Styles.Add("CustomStyle");

//Set Color
style.Color = Color.Red;

//Set default style
worksheet.SetDefaultColumnStyle(2, 5, style);

//Save to file
workbook.SaveToFile("SetDefaultColumnStyle.xlsx");
public void SetDefaultColumnStyle(int firstColumnIndex, int lastColumnIndex, CellStyle defaultStyle)

Parameters

firstColumnIndex int

First column index.

lastColumnIndex int

Last column index.

defaultStyle CellStyle

Default style.

SetDefaultRowStyle(int, CellStyle)

Sets default style for row. The following code illustrates how to set the default style for a row:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create style
CellStyle style = workbook.Styles.Add("CustomStyle");

//Set Color
style.Color = Color.Red;

//Set default style
worksheet.SetDefaultRowStyle(2, style);

//Save to file
workbook.SaveToFile("SetDefaultRowStyle.xlsx");
public void SetDefaultRowStyle(int rowIndex, CellStyle defaultStyle)

Parameters

rowIndex int

Row index.

defaultStyle CellStyle

Default style.

SetDefaultRowStyle(int, int, CellStyle)

Sets default style for row. The following code illustrates how to set the default style for rows:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create style
CellStyle style = workbook.Styles.Add("CustomStyle");

//Set Color
style.Color = Color.Red;

//Set default style
worksheet.SetDefaultRowStyle(2, 5, style);

//Save to file
workbook.SaveToFile("SetDefaultRowStyle.xlsx");
public void SetDefaultRowStyle(int firstRowIndex, int lastRowIndex, CellStyle defaultStyle)

Parameters

firstRowIndex int

First row index.

lastRowIndex int

Last row index.

defaultStyle CellStyle

Default style.