Append/Insert/Delete Operations
To manage rows and columns, the worksheet provides methods for appending, inserting, and removing them:
var sheet = grid.CurrentWorksheet;
// Append rows or columns at the end
sheet.AppendRows(int rowCount);
sheet.AppendCols(int colCount);
// Insert rows or columns at a specified position
sheet.InsertRows(int rowIndex, int rowCount);
sheet.InsertCols(int colIndex, int colCount);
// Delete rows or columns starting from a specified position
sheet.DeleteRows(int rowIndex, int rowCount);
sheet.DeleteCols(int colIndex, int colCount);
In these methods, the first parameter specifies the zero-based index at which the operation begins, and the second parameter indicates the number of rows or columns to be affected.
Alternatively, actions for inserting or removing rows and columns can be utilized, offering capabilities for undoing, redoing, and repeating these operations:
// Insert rows or columns
var insertRowAction = new unvell.ReoGrid.Actions.InsertRowsAction(rowIndex, rowCount);
var insertColAction = new unvell.ReoGrid.Actions.InsertColumnsAction(colIndex, colCount);
// Remove rows or columns
var removeRowAction = new unvell.ReoGrid.Actions.RemoveRowsAction(rowIndex, rowCount);
var removeColAction = new unvell.ReoGrid.Actions.RemoveColumnsAction(colIndex, colCount);
// Execute actions
sheet.DoAction(insertRowAction);
sheet.DoAction(insertColAction);
sheet.DoAction(removeRowAction);
sheet.DoAction(removeColAction);
Setting Row and Column Counts
To modify the number of rows and columns in a worksheet, the following methods are available:
// To resize both rows and columns
sheet.Resize(int rowCount, int colCount);
// To adjust the number of rows or columns individually
sheet.SetRows(6); // Sets the number of rows
sheet.SetCols(5); // Sets the number of columns
Predefined Values
The table below outlines the initial, minimum, and maximum values for various parameters of a worksheet, along with their respective data types:
| Initial | Minimum | Maximum | Data type | |
|---|---|---|---|---|
| Number of Rows | 200 | 1 | 1,048,576 | Paging-indexed two-dimensional array |
| Number of Columns | 100 | 1 | 32,768 | Paging-indexed two-dimensional array |
| Row Height (pixels) | 20 | 0 | 65,535 | ushort |
| Column Width (pixels) | 70 | 0 | 65,535 | ushort |
Adjusting Height and Width
To modify the height of rows or the width of columns, the following methods can be employed:
// To set the height of all rows
sheet.SetRowsHeight(0, grid.RowCount, 10);
// To set the width of all columns
sheet.SetColsWidth(0, grid.ColCount, 10);
Alternatively, to adjust the size of headers with the ability to undo these changes, the following actions can be used:
// To change the height of a range of rows
sheet.DoAction(new SetRowsHeightAction(0, 10, 30));
// To change the width of a range of columns
sheet.DoAction(new SetColumnsWidthAction(0, 10, 30));
The SetRowsHeightAction and SetColumnsWidthAction constructors require specifying the starting index, the number of rows or columns to affect, and the new height or width in pixels, respectively. These actions are part of ReoGrid's undo/redo framework, allowing for more flexible document editing.
Auto row height
ReoGrid will automatically adjust the row height when value changed by editing cell:
Disable auto adjusting
It is possible to change this behavior for each rows:
var rowHeader = sheet.RowHeaders[2];
rowHeader.IsAutoHeight = false;
Auto fit row height and column width
When double clicked on the separators of header, ReoGrid will adjust the row height and column width to fit the largest cell.
Before:
After:
Code to do this:
sheet["A2"] = "This is a long text";
sheet.AutoFitColumnWidth(0, false);
The second argument indicates that whether or not to use action to perform this operation. Using action have the ability to undo by using Undo method or pressing Ctrl+Z.
Hide & Unhide
Like Excel, ReoGrid also support to hide rows or columns, call the following methods to hide/show rows or columns.
sheet.HideRows(int start, int count);
sheet.ShowRows(int start, int count);
sheet.HideColumns(int start, int count);
sheet.ShowColumns(int start, int count);
A hidden row or column will be displayed as a single line.
Note
- Both
HideRowsandHideColumnswill collapse outlines automatically, if any rows belong to a part of outline has been hidden. - Both
ShowRowsandShowColumnswill expand outlines automatically, if any rows belong to a part of outline has been shown.
Example:
Hide rows that grouped by an outline:
The outline will be collapsed automatically.
More details refer to Group & Outline.
Check for hidden headers and cells
Check whether or not a row or column is hidden:
bool Worksheet.IsHiddenRow(int row);
bool Worksheet.IsHiddenColumn(int col);
Check whether or not a cell is on a hidden row or column:
bool Worksheet.IsHiddenCell(int row, int col);
bool Worksheet.IsHiddenCell(string addressOrName);
bool Worksheet.IsHiddenCell(ReoGridCell cellInstance);
// or by call from a cell instance
var cell = sheet.Cells["H8"];
bool hidden = cell.IsHidden;
Get the instances of header
// get worksheet instance from grid control
var sheet = grid.CurrentWorksheet;
// get header instances
var colHeader = sheet.ColumnHeaders[2];
var rowHeader = sheet.RowHeaders[3];
Change column header text
// set text of column header
colHeader.Text = "Product";
Change row header text
sheet.RowHeaders[1].Text = "Header";
Change row header width
It is possible to change the width of row header if you need to display more text:
sheet.RowHeaderWidth = 100;
sheet.RowHeaders[1].Text = "Row Header";
Hide row via header instance
// hide 4th row (zero-based)
rowHeader.IsHidden = true;
Change width or height of header
sheet.RowHeaders[3].HeightInPixel = 30; // height in pixel
Performance difference between instance call and Control API
Some operations could be performed via header instance call as well as control API call, but header instance call will bring a performance issue. For the sample below, it is highly recommended to use the method 2.
Method 1: Call header instance to change height of rows
sheet.RowHeaders[2].HeightInPixel = 30;
sheet.RowHeaders[3].HeightInPixel = 30;
sheet.RowHeaders[4].HeightInPixel = 30;
Method 2: Call control API to change height of rows
sheet.SetRowsHeight(2, 3, 30); // from index 2, rows is 3
The method 2 has a quite of performance benefit than the method 1.
Column Cells Type
It is possible to specify a default cells type for an entire column.
// ColumnHeaders property returns an instance of column header
// it accepts a number of index or an address code as string to locate a column
sheet.ColumnHeaders["A"].DefaultCellBody = typeof(unvell.ReoGrid.CellTypes.CheckBoxCell);
// set horizontal alignment for all cells on this column to center
sheet.ColumnHeaders["A"].Style.HorizontalAlign = ReoGridHorAlign.Center;
// give check box a small padding (2 pixels)
sheet.ColumnHeaders["A"].Style.Padding = new System.Windows.Forms.Padding(2);
Only set the cells type will not cause any cells displayed, cells body will be displayed if cells data is filled.
sheet["A1:A5"] = new object[] { false, true, false, false, true };
Check box cell can accept a data as bool value, the grid shows as below:
