How to: Customize the Spreadsheet Editors

• Oct 18, 2022
• 4 minutes to read

Note

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

In the Model Editor

This section shows how to use the Model Editor to customize the Spreadsheet Property Editor in a WinForms or ASP.NET Web Forms project.

Show or Hide the Formula Bar

The SpreadsheetPropertyEditor and ASPxSpreadsheetPropertyEditor display the Spreadsheet control with a Formula Bar (WinForms / ASP.NET Web Forms).

Formula Bar in a WinForms application

Formula Bar in an ASP.NET Web Forms application

In the Model Editor, navigate to the Views | <DetailView> | Items | <PropertyEditor> node and specify the EnableFormulaBar property to show or hide the Formula Bar.

Specify MenuManagerType for a Detail View

Note that the SpreadsheetPropertyEditor menu manager contains a Ribbon Control or Bars, the ASPxSpreadsheetPropertyEditor menu manager contains only a Ribbon Control.

Navigate to the Views | <DetailView> | Items | <PropertyEditor> node and set the editor’s MenuManagerType (WinForms / ASP.NET Web Forms).

In an ASP.NET Web Forms application, the Ribbon control is shown in One Line Mode. To change the Ribbon mode or show/hide the Ribbon control within code, call the ASPxSpreadsheetPropertyEditor‘s SetRibbonMode method as shown below. Do not use the ASPxSpreadsheet.RibbonMode property in this scenario; otherwise, the Ribbon control may display incorrectly.

C#

using DevExpress.ExpressApp;
using DevExpress.ExpressApp.Office.Web;
using DevExpress.Web.ASPxSpreadsheet;
// ...
public class WebSpreadsheetRibbonController : ObjectViewController<DetailView, Document> {
    protected override void OnActivated() {
        base.OnActivated();
        ASPxSpreadsheetPropertyEditor spreadsheetPropertyEditor = View.FindItem("Data") as ASPxSpreadsheetPropertyEditor;
        if (spreadsheetPropertyEditor != null) {
            SpreadsheetRibbonMode mode = SpreadsheetRibbonMode.Ribbon;
            spreadsheetPropertyEditor.SetRibbonMode(mode);
        }
    }
}

VB.NET

Imports DevExpress.ExpressApp
Imports DevExpress.ExpressApp.Office.Web
Imports DevExpress.Web.ASPxSpreadsheet
' ...
Public Class WebSpreadsheetRibbonController
    Inherits ObjectViewController(Of DetailView, Document)
    Protected Overrides Sub OnActivated()
        MyBase.OnActivated()
        Dim spreadsheetPropertyEditor As ASPxSpreadsheetPropertyEditor = TryCast(View.FindItem("Data"), ASPxSpreadsheetPropertyEditor)
        If spreadsheetPropertyEditor IsNot Nothing Then
            Dim mode As SpreadsheetRibbonMode = SpreadsheetRibbonMode.Ribbon
            spreadsheetPropertyEditor.SetRibbonMode(mode)
        End If
    End Sub
End Class

In Code

Change the Document Storage Format

1. In the WinForms or ASP.NET Web Forms Module project, create a View Controller. If your solution does not contain WinForms-specific module project, add the Controller to the WinForms application project.

2. In the overridden OnActivated method, access the SpreadsheetPropertyEditor or ASPxSpreadsheetPropertyEditor as described in the Ways to Access UI Elements and Their Controls topic.

3. Specify the editor’s DocumentFormat property.

   C#

   using DevExpress.ExpressApp;
   using DevExpress.ExpressApp.Office.Win;
   using DevExpress.Spreadsheet;
   // ...
   public class SpreadsheetDocumentFormatController : ObjectViewController<DetailView, Document> {
       protected override void OnActivated() {
           base.OnActivated();
           SpreadsheetPropertyEditor spreadsheetPropertyEditor = View.FindItem("Data") as SpreadsheetPropertyEditor;
           if (spreadsheetPropertyEditor != null) {
               spreadsheetPropertyEditor.DocumentFormat = DocumentFormat.OpenXml;
           }
       }
   }
   

   VB.NET

   Imports DevExpress.ExpressApp
   Imports DevExpress.ExpressApp.Office.Win
   Imports DevExpress.Spreadsheet
   ' ...
   Public Class SpreadsheetDocumentFormatController
       Inherits ObjectViewController(Of DetailView, Document)
       Protected Overrides Sub OnActivated()
           MyBase.OnActivated()
           Dim spreadsheetPropertyEditor As SpreadsheetPropertyEditor = TryCast(View.FindItem("Data"), SpreadsheetPropertyEditor)
           If spreadsheetPropertyEditor IsNot Nothing Then
               spreadsheetPropertyEditor.DocumentFormat = DocumentFormat.OpenXml
           End If
       End Sub
   End Class
   

Customize the WinForms SpreadsheetPropertyEditor Menu

The SpreadsheetPropertyEditor menu does not display all available toolbars and ribbon tabs. Use the static SpreadsheetPropertyEditor.DefaultSpreadsheetToolbarType property to customize toolbars. The available items are listed in the SpreadsheetToolbarType enumeration.

C#

using DevExpress.ExpressApp.Office.Win;
using DevExpress.XtraSpreadsheet;
// ...
SpreadsheetPropertyEditor.DefaultSpreadsheetToolbarType |= 
    SpreadsheetToolbarType.ChartTools | SpreadsheetToolbarType.TableTools;

VB.NET

Imports DevExpress.XtraSpreadsheet
Imports DevExpress.ExpressApp.Office.Win
' ...
SpreadsheetPropertyEditor.DefaultSpreadsheetToolbarType = _ 
    SpreadsheetPropertyEditor.DefaultSpreadsheetToolbarType Or _
    SpreadsheetToolbarType.ChartTools Or SpreadsheetToolbarType.TableTools

Handle the SpreadsheetMenuManagerController.CustomizeSpreadsheetToolbarType event to change the toolbars and tabs for a specific editor only:

1. In the WinForms Module project, create a View Controller. If your solution does not contain this project, add the Controller to the WinForms application project.

2. Access the SpreadsheetMenuManagerController and subscribe to its CustomizeSpreadsheetToolbarType event in the overridden OnActivated method.

3. In the event handler, specify the CustomizeSpreadsheetToolbarTypeEventArgs.SpreadsheetToolbarType property.

C#

using DevExpress.ExpressApp;
using DevExpress.ExpressApp.Office.Win;
using DevExpress.XtraSpreadsheet;
// ...
public class CustomSpreadsheetController : ViewController {
    protected override void OnActivated() {
        base.OnActivated();
        SpreadsheetMenuManagerController controller = Frame.GetController<SpreadsheetMenuManagerController>();
        if (controller != null) {
            controller.CustomizeSpreadsheetToolbarType += Controller_CustomizeSpreadsheetToolbarType;
        }
    }
    private void Controller_CustomizeSpreadsheetToolbarType(object sender, CustomizeSpreadsheetToolbarTypeEventArgs e) {
        e.SpreadsheetToolbarType |= SpreadsheetToolbarType.ChartTools | SpreadsheetToolbarType.TableTools;
    }
}

VB.NET

Imports DevExpress.ExpressApp
Imports DevExpress.ExpressApp.Office.Win
Imports DevExpress.XtraSpreadsheet
' ...
Public Class CustomSpreadsheetController
    Inherits ViewController
    Protected Overrides Sub OnActivated()
        MyBase.OnActivated()
        Dim controller As SpreadsheetMenuManagerController = Frame.GetController(Of SpreadsheetMenuManagerController)()
        If controller IsNot Nothing Then
            AddHandler controller.CustomizeSpreadsheetToolbarType, AddressOf Controller_CustomizeSpreadsheetToolbarType
        End If
    End Sub
    Private Sub Controller_CustomizeSpreadsheetToolbarType(ByVal sender As Object, ByVal e As CustomizeSpreadsheetToolbarTypeEventArgs)
        e.SpreadsheetToolbarType = e.SpreadsheetToolbarType Or _
        SpreadsheetToolbarType.ChartTools Or SpreadsheetToolbarType.TableTools
    End Sub
End Class

You can also customize the Bars menu at runtime. Your customizations are stored in the user’s model differences.