Rows and Columns

• Jan 23, 2019
• 7 minutes to read

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. Refer to the How to: Restrict the Number of Visible Rows and Columns in a Worksheet example for details.

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.

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.

• Insert a row
• Insert a column
• Delete rows and columns
• Hide a column
• Hide a row
• Unhide rows and columns
• Freeze rows and columns
• Unfreeze rows and columns
• Set column width
• Set row height

 

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

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.