Spreadsheet Document API
- 4 min 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.
You require a license to the DevExpress Office File API or DevExpress Universal Subscription to use this component or library in production code. Refer to the DevExpress Subscription page for pricing information.
The major Spreadsheet Document API features are:
- Excel formats: XLS, XLSX, XLSB, XLSM, XLTX, XLTM and XLT.
- Text formats: TXT and CSV.
- Other formats: PDF and HTML (export only).
- 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.
- 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.
- Add different types of data to worksheet cells.
- Import data to cells from different data sources (arrays, lists and data tables).
- 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.
- Use formulas to perform calculations with different types of cell references and a comprehensive set of built-in functions.
- Create shared and array formulas.
- Evaluate worksheet formulas with Formula Engine: parse a formula into an expression tree, modify it and rebuild the string expression.
- 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.
- 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: display only rows that meet filtering criteria to arrange large amounts of data.
- Group data: split data into separate groups and calculate summaries for each group.
- 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 format and scale options.
- 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.
- 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).
- Generate a set of documents based on a single template and include unique data values retrieved from a data source into each document.