Rows and Columns in Windows Forms Spreadsheet

23 Jul 20265 minutes to read

This section explains operations related to rows and columns in the Spreadsheet

Inserting rows and columns

Spreadsheet provides support for dynamically inserting rows and columns into a worksheet.

//For Inserting Rows
spreadsheet.ActiveSheet.InsertRow(2, 3);
spreadsheet.ActiveGrid.Model.InsertRows(2, 3);

//For Inserting Columns
spreadsheet.ActiveSheet.InsertColumn(3, 2);
spreadsheet.ActiveGrid.Model.InsertColumns(3, 2);

Events

Below events of SpreadsheetGridModel are triggered while inserting the rows and columns.

//To notify when rows are inserted
spreadsheet.ActiveGrid.Model.RowsInserted += Model_RowsInserted;

void Model_RowsInserted(object sender, GridRangeInsertedEventArgs e)
{
}

//To notify when Columns are inserted
spreadsheet.ActiveGrid.Model.ColumnsInserted += Model_ColumnsInserted;

void Model_ColumnsInserted(object sender, GridRangeInsertedEventArgs e)
{
}

Deleting rows and columns

Spreadsheet provides support for deleting rows and columns from a worksheet,

//For Deleting Rows
spreadsheet.ActiveSheet.DeleteRow(5, 2);
spreadsheet.ActiveGrid.Model.RemoveRows(5, 2);

//For Deleting Columns
spreadsheet.ActiveSheet.DeleteColumn(3, 2);
spreadsheet.ActiveGrid.Model.RemoveColumns(3, 2);

Events

Below events of SpreadsheetGridModel are triggered while deleting the rows and columns.

//To notify when rows are deleted
spreadsheet.ActiveGrid.Model.RowsRemoved += Model_RowsRemoved;

void Model_RowsRemoved(object sender, GridRangeRemovedEventArgs e)
{
}

//To notify when columns are deleted

spreadsheet.ActiveGrid.Model.ColumnsRemoved += Model_ColumnsRemoved;

void Model_ColumnsRemoved(object sender, GridRangeRemovedEventArgs e)
{
}

Hiding rows and columns

Rows and columns can be hidden using the HideRow and HideColumn methods.

//For Hiding Rows
spreadsheet.ActiveSheet.HideRow(5);
spreadsheet.ActiveGrid.RowHeights.SetHidden(5, 5, true);

//For Hiding Columns
spreadsheet.ActiveSheet.HideColumn(4);
spreadsheet.ActiveGrid.ColumnWidths.SetHidden(4, 4, true);

Unhiding rows and columns

The rows and columns in the Spreadsheet can be unhidden using the ShowRow and ShowColumn methods.

//For Unhiding Rows
spreadsheet.ActiveSheet.ShowRow(5, true);
spreadsheet.ActiveGrid.RowHeights.SetHidden(5, 5, false);

//For Unhiding Columns
spreadsheet.ActiveSheet.ShowColumn(4, true);
spreadsheet.ActiveGrid.ColumnWidths.SetHidden(4, 4, false);

Row Height and Column Width

Spreadsheet provides support to adjust the row height and column width. And also can import the adjusted row height and column width from Excel. Spreadsheet provides support to fit the row and column based on their contents.

//For setting RowHeight for 4th Row

spreadsheet.ActiveGrid.SetRowHeight(4, 4, 30);
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Row(4), true);

//For setting ColumnWidth for 5th Column
spreadsheet.ActiveGrid.SetColumnWidth(5, 5, 22);
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Col(5), true);

NOTE

When you insert, delete, hide, or unhide rows or columns inside a Grouping, the RefreshOutlines method must be invoked to refresh/update the Outlines of the Group.

Freezing rows and columns

Spreadsheet provides support for Freeze panes to keep an area of a worksheet visible while you scroll to another area of the worksheet.

//Freeze panes

//To Freeze 4 rows and 4 columns
spreadsheet.Workbook.ActiveSheet.Range[4, 4].FreezePanes();
spreadsheet.ActiveGrid.FrozenRows = 5;
spreadsheet.ActiveGrid.FrozenColumns = 5;

Unfreezing rows and columns

Spreadsheet provides support to unfreeze the freeze panes in the worksheet of Spreadsheet.

//Unfreeze panes

//To Unfreeze 4 rows and 4 columns
spreadsheet.Workbook.ActiveSheet.RemovePanes();
spreadsheet.ActiveGrid.FrozenRows = 1;
spreadsheet.ActiveGrid.FrozenColumns = 1;

Auto fit rows and columns

Spreadsheet provides support to fit the rows or columns based on their content at run time.

You can fit the rows/columns by calling AutoFitRows and AutoFitColumns methods of XlsIO’s IRange. Also set the adjusted row height and column width into the grid by using SetRowHeight and SetColumnWidth methods of SpreadsheetGrid.

//To AutoFit a single column,

spreadsheet.ActiveSheet.AutofitColumn(2);
spreadsheet.ActiveGrid.SetColumnWidth(2,2,spreadsheet.ActiveSheet.GetColumnWidthInPixels(2)); 

//To AutoFit multiple columns,

spreadsheet.ActiveSheet["A1:D100"].AutofitColumns();

for(int i = 1; i <= 4 ; i++)
{
   spreadsheet.ActiveGrid.SetColumnWidth(i,i,spreadsheet.ActiveSheet.GetColumnWidthInPixels(i));
}

//To AutoFit a single row,
spreadsheet.ActiveSheet.AutofitRow(3);
spreadsheet.ActiveGrid.SetRowHeight(3,3,spreadsheet.ActiveSheet.GetRowHeightInPixels(3)); 

//To AutoFit multiple rows,
spreadsheet.ActiveSheet["B1:B5"].AutofitRows();

for(int i = 1; i <= 5 ; i++)
{
   spreadsheet.ActiveGrid.SetRowHeight(i,i,spreadsheet.ActiveSheet.GetRowHeightInPixels(i));
}