Welcome | Get started | Dive into Lino | Contribute | Topics | Reference | More

export_excel : Exporting to Excel

The lino.modlib.export_excel plugin adds a button Export to Excel to every grid view.

This page is a tested document and the following instructions are used for initialization:

>>> from lino import startup
>>> startup('lino_book.projects.min3.settings.doctests')
>>> from lino.api.doctest import *

Which means that code snippets in this document are tested using the lino_book.projects.min3 demo project.

Overview

Robin has twelve appointments in the period 20141023..20141122:

>>> settings.SITE.the_demo_date
datetime.date(2017, 8, 19)

>>> from lino.utils import i2d
>>> pv = dict(start_date=i2d(20170801), end_date=i2d(20170831))
>>> rt.login('robin').show(cal.MyEntries, param_values=pv, header_level=1)
... 
=============================================================================
My appointments (Responsible user Robin Rood, Dates 01.08.2017 to 31.08.2017)
=============================================================================
====================================================== ===========================
 Calendar entry                                         Workflow
------------------------------------------------------ ---------------------------
 `Breakfast (01.08.2017 10:20) <Detail>`__              **☒ Cancelled**
 `Seminar (03.08.2017 08:30) <Detail>`__                **☒ Cancelled**
 `Interview (05.08.2017 11:10) <Detail>`__              **☒ Cancelled**
 `Breakfast (07.08.2017 09:40) <Detail>`__              **☒ Cancelled**
 `Seminar (09.08.2017 13:30) <Detail>`__                **☒ Cancelled**
 `Interview (11.08.2017 10:20) <Detail>`__              **☒ Cancelled**
 `Breakfast (13.08.2017 08:30) <Detail>`__              **☒ Cancelled**
 `Seminar (15.08.2017 11:10) <Detail>`__                **☒ Cancelled**
 `Interview (17.08.2017 09:40) <Detail>`__              **☒ Cancelled**
 `Breakfast (19.08.2017 13:30) <Detail>`__              **☒ Cancelled**
 `Absent for private reasons (20.08.2017) <Detail>`__   **☐ Draft** → [☼] [☒]
 `Seminar (21.08.2017 10:20) <Detail>`__                **☐ Draft** → [☼] [☒]
 `Absent for private reasons (23.08.2017) <Detail>`__   **? Suggested** → [☼] [☒]
 `Interview (23.08.2017 08:30) <Detail>`__              **? Suggested** → [☼] [☒]
 `Breakfast (25.08.2017 11:10) <Detail>`__              **☐ Draft** → [☼] [☒]
 `Absent for private reasons (26.08.2017) <Detail>`__   **☐ Draft** → [☼] [☒]
 `Seminar (27.08.2017 09:40) <Detail>`__                **? Suggested** → [☼] [☒]
 `Interview (29.08.2017 13:30) <Detail>`__              **☐ Draft** → [☼] [☒]
 `Breakfast (31.08.2017 10:20) <Detail>`__              **? Suggested** → [☼] [☒]
====================================================== ===========================

Building the file

Let's export them to .xls.

When exporting to .xls, the URL is rather long because it includes detailed information about the grid columns: their widths (cw), whether they are hidden (ch) and their ordering (ci). This is necessary because we want the resulting .xls sheet to reflect if the client has changed these.

>>> url = "/api/cal/MyEntries?_dc=1414106085710"
>>> url += "&cw=411&cw=287&cw=411&cw=73&cw=274&cw=140&cw=274&cw=220&cw=220&cw=220&cw=287&cw=181&cw=114&cw=181&cw=73&cw=73&cw=274&cw=140&cw=274&cw=274&cw=181&cw=274&cw=140"
>>> url += "&ch=&ch=true&ch="
>>> url += "&ch=true&ch=true&ch=true&ch=true&ch=true&ch=false&ch=true&ch=true&ch=false&ch=false&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true"
>>> url += "&ci=detail_link&ci=summary&ci=workflow_buttons&ci=id&ci=owner_type&ci=owner_id&ci=user&ci=modified&ci=created&ci=build_time&ci=build_method&ci=start_date&ci=start_time&ci=end_date&ci=end_time&ci=auto_type&ci=event_type&ci=transparent&ci=room&ci=priority&ci=state&ci=assigned_to&ci=owner&name=0"
>>> url += "&pv=01.08.2017&pv=31.08.2017&pv=&pv=&pv=2&pv=&pv=&pv=&pv=y"
>>> url += "&an=export_excel&sr=61"

>>> print(' '.join(cal.MyEntries.params_layout.main.split()))
start_date end_date observed_event state user assigned_to project event_type room show_appointments

>>> test_client.force_login(rt.login('robin').user)
>>> res = test_client.get(url, REMOTE_USER='robin')
>>> print(res.status_code)
200
>>> result = json.loads(res.content.decode())
>>> len(result)
2
>>> result['success']
True
>>> print(result['open_url'])
/media/cache/appyxlsx/127.0.0.1/cal.MyEntries.xlsx

Testing the generated file

The action performed without error. But does the file exist?

>>> from pathlib import Path
>>> p = Path(settings.MEDIA_ROOT) / 'cache/appyxlsx/127.0.0.1/cal.MyEntries.xlsx'
>>> p.exists()
True

In order to test whether the file is really okay, we load it using openpyxl.

>>> from openpyxl import load_workbook
>>> wb = load_workbook(filename=p)

>>> print(wb.sheetnames[0])
My appointments (Responsible us

>>> ws = wb.active
>>> print(ws.title)
My appointments (Responsible us

Note that long titles are truncated because Excel does not support worksheet names longer than 32 characters.

It has 5 columns and 13 rows:

>>> rows = list(ws.rows)
>>> print("{}, {}".format(len(list(ws.columns)), len(rows)))
5, 19

The first row contains our column headings. Which differ from those of the table above because our user had changed them manually:

>>> print(' | '.join([cell.value for cell in rows[0]]))
Calendar entry | Workflow | Created | Start date | Start time

>>> print(' | '.join([str(cell.value) for cell in rows[1]]))
... 
`Beratung (02.08.2017 13:30) <...>`__ | **☑ Took place** → ... | ... | 2017-08-02 00:00:00 | 13:30:00

Unicode

>>> test_client.force_login(rt.login('romain').user)
>>> res = test_client.get(url, REMOTE_USER='romain')
>>> print(res.status_code)
200
>>> wb = load_workbook(filename=p)
>>> ws = wb.active
>>> print(ws.title)
Mes rendez-vous (Utilisateur re

>>> rows = list(ws.rows)
>>> print(' | '.join([cell.value for cell in rows[0]]))
Entrée calendrier | Workflow | Créé | Date de début | Heure de début

>>> print(' | '.join([str(cell.value) for cell in rows[1]]))
... 
`Beratung (02.08.2017 13:30) <...>`__ | **☑ Terminé** → ... | ... | 2017-08-02 00:00:00 | 13:30:00

More queries

>>> url = "/api/cal/Events?an=export_excel"
>>> test_client.get(url, REMOTE_USER='robin').status_code
200

>>> url = "/api/cal/EntriesByDay?an=export_excel"
>>> test_client.get(url, REMOTE_USER='robin').status_code
200

The following failed with ValueError: Cannot convert 1973-07-21 to Excel until 20161014:

>>> url = "/api/contacts/Persons?an=export_excel"
>>> url += "&cw=123&cw=185&cw=129&cw=64&cw=64&cw=34&cw=64&cw=129&cw=129&cw=123&cw=123&cw=70&cw=123&cw=129&cw=129&cw=129&cw=70&cw=70&cw=129&cw=129&cw=366&cw=129&cw=129&cw=129&cw=129&cw=58&cw=76&cw=185&cw=185&cw=185&cw=185"
>>> url += "&ch=&ch=&ch=&ch=&ch=&ch=&ch=&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=true&ch=false&ch=true&ch=true&ch=true&ch=true&ch=true"
>>> url += "&ci=name_column&ci=address_column&ci=email&ci=phone&ci=gsm&ci=id&ci=language&ci=url&ci=fax&ci=country&ci=city&ci=zip_code&ci=region&ci=addr1&ci=street_prefix&ci=street&ci=street_no&ci=street_box&ci=addr2&ci=name&ci=remarks&ci=title&ci=first_name&ci=middle_name&ci=last_name&ci=gender&ci=birth_date&&ci=age"
>>> url += "&name=0&&pv=&pv=&pv=&pv="
>>> test_client.get(url, REMOTE_USER='robin').status_code
200