Access the Spreadsheet Control

• Jun 17, 2019
• 2 minutes to read

This topic describes how to access and customize the SpreadsheetControl object in WinForms applications that use the Office Module. The steps below demonstrate how to restrict the number of visible rows and columns in a worksheet.

1. Create a custom ViewController in the WinForms module project.
2. In the overridden OnActivated method, get the SpreadsheetServiceController and subscribe to its CustomizeSpreadsheetControl event.

3. In the CustomizeSpreadsheetControl‘s handler, use the SetSize to specify the number of visible rows and columns for an active worksheet.

   C#

   using DevExpress.ExpressApp;
   using DevExpress.ExpressApp.Office.Win;
   using DevExpress.Spreadsheet;
   using DevExpress.XtraSpreadsheet;
   // ...
   public class CustomSpreadsheetController : ViewController {
       protected override void OnActivated() {
           base.OnActivated();
           SpreadsheetServiceController controller = Frame.GetController<SpreadsheetServiceController>();
           if(controller != null) {
               controller.CustomizeSpreadsheetControl += Controller_CustomizeSpreadsheetControl;
           }
       }
       private void Controller_CustomizeSpreadsheetControl(object sender, CustomizeSpreadsheetEventArgs e) {
           SpreadsheetControl spreadsheetControl = ((SpreadsheetPropertyEditor)sender).SpreadsheetControl;
           Worksheet worksheet = spreadsheetControl.ActiveWorksheet;
           spreadsheetControl.WorksheetDisplayArea.SetSize(worksheet.Index, 5, 10);
       }
   }
   

   VB.NET

   Imports DevExpress.ExpressApp
   Imports DevExpress.ExpressApp.Office.Win
   Imports DevExpress.Spreadsheet
   Imports DevExpress.XtraSpreadsheet
   ' ...
   Public Class CustomSpreadsheetController
       Inherits ViewController
       Protected Overrides Sub OnActivated()
           MyBase.OnActivated()
           Dim controller As SpreadsheetServiceController = Frame.GetController(Of SpreadsheetServiceController)()
   
           If controller IsNot Nothing Then
              AddHandler controller.CustomizeSpreadsheetControl, AddressOf Controller_CustomizeSpreadsheetControl
           End If
       End Sub
       Private Sub Controller_CustomizeSpreadsheetControl(ByVal sender As Object, ByVal e As CustomizeSpreadsheetEventArgs)
           Dim spreadsheetControl As SpreadsheetControl = DirectCast(sender, SpreadsheetPropertyEditor).SpreadsheetControl
           Dim worksheet As Worksheet = spreadsheetControl.ActiveWorksheet
           spreadsheetControl.WorksheetDisplayArea.SetSize(worksheet.Index, 5, 10)
       End Sub
   End Class