Spreadsheet Document API
- 8 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 a 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.
- 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.
- Threaded Comments
- Use threaded 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:
- 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 Document API uses 256-bit AES encryption for XLSX files and the custom RC4 CryptoAPI implementation for XLS files.
- Specify a password to modify a workbook. Make a workbook read-only and specify a password to prevent modifications from unauthorized users.
- Sign Excel documents. Add digital signatures to workbooks, configure signature options, and validate existing signatures to ensure the authenticity and integrity of document content.
Validate Cell Values
You can restrict user input and utilize data validation to reduce data entry errors and ensure data integrity. To do this, 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 and Export Documents
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 UI Development Subscriptions
In addition to Office File API, DevExpress offers a wide variety of UI Controls for desktop platforms (WinForms, WPF) and the web (Blazor, ASP.NET Core, JavaScript, Web Forms, MVC). DevExpress UI controls seamlessly integrate into your projects and help you craft visually stunning and user-friendly applications. With a wide array of controls at your disposal, from data grids to responsive layouts and data visualization tools, you can build applications that meet and exceed user expectations.
In particular, Spreadsheet UI Controls are available for WinForms, WPF, ASP.NET Core, ASP.NET Web Forms, and ASP.NET MVC.
If you need UI Controls for a specific platform, give the following DevExpress subscriptions a try:
If you develop applications for multiple .NET / JavaScript platforms or you are interested in additional products DevExpress has to offer, take a look at our bigger bundle - DevExpress Universal subscription. This subscription includes Office File API, all DevExpress UI Controls for .NET / JavaScript, and several additional products for cross-platform development.
If you haven’t yet done so, download our fully-functional 30-day trial version and try out DevExpress controls and libraries in your projects: