devexpress-office-file-api-spreadsheet

name	devexpress-office-file-api-spreadsheet
description	Build .NET applications with the DevExpress Spreadsheet Document API for creating, reading, modifying, and exporting Excel workbooks programmatically without Microsoft Office. Use when working with .xlsx, .xls, .xlsm, .csv spreadsheet files, cell formatting, formulas, charts, pivot tables, data import/export, or workbook printing. Also use when someone mentions "DevExpress Spreadsheet", "Spreadsheet Document API", "IWorkbook", "Worksheet", "DevExpress.Spreadsheet", "create Excel file in C#", "read xlsx in .NET", "spreadsheet export to PDF", "cell formatting", or asks about any Excel/spreadsheet automation with DevExpress. Covers both .NET and .NET Framework.
metadata	{"author":"DevExpress","version":26.1,"source-commit":"12dab7a5b121db6eefabc59e4e6982bb5d1c35da"}

DevExpress Spreadsheet Document API

The Spreadsheet Document API is a non-visual .NET library for creating, loading, editing, and exporting spreadsheet documents (Excel workbooks) programmatically — without requiring Microsoft Office. It supports .xlsx, .xls, .xlsm, .csv, .txt formats and can export to PDF, HTML, and images.

When to Use This Skill

Use this skill when you need to:

• Create Excel workbooks (.xlsx, .xls, .xlsm) programmatically without Microsoft Office
• Read, parse, or extract data from existing .xlsx/.xls/.csv files
• Modify existing spreadsheets (add data, change formatting, update formulas)
• Apply cell formatting (fonts, colors, borders, number formats, conditional formatting)
• Work with formulas and calculated values (SUM, VLOOKUP, IF, array formulas, UDFs)
• Create and configure charts within spreadsheets (column, line, pie, scatter, pivot, Excel 2016)
• Build pivot tables from worksheet data
• Import data from arrays, collections, DataTables, or databases
• Export spreadsheets to PDF, HTML, or images
• Print spreadsheets or configure page setup and print titles
• Protect workbooks or individual worksheets with passwords
• Work with named ranges, tables (ListObjects), data validation, sparklines

Prerequisites & Installation

NuGet Packages

Package	Purpose
DevExpress.Document.Processor	Core spreadsheet processing (create, load, edit, save)

.NET (6/7/8+)

dotnet add package DevExpress.Document.Processor

.NET Framework (4.6.2+)

Install-Package DevExpress.Document.Processor

See references/getting-started-dotnet-fw.md for the full framework-specific guide.

Important: All DevExpress packages in a project must share the same version number. A valid DevExpress license is required.

Before You Start — Ask the Developer

Before generating code, ask these questions to avoid rework:

General Questions

1. Target framework: Are you using .NET 8+, .NET 6/7, or .NET Framework 4.x?
2. New or existing project?: Creating new or adding to existing?
3. Hosting model: Console app, ASP.NET Core, Blazor, MAUI, WinForms, WPF, or something else?

Spreadsheet-Specific Questions

4. Operation type: Create new, read existing, modify, export, or convert?
5. Features needed: Formatting, formulas, charts, pivot tables, data import, mail merge, protection?
6. Output format: .xlsx, .csv, PDF, HTML, or image?

Rule: If the developer's answer is ambiguous or missing, ask before generating code. Do not guess.

Component Overview

The Spreadsheet Document API provides:

• Document management: Create, load, save, and export workbooks (Workbook, IWorkbook, DocumentFormat)
• Cell operations: Read/write values, formulas, formatting (Cell, CellRange, Worksheet)
• Data operations: Import/export, sorting, filtering, grouping, data validation (WorksheetExtensions.Import)
• Visual elements: Charts, conditional formatting, sparklines, shapes, pictures (ChartCollection, ConditionalFormattingCollection)
• Document features: Named ranges, tables (ListObjects), pivot tables, comments, hyperlinks, mail merge
• Output: PDF/HTML/image export, printing with page setup

Core Entry Point

using DevExpress.Spreadsheet;

// Create a new workbook
using (Workbook workbook = new Workbook())
{
    Worksheet sheet = workbook.Worksheets[0];
    sheet.Cells["A1"].Value = "Hello, DevExpress!";
    workbook.SaveDocument("output.xlsx", DocumentFormat.Xlsx);
}

Documentation & Navigation Guide

Getting Started

📄 Refer to references/getting-started.md

When you need to:

• Set up the Spreadsheet Document API for the first time (.NET 6/7/8+)
• Install NuGet packages and configure the DevExpress feed
• Create your first workbook, load an existing file, or save in different formats
• Load custom fonts at runtime using DXFontRepository (Linux, Docker, cloud)
• See a complete working example

Worksheet Operations

📄 Refer to references/worksheet-operations.md

When you need to:

• Add, remove, or rename worksheets
• Copy a worksheet within the same workbook
• Move a worksheet to a different position
• Set the active worksheet or iterate over all worksheets
• Show or hide worksheets (including VeryHidden)

Getting Started (.NET Framework)

📄 Refer to references/getting-started-dotnet-fw.md

When you need to:

• Set up the API in a .NET Framework 4.6.2+ project
• Understand which assembly DLLs are needed if not using NuGet
• Configure PDF export using Windows GDI on .NET Framework

Cell Formatting

📄 Refer to references/cell-formatting.md

When you need to:

• Apply fonts, colors, borders, and alignment to cells or ranges
• Set number formats (dates, currencies, percentages, custom)
• Use BeginUpdateFormatting/EndUpdateFormatting for batch range formatting
• Apply conditional formatting rules (data bars, color scales, icon sets)
• Merge cells or adjust row height and column width
• Apply different fonts or colors to parts of a cell value (rich text, RichTextString)

Formulas & Calculations

📄 Refer to references/formulas.md

When you need to:

• Add formulas using FormulaInvariant (portable, English-language syntax)
• Calculate workbook formulas programmatically (Workbook.Calculate)
• Work with named ranges in formulas (DefinedNames)
• Create array formulas or shared formulas
• Register user-defined functions (UDF) via Workbook.CustomFunctions
• Parse and analyze formula expression trees
• Use dynamic array functions (XLOOKUP, XMATCH, SORT, SORTBY, FILTER, UNIQUE) — v26.1+
• Assign formulas that spill results into adjacent cells (DynamicArrayFormula)

Data Import & Export

📄 Refer to references/data-import-export.md

When you need to:

• Import data from arrays, IEnumerable<T>, DataTable, or custom objects
• Export worksheet ranges to DataTable
• Work with CSV encoding, delimiters, or format-specific import options
• Handle BeforeImport/BeforeExport events for format-specific settings

Charts

📄 Refer to references/charts.md

When you need to:

• Create charts (column, line, pie, bar, scatter, area, bubble, stock, Excel 2016)
• Configure axes, legends, titles, and data labels
• Build combination (multi-type) charts
• Add charts to a dedicated chart sheet
• Style chart elements (fill, outline, font)

Pivot Tables

📄 Refer to references/pivot-tables.md

When you need to:

• Create pivot tables from worksheet data
• Configure row, column, data, and filter fields
• Apply pivot table styles or refresh data
• Sort, filter, or group pivot table items

Protection & Security

📄 Refer to references/protection.md

When you need to:

• Protect a workbook structure (prevent adding/deleting sheets)
• Protect individual worksheets and lock/unlock specific cells
• Encrypt files with a password on open
• Set write-protection or read-only recommendations
• Define password-protected ranges for specific users

PDF & Image Export

📄 Refer to references/export.md

When you need to:

• Export a workbook or worksheet to PDF with PDF/UA-2 accessibility (v26.1+)
• Configure PDF page size, margins, orientation, headers/footers
• Export to HTML or render worksheets as images
• Configure print settings (print area, page breaks, print titles)

Advanced Features

📄 Refer to references/advanced-features.md

When you need to:

• Work with tables/ListObjects (structured references, table styles)
• Add or validate data with DataValidations
• Insert sparklines, shapes, or pictures
• Add hyperlinks or comments to cells
• Perform mail merge from an object data source
• Work with named ranges (DefinedNames)
• Bind worksheet ranges to external data sources
• Sort a range by one or more columns (AutoFilter.SortState)
• Apply auto-filter criteria to a range (AutoFilter.Apply, ApplyCustomFilter, ApplyFilterCriteria)
• Group rows or columns into collapsible outlines (Rows.Group, Columns.Group)

Safe Spreadsheet Processing (v26.1+)

📄 Refer to references/safe-spreadsheet-processing.md

When you need to:

• Reject oversized or structurally abnormal workbooks before full parsing (DoS protection)
• Strip macros, OLE objects, ActiveX, external connections, and pivot caches on load
• Remove personal metadata, comments, and hidden rows/columns/sheets before sharing (GDPR, HIPAA, SOX)

Sign Excel Files (v26.1+)

⚠️ This feature requires DevExpress v26.1+. Reference to be added in a future update.

Quick Start Example

A complete example — create a formatted sales report and save as .xlsx:

using DevExpress.Spreadsheet;
using System.Drawing;

using (Workbook workbook = new Workbook())
{
    Worksheet sheet = workbook.Worksheets[0];
    sheet.Name = "Sales Report";

    workbook.BeginUpdate();
    try
    {
        // Headers
        sheet["A1"].Value = "Product";
        sheet["B1"].Value = "Q1 Sales";
        sheet["C1"].Value = "Q2 Sales";
        sheet["D1"].Value = "Total";

        // Format header row
        CellRange headers = sheet.Range["A1:D1"];
        headers.Font.Bold = true;
        headers.Font.Color = Color.White;
        headers.FillColor = Color.FromArgb(68, 114, 196);
        headers.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;

        // Data rows
        sheet["A2"].Value = "Widget A";
        sheet["B2"].Value = 15000;
        sheet["C2"].Value = 18500;
        sheet["D2"].FormulaInvariant = "=B2+C2";

        sheet["A3"].Value = "Widget B";
        sheet["B3"].Value = 22000;
        sheet["C3"].Value = 19800;
        sheet["D3"].FormulaInvariant = "=B3+C3";

        // Totals row
        sheet["A4"].Value = "Totals";
        sheet["A4"].Font.Bold = true;
        sheet["B4"].FormulaInvariant = "=SUM(B2:B3)";
        sheet["C4"].FormulaInvariant = "=SUM(C2:C3)";
        sheet["D4"].FormulaInvariant = "=SUM(D2:D3)";

        // Number format for currency columns
        sheet.Range["B2:D4"].NumberFormat = "$#,##0";

        // Auto-fit columns
        sheet.Columns.AutoFit(0, 3);
    }
    finally
    {
        workbook.EndUpdate();
    }

    workbook.SaveDocument("SalesReport.xlsx", DocumentFormat.Xlsx);

    workbook.ExportToPdf("SalesReport.pdf");
}

What This Does

Creates an Excel file with a formatted sales table (bold headers, blue background, currency formatting, SUM formulas), saves as .xlsx, and exports to PDF. Both files are saved in the working directory.

Key Properties & API Surface

Workbook

Property/Method	Type	Description
Worksheets	WorksheetCollection	Access all worksheets by index or name
SaveDocument(path)	void	Save to file in detected or specified format
LoadDocument(path)	void	Load from file or stream
ExportToPdf(path)	void	Export entire workbook to PDF
Calculate()	void	Recalculate all marked formulas
CalculateFull()	void	Recalculate all formulas regardless of state
BeginUpdate() / EndUpdate()	void	Batch edits for performance
DocumentSettings	DocumentSettings	Calculation, encryption, culture, print options
CustomFunctions	CustomFunctionCollection	Register UDFs
DefinedNames	DefinedNameCollection	Workbook-scoped named ranges/constants

Worksheet

Property/Method	Type	Description
Cells[ref]	Cell	Access a cell by A1 reference (e.g., "A1")
Range[ref]	CellRange	Access a range (e.g., "A1:D10")
this[string]	Cell	Shorthand indexer: sheet["A1"]
Name	string	Worksheet tab name
Columns	ColumnCollection	Access columns for sizing
Rows	RowCollection	Access rows for sizing
Charts	ChartCollection	Charts embedded in this worksheet
Tables	TableCollection	ListObjects (structured tables)
DataValidations	DataValidationCollection	Data validation rules
ConditionalFormattings	ConditionalFormattingCollection	Conditional formatting rules
GetUsedRange()	CellRange	Returns the range of populated cells
GetDataRange()	CellRange	Returns the data range (excludes header)
Protect(password, permissions)	void	Protect the worksheet

Cell / CellRange

Property/Method	Type	Description
Value	CellValue	Cell value (auto-typed: string, double, bool, DateTime)
FormulaInvariant	string	Formula in invariant (English) syntax — use this
Formula	string	Formula in current culture's syntax — avoid for portability
Font	SpreadsheetFont	Font settings (Bold, Color, Size, Name)
FillColor	Color	Cell background color
Fill.BackgroundColor	Color	Cell fill background (via Formatting object)
Alignment	Alignment	Horizontal/vertical, wrap, indent, rotation
NumberFormat	string	Excel format string (e.g., "$#,##0.00")
Borders	Borders	Cell border styles and colors
BeginUpdateFormatting()	Formatting	Start batch formatting a range
EndUpdateFormatting(f)	void	Apply batch formatting

Common Patterns

Load, Modify, Save

using (Workbook workbook = new Workbook())
{
    workbook.LoadDocument("input.xlsx");
    Worksheet sheet = workbook.Worksheets["Sheet1"];
    sheet["A1"].Value = "Updated";
    workbook.SaveDocument("output.xlsx", DocumentFormat.Xlsx);
}

Import from DataTable

using (Workbook workbook = new Workbook())
{
    Worksheet sheet = workbook.Worksheets[0];
    // Import with header row starting at row 0, column 0
    sheet.Import(myDataTable, addHeader: true, firstRowIndex: 0, firstColumnIndex: 0);
    sheet.Columns.AutoFit(0, sheet.GetUsedRange().ColumnCount - 1);
    workbook.SaveDocument("imported.xlsx", DocumentFormat.Xlsx);
}

Create a Chart

Chart chart = sheet.Charts.Add(ChartType.ColumnClustered, sheet["A1:D4"]);
chart.TopLeftCell = sheet.Cells["F2"];
chart.BottomRightCell = sheet.Cells["L15"];
chart.Title.Visible = true;
chart.Title.SetValue("Quarterly Sales");
chart.Legend.Position = LegendPosition.Bottom;

Batch Format a Range

CellRange range = sheet.Range["A1:D10"];
Formatting fmt = range.BeginUpdateFormatting();
fmt.Font.Bold = true;
fmt.Font.Size = 12;
fmt.Fill.BackgroundColor = Color.LightYellow;
fmt.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
range.EndUpdateFormatting(fmt);

Troubleshooting

Symptom	Cause	Solution
Formula shows as text	Used Value instead of formula property	Use cell.FormulaInvariant = "=SUM(A1:A10)"
Exported PDF is blank	No print area or data off-screen	Check sheet.GetUsedRange() is not empty
Formula returns wrong locale	Used Formula (localized) instead of FormulaInvariant	Always use FormulaInvariant for portable code
Version mismatch error	Mixed DevExpress package versions	All DX packages must use the same version (e.g., all 25.2.x)
License error at runtime	Missing DevExpress license	Register license per installation guide
NullReferenceException on Cells	Worksheet name does not exist	Use workbook.Worksheets[0] or check Worksheets.Contains()
Import does not add headers	Missing addHeader: true parameter	Pass addHeader: true to sheet.Import(...)
ComplianceViolationException on load/save	FIPS mode active; operation uses non-compliant algorithm (RC4, SHA-1)	Use XLSX with AES-256 encryption (EncryptionOptions.Type). Detect FIPS mode with OperatingSystemLevelFipsMode.IsEnabled.

Constraints & Rules

CRITICAL — follow these rules in every interaction:

1. Build verification: After making changes, verify the project builds with dotnet build. Check for errors before reporting success.
2. NuGet packages: Use only DevExpress.Document.Processor. Do not guess other package names.
3. Namespace imports: Always include using DevExpress.Spreadsheet; and other necessary directives.
4. Version consistency: All DevExpress packages must use the same version (25.2.x).
5. License: DevExpress requires a valid license. Remind the developer if they hit license errors.
6. No destructive changes: Preserve existing code structure. Only add or modify what is necessary.
7. Framework detection: Check .csproj for target framework. Use Workbook for both .NET and .NET Framework.
8. Formula syntax: Use FormulaInvariant (English function names) rather than localized Formula property for portable code.

Using DevExpress Documentation MCP

If the DxDocs MCP server is available, use it to supplement this skill:

• Search: devexpress_docs_search with technology "Spreadsheet" and your question.
• Fetch: devexpress_docs_get_content with a docs URL to get full article content.

When to use MCP vs. built-in references:

• Built-in: Getting started, common patterns, key properties, troubleshooting.
• MCP: Advanced scenarios, version-specific changes, uncommon features, or questions outside this skill.
• Always MCP for: Exact method signatures, event args, or enum values when uncertain.

---

Next Steps

Start with Getting Started to install and configure the Spreadsheet Document API, then explore specific features through the navigation guide above.