Working with Spreadsheet in UWP Spreadsheet (SfSpreadsheet)

23 Jul 202611 minutes to read

This section explains accessing the Worksheet, Grid, and the events associated with them.

Accessing the Worksheet

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

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

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

//By Specifying the sheet name as,
spreadsheet.Workbook.Worksheets["sheet1"]

//Access the Active worksheet as,
spreadsheet.ActiveSheet

For more information regarding working with worksheets, you can refer the XlsIO UG link

NOTE

ActiveGrid and ActiveSheet property 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 the SfSpreadsheet, the WorkbookLoaded Event is invoked, and when the workbook is unloaded from the SfSpreadsheet, the WorkbookUnloaded Event is invoked.

When the worksheet is added to the SfSpreadsheet, the WorksheetAdded Event is invoked, and when the worksheet is removed from the SfSpreadsheet, the WorksheetRemoved Event is invoked.

Hence 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 the SfSpreadsheet, either by passing the sheet name in the GridCollection or by invoking the WorkbookLoaded Event of SfSpreadsheet.

By using Sheet Name

The following 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 an event, QueryRange, for example. In virtual mode, data will be dynamically loaded into the SpreadsheetGrid on demand or when users need 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 range of cells in the workbook using the IRange interface.

The following code shows several ways of accessing a single cell or 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"];

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

// Access 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 XlsIO UG.

NOTE

If the user has made any modifications with XlsIO range in SfSpreadsheet, then 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 Value property of IRange and to get the value of the cell along with its format, DisplayText property can be used.

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

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

Setting the value or formula to a cell

In SfSpreadsheet, to update a 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 delete the contents of a cell, or delete the contents along with its formatting (comments, Conditional formats, etc.).

The following code illustrates the different ways of deleting 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 specified ExcelClearOptions,
spreadsheet.Workbook.Worksheets[0].Range[3, 3].Clear(ExcelClearOptions.ClearDataValidations);

NOTE

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

Refreshing the view

SfSpreadsheet allows you to invalidate or refresh the view either by specifying the specific range or 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 grid,
spreadsheet.ActiveGrid.InvalidateVisual();

//Invalidates 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 into mentioned cell, by using ScrollInView method of SpreadsheetGrid.

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

Formula Bar

The Formula Bar is located above the worksheet area of the SfSpreadsheet. The formula bar displays the data or formula stored in the active cell.
Users can set the visibility state of Formula Bar using FormulaBarVisibility property of SfSpreadsheet.

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

Identify whether the workbook is modified or not

IsCellModified property of WorkbookImpl is used to identify whether any cell has been modified in a workbook after importing. Since it is an internal property, access it using Reflection.

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

Suppress message boxes in Spreadsheet

In Spreadsheet, warning messages and error alerts are displayed while performing actions, similar to Excel. To suppress those alerts, set the DisplayAlerts property to false.

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

Suspend and resume formula calculation

Spreadsheet provides support to suspend the formula calculation and resume it when needed using the SuspendFormulaCalculation and ResumeFormulaCalculation methods.

Resuming formula calculation will recalculate all the formula cells in a workbook. This helps improve performance when you update the values of a large number of cells by skipping the dependent cell recalculation on every cell 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 such as copy-paste options and fill series options, 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 popup again, 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 worksheets in the workbook changed
	
    if(e.PropertyName == "ActiveSheet")
    {

        //Implement code
    }
}