Working with SfSpreadsheet in WPF Spreadsheet (SfSpreadsheet)

23 Jul 202611 minutes to read

This section explains how to access the worksheet, grid, and the events associated with SfSpreadsheet.

Accessing the worksheet

A workbook is an Excel document in SfSpreadsheet. It is an object that exposes the IWorkbook interface. The currently loaded workbook in SfSpreadsheet can be accessed by using the Workbook property of SfSpreadsheet.

A workbook consists of one or more worksheets stored within the worksheet collection. The worksheets in the collection can be accessed in the following ways:

//By specifying the index,
spreadsheet.Workbook.Worksheets[0]

//By specifying the sheet name,
spreadsheet.Workbook.Worksheets["Sheet1"]

//Access the Active worksheet,
spreadsheet.ActiveSheet

For more information regarding working with worksheets, refer to the XlsIO UG link.

NOTE

The ActiveGrid and ActiveSheet properties can be accessed only after the WorkbookLoaded event of SfSpreadsheet is triggered.

Accessing the grid

Each worksheet in the workbook is loaded into the view as SpreadsheetGrid in SfSpreadsheet.

When the workbook is loaded in SfSpreadsheet, the WorkbookLoaded event is invoked, and when the workbook is removed from SfSpreadsheet, the WorkbookUnloaded event is invoked.

When the worksheet is added into SfSpreadsheet, the WorksheetAdded event is invoked, and when the worksheet is removed from SfSpreadsheet, the WorksheetRemoved event is invoked.

You can access the ActiveGrid either in the WorkbookLoaded or WorksheetAdded event.

spreadsheet.WorksheetAdded += spreadsheet_WorksheetAdded;
spreadsheet.WorksheetRemoved += spreadsheet_WorksheetRemoved;

void spreadsheet_WorksheetAdded(object sender, WorksheetAddedEventArgs args)
{

   //Access the Active SpreadsheetGrid and hook the events associated with it.
    var grid = spreadsheet.ActiveGrid;
    grid.CurrentCellActivated += grid_CurrentCellActivated;
}

void spreadsheet_WorksheetRemoved(object sender, WorksheetRemovedEventArgs args)
{

   //Access the Active SpreadsheetGrid and unhook the events associated with it.
    var grid = spreadsheet.ActiveGrid;
    grid.CurrentCellActivated -= grid_CurrentCellActivated;
}

You can also access each SpreadsheetGrid in SfSpreadsheet in two ways: by passing the particular sheet name in the GridCollection or by invoking WorkbookLoaded event of SfSpreadsheet.

By using sheet name

For reference, this example sets the row and column count dynamically for the second sheet in the workbook.

var sheet = spreadsheet.Workbook.Worksheets[1];
spreadsheet.GridCollection[sheet.Name].RowCount = 50;
spreadsheet.GridCollection[sheet.Name].ColumnCount = 12;

By using event

spreadsheet.WorkbookLoaded += spreadsheet_WorkbookLoaded;
spreadsheet.WorkbookUnloaded += spreadsheet_WorkbookUnloaded;

void spreadsheet_WorkbookLoaded(object sender, WorkbookLoadedEventArgs args)
{

  //Hook the events here.

   foreach (var grid in args.GridCollection)
   {
    grid.QueryRange += grid_QueryRange;
   }
}

void spreadsheet_WorkbookUnloaded(object sender, WorkbookUnloadedEventArgs args)
{

  //Unhook the events here.

   foreach (var grid in args.GridCollection)
   {
    grid.QueryRange -= grid_QueryRange;
   }
}

NOTE

SfSpreadsheet supports virtual mode, which lets you dynamically provide data to the grid by handling the QueryRange event. In virtual mode, data is dynamically loaded into the SpreadsheetGrid on demand when the user needs to view the data.

Setting the ActiveSheet programmatically

SfSpreadsheet allows you to set the ActiveSheet programmatically by specifying the sheet name in the SetActiveSheet method of SfSpreadsheet.

spreadsheet.SetActiveSheet("Sheet5");

Accessing the cell or range of cells

SfSpreadsheet allows you to access a single cell or a range of cells in the workbook using the IRange interface.

The following code shows the several ways of accessing a single cell or a range of cells in the worksheet:

// Access a cell by specifying cell address.
var cell = spreadsheet.Workbook.Worksheets[0].Range["A3"];

// Access a cell by specifying cell row and column index.
var cell1 = spreadsheet.Workbook.Worksheets[0].Range[3, 1];

// Access a cell by specifying a user-defined name.
var cell2 = spreadsheet.Workbook.Worksheets[0].Range["Namerange"];

// Accessing a range of cells by specifying the cell's address.
var cell3 = spreadsheet.Workbook.Worksheets[0].Range["A5:C8"];

// Accessing a range of cells by specifying the cell row and column index.
var cell4 = spreadsheet.Workbook.Worksheets[0].Range[15, 1, 15, 3];

For more reference regarding accessing the range, refer to the XlsIO UG.

NOTE

If the user has made any modifications with the XlsIO range in SfSpreadsheet, they should refresh the view to update the modifications in SpreadsheetGrid.

Accessing the value of a cell

SfSpreadsheet allows you to access the value of a cell by using the Value property of IRange. To get the value of the cell along with its format, use the DisplayText property.

// Access a cell value by using the "Value" property.
var cellValue = spreadsheet.Workbook.Worksheets[1].Range["A3"].Value;

// Access a cell value by using the "DisplayText" property.
var displayValue = spreadsheet.Workbook.Worksheets[1].Range[4, 1].DisplayText;

Setting the value or formula to a cell

In SfSpreadsheet, to update the cell value or formula programmatically, invoke the SetCellValue method of SpreadsheetGrid and then invalidate that cell to update the view.

var range = spreadsheet.ActiveSheet.Range[2,2];
spreadsheet.ActiveGrid.SetCellValue(range, "cellValue");
spreadsheet.ActiveGrid.InvalidateCell(2, 2);

Clearing the value or formatting from a cell

SfSpreadsheet allows you to clear the contents of a cell or clear the contents along with its formatting (comments, conditional formats, data validations, and so on).

The following code illustrates the different ways of clearing the value from a cell:

//To clear the contents in the range alone,
spreadsheet.Workbook.Worksheets[0].Range[3, 3].Clear();

//To clear the contents along with its formatting in the range,
spreadsheet.Workbook.Worksheets[0].Range[3, 3].Clear(true);

//To clear the range with the specified ExcelClearOptions,
spreadsheet.Workbook.Worksheets[0].Range[3, 3].Clear(ExcelClearOptions.ClearDataValidations);

NOTE

ExcelClearOptions is an enum which specifies the possible directions to clear the cell formats, content, comments,conditional format,data validation or clear all of them.

Refreshing the view

SfSpreadsheet allows you to invalidate or refresh the view either by specifying a specific range or the full range.

The following code demonstrates the different ways of refreshing the view:

//Invalidates the mentioned cell in the grid,
spreadsheet.ActiveGrid.InvalidateCell(3, 3);

//Invalidates the range,
var range = GridRangeInfo.Cells(5, 4, 6, 7);
spreadsheet.ActiveGrid.InvalidateCell(range);

//Invalidates all the cells in the grid,
spreadsheet.ActiveGrid.InvalidateCells();

//Invalidates the measurement state(layout) of the grid,
spreadsheet.ActiveGrid.InvalidateVisual();

//Invalidates only the cell borders in the range,
var range = GridRangeInfo.Cells(2, 4, 6, 4);
spreadsheet.ActiveGrid.InvalidateCellBorders(range);

Scrolling the grid programmatically

SfSpreadsheet allows the user to scroll the grid to the specified cell by using the ScrollInView method of SpreadsheetGrid.

spreadsheet.ActiveGrid.ScrollInView(new RowColumnIndex(5, 5));

Formula bar

The formula bar is located above the worksheet area of SfSpreadsheet. The formula bar displays the data or formula stored in the active cell.

You can set the visibility state of the formula bar using the FormulaBarVisibility property of SfSpreadsheet.

<syncfusion:SfSpreadsheet x:Name="spreadsheet" FormulaBarVisibility="Collapsed"/>
spreadsheet.FormulaBarVisibility = System.Windows.Visibility.Collapsed;

Identify whether the workbook is modified or not

The IsCellModified property of WorkbookImpl indicates whether any cell in a workbook has been modified after importing. Because IsCellModified is an internal property, it must be accessed using reflection.

var workbook = spreadsheet.Workbook as WorkbookImpl; 
BindingFlags bindFlags = BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static; 
var value = typeof(WorkbookImpl).GetProperty("IsCellModified", bindFlags).GetValue(workbook);

Suppress message boxes in spreadsheet

In SfSpreadsheet, warning messages and error alerts are displayed while performing certain actions, similar to Excel. If you want to avoid these alerts, set the DisplayAlerts property to false.

//To suppress message boxes in Spreadsheet
spreadsheet.DisplayAlerts = false;

Suspend and resume formula calculation

SfSpreadsheet provides support to suspend the formula calculation and resume it whenever needed, using the SuspendFormulaCalculation and ResumeFormulaCalculation methods.

Resuming the formula calculation triggers a single recalculation pass across all formula cells in the workbook. This is helpful for improving performance when you update the value of many cells, because dependent cells are not recalculated on each individual value change.

//Resumes the automatic formula calculation
spreadsheet.ResumeFormulaCalculation();

//Suspends the automatic formula calculation
spreadsheet.SuspendFormulaCalculation();

Close the popup programmatically

In SfSpreadsheet, popup windows are used to display options like the copy-paste option, the fill series option, and so on, which are closed automatically on certain actions. However, you can also close the popup programmatically by using the ShowHidePopup method of SpreadsheetGrid.

//To close the popup
spreadsheet.ActiveGrid.ShowHidePopup(false);

//To show the closed popup, if needed.
spreadsheet.ActiveGrid.ShowHidePopup(true);

Identify when the active sheet is changed

SfSpreadsheet provides support to identify when the active sheet is changed by using the PropertyChanged event of SfSpreadsheet, as shown below.

Spreadsheet.PropertyChanged += Spreadsheet_PropertyChanged;

void Spreadsheet_PropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
{

    // When the active worksheet in the workbook changes.

    if (e.PropertyName == "ActiveSheet")
    {

        //Implement code
    }
}

See also