How to: Access the Spreadsheet Editor Controls

• Dec 07, 2021
• 5 minutes to read

This topic describes how to access and customize the following controls in XAF applications with the Office Module:

Platform	Control	Purpose
WinForms	SpreadsheetControl	Used to show Spreadsheet documents
ASP.NET Web Forms	ASPxSpreadsheet	Used to show Spreadsheet documents
ASP.NET Web Forms	ASPxUploadControl	Used to upload Spreadsheet documents to a server

Note

XAF does not support the Spreadsheet editor for ASP.NET Core Blazor applications.

Access the WinForms-Specific Control (SpreadsheetControl)

Follow the steps below to access the SpreadsheetControl and 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, access the SpreadsheetControl and subscribe to its DocumentLoaded event.

4. In the DocumentLoaded event handler, use the SetSize method 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 WinSpreadsheetController : 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;
           spreadsheetControl.DocumentLoaded += SpreadsheetControl_DocumentLoaded;
       }
       private void SpreadsheetControl_DocumentLoaded(object sender, System.EventArgs e) {
           SpreadsheetControl spreadsheetControl = (SpreadsheetControl)sender;
           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 WinSpreadsheetController
       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
           AddHandler spreadsheetControl.DocumentLoaded, AddressOf SpreadsheetControl_DocumentLoaded
       End Sub
       Private Sub SpreadsheetControl_DocumentLoaded(ByVal sender As Object, ByVal e As System.EventArgs)
           Dim spreadsheetControl As SpreadsheetControl = DirectCast(sender, SpreadsheetControl)
           Dim worksheet As Worksheet = spreadsheetControl.ActiveWorksheet
           spreadsheetControl.WorksheetDisplayArea.SetSize(worksheet.Index, 5, 10)
       End Sub
   End Class
   

Access ASP.NET Web Forms-Specific Controls

Access the ASPxSpreadsheet Control

Follow the steps below to access the ASPxSpreadsheet control and restrict the number of visible rows and columns in a worksheet.

1. Create a custom ViewController in the ASP.NET Web Forms module project.
2. In the overridden OnActivated method, handle the ASPxSpreadsheetPropertyEditor.ControlCreated event.

3. In the event handler, use the SetSize method to specify the number of visible rows and columns.

   C#

   using System;
   using DevExpress.ExpressApp;
   using DevExpress.ExpressApp.Office.Web;
   using DevExpress.Web.ASPxSpreadsheet;
   // ...
   public class WebSpreadsheetController : ViewController<DetailView> {
       protected override void OnActivated() {
           base.OnActivated();
           foreach (ASPxSpreadsheetPropertyEditor editor in View.GetItems<ASPxSpreadsheetPropertyEditor>()) {
               if (editor.ASPxSpreadsheetControl != null) {
                   editor.ASPxSpreadsheetControl.Load += SpreadsheetControl_Load;;
               }
               else {
                   editor.ControlCreated += Editor_ControlCreated;
               }
           }
       }
       void Editor_ControlCreated(object sender, EventArgs e) {
           ASPxSpreadsheet spreadsheet = ((ASPxSpreadsheetPropertyEditor)sender).ASPxSpreadsheetControl;
           spreadsheet.Load += SpreadsheetControl_Load;
       }
       void SpreadsheetControl_Load(object sender, EventArgs e) {
           ASPxSpreadsheet spreadsheetControl = (ASPxSpreadsheet)sender;
           spreadsheetControl.WorksheetDisplayArea.SetSize(0, 5, 10);
       }
   }
   

   VB.NET

   Imports System
   Imports DevExpress.ExpressApp
   Imports DevExpress.ExpressApp.Office.Web
   Imports DevExpress.Web.ASPxSpreadsheet
   ' ...
   Public Class WebSpreadsheetController
       Inherits ViewController(Of DetailView)
       Protected Overrides Sub OnActivated()
           MyBase.OnActivated()
           For Each editor As ASPxSpreadsheetPropertyEditor In View.GetItems(Of ASPxSpreadsheetPropertyEditor)()
               If editor.ASPxSpreadsheetControl IsNot Nothing Then
                   AddHandler editor.ASPxSpreadsheetControl.Load, AddressOf SpreadsheetControl_Load
               Else
                   AddHandler editor.ControlCreated, AddressOf Editor_ControlCreated
               End If
           Next editor
       End Sub
       Private Sub Editor_ControlCreated(ByVal sender As Object, ByVal e As EventArgs)
           Dim spreadsheet As ASPxSpreadsheet = DirectCast(sender, ASPxSpreadsheetPropertyEditor).ASPxSpreadsheetControl
           AddHandler spreadsheet.Load, AddressOf SpreadsheetControl_Load
       End Sub
       Private Sub SpreadsheetControl_Load(ByVal sender As Object, ByVal e As EventArgs)
           Dim spreadsheetControl As ASPxSpreadsheet = DirectCast(sender, ASPxSpreadsheet)
           spreadsheetControl.WorksheetDisplayArea.SetSize(0, 5, 10)
       End Sub
   End Class
   

Access the ASPxUploadControl

Follow the steps below to access the ASPxUploadControl and set the maximum size of the file to be uploaded on a server.

1. Create a custom ViewController in the ASP.NET Web Forms module project.
2. In the overridden OnActivated method, handle the ASPxSpreadsheetPropertyEditor.ControlCreated event.
3. In the event handler, use the MaxFileSize method to specify the maximum size of the file to be uploaded on a server.

C#

using DevExpress.ExpressApp;
using DevExpress.ExpressApp.Office.Web;
using DevExpress.Web;
using System;
// ...
public class WebSpreadsheetUploadController : ViewController<DetailView> {
    protected override void OnActivated() {
        base.OnActivated();
        foreach (ASPxSpreadsheetPropertyEditor editor in View.GetItems<ASPxSpreadsheetPropertyEditor>()) {
            if (editor.ASPxUploadControl != null) {
                CustomizeSpreadsheetUploadControl(editor.ASPxUploadControl);
            }
            else {
                editor.ControlCreated += Editor_ControlCreated;
            }
        }
    }
    private void Editor_ControlCreated(object sender, EventArgs e) {
        ASPxUploadControl spreadsheetUploadControl = ((ASPxSpreadsheetPropertyEditor)sender).ASPxUploadControl;
        CustomizeSpreadsheetUploadControl(spreadsheetUploadControl);
    }
    private void CustomizeSpreadsheetUploadControl(ASPxUploadControl uploadControl) {
        uploadControl.ValidationSettings.MaxFileSize = 104857600;
    }
    protected override void OnDeactivated() {
        base.OnDeactivated();
        foreach (ASPxSpreadsheetPropertyEditor editor in View.GetItems<ASPxSpreadsheetPropertyEditor>()) {
            editor.ControlCreated -= Editor_ControlCreated;
        }
    }
}

VB.NET

Imports DevExpress.ExpressApp
Imports DevExpress.ExpressApp.Office.Web
Imports DevExpress.Web
Imports System
' ...
Public Class WebSpreadsheetUploadController
    Inherits ViewController(Of DetailView)
    Protected Overrides Sub OnActivated()
        MyBase.OnActivated()
        For Each editor As ASPxSpreadsheetPropertyEditor In View.GetItems(Of ASPxSpreadsheetPropertyEditor)()
            If editor.ASPxUploadControl IsNot Nothing Then
                CustomizeSpreadsheetUploadControl(editor.ASPxUploadControl)
            Else
                AddHandler editor.ControlCreated, AddressOf Editor_ControlCreated
            End If
        Next editor
    End Sub
    Private Sub Editor_ControlCreated(ByVal sender As Object, ByVal e As EventArgs)
        Dim spreadsheetUploadControl As ASPxUploadControl = DirectCast(sender, ASPxSpreadsheetPropertyEditor).ASPxUploadControl
        CustomizeSpreadsheetUploadControl(spreadsheetUploadControl)
    End Sub
    Private Sub CustomizeSpreadsheetUploadControl(ByVal uploadControl As ASPxUploadControl)
        uploadControl.ValidationSettings.MaxFileSize = 10485
    End Sub
    Protected Overrides Sub OnDeactivated()
        MyBase.OnDeactivated()
        For Each editor As ASPxSpreadsheetPropertyEditor In View.GetItems(Of ASPxSpreadsheetPropertyEditor)()
            RemoveHandler editor.ControlCreated, AddressOf Editor_ControlCreated
        Next editor
    End Sub
End Class