Rows and Columns in WPF Spreadsheet (SfSpreadsheet)

23 Jul 20266 minutes to read

This section explains the operations related to rows and columns in SfSpreadsheet.

Insert Rows and Columns

SfSpreadsheet lets you dynamically insert 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)
{
}

Delete Rows and Columns

SfSpreadsheet lets you delete 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_ColumnsInserted(object sender, GridRangeInsertedEventArgs e)
{
}

Hide Rows and Columns

SfSpreadsheet lets you hide rows and columns by calling 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);

Unhide Rows and Columns

You can unhide rows/columns in SfSpreadsheet by calling 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

SfSpreadsheet provides support to adjust the row height and column width. The adjusted row height and column width can also be imported from Excel.

//For setting RowHeight for 4th Row (1-based index; value is in pixels)
spreadsheet.ActiveGrid.SetRowHeight(4, 4, 30);
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Row(4), true);

//For setting ColumnWidth for 5th Column (1-based index; value is in pixels)
spreadsheet.ActiveGrid.SetColumnWidth(5, 5, 22);
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Col(5), true);

NOTE

If you insert/delete or hide/unhide rows/columns inside a Grouping, the RefreshOutlines method must be invoked to refresh the group outlines.

Freeze Rows and Columns

SfSpreadsheet provides support for freezing 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;

Unfreeze Rows and Columns

SfSpreadsheet provides support to unfreeze the previously frozen panes in the worksheet.

//Unfreeze panes

//To unfreeze the previously frozen rows and columns
spreadsheet.Workbook.ActiveSheet.RemovePanes();
spreadsheet.ActiveGrid.FrozenRows = 1;
spreadsheet.ActiveGrid.FrozenColumns = 1;

Auto Fit Rows and Columns

SfSpreadsheet lets you fit rows and columns to 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));
}

See Also