Class Worksheet

Worksheet class

Encapsulates the object that represents a single worksheet.

public class Worksheet

Properties

NameDescription
ActiveCell { get; set; }
AllowSelectingLockedCell { get; set; }Represents if the user is allowed to select locked cells on a protected worksheet.
BackgroundImage { get; set; }Gets and sets worksheet background image.
Cells { get; }
CodeName { get; }Represents worksheet code name.
Columns { get; }
ColumnsCount { get; set; }Gets/Sets Sheet’s columns number .
Comments { get; }
Controls { get; }Gets the cell control collection.
CustomColumnCaption { get; set; }Gets or sets the custom column caption for the worksheet.
CustomRowCaption { get; set; }Gets or sets the custom row caption for the worksheet.
DataMember { get; }Gets the data source that the Worksheet object is displaying data for.
DataSource { get; }Gets the specific list in a DataSource for Worksheet object.
DisplayRightToLeft { get; set; }
DisplayZeros { get; set; }True if zero values are displayed.
FirstVisibleColumn { get; set; }
FirstVisibleRow { get; set; }Represents first visible row index.
FrozenCols { get; set; }Gets or sets Worksheet’s frozen column count. Frozen will start at first column.
FrozenRows { get; set; }Gets or sets Worksheet’s frozen row count. Frozen will start at first row.
GridDesktop { get; }Gets Sheet’s GridDesktop object.
GridlinesVisible { get; set; }Gets or sets a value indicating whether the gridelines are visible.Default is true.
Hyperlinks { get; }Gets the HyperlinkCollection collection.
Index { get; }Gets the index of sheet in the worksheets collection.
IsVeryHidden { get; set; }Indicates wether the sheet is hidden and cannot be shown in the user interface (UI).
IsVisible { get; set; }
MergesCount { get; }Gets the count of the merges
Name { get; set; }Gets or sets the name of the worksheet.
OutlineShown { get; set; }Indicates whether show outline.
Pictures { get; }Gets a Pictures collection.
PivotTables { get; }Gets the pivotTables in the worksheet.
Protected { get; set; }Indicates if the worksheet is protected.
RowFilter { get; }Gets the RowFilterSettings object of the sheet.
Rows { get; }
RowsCount { get; set; }Gets/Sets Sheet’s rows number .
Selected { get; set; }Indicates whether this worksheet is selected when the workbook is opened.
Shapes { get; }Gets a Shape collection.
ShowGridlines { get; set; }
TabColor { get; set; }Represents worksheet tab color.
Validations { get; }Gets the data validation setting collection in the worksheet.
Visible { get; set; }Represents if the worksheet is visible.
Workbook { get; }
Zoom { get; set; }Represents the scaling factor in percent. It should be between 10 and 400.

Methods

NameDescription
AddAutoFilter(int, int, int)Sets the range to which the specified AutoFilter applies.
AddColumn()Adds a new column.
AddCustomFilter(int, string)Add custom filter for the specified row.
AddCustomFilter(int, int, object[], GridFilterOperatorType[])Add custom filter for the specified row range from start row to end row.
AddRow()Adds a new row.
AddSelectedRange(CellRange)Add a new selected range to the worksheet.
AutoFitColumn(int)Autofits the column width.
AutoFitColumn(int, int, int)Autofits the column width.
AutoFitColumns()Autofits all columns in this worksheet.
AutoFitColumns(int, int)Autofits the columns width.
AutoFitColumns(int, int, int, int)Autofits the columns width.
AutoFitRow(int)Autofits the row height.
AutoFitRow(int, int, int)Autofits the row height.
AutoFitRow(int, int, int, int)Autofits row height in a rectangle range.
AutoFitRows()Autofits all rows in this worksheet.
AutoFitRows(int, int)Autofits row height in a range.
AutoFitRowsMerged()Autofits all rows in this worksheet.It will also auto fit row height when the cells is merged in a row.
CalculateFormula(string)Calculates a formula.
CellInMerged(CellLocation)Gets a value whether the specified cell location is in merges.
CellRangeInMerge(CellRange)Gets a value that indicates whether the specified cell range in merges.
ClearComments()Clears all comments in designer spreadsheet.
ClearMerges()Clear all merges.
ClearSelection()Clear the selection in Worksheet.
ColInMerged(int)Gets a value that indicates whether the specified column in merges.
ColInSelection(int)Determines whether the specified column at index is in selected.
Copy(Worksheet)Copies contents and formats from another worksheet.
CreateRange(string, string)create CellRange .
CreateRange(int, int, int, int)create CellRange .
DataBind(object, string)Binds data from data source object with Worksheet object.
DataUnbind()Unbind data from data source object with Worksheet object.
ExportDataTable(int, int, int, int, bool)Exports data in the Cells collection of a Worksheet to a specifed DataTable object.
ExportDataTable(int, int, int, int, GridExportTableOptions)Exports data in the Cells collection to a DataTable object.
ExportDataTable(int, int, int, int, bool, bool)Exports data in the Cells collection of a Worksheet to a new DataTable object.
FilterString(int, string)Sets the filter for the column.notice we shall call AddAutoFilter before calling of filterString The filter criteria string. notice we use comma->"," as split char,so the cell value you want to filter shall not contains with comma filterString(10,“123,456”) means column 10 shall contain 123 or 456, filterString(10,“123”) means column10 shall contain 123 value split with comma,eg. 123,456,789 or abc
FreezePanes(string, int, int)Freezes panes at the specified cell in the worksheet.
FreezePanes(int, int, int, int)Freezes panes at the specified cell in the worksheet.
GetAllSelectedRanges()Gets all selected ranges of this worksheet.
GetCellLocationByClientPoint(Point)Gets the cell location by client coordinates point.
GetColumnCaption(int)Gets the column caption. If the caption is not set, returns empty string.
GetFirstVisibleColumn()Get the first visible column index of sheet view.
GetFirstVisibleRow()Get the first visible row index of sheet view.
GetFocusedCell()Gets the focused cell.
GetFocusedCellLocation()Gets a cell location which is focused.
GetFreezedPanes(out int, out int, out int, out int)Gets the freeze panes.
GetIsReadonly(int, int)Gets whether the cell is readonly.this is an extended attribute of GridWeb ,it will not keep in actual excel file
GetLastSelection()Gets the cell range of last selected.
GetMerge(int)Gets the cell range of the merge at the specified index.
GetMergeByCell(int, int)get the the merge area that contains the cell at row,column location ,return null if all the merge areas does not include this cell location
GroupColumns(int, int)Groups columns.
GroupColumns(int, int, bool, bool)Groups columns.
GroupColumns(int, int, int, bool)Groups columns.
GroupRows(int, int)Groups rows.
GroupRows(int, int, bool)Groups rows.
GroupRows(int, int, bool, bool)Groups rows.
GroupRows(int, int, int, bool)Groups rows.
GroupRows(int, int, int, bool, bool)Groups rows.
ImportData(DataTable, int, int, GridImportTableOptions)Import data from custom data table.
ImportDataTable(DataTable, bool, int, int)Imports a DataTable object into a worksheet.
InSelection(CellLocation)Determines whether the specified cell location is in selected.
InsertColumn(int)Inserts a new column.
InsertRow(int)Inserts a new row.
IsCellFocused()Determines whether the cell is focused.
IsProtected()Indicates if the worksheet is protected.
Merge(CellRange)Merges a specified range of cells into a single cell.
MergeFocused(CellLocation)Gets the index of the focused merge.
MoveTo(int)Moves the sheet to another location in the spreadsheet.
RefreshFilter()Refresh auto filters to hide or unhide the rows.
RemoveAutoFilter()Remove the auto filter of the worksheet.
RemoveColumn(int)Removes a column at the specified index.
RemoveRow(int)Removes a row at the specified index.
ResetFilter(int)reset filter type to none
ResetFilters(int, int)reset filter type to none
RowInMerged(int)Gets a value that indicates whether the specified row in merges.
RowInSelection(int)Determines whether the specified row at index is in selected.
SetAllCellsEditable()Makes all cells editable.this is extended attribute
SetAllCellsReadonly()Makes all cells readonly.this is extended attribute notice this attribute can not keep in actual cell,if you want to keep protect please use setProtect
SetColumnCaption(int, string)Sets the caption for the column.please note this is an extension attribute and can not keep in excel file
SetFirstVisibleColumn(int)Set the first visible column index of sheet view.
SetFirstVisibleRow(int)Set the first visible row index of sheet view.
SetFocusedCell(CellLocation)Sets the cell focus at the specified location.
SetFocusedCell(int, int)Sets the cell focus at the specified column and row.
SetFont(CellRange, Font)Sets font object to cellRange.
SetFontColor(CellRange, Color)Sets font color to cellRange.
SetIsReadonly(int, int, bool)Sets whether the cell is readonly.this is an extended attribute ,it will not keep in actual excel file
SetProtect()Protects worksheet.
SetProtected(CellRange, bool)Sets cellRange whether it is protected.this is an extended method used only in Grid. This method has nothing to do with the cell.Style.CellLocked property It will not take affect after save to excel file.
SetStyle(CellRange, Style)Sets style object to cellRange.
SetStyle(GridCellArea, Style)Sets style object to cellRange.
UnFreezePanes()Unfreezes panes in the worksheet.
UngroupColumns(int, int)Ungroups columns.
UngroupColumns(int, int, bool)Ungroups columns.
UngroupRows(int, int)Ungroups rows.
UngroupRows(int, int, bool)Ungroups rows.
Unmerge(CellLocation)Remove the specified cell location from merges.
Unmerge(CellRange)Unmerges a specified range of cells into a single cell.
Unmerge(int, int)Remove the specified cell row column index from merges.
UnProtect()unProtects worksheet.
static CellIndexToR1C1(int, int)Gets cell r1c1 style name according to its row and column indexes.

Fields

NameDescription
static DEFAULT_COLUMNSCOUNTDefault columns count of worksheet.
static DEFAULT_ROWSCOUNTDefault rows count of worksheet.

See Also