Spreadsheet Document API

  • 7 minutes to read

The Spreadsheet Document API is a cross-platform .NET library that allows you to create, load, modify, save, and print Microsoft® Excel® workbooks in code. You can use this library in desktop and web applications that target a variety of platforms (Windows Forms, WPF, ASP.NET Web Forms, ASP.NET MVC, ASP.NET Core, and Blazor).

Important

You need an active license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this library in production code.

If you are new to the Spreadsheet Document API, refer to the following topics first:

Spreadsheet Document Elements

The Spreadsheet Document API allows you to create, access, modify, or remove the following document elements:

Workbook

Create a Workbook instance to start working with spreadsheet documents in code. You can create a new document, load an existing file, retrieve data from different data sources (such as arrays, lists, and data tables), clone and merge workbooks. The Spreadsheet Document API allows you to save modified data to a file, print, or export your documents 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 a spreadsheet document contain 1,048,576 rows and 16,384 columns. 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.

You can assign different values to cells: numbers, dates, text, logical values, and formulas.

Defined Names
You can define descriptive names for cells, formulas, and constants to make it easier to understand and maintain your formulas.
Shapes and Pictures
The Spreadsheet Document API 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 Document API 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 Document API supports over 400 Excel-compatible functions. 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 Document API also supports user-defined (custom) functions.

Document Appearance and Cell Formatting

The Spreadsheet Document API 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 appearance of your 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 select from predefined 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.

Bind to Data

You can bind a cell range or 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 Spreadsheet Document API supports the following features that help you manage and organize data in a worksheet:

  • 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 Secure Documents

The Spreadsheet Document API 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.

The Spreadsheet Document API allows you to print the entire workbook or individual sheets and specify different print settings: add headers and footers to a printout, set a print area, repeat specific rows and columns on every page, and define other print-related settings (such as page orientation, paper size, page margins, and so on).

You can also save a document as a PDF file or create an image from a worksheet or specific cell range.

Mail Merge

The Spreadsheet Document API 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 data-bound templates to XLSX and XLS files and reuse them later to create new documents and reports.

DevExpress Spreadsheet UI Controls

If your desktop or web application requires a full spreadsheet processing user interface, you can use the following DevExpress Spreadsheet UI controls: