ExcelExport4D

Fluent TDataSet-to-Excel export for Delphi — interactive through COM or headless through native OpenXML, under the same API.

ExcelExport4D is a focused Delphi library for exporting tabular TDataSet content to Excel.

It provides two backends behind the same fluent API:

a COM backend, which drives Microsoft Excel installed on the machine;
an OpenXML backend, which generates .xlsx files directly, without requiring Excel.

Both backends can coexist in the same project. For example, an “Open in Excel” button can use COM, while a “Save as .xlsx” or “Attach to e-mail” action can use OpenXML.

The choice is made per scenario, not per project.

Why this project exists

In many Delphi applications, exporting data to Excel becomes a mix of repeated code, fragile COM automation, cell-by-cell writing, scattered formatting rules, and inconsistent handling of internal fields.

ExcelExport4D exists to solve one specific problem well:

export tabular TDataSet data to Excel with explicit formatting rules, field exclusions, storage semantics, and a clear choice between COM and OpenXML output.

It is intentionally focused. It does not try to replace Excel, become a report designer, or expose the full Excel object model. It provides a small and predictable mechanism for producing useful spreadsheets from Delphi datasets.

Architecture overview

ExcelExport4D is organized around a shared core and two concrete backends:

Core resolves rules, exclusions, storage semantics, sheet names and export lifecycle.
COM backend drives Microsoft Excel through OLE Automation.
OpenXML backend generates .xlsx files directly through System.Zip.

For a detailed explanation of the internal flow, see Architecture.md.

Architecture overview
Quick overview
What ExcelExport4D is
What ExcelExport4D is not
Two backends, one API
When to use each backend
Requirements
Installation
Quick start
Features
Rules and formatting
Column headers
Storage semantics
Field exclusion
Supported field types
Multi-sheet export
Fields mode
Progress callback
Demo application
Tests
Architecture
Repository layout
Design decisions
Scope and limitations
Documentation
Versioning
License

Quick overview

Interactive: open the workbook in Excel

TExcelExport.New
  .FromDataSet(qryOrders)
  .WithTitle('Orders')
  .Execute;

Headless: write an `.xlsx` file directly

TExcelExportXlsx.New
  .FromDataSet(qryOrders)
  .WithTitle('Orders')
  .SaveAs('orders.xlsx')
  .Execute;

Both backends share the same fluent API, rule system, exclusion system, storage semantics and core validation logic. The rendering engine is intentionally different in each backend.

What ExcelExport4D is

ExcelExport4D is a tabular export mechanism for Delphi datasets.

It is useful when you want to:

export TDataSet records to Excel;
generate a clean .xlsx file without Excel installed;
drive Microsoft Excel when the user expects an interactive workbook;
apply formatting rules by field type or field name;
exclude technical/internal fields, such as audit fields, row versions, integration payloads or binary data;
preserve text values that Excel might otherwise reinterpret;
convert numeric text to real Excel numbers when needed;
create simple multi-sheet workbooks from multiple datasets;
export a lightweight field-structure sheet.

What ExcelExport4D is not

ExcelExport4D is not a complete Excel manipulation library.

This version does not try to:

read existing .xlsx files;
edit existing workbooks;
append incrementally to an existing workbook;
use Excel templates;
generate charts;
generate pivot tables;
calculate formulas;
create formulas automatically;
apply conditional formatting;
manipulate images;
merge cells;
build complex report layouts with multiple blocks per sheet;
replace full reporting tools;
replace general-purpose commercial Excel libraries.

This boundary is intentional. ExcelExport4D focuses on predictable tabular export from TDataSet.

Two backends, one API

Exporting a dataset to Excel usually falls into two different workflows:

the user wants a live Excel workbook after export;
the application only needs to generate an .xlsx file.

ExcelExport4D supports both.

COM backend (`TExcelExport`)

The COM backend drives Microsoft Excel through OLE Automation.

Use it when:

the application runs on Windows;
Microsoft Excel is installed;
the user expects Excel to open after export;
the exported workbook is part of an interactive desktop workflow.

TExcelExport.New
  .FromDataSet(qryOrders)
  .WithTitle('Orders')
  .Execute;

OpenXML backend (`TExcelExportXlsx`)

The OpenXML backend generates .xlsx files directly using System.Zip and generated XML parts.

Use it when:

Excel may not be installed;
the application only needs to save a file;
the export runs in a headless or non-interactive context;
the file will be attached, archived, returned by an API, or processed later.

TExcelExportXlsx.New
  .FromDataSet(qryOrders)
  .WithTitle('Orders')
  .SaveAs('orders.xlsx')
  .Execute;

When to use each backend

Scenario	Recommended backend
The user wants to see the workbook immediately after export	COM
The user only needs the generated `.xlsx` file	OpenXML
Desktop workflow where Excel is part of the process	COM
Environment without Microsoft Excel installed	OpenXML
Service, scheduler, API or headless process	OpenXML
Automated tests over generated files	OpenXML
Need real Excel AutoFit behavior	COM
Need cross-platform file generation	OpenXML

When in doubt, start with OpenXML. It has fewer runtime requirements. Use COM when true Excel interaction is part of the workflow.

Requirements

General

Delphi 11 or later is the validated target.
TDataSet-based data source.
No external runtime dependencies for the OpenXML backend.

Earlier Delphi versions may work, but they are not officially validated for this initial release.

COM backend

Windows.
Microsoft Excel installed.

OpenXML backend

No Microsoft Excel installation required.
Uses Delphi RTL units such as System.Zip, System.Classes and System.SysUtils.

Installation

Clone the repository and add the src folder to your project’s Search Path:

Project → Options → Building → Delphi Compiler → Search Path

For the COM backend:

uses
  ExcelExport4D;

For the OpenXML backend:

uses
  ExcelExport4D,
  ExcelExport4D.Xlsx;

On Windows, ExcelExport4D exposes TExcelExport as the COM backend alias.

On non-Windows platforms, ExcelExport4D still exposes the core public types and interfaces without pulling COM/OLE units. For native .xlsx generation, import ExcelExport4D.Xlsx and use TExcelExportXlsx.New.

Quick start

Minimal COM export

TExcelExport.New
  .FromDataSet(qryCustomers)
  .Execute;

Minimal OpenXML export

TExcelExportXlsx.New
  .FromDataSet(qryCustomers)
  .SaveAs('customers.xlsx')
  .Execute;

Export with formatting rules

TExcelExportXlsx.New
  .FromDataSet(qryOrders)
  .WithTitle('Orders')
  .SaveAs('orders.xlsx')
  .SkipTagged(-2)
  .ExcludeFieldNames(['DebugInfo', 'RowVersion'])
  .ExcludeFieldTypes([ftBlob, ftGraphic, ftStream])
  .BooleanText('Yes', 'No')
  .HeaderBold(True)
  .HeaderAlignment(xhaCenter)
  .BorderBlock(True)
  .GlobalAutoFit(True)

  .RuleByFieldType(ftCurrency)
    .Alignment(xhaRight)
    .NumberFormat('#,##0.00')
    .AutoFit(True)
    .Done

  .RuleByFieldType(ftMemo)
    .Alignment(xhaLeft)
    .WrapText(True)
    .Width(45)
    .Done

  .RuleByFieldName('CreatedAt')
    .Alignment(xhaCenter)
    .NumberFormat('yyyy-mm-dd hh:mm:ss')
    .AutoFit(True)
    .Done

  .RuleByFieldName('CustomerCode')
    .StoreAsText(True)
    .AutoFit(True)
    .Done

  .Execute;

Features

Feature	COM	OpenXML
Fluent API	✓	✓
Data export mode	✓	✓
Fields mode	✓	✓
Multiple sheets	✓	✓
Rules by field type	✓	✓
Rules by field name	✓	✓
Field exclusion by `Tag`	✓	✓
Field exclusion by name	✓	✓
Field exclusion by type	✓	✓
`NumberFormat`	✓	✓
`StoreAsText`	✓	✓
`StoreAsNumber`	✓	✓
Boolean text customization	✓	✓
Header bold	✓	✓
Header alignment	✓	✓
Borders	✓	✓
`WrapText`	✓	✓
Explicit column width	✓	✓
`AutoFit`	Real, via Excel	Heuristic
Progress callback	✓	✓
Save as `.xlsx`	✓	✓
Open Excel after export	✓	—
Runs without Excel installed	—	✓
Suitable for headless generation	Not recommended	✓

Rules and formatting

Rules define column behavior.

You can target fields by type:

.RuleByFieldType(ftCurrency)
  .Alignment(xhaRight)
  .NumberFormat('#,##0.00')
  .Done

Or by field name:

.RuleByFieldName('InvoiceDate')
  .Alignment(xhaCenter)
  .NumberFormat('yyyy-mm-dd')
  .Done

Rule precedence is:

rules by FieldType;
rules by FieldName.

A field-name rule overrides only the attributes it explicitly sets. It does not clear attributes inherited from a field-type rule unless it sets them explicitly.

Each rule builder supports:

Alignment(value)        // xhaLeft, xhaCenter, xhaRight
NumberFormat(pattern)   // Excel number format string
WrapText(value)         // word-wrap
AutoFit(value)          // column auto-fit
Width(chars)            // explicit width in Excel character units
StoreAsNumber(value)    // see Storage semantics
StoreAsText(value)      // see Storage semantics
Done                    // returns to the main export chain

Column headers

In data export mode, ExcelExport4D uses each field's DisplayLabel as the Excel column header.

DataSet.FieldByName('CustomerName').DisplayLabel := 'Customer';
DataSet.FieldByName('CreatedAt').DisplayLabel := 'Created At';

The field is still identified by its FieldName in the dataset, but the exported Excel header uses the visual label.

Rules and exclusions still use the field name.

.RuleByFieldName('CustomerName')
.ExcludeFieldName('InternalCode')

In short:

DisplayLabel controls the text shown in the Excel header.
FieldName controls rules, exclusions and technical field identification.

Storage semantics

Visual formatting and storage semantics are not the same thing.

ExcelExport4D keeps them explicit.

`NumberFormat(...)`

NumberFormat controls how Excel displays the value.

Examples:

.NumberFormat('#,##0.00')

.NumberFormat('yyyy-mm-dd hh:mm:ss')

It does not, by itself, mean that the value is stored as text or number. It is a formatting rule.

`StoreAsText(True)`

StoreAsText(True) forces the value to be written as text, even if the original dataset field is numeric, date/time or numeric-looking text.

Use it for:

customer codes;
product codes;
codes with leading zeros;
apartment numbers;
postal codes;
phone numbers;
serial numbers;
external identifiers;
long numeric-looking values;
values that Excel might show in scientific notation.

Example:

.RuleByFieldName('CustomerCode')
  .StoreAsText(True)
  .Done

When StoreAsText(True) is used without an explicit NumberFormat, ExcelExport4D forces the column number format to @ (Text).

`StoreAsNumber(True)`

StoreAsNumber(True) attempts to convert textual values to real Excel numbers before writing.

Use it when the dataset stores numeric values in string fields, but the resulting spreadsheet should support numeric sorting, formulas, totals or charts.

Example:

.RuleByFieldName('AmountAsText')
  .NumberFormat('#,##0.00')
  .StoreAsNumber(True)
  .Done

StoreAsNumber(True) tries, in this order: Int64, invariant numeric parsing, and finally the current locale as fallback for legacy/localized textual numeric values.

Precedence

If both StoreAsText(True) and StoreAsNumber(True) are configured for the same column, StoreAsText(True) wins.

This prevents accidental conversion of values that must remain textual.

Field exclusion

Exclusions are cumulative.

By `Tag`

.SkipTagged(-2)

Useful when the dataset field itself carries the intent to be hidden.

By field name

.ExcludeFieldName('InternalCode')
.ExcludeFieldNames(['DebugInfo', 'RowVersion'])

Field-name exclusions are case-insensitive.

By field type

.ExcludeFieldTypes([ftBlob, ftGraphic, ftStream])

Useful for structural exclusions such as binary payloads.

Supported field types

ExcelExport4D handles common TFieldType values directly and falls back to textual representation when possible.

Native handling

Field type	Handling
`ftBoolean`	Exported as configurable text. Defaults to `Yes` / `No`.
`ftDate`	Exported as an Excel date value with default format `yyyy-mm-dd`.
`ftTime`	Exported as an Excel time value with default format `hh:mm:ss`.
`ftDateTime`	Exported as an Excel date/time value with default format `yyyy-mm-dd hh:mm:ss`.
`ftTimeStamp`	Exported as an Excel date/time value with default format `yyyy-mm-dd hh:mm:ss`.
`ftSmallint`, `ftInteger`, `ftWord`, `ftLargeint`, `ftAutoInc`, `ftByte`, `ftShortint`, `ftLongWord`	Exported as numbers.
`ftFloat`, `ftBCD`, `ftFMTBcd`	Exported as numbers.
`ftCurrency`	Exported as a number.
`ftString`, `ftWideString`	Exported as text unless `StoreAsNumber(True)` is used.
`ftMemo`, `ftWideMemo`	Exported as text; commonly used with `WrapText(True)` and explicit width.

Recommended exclusions

Field type	Recommendation
`ftBlob`	Exclude with `ExcludeFieldTypes`.
`ftGraphic`	Exclude with `ExcludeFieldTypes`.
`ftStream`	Exclude with `ExcludeFieldTypes`.

Example:

.ExcludeFieldTypes([ftBlob, ftGraphic, ftStream])

Other field types

Other field types are exported using their textual representation when the TField provides one.

For special field types such as GUIDs, JSON, XML, bytes or database-driver-specific fields, validate the generated result in your own project and apply explicit field-name rules when needed.

Multi-sheet export

You can generate one workbook with multiple sheets:

TExcelExportXlsx.New
  .FromDataSet(qryPending)
  .WithTitle('Orders')
  .AddSheet('Closed', qryClosed)
  .AddSheet('Cancelled', qryCancelled)
  .SaveAs('orders.xlsx')
  .Execute;

FromDataSet defines the first sheet. Each AddSheet call adds another sheet to the same workbook.

Sheet names are normalized according to Excel rules:

maximum 31 characters;
forbidden characters are replaced;
duplicate names are deduplicated automatically.

Fields mode

ExcelExport4D can export the dataset field structure instead of records.

Field names only

TExcelExport.New
  .FromDataSet(qryOrders)
  .WithTitle('Order Fields')
  .ModeFields(False)
  .Execute;

Field names with basic metadata

TExcelExport.New
  .FromDataSet(qryOrders)
  .WithTitle('Order Fields')
  .ModeFields(True)
  .Execute;

When ModeFields(True) is used, the generated sheet includes:

FieldName;
FieldType;
FieldSize;
DisplayName.

This is intentionally a compact field overview, not a full schema analysis tool.

Progress callback

TExcelExportXlsx.New
  .FromDataSet(qryOrders)
  .SaveAs('orders.xlsx')
  .OnProgress(
    procedure(ACurrent, ATotal: Integer)
    begin
      ProgressBar.Max := ATotal;
      ProgressBar.Value := ACurrent;
    end)
  .Execute;

The callback is invoked while writing data rows.

The callback runs on the same thread that called Execute. If the export runs in a worker thread, marshal UI updates with TThread.Queue or TThread.Synchronize.

File handling

SaveAs(...) requires a .xlsx file name:

.SaveAs('orders.xlsx')

ExcelExport4D rejects other extensions to avoid generating an OpenXML workbook with a legacy extension inconsistent with its content.

The OpenXML backend writes the package to a temporary file first and then moves it into place at the end of a successful export.

Demo application

The examples/BasicDemo folder contains an FMX demo application.

It demonstrates:

COM export;
OpenXML export;
SaveAs;
multi-sheet export;
fields mode;
basic field metadata;
rules by field type;
rules by field name;
exclusion by Tag, field name and field type;
StoreAsText;
StoreAsNumber;
Unicode/Japanese text;
memo/wrap-text behavior;
progress callback.

COM scenarios require Windows and Microsoft Excel installed. OpenXML scenarios do not require Excel.

Tests

The test suite uses DUnitX.

The always-on tests do not require Microsoft Excel.

Current coverage includes:

demo dataset structure and data integrity;
field filtering;
rule merging;
style resolution;
sheet-name normalization and deduplication;
data mode;
fields mode;
export lifecycle success/failure contracts;
StoreAsText;
StoreAsNumber;
.xlsx extension validation;
OpenXML package structure;
OpenXML worksheet content;
OpenXML field exclusions;
OpenXML multi-sheet workbook entries.

Optional COM integration tests can be enabled with a compiler define when Microsoft Excel is available on the test machine.

Architecture

The project is organized around a shared core and concrete backends:

ExcelExport4D.Core
  Shared fluent API, rules, exclusions, style resolution,
  dataset handling, sheet-name normalization, validation and lifecycle.

ExcelExport4D.Com
  COM backend. Uses Microsoft Excel automation and batch writing
  through VarArray.

ExcelExport4D.Xlsx
  OpenXML backend. Generates XML parts and packages the workbook
  with System.Zip.

ExcelExport4D
  Public facade. Re-exports core types and, on Windows, exposes
  TExcelExport as the COM backend alias.

The core lifecycle is:

ValidateBeforeExecute
DoBeginExport
DoRenderSheet for each sheet
DoEndExport

Backends implement only the environment-specific rendering and finalization details.

Repository layout

ExcelExport4D/
├── .gitattributes
├── .gitignore
├── LICENSE
├── README.md
│
├── assets/
│   ├── banner.png
│   ├── excelexport4d-architecture.png
│   └── screenshots/
│       └── fmx-demo-main-window.png
│
├── docs/
│   ├── Architecture.md
│   ├── Guide_en.md
│   └── Guide_pt-BR.md
│
├── examples/
│   └── BasicDemo/
│       ├── project/
│       │   ├── ExcelExport4D.BasicDemo.dpr
│       │   └── ExcelExport4D.BasicDemo.dproj
│       └── src/
│           ├── ExcelExport4D.BasicDemo.Data.pas
│           ├── ExcelExport4D.BasicDemo.Main.fmx
│           └── ExcelExport4D.BasicDemo.Main.pas
│
├── src/
│   ├── ExcelExport4D.pas
│   ├── ExcelExport4D.Core.pas
│   ├── ExcelExport4D.Com.pas
│   └── ExcelExport4D.Xlsx.pas
│
└── tests/
    ├── project/
    │   ├── ExcelExport4D.Test.dpr
    │   └── ExcelExport4D.Test.dproj
    └── src/
        └── ExcelExport4D.Tests.pas

Design decisions

Why two backends?

Interactive desktop workflows and headless file generation are different problems.

The COM backend solves the first case. The OpenXML backend solves the second. Sharing the fluent API does not mean pretending the environments are the same.

Why focus on tabular export?

Because full spreadsheet manipulation, report design, dashboards, templates, charts and formulas are broader problems.

ExcelExport4D solves tabular export and deliberately stops there.

Why `TDataSet`?

Because TDataSet is still a central abstraction in Delphi applications for tabular data.

ExcelExport4D builds on that instead of introducing a separate data model.

Why field exclusion by `Tag`, name and type?

Because Delphi projects use different conventions.

Tag is pragmatic.
Name-based exclusion is explicit.
Type-based exclusion is useful for structural cases such as blobs or streams.

All three are cumulative.

Why explicit storage semantics?

Because visual appearance and stored value type are not the same thing in Excel.

A code with leading zeros should remain text. A numeric value stored as text may need to become a number. ExcelExport4D makes those decisions explicit through StoreAsText and StoreAsNumber.

Why preserve dataset position?

Because exporting should not silently leave the caller on a different record when the dataset supports restoring the original position.

Why does OpenXML require `SaveAs`?

Because the OpenXML backend does not control a visible Excel instance. It must produce a file.

Scope and limitations

This version of ExcelExport4D is focused on:

tabular export;
explicit formatting rules;
field exclusion;
storage semantics;
COM and OpenXML scenarios behind the same API.

It is not intended to:

reproduce the full Excel object model;
replace report design tools;
provide a template engine;
read or edit existing workbooks;
provide a streaming writer for extremely large datasets;
implement advanced workbook features such as charts, formulas, pivot tables or conditional formatting.

That scope boundary is intentional.

Documentation

Additional documentation:

Versioning

The project follows Semantic Versioning.

License

MIT License — see LICENSE.

Name		Name	Last commit message	Last commit date
assets		assets
docs		docs
examples/BasicDemo		examples/BasicDemo
src		src
tests		tests
.gitattributes		.gitattributes
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md

Latest commit

History

Folders and files

Repository files navigation

ExcelExport4D

Why this project exists

Architecture overview

Table of contents

Quick overview

Interactive: open the workbook in Excel

Headless: write an .xlsx file directly

What ExcelExport4D is

What ExcelExport4D is not

Two backends, one API

COM backend (TExcelExport)

OpenXML backend (TExcelExportXlsx)

When to use each backend

Requirements

General

COM backend

OpenXML backend

Installation

Quick start

Minimal COM export

Minimal OpenXML export

Export with formatting rules

Features

Rules and formatting

Column headers

Storage semantics

NumberFormat(...)

StoreAsText(True)

StoreAsNumber(True)

Precedence

Field exclusion

By Tag

By field name

By field type

Supported field types

Native handling

Recommended exclusions

Other field types

Multi-sheet export

Fields mode

Field names only

Field names with basic metadata

Progress callback

File handling

Demo application

Tests

Architecture

Repository layout

Design decisions

Why two backends?

Why focus on tabular export?

Why TDataSet?

Why field exclusion by Tag, name and type?

Why explicit storage semantics?

Why preserve dataset position?

Why does OpenXML require SaveAs?

Scope and limitations

Documentation

Versioning

License

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages

Headless: write an `.xlsx` file directly

COM backend (`TExcelExport`)

OpenXML backend (`TExcelExportXlsx`)

`NumberFormat(...)`

`StoreAsText(True)`

`StoreAsNumber(True)`

By `Tag`

Why `TDataSet`?

Why field exclusion by `Tag`, name and type?

Why does OpenXML require `SaveAs`?