Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Collapse]Visual Elements
     Workbook
     Worksheet
    [Expand]Cells and Cell Ranges
     Rows and Columns
     Formulas
     Name Box
     Pictures
    [Expand]Dialogs
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Rows and Columns

Cells in a worksheet are organized into 1,048,576 rows (Worksheet.Rows) and 16,384 columns (Worksheet.Columns). Each row is numbered (1, 2, 3, ...) and each column is lettered (A, B, C, ...) if the A1 reference style is used, or numbered (1, 2, 3, ...) if the R1C1 reference style is specified. Row and column headers are displayed at the left and at the top of a worksheet, respectively. You can hide or show these headers using the SpreadsheetViewOptions.ShowColumnHeaders, SpreadsheetViewOptions.ShowRowHeaders or WorksheetView.ShowHeadings property. To enable end-users to change the visibility of row and column headers, provide the Show Ribbon page group for the SpreadsheetControl. For an example on how to provide a Ribbon UI for the SpreadsheetControl, see the Getting Started topic.

By default, the SpreadsheetControl displays all columns and rows, but you can specify the number of columns and rows available for display and edit via the WorksheetDisplayArea.SetSize method. This method allows you to restrict the number of visible columns and rows for the specified worksheet.

The SpreadsheetControl also allows you to customize the appearance of row and column headers. For example, you can specify the height of column headers and width of row headers using the SpreadsheetViewOptions.ColumnHeaderHeight and SpreadsheetViewOptions.RowHeaderWidth property, respectively. You can also perform custom painting of headers by handling the SpreadsheetControl.CustomDrawRowHeader, SpreadsheetControl.CustomDrawRowHeaderBackground, SpreadsheetControl.CustomDrawColumnHeader and SpreadsheetControl.CustomDrawColumnHeaderBackground events. Use the properties provided by the CustomDrawObjectEventsArgs base class to paint text, graphic primitives or images. The image below shows a document with custom row and column headers.

Expanded End-User Capabilities

To enable end-users to modify rows and columns in a worksheet, add a set of Ribbon pages to the SpreadsheetControl.

The table below lists the basic row and column operations available to end-users via Ribbon pages (or the spreadsheet context menu) and the corresponding API methods and properties.

Action

End-user capabilities

API methods/properties

Insert a row

  • On the Home tab, in the Cells group, click the Insert button and select the Insert Sheet Rows item.

  • Right-click a cell within the selected row(s) and click the Insert item in the context menu.

Row.Insert

RowCollection.Insert

Worksheet.InsertCells

How to: Add a New Row or Column to a Worksheet

Insert a column

  • On the Home tab, in the Cells group, click the Insert button and select the Insert Sheet Columns item from the drop-down list.

  • Right-click a cell within the selected column(s) and click the Insert item in the context menu.

Column.Insert

ColumnCollection.Insert

Worksheet.InsertCells

How to: Add a New Row or Column to a Worksheet

Delete rows and columns

  • On the Home tab, in the Cells group, click the Delete button and select the Delete Sheet Rows or Delete Sheet Columns item.

  • Right-click a cell within the selected column(s) or row(s), and then click the Delete item in the context menu.

Row.Delete

RowCollection.Remove

Column.Delete

ColumnCollection.Remove

Worksheet.DeleteCells

How to: Delete a Row or Column from a Worksheet

Hide a column

  • Set the column width to zero by dragging the boundary of the column heading.

  • On the Home tab, in the Cells group, click the Format button, and then select Hide & Unhide | Hide Columns.

  • Right-click the selected column and select the Hide item from the context menu.

  • Press CTRL+0.

Column.Visible

How to: Show or Hide a Row or Column

Hide a row

  • Set the row height to zero by dragging the boundary of the row heading.

  • On the Home tab, in the Cells group, click the Format button and select Hide & Unhide | Hide Rows.

  • Right-click the selected row and select the Hide item from the context menu.

  • Press CTRL+9.

Row.Visible

How to: Show or Hide a Row or Column

Unhide columns and rows

  • On the Home tab, in the Cells group, click the Format button, and then select Hide & Unhide | Unhide Columns or Unhide Rows.

  • Select and right-click the columns or rows surrounding the hidden columns or rows, and choose the Unhide item from the context menu.

  • Press CTRL+SHIFT+9 to display hidden rows, or CTRL+SHIFT+0 to display hidden columns.

Row.Visible

Column.Visible

How to: Show or Hide a Row or Column

Freeze rows and columns

On the View tab, in the Window group, click the Freeze Panes button to display the drop-down list.

Select one of the following.
  • Freeze Panes - lock multiple columns and/or multiple rows simultaneously.

  • Freeze Top Row - lock the first row only.

  • Freeze First Column - lock the left column only.

A black border appears beneath the frozen row(s) and to the right of the frozen column(s).

Worksheet.FreezeRows

Worksheet.FreezeColumns

Worksheet.FreezePanes

How to: Freeze and Unfreeze Rows and Columns

Unfreeze rows and columns

On the View tab, in the Window group, click the Freeze Panes button and select the Unfreeze Panes item.

Worksheet.UnfreezePanes

How to: Freeze and Unfreeze Rows and Columns

Set column width

  • Drag the right boundary of the column heading to resize the column width.

  • To change the width to automatically fit the content, click the Format button in the Cells group, and then click AutoFit Column Width.

  • To match one column width to another, click the Paste Special button in the Clipboard group within the Home tab.

    In the invoked Paste Special dialog, select the Column widths item.

Worksheet.DefaultColumnWidthInCharacters

Worksheet.DefaultColumnWidthInPixels

Worksheet.DefaultColumnWidth

Column.Width

Column.WidthInCharacters

Column.WidthInPixels

Range.ColumnWidth

Column.AutoFit

ColumnCollection.AutoFit

Range.CopyFrom

How to: Specify Row Height or Column Width

Set row height

  • Drag the lower boundary of the row heading to resize the row height.

  • To change the height to automatically fit the content, click the Format button in the Cells group, and then click AutoFit Row Height.

Worksheet.DefaultRowHeight

Row.Height

Range.RowHeight

Row.AutoFit

RowCollection.AutoFit

How to: Specify Row Height or Column Width

Expanded End-User Restrictions

The SpreadsheetControl allows you to prevent end-users from modifying rows and columns in a worksheet. For example, it may be necessary to restrict end-users from showing or deleting columns or rows that contain formulas. To implement end-user restrictions, use the SpreadsheetControlOptions.Behavior property, which provides access to the restriction settings. The table below lists possible restrictions you can set when working with columns and rows in the SpreadsheetControl.

Restriction

Description

SpreadsheetRowBehaviorOptions.AutoFit Gets or sets whether or not end-users are allowed to use the AutoFit feature for rows.
SpreadsheetRowBehaviorOptions.Delete Gets or sets whether or not end-users are allowed to delete rows.
SpreadsheetRowBehaviorOptions.Hide Gets or sets whether or not end-users are allowed to hide rows.
SpreadsheetRowBehaviorOptions.Insert Gets or sets whether or not end-users are allowed to insert rows.
SpreadsheetRowBehaviorOptions.Resize Gets or sets whether or not end-users are allowed to resize rows.
SpreadsheetRowBehaviorOptions.Unhide Gets or sets whether or not end-users are allowed to unhide rows.
SpreadsheetColumnBehaviorOptions.AutoFit Gets or sets whether or not end-users are allowed to use the AutoFit feature for columns.
SpreadsheetColumnBehaviorOptions.Delete Gets or sets whether or not end-users are allowed to delete columns.
SpreadsheetColumnBehaviorOptions.Hide Gets or sets whether or not end-users are allowed to hide columns.
SpreadsheetColumnBehaviorOptions.Insert Gets or sets whether or not end-users are allowed to insert columns.
SpreadsheetColumnBehaviorOptions.Resize Gets or sets whether or not end-users are allowed to resize columns.
SpreadsheetColumnBehaviorOptions.Unhide Gets or sets whether or not end-users are allowed to unhide columns.
SpreadsheetBehaviorOptions.FreezePanes Gets or sets whether or not end-users are allowed to freeze panes.

Set the desired property to the DocumentCapability.Disabled or DocumentCapability.Hidden value, to disable or hide the corresponding command in the Ribbon UI and the spreadsheet popup menu. You can also prevent the context menu from being displayed, as described in the How to: Customize or Hide the Popup Menu example.

How would you rate this topic?​​​​​​​