Row and Column

Append/Insert/Delete

The following methods could be used to append/insert/remove rows or columns:

var sheet = grid.CurrentWorksheet;

sheet.AppendRows(int rowIndex);
sheet.AppendCols(int colIndex);
sheet.InsertRows(int rowIndex, int rowCount);
sheet.InsertCols(int colIndex, int colCount);
sheet.DeleteRows(int rowIndex, int rowCount);
sheet.DeleteCols(int colIndex, int colCount);

where first int is number of row/column, zero-based decimal number, second int is number of rows to be inserted or removed.

Or use the following actions to insert/remove rows or columns, they provide the ability to undo/redo/repeat these operations.

var action = new unvell.ReoGrid.Actions.InsertRowsAction(int, int);
var action = new unvell.ReoGrid.Actions.InsertColumnsAction(int, int);
var action = new unvell.ReoGrid.Actions.RemoveRowsAction(int, int);
var action = new unvell.ReoGrid.Actions.RemoveColumnsAction(int, int);

sheet.DoAction(action);

Set row and column count

To change number of rows and number of columns, use following methods of worksheet:

sheet.Resize(int rowCount, int colCount);  // resize 

// or use these methods
sheet.SetRows(6);     // only set number of rows
sheet.SetCols(5);     // only set number of columns

23

Run Script

If script execution is available (provided in Full release package), the following script are also available to change worksheet size.

sheet.rows = 200;   // set number of rows to 200
sheet.cols = 100;   // set number of columns to 100

Predefined Values

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 20 pixel 0 65,535 ushort
Column width 70 pixel 0 65,535 ushort

Change height and width

To change row height or column width, use following two methods:

sheet.SetRowsHeight(0, grid.RowCount, 10);
sheet.SetColsWidth(0, grid.ColCount, 10);

38

Or use the following action to do change headers size, they provides the ability to undo the operations.

sheet.DoAction(new SetRowsHeightAction(0, 10, 30));
sheet.DoAction(new SetColumnsWidthAction(0, 10, 30));

39

Auto row height

ReoGrid will automatically adjust the row height when value changed by editing cell:
22

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:

218

After:

219

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.
96

Note

  • Both HideRows and HideColumns will collapse outlines automatically, if any rows belong to a part of outline has been hidden.
  • Both ShowRows and ShowColumns will expand outlines automatically, if any rows belong to a part of outline has been shown.

Example:
58
Hide rows that grouped by an outline:
59
The outline will be collapsed automatically.
60
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";

Custom Header

Change row header text

sheet.RowHeaders[1].Text = "Header";

117

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";

116

Hide row via header instance

// hide 4th row (zero-based)
rowHeader.IsHidden = true;

98

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.
110

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:
111


Next: Range