Working With Spreadsheet in Windows Forms Spreadsheet

23 Jul 202611 minutes to read

This section explains how to access the Worksheet, Grid, and related events.

Accessing the worksheet

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

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 Spreadsheet is triggered

Accessing the Grid

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

When the workbook is loaded in the Spreadsheet, the WorkbookLoaded Event is invoked and when the workbook is removed from Spreadsheet, the WorkbookUnloaded Event is invoked.

When the worksheet is added into the Spreadsheet, the WorksheetAdded Event is invoked and when the worksheet is removed in the Spreadsheet, 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 the each SpreadsheetGrid in the Spreadsheet either by passing the particular sheet name in the GridCollection or by invoking WorkbookLoaded Event of Spreadsheet.

Using a Sheet Name

For your reference, setting 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;

Using Events

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

Spreadsheet 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

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

spreadsheet.SetActiveSheet("Sheet5");

Accessing the cell or range of cells

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

The following code shows several ways to access 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 cells by specifying user defined name.
var cell2 = spreadsheet.Workbook.Worksheets[0].Range["Namerange"];

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

// Accessing a range of cells by specifying 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 Spreadsheet, then they should refresh the view to update the modifications in SpreadsheetGrid.

Accessing the value of a cell

Spreadsheet 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

To update a cell’s value or formula programmatically in the Spreadsheet, 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

Spreadsheet allows you to delete the contents of a cell or delete the contents along with its formatting(comments,Conditional formats,..) also.

The following code illustrates the different way 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 that specifies the ways to clear cell formats, content, comments, conditional formats, data validation, or to clear all of them.

Refreshing the view

Spreadsheet 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

Spreadsheet allows you to scroll the grid to a 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 the Spreadsheet. 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 Spreadsheet.

spreadsheet.FormulaBarVisibility = true;

Identify whether the workbook is modified or not

IsCellModified property of WorkbookImpl is used to identify whether any cell modified in a workbook or not after importing. Since it is an internal property, access it 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 Spreadsheet, warning messages, error alerts are displayed while performing some actions like Excel. If you want to avoid those alerts, then 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 whenever needed using the SuspendFormulaCalculation and ResumeFormulaCalculation method.

Resuming formula calculation will recalculate all the formula cells in a workbook. This would be helpful to improve the performance when you are updating the value of more number of cells by skipping the dependent cells recalculation on each cell value changed.

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

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

Close the popup programmatically

In the Spreadsheet, popup windows are used to display options such as the copy-paste and fill-series options, which close 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

Spreadsheet provides support to identify when the active sheet is changed by using PropertyChanged event of Spreadsheet like 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
    }
}