Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Collapse]Examples
    [Expand]Files
    [Expand]Workbooks
    [Expand]Worksheets
    [Expand]Rows and Columns
    [Expand]Cells
    [Collapse]Formulas
      How to: Use Cell and Worksheet References in Formulas
      How to: Use Names in Formulas
      How to: Create Named Formulas
      How to: Use Functions and Nested Functions in Formulas
      How to: Create Shared Formulas
      How to: Create Array Formulas
      How to: Create a Simple Custom Function
      How to: Use Excel Add-Ins in the Spreadsheet Control
      How to: Create a Custom Function that Returns an Array
    [Expand]Import and Export Data
    [Expand]Data Binding
    [Expand]Mail Merge
    [Expand]Formatting Cells
    [Expand]Conditional Formatting
    [Expand]Group Data
    [Expand]Filter Data
    [Expand]Tables
    [Expand]Pivot Tables
    [Expand]Printing
    [Expand]Pictures
    [Expand]Charts
    [Expand]Protection
    [Expand]Customization
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

How to: Use Excel Add-Ins in the Spreadsheet Control

This example demonstrates how to use Excel add-ins in the SpreadsheetControl. An Excel add-in represents a file containing code that provides additional functionality to Microsoft® Excel® (e.g., custom functions). Add-ins have a variety of practical applications in the business world. For example, the Bloomberg Excel add-in allows end-users to retrieve Bloomberg market, historical and reference data for further analysis in an Excel spreadsheet. The SpreadsheetControl's custom functions provide the capability to use a variety of Excel add-ins in your spreadsheet application.

In particular, this code sample runs Microsoft® Excel® in the background to evaluate a custom function called SPHEREMASS in the add-in macro code and display the result in a spreadsheet application. The custom function contained in the add-in file calculates the mass of a sphere made of a material with a specified density. If the density is not provided, the density of water is used.

Note

You must have Microsoft® Excel® installed on your computer to run the application.

Expanded Implementation

To use Excel add-ins in your spreadsheet application, do the following.

  1. For interaction with Microsoft® Excel®, create an instance of the ExcelAppHelper class, which contains the basic methods required for opening an Excel spreadsheet and evaluating macro code in the add-in file.

  2. Create an AddInFunction object that represents a custom function. Inherit it from the ICustomFunction base interface, provide a function name (IFunction.Name), and specify the function parameters (IFunction.Parameters) and return type (IFunction.ReturnType). For more details on custom function implementation, refer to the User-Defined Functions (UDF) topic.

  3. To provide compatibility with a Microsoft® Excel® application, create the ConvertParameter and ConvertResultValue methods of the AddInFunction class. These methods allow conversion of the SpreadsheetControl's parameters to an Excel object's parameters, and vice versa. It is necessary to pass function parameters specified in a spreadsheet application to Microsoft® Excel® for further function calculation, and to display the function results correctly in the SpreadsheetControl.

  4. Implement the IFunction.Evaluate method. Within it, call the RunMacros method of the ExcelAppHelper class to run an Excel macro to evaluate a custom function.

  5. To use a custom function in a worksheet, add an instance of your function to the collection of workbook custom functions.

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=E5012.

How would you rate this topic?​​​​​​​