# Export collection as excel/csv

**URL:** <https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579>\
**Category:** Support\
**Created:** [August 11, 2020, 11:18am UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579 "2020-08-11T11:18:55Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Boerul](https://avatars.discourse-cdn.com/v4/letter/b/b5ac83/32.png) [@Boerul](https://discourse.getcockpit.com/u/Boerul)\
**Post date:** [August 11, 2020, 11:18am UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/1 "2020-08-11T11:18:55Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![raffaelj](https://yyz2.discourse-cdn.com/flex030/user_avatar/discourse.getcockpit.com/raffaelj/32/331_2.png) [@raffaelj](https://discourse.getcockpit.com/u/raffaelj)\
**Post date:** [August 12, 2020, 12:55pm UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/2 "2020-08-12T12:55:00Z")

</div>

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](https://github.com/PHPOffice/PhpSpreadsheet) library to create the sheets.

> **[raffaelj/cockpit\_SheetExport](https://github.com/raffaelj/cockpit_SheetExport)**
>
> Spread sheet export - addon for Cockpit CMS (draft/experimental) - raffaelj/cockpit\_SheetExport

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

---

<div class="post-metadata">

**Author:** ![Boerul](https://avatars.discourse-cdn.com/v4/letter/b/b5ac83/32.png) [@Boerul](https://discourse.getcockpit.com/u/Boerul)\
**Post date:** [August 12, 2020, 4:01pm UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/3 "2020-08-12T16:01:03Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![raffaelj](https://yyz2.discourse-cdn.com/flex030/user_avatar/discourse.getcockpit.com/raffaelj/32/331_2.png) [@raffaelj](https://discourse.getcockpit.com/u/raffaelj)\
**Post date:** [August 12, 2020, 6:25pm UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/4 "2020-08-12T18:25:52Z")

</div>

> [@Boerul](#):
>
> It would be nnice to export the \_created and \_updated values of the entry.

I [added](https://github.com/raffaelj/cockpit_SheetExport/commit/22d41e50c697bfd2e5335a0dc4b299901b01a779) 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`.

> [@Boerul](#):
>
> I modified the export butoon of the collection in /modules/Collections/views/entries.php to go to the same url + type=xlsx.

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:

```html
<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:

> <https://github.com/agentejo/cockpit/blob/next/modules/Collections/views/entries.php#L48-L49>

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

        }

    });

});

```

---

<div class="post-metadata">

**Author:** ![ritzdank](https://avatars.discourse-cdn.com/v4/letter/r/f19dbf/32.png) [@ritzdank](https://discourse.getcockpit.com/u/ritzdank)\
**Post date:** [February 1, 2021, 10:15pm UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/5 "2021-02-01T22:15:48Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![raffaelj](https://yyz2.discourse-cdn.com/flex030/user_avatar/discourse.getcockpit.com/raffaelj/32/331_2.png) [@raffaelj](https://discourse.getcockpit.com/u/raffaelj)\
**Post date:** [February 2, 2021, 2:29pm UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/6 "2021-02-02T14:29:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ritzdank](https://avatars.discourse-cdn.com/v4/letter/r/f19dbf/32.png) [@ritzdank](https://discourse.getcockpit.com/u/ritzdank)\
**Post date:** [February 2, 2021, 4:30pm UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/7 "2021-02-02T16:30:37Z")

</div>

@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](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!

---

<div class="post-metadata">

**Author:** ![raffaelj](https://yyz2.discourse-cdn.com/flex030/user_avatar/discourse.getcockpit.com/raffaelj/32/331_2.png) [@raffaelj](https://discourse.getcockpit.com/u/raffaelj)\
**Post date:** [February 3, 2021, 11:11am UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/8 "2021-02-03T11:11:28Z")

</div>

> [@ritzdank](#):
>
> 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?

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.

> <https://github.com/agentejo/cockpit/blob/next/modules/Cockpit/views/layouts/app.php#L221>

Otherwise

> [@raffaelj](#):
>
> 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

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

> <https://github.com/raffaelj/cockpit_Tables/blob/master/views/entries.php#L280-L307>

> <https://github.com/raffaelj/cockpit_Tables/blob/master/views/entries.php#L1004-L1018>

---

<div class="post-metadata">

**Author:** ![Boerul](https://avatars.discourse-cdn.com/v4/letter/b/b5ac83/32.png) [@Boerul](https://discourse.getcockpit.com/u/Boerul)\
**Post date:** [July 30, 2021, 7:56am UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/9 "2021-07-30T07:56:50Z")

</div>

Hello guys,

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

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

```

to

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

```

…but it just throws a JSON at me.

Can you please help me with a solution?

---

<div class="post-metadata">

**Author:** ![abernh](https://yyz2.discourse-cdn.com/flex030/user_avatar/discourse.getcockpit.com/abernh/32/859_2.png) [@abernh](https://discourse.getcockpit.com/u/abernh)\
**Post date:** [August 3, 2021, 1:20pm UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/10 "2021-08-03T13:20:51Z")

</div>

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

> <https://github.com/raffaelj/cockpit_SheetExport/blob/22d41e50c697bfd2e5335a0dc4b299901b01a779/admin.php#L14>

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

> <https://github.com/raffaelj/cockpit_SheetExport/blob/22d41e50c697bfd2e5335a0dc4b299901b01a779/Controller/Export.php#L9>

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](https://de.wikipedia.org/wiki/Don%E2%80%99t_repeat_yourself).

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.

---

<div class="post-metadata">

**Author:** ![Boerul](https://avatars.discourse-cdn.com/v4/letter/b/b5ac83/32.png) [@Boerul](https://discourse.getcockpit.com/u/Boerul)\
**Post date:** [August 19, 2021, 3:28pm UTC](https://discourse.getcockpit.com/t/export-collection-as-excel-csv/1579/11 "2021-08-19T15:28:15Z")

</div>

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