What it does 

The XLS Exporter adds a backend module (Web > XLS Exporter) that offers editors one or more preconfigured exports for the currently selected page. Each export runs a database query and streams the result to the browser as a spreadsheet file. The editor picks a file name and one of the supported formats — xlsx, xls, ods or csv — and downloads the file.

Exports are defined by integrators in page TSconfig. A single definition describes which table to read, which columns to select, how to filter and join, and which column headers to write — see Configuration.

Declarative exports 

Version 5 introduces a declarative export format: a definition describes what to read — table, columns, filters and joins — and the extension builds the query for it with the TYPO3 QueryBuilder. Integrators no longer write SQL statements by hand, which makes exports easier to read, maintain and extend. See Migration from version 4 for how to convert an export from version 4.

Features 

• Read any table, with SELECT, WHERE, JOIN / LEFT JOIN / RIGHT JOIN and a record count, all expressed as TSconfig.
• Output as xlsx, xls, ods or csv via PhpSpreadsheet.
• Delivered as a site set, so integrators enable it per site.
• Three PSR-14 events to shape the generated spreadsheet — see Developer.

Requirements 

• TYPO3 13.4 LTS or 14.3 LTS
• PHP 8.2, 8.3, 8.4 or 8.5

Install with Composer 

composer require calien/xlsexport

Activate the site set 

The extension ships an example export as the site set XLS Exporter (calien/xlsexport). Assign it to a site to make the example available on that site's pages:

dependencies:
  - calien/xlsexport

The set delivers the example page TSconfig under mod.web_xlsexport. Replace it with your own exports as described in Configuration. The backend module itself is always available under Web > XLS Exporter and needs no set to work.

Downloading an export 

1. Open the module Web > XLS Exporter.
2. Select the page whose exports you want in the page tree. The module lists every export configured for that page, together with the number of records it currently contains.
3. For the export you want, adjust the suggested File name if needed and choose a Format (xlsx, xls, ods or csv).
4. Select Export. The file is generated and downloaded to your computer.

An export whose record count is 0 cannot be downloaded — its button is disabled.

If the module shows the message "Please choose a page on the left", no page is selected yet. If a page has no exports, the module says so; if a single export is misconfigured, it is skipped and a warning naming it is shown, while the remaining exports stay available.

Expression types 

Parameter types 

The type keyword selects the \TYPO3\CMS\Core\Database\Connection parameter type used to bind the value:

AlternateFirstColumnInSheetEvent 

Dispatched once, before anything is written, to decide the first spreadsheet column the header and rows start in. The default is A. Use it to leave room for a logo or leading columns your own listener fills.

Mutable

yes, via setFirstColumn(string $firstColumn)

<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\EventListener;

use Calien\Xlsexport\Event\Export\AlternateFirstColumnInSheetEvent;
use TYPO3\CMS\Core\Attribute\AsEventListener;

final class MoveExportStart
{
    #[AsEventListener]
    public function __invoke(AlternateFirstColumnInSheetEvent $event): void
    {
        $event->setFirstColumn('B');
    }
}

AlternateHeaderLineEvent 

Dispatched before the header row is written, carrying the field labels from fieldLabels and the export key. Use it to translate, reorder or extend the column headers.

Mutable

yes, via setHeaderFieldLabels(array $headerFieldLabels)

Read

getHeaderFieldLabels(), getConfiguration() (the export key)

#[AsEventListener]
public function __invoke(AlternateHeaderLineEvent $event): void
{
    if ($event->getConfiguration() === 'orders') {
        $event->setHeaderFieldLabels(['#', 'Order number', 'Total']);
    }
}

ManipulateRowEntryEvent 

Dispatched for every data row before it is written, carrying the raw row (as fetched from the database), the header labels and the export key. Use it to format values, resolve foreign keys to readable labels, or add computed columns.

Mutable

yes, via setRow(array $row)

Read

getRow(), getFieldLabels(), getConfigurationKey()

#[AsEventListener]
public function __invoke(ManipulateRowEntryEvent $event): void
{
    $row = $event->getRow();
    $row['created'] = date('Y-m-d', (int)($row['created'] ?? 0));
    $event->setRow($row);
}

Breaking changes 

Rewriting an export 

Take the export query of a version 4 definition and express its parts as configuration: the FROM table becomes table, the selected columns become select, and each WHERE clause becomes a condition. The %d placeholder that was filled with the page id becomes ###CURRENT_ID###.

mod.tx_xlsexport.settings.exports {
    addresses {
        label = Addresses
        table = tt_address
        export (
            select uid, first_name, last_name from tt_address
            where pid = %d and deleted = 0 and hidden = 0
        )
        exportfields {
            10 = uid
            20 = first_name
            30 = last_name
        }
        exportfieldnames {
            10 = ID
            20 = First name
            30 = Last name
        }
    }
}

mod.web_xlsexport {
    addresses {
        label = Addresses
        table = tt_address
        select {
            10 = uid
            20 = first_name
            30 = last_name
        }
        fieldLabels {
            10 = ID
            20 = First name
            30 = Last name
        }
        where {
            10 {
                fieldName = pid
                parameter = ###CURRENT_ID###
                expressionType = eq
                type = int
            }
        }
    }
}

5.0.0 - Declarative, QueryBuilder-based exports 

A rewrite that introduces a declarative export format and modernises the extension for TYPO3 13 and 14.