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.
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.
- 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.
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).
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.
Protect and Encrypt Data
The WPF Spreadsheet allows you to protect your documents to prevent unauthorized access. Security options include:
- Restrict changes to workbooks and worksheets. Protect the workbook structure and lock cells in a worksheet so that users can execute only specific actions.
- Grant user permissions. Unlock specific cell ranges in a protected worksheet for authorized users.
- Encrypt a document with a password The Spreadsheet control uses 256-bit AES encryption for XLSX files and the custom RC4 CryptoAPI implementation for XLS files.
- Configure which actions are available to users. Unavailable operations are disabled or hidden in the ribbon UI and corresponding context menus.
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.
Print Documents
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.
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:
- Load, save, and export (to PDF and HTML) documents asynchronously
- Import data from different data sources (such as arrays, lists, and data tables) and export a cell range to a DataTable
- Clone and merge workbooks
- Save a cell range as an image
- Digitally sign Microsoft Excel documents
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.