VCL Spreadsheet Controls
- 4 minutes to read
VCL Spreadsheet controls allow you to create applications with a user experience inspired by Microsoft Excel®.
Refer to the following topics to get started with the Spreadsheet controls:
- How to: Create a Ribbon or Toolbar UI for your Spreadsheet-based Application.
- How to: Use Spreadsheet Control Commands
Spreadsheet
The Spreadsheet control displays spreadsheet documents and allows users to edit document content.
Refer to the following topics for more information:
- Cell Management
- Clipboard Operations
- Supported Keystrokes
- Floating Containers
- Comments
- Action History
Report Designer
The Report Designer is a data-aware version of the Spreadsheet control. The Report Designer allows users to create reports that obtain data from a plain dataset or multiple tables that form a one-to-many (master-detail) relationship. A report template includes placeholder fields bound to dataset fields. The Report Designer populates these placeholders with data when it generates spreadsheet documents (reports).
Refer to the following topics for more information on spreadsheet-based reports:
Formula Bar
The Formula Bar complements the Spreadsheet and Report Designer controls with additional cell view and edit options found in popular spreadsheet applications.
Automatic Ribbon/Toolbar UI Generation
You can create a spreadsheet-based application with a fully-functional Ribbon or Toolbar UI in a few clicks:
Refer to the following topic for details: How to: Create a Ribbon or Toolbar UI for your Spreadsheet-based Application.
Note
You need a license to Express NavigationPack or VCL Subscription to use the Ribbon and Toolbar UI in your VCL applications.
Group and Sort Data
The Spreadsheet and Report Designer controls support the following data shaping functionality:
- Users can sort a cell range in ascending or descending order.
Users can aggregate rows and columns into collapsible groups that display headers and summary columns. (Group rows and columns work similarly to the data outline functionality in Microsoft Excel®.)
Refer to the following topics for more information:
Protect and Encrypt Data
The Spreadsheet controls allow you to protect your documents to prevent unauthorized access. Security options include:
- Document Encryption and Password Protection
- The Spreadsheet controls use 256-bit AES encryption for XLSX files and the custom RC4 CryptoAPI implementation for XLS files.
- Document Structure Protection
- Users can “lock” document structure – disable changes to worksheet names, state, order, etc.
- Worksheet Content Protection
- Users can restrict specific actions in a protected worksheet, such as changes to cell content, column management operations, and more.
Formula Expressions
The Spreadsheet controls support over 300 built-in functions compatible with Microsoft Excel® formulas. You can use basic mathematical functions to aggregate data in a spreadsheet or create intricate formula expressions with statistical, logical, and information-related functions. The Spreadsheet controls also allow you to implement custom functions.
Refer to the following topics for information on how to work with formula expressions:
Conditional Formatting
The Conditional Formatting functionality allows you to emphasize important data. Individual rules apply custom formatting to cells that meet specific criteria.
Supported Document Formats
The Spreadsheet and Report Designer controls support the following spreadsheet document formats:
- Office Open XML (XLSX and XLTX)
- Comma-separated values (CSV)
- Plain text (TXT)
- Excel Binary File Format (XLS and XLT)
- HyperText Markup Language (HTML); only document export is supported.
- OpenDocument Spreadsheet format (ODS); only document import is supported.
Refer to the following topic for details: Export and Import Data.
Spreadsheet-Based Functionality in Other DevExpress VCL Products
Formula Expression and Conditional Formatting functionality is implemented as shared libraries. Their public API is also available in the following products:
- TcxGrid
- A Data Grid control.
- TcxDBTreeList
- A data-aware Tree List control.
- TcxTreeList
- A control that displays data in a tree-like manner.
- TcxVerticalGrid
- The vertical grid control that implements single record and banded layouts.
- TcxDBVerticalGrid
- Represents the data-aware vertical grid control.
- TcxVirtualVerticalGrid
- The vertical grid control that implements single record, multi record and banded layouts.
The data export functionality of the controls in the list above and in the list below relies on the VCL Spreadsheet export engine:
- TcxPivotGrid
- An unbound Pivot Grid control.
- TcxDBPivotGrid
- A data-aware Pivot Grid control.
- TcxScheduler
- The scheduler control.