You are viewing help content for pre-release software. This document and the features it describes are subject to change. Switch to the current version.

WPF Spreadsheet

  • 7 minutes to read

The WPF Spreadsheet control emulates Microsoft® Excel® capabilities and allows you to create, load, edit, save, and print spreadsheet documents.

Spreadsheet Main Page

If you are new to the Spreadsheet control or consider it for an upcoming project, review the following topics first:

Spreadsheet Document Elements

Review the list of supported document elements. You can manage these elements in code or from the Spreadsheet control’s user interface.

Workbook

A workbook is a spreadsheet document. When you load the Spreadsheet control, it displays an empty workbook. You can create a new document, load an existing file, or retrieve data from different data sources (arrays, lists, data tables, and so on). The Spreadsheet control allows you to save modified data to a file or export your document to PDF and HTML.

Supported File Formats

Worksheet
A worksheet is a single page within a workbook. You can create, rename, move, copy, hide, or delete worksheets.
Rows and Columns
Worksheets in the Spreadsheet control display 1,048,576 rows and 16,384 columns (the number of rows and columns in spreadsheet management programs such as Microsoft Excel). You can insert, copy, hide, freeze, resize, or remove rows and columns.
Cells and Cell Ranges

All worksheet data is stored in cells. A cell reference defines the position of a cell or cell range in a worksheet.

Cells can contain different values: numbers, dates, text, logical values, and formulas. You can embed custom data editors within cells to facilitate and validate user input.

Defined Names
You can define descriptive names for cells, formulas, and constants to make your formulas easier to understand and maintain. The Spreadsheet control ships with the Name Box and Name Manager that allow users to create, view, edit, and delete names.
Shapes and Pictures
The Spreadsheet control allows you to add shapes and pictures to worksheets. All shape types are supported: from simple lines and rectangles to 3D shapes with advanced effects.
Charts and Sparklines
You can create charts and sparklines to visualize data in your documents. The Spreadsheet control supports a broad range of 2D and 3D charts: from the commonly used Column and Line charts to modern Excel 2016 charts (Box and Whisker, Histogram, Pareto, Waterfall, Treemap, Sunburst, and Funnel).
Tables
You can convert a cell range into a table. Spreadsheet tables allow you to manage data more effectively: you can sort and filter table data, use table names in formulas create a calculated column, display the total row, and so on.
Pivot Tables
Create Excel-inspired Pivot Tables to summarize and analyze large amounts of data in your document. Pivot Tables are highly flexible and interactive. You can easily change the report layout and specify advanced Pivot Table options to create a summary table that fully addresses your requirements.
Comments
Use comments to attach additional information to worksheet cells.
Hyperlinks
Insert hyperlinks into worksheet cells to navigate to a web page or a specific location in a workbook.

Formulas and Functions

You can create formulas and perform calculations of any complexity. The Spreadsheet control ships with the Formula Bar that allows users to view, enter, and edit formulas.

The Spreadsheet control supports over 400 built-in functions designed to address a broad range of usage scenarios. You can use basic mathematical functions to aggregate data within a spreadsheet or create complex formulas with statistical, engineering, and financial functions. In addition to the predefined functions, the Spreadsheet control also supports user-defined (custom) functions.

Spreadsheet Formulas

Document Appearance and Cell Formatting

The Spreadsheet control supports a rich set of formatting options that allow you to change the appearance of the entire document or individual cells.

Document Theme
A document theme is a set of fonts, colors, and graphic effects you can use to change the look of your entire document. You can apply a custom theme to your workbook or change predefined theme colors.
Cell Styles
Use cell styles to apply multiple format attributes to worksheet cells simultaneously. You can choose from a broad range of built-in cell styles similar to Microsoft® Excel® or create a custom style.
Direct Cell Formatting
Apply different format attributes to individual cells and cell ranges: color the cell background, change font settings, align cell content, add borders, and specify number formats. You can also assign rich formatted text to a cell.
Conditional Formatting
Use conditional formatting (colors, icons, and data bars) to highlight cell values based on specific criteria or illustrate trends within cells.
Cell Templates
Use the SpreadsheetControl.CellTemplate property to specify a data template that defines cell appearance. You can create multiple cell templates. The SpreadsheetControl.CellTemplateSelector property allows you to implement custom logic to specify which template to use.

Bind to Data

You can bind a cell range or a table to various data sources: ADO.NET data sets, SQL databases, XML files, and lists of custom objects. Two-way data binding is supported.

You can also use a cell range as a data source for any DevExpress or third-party data-aware UI control (Data Grid, Chart, and so on).

Spreadsheet Data Binding

Organize and Shape Data

The WPF Spreadsheet control supports the following features that help you manage and organize data more effectively:

  • Sort data in a cell range or table in ascending or descending order.
  • Filter data in a cell range or table to display only rows that meet specific criteria. Different types of filters are available: Text Filters, Number Filters, Date Filters, and Filter by Values.
  • Group rows and columns and display summary information for each group. You can group data manually, create an automatic outline based on summary formulas, or calculate subtotals for related rows.

Spreadsheet Data Outline

Protect and Encrypt Data

The WPF Spreadsheet allows you to protect your documents to prevent unauthorized access. Security options include:

Validate Cell Values

Restrict user input and utilize data validation to reduce data entry errors and ensure data integrity. You can create a drop-down list of valid values, limit the number of text characters in a cell, restrict data entry to values within a specific range, validate data based on spreadsheet formulas, and display input and error messages for validated cells.

Spreadsheet Data Validation

You can print documents in code or use the built-in Print dialog. Invoke the Preview window to preview your document before printing.

The Page Setup dialog allows users to customize page settings, specify page margins, add headers and footers, set a print area, and define rows and columns to repeat on every page.

Spreadsheet Page Setup

Mail Merge

The WPF Spreadsheet control supports Mail Merge operations. Use Mail Merge to automatically generate personalized letters and business-centric reports of any complexity. Merged documents are generated based on a template that is bound to a data source. You can save mail merge templates to XLSX and XLS files and reuse them later to quickly create new documents and reports.

DevExpress Spreadsheet Document API

The WPF Spreadsheet control and the non-visual Spreadsheet Document API library share a common API used to manage spreadsheet documents in code. However, this library offers several unique features you may find useful for your Spreadsheet app:

You need an active license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use the Spreadsheet Document API library in production code. For pricing information, refer to the following page: DevExpress Subscription Plans.