Export collection as excel/csv

Support
1

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!

2

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.

3

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?

4

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'];
            }

        }

    });

});
5

I’ve downloaded the folder into /addons but the page only shows the message “Uuuups, something went wrong.” Tried to look into apache error logs to see what’s causing the error but couldn’t find anything there.

Cockpit Version 0.11.2
php 7.3.19-1~deb10u1

Do I need to install any additional packages/libraries?

6

@ritzdank You have to rename the addon to SheetExport (without cockpit_ prefix).

7

@raffaelj Great! Works like charm! My next take will be to make a export button that hrefs to for example: https://localhost:8080/cockpit/collections/export/News?type=csv and display that export button in the backend of cockpit. If I understand it correctly, the best way to hook modify the frontend is to change it in bootstrap.php or could it be done by modifying the addon SheetExport? In any case, thanks for your quick help!

8

Don’t modify the SheetExport addon directly. If you find a generic way, that works with all possible data types (arrays, objects, collection links…), feel free to send a pull request.

For small changes, you can use your config/bootstrap.php. Or create a custom addon, e. g. “MyProjectCustom” and add your changes there.

You could use the event app.layout.contentbefore to add a button above the content. This should work fine for static buttons.

Otherwise

If you need some inspiration for an export button for filtered collections, have a look at the Tables addon.

9

Hello guys,

Is it possible to use the export XLSX feature on forms? I tried to modify the href from the export button

@route('/collections/export/'.$collection['name'])

to

@route('/forms/export/'.$collection['name'])

…but it just throws a JSON at me.

Can you please help me with a solution?

10

The route /collections/export/:collectionname is bound here

And as you can see in the invoked Export controller the current implementation is only working for collections.

The fastest approach to allow exporting forms would be to

  • create an adjusted copy of the Export controller to export specifically forms.
  • bind the route for forms /forms/export/:formname to use the new controller

But that would not be very DRY.

The DRYer approach would be to extend the Export controller to allow for exporting forms as well as collections, create a PR and hope for a quick merge of the new functionality.

11

Thank you for the answer. I really don’t know how to do that but thanks’ anyway.
Cheers!