Export collection as excel/csv

Hello guys,

I’m trying to find a way to export collection entries as excel/csv and not json. Does anybody knows a way to do that?

Thank’s for your help!

I wrote a spread sheet export in the past for a MySQL tables addon, which I released a few days ago. Since the logic was quite similar for collections, I wrote a quick draft to export spread sheets of collections. I used the PhpSpreadsheet library to create the sheets.

Feel free to try it out and to send some feedback after testing it.

Thank’s for your interest in my question! It works great.
It would be nnice to export the _created and _updated values of the entry.
I modified the export butoon of the collection in /modules/Collections/views/entries.php to go to the same url + type=xlsx.
Now I’m trying to export the _created date as date and not timestamp. No luck so far. Any ideas?

I added an option for more meta data. My main goal was to give users/clients some readable spread sheets - and they normally don’t care about meta data…

If you add &options[meta]=1, all meta data (_id, _created, _modified, _by, _mby) will be added to the output. If you want some of them, just send them as an array with the options, e. g. &options[meta][0]=_id&options[meta][1]=_created.

You don’t have to modify the core views. Instead you can copy the file
modules/collections/views/entries.php to
config/collections/name_of_collection/views/entries.php and change the menu over there, e. g. if you add this:

<li class="uk-text-truncate"><a href="@route('/collections/export/'.$collection['name'])?type=xlsx" download="{{ $collection['name'] }}.collection.json">@lang('Export XLSX')</a></li>
<li class="uk-text-truncate"><a href="@route('/collections/export/'.$collection['name'])?type=xlsx&options[meta]=1">@lang('Export XLSX (meta)')</a></li>

here:

Now you can use the event system to hook into the results. The SheetExport addon adds a new event collections.export.before.

Example for your custom config/bootstrap.php:

<?php

// fires before the export happens
$app->on('collections.export.before', function($collection, &$type, &$options) {

    // force modern version
    if ($type == 'xls') $type = 'xlsx';

    // modify data after entries are found
    $this->on('collections.find.after', function($name, &$entries) {

        foreach ($entries as &$entry) {

            // reformat dates
            if (isset($entry['_modified'])) {
                $entry['_modified'] = date('Y-m-d, H:i:s', $entry['_modified']);
            }
            if (isset($entry['_created'])) {
                $entry['_created']  = date('Y-m-d, H:i:s', $entry['_created']);
            }

            // export user names instead of user ids
            if (isset($entry['_by']) && $user = $this->storage->findOne('cockpit/accounts', ['_id' => $entry['_by']])) {
                $entry['_by'] = !empty($user['name']) ? $user['name'] : $user['user'];
            }
            if (isset($entry['_mby']) && $user = $this->storage->findOne('cockpit/accounts', ['_id' => $entry['_mby']])) {
                $entry['_mby'] = !empty($user['name']) ? $user['name'] : $user['user'];
            }

        }

    });

});