Working With Spreadsheet in Windows Forms Spreadsheet

9 Oct 202311 minutes to read

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

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 by 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.

By using 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;

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

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 the 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 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 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

In Spreadsheet, to update the cell value and formula programmatically, SetCellValue method of SpreadsheetGrid should be invoked 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 which specifies the possible directions to clear the cell formats, content, comments,conditional format,data validation or 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 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 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 Spreadsheet, popup windows are used to display the options like copy paste option, fill series option, etc. which will be closed automatically on certain actions. However you can also able to 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
    }
}