Spreadsheet Document API

• Apr 23, 2019
• 4 minutes to read

The Spreadsheet Document API is a non-visual .NET library that provides the complete spreadsheet functionality available via its API (the Workbook instance). It allows you to create, load, modify, save and print spreadsheet documents, so that you do not need Microsoft® Excel® to be installed on your computer.

The spreadsheet component also provides comprehensive data shaping and analysis tools, such as data mining, grouping, filtering, and charting. The built-in formula calculation engine allows you to create simple formulas to summarize worksheet information or construct complex formulas containing mathematical, statistical and other predefined functions for advanced data analysis.

Important

The Universal Subscription or an additional Office File API Subscription is required to use this component or library in production code. Refer to the DevExpress Subscription page for pricing information.

The major features of the Spreadsheet Document API are listed below.

Supported Formats

Create, load, convert and save workbooks to popular file formats.

• Excel formats: XLS, XLSX, XLSM, XLTX, XLTM and XLT.
• Text formats: TXT and CSV.
• Other formats: PDF and HTML (export only).

Worksheet Basics

• Manage worksheets: create, copy, rename, move, hide and delete worksheets.
• Customize worksheet view options: change the zoom level and control the visibility of gridlines and headings.
• Specify print settings: set paper size, margins and orientation for worksheet pages.

Cells and Cell Ranges

• Manipulate cells and cell ranges: insert, copy, clear, delete, merge and unmerge cells.
• Add formulas, comments and hyperlinks to cells.
• Use defined names to name individual cells and cell ranges.
• Format cells: apply a cell style, or directly change cell color, alignment, borders and font settings.
• Specify cell number format.
• Create conditional formatting rules to change appearance of worksheet cells based on specific conditions.

Rows and Columns

• Modify worksheet rows and columns by inserting, copying, hiding or deleting them.
• Adjust row height and column width.

Data Management

• Add different types of data to worksheet cells.
• Import data to cells from different data sources (arrays, lists and data tables).

Data Binding

• Use a cell range or a worksheet table as a data source for any data-aware control.
• Establish two-way binding to various data sources so that you can further analyze or modify the data.

Formulas

• Perform calculations using formulas with different types of cell references and a comprehensive set of built-in functions.
• Create shared and array formulas.
• Evaluate worksheet formulas using Formula Engine, which provides the capability to parse a formula into an expression tree, modify it and rebuild the string expression.

Protection

• Protect workbook structure: prevent end-users from adding, deleting or renaming worksheets.
• Protect worksheets: prevent end-users from editing certain cells, applying formatting options, or making structural changes to worksheets.
• Protect individual cell ranges and unlock specific ranges in a protected worksheet for authenticated users.

Data Shaping Tools

• Organize data in tables: insert, copy, modify and delete tables. Format tables using one of the built-in table styles or create your own custom styles.
• Sort data in a range in ascending or descending order.
• Filter data: use the filtering functionality to arrange large amounts of data by displaying only rows that meet filtering criteria.
• Group data: split data into separate groups and calculate summaries for each group.

Charts and Graphics

• Add shapes to a worksheet, connect and group shapes, change a shape’s fill and outline settings, add text to a shape, and remove shapes from a document.
• Add pictures to a worksheet: insert, move, change, transform and delete pictures.
• Use the Spreadsheet Chart API to create a basic chart in code and fully customize the appearance of any chart element.
• Create sparkline groups of different types, and adjust their formatting and scaling options.

Pivot Tables

• Create a pivot table using a cell range as a data source or base your report on the data cache of the existing pivot table.
• Calculate summaries against data fields using a wide range of aggregate functions and calculation options.
• Change the PivotTable layout and format a pivot table by applying a preset or custom style.
• Sort, filter and group items in a PivotTable report.
• Create calculated fields and calculated items.

Printing and Exporting

• Print a workbook or individual worksheets to a printer or file.
• Specify various print options: add headers and footers to the printout, set a print area on a worksheet, repeat specific rows and columns on every page, and define other print-related settings (such as page orientation, paper size, page margins, etc.).
• Export a workbook to a PDF file, or save it as a web-page (export to HTML).

Mail Merge

• Automatically generates a set of documents based on a single template and include unique data values retrieved from a data source into each document.