I'm creating a plugin to export a spreadsheet file (.ods or .xlsx)

[ColinMaudry]

Hi,

I have started developing a plugin to export records as a spreadsheet file. It could be ods or xlsx, whatever is easier.

I have spotted the following packages:

• ods files: https://pypi.org/project/odswriter/
• xlsx files: https://openpyxl.readthedocs.io/en/stable/index.html (quite powerful) or https://xlsxwriter.readthedocs.io/ (faster)

This is the code I have so far, I test it with the --plugins-dir option:

from datasette import hookimpl
from datasette.utils.asgi import Response
import odswriter as ods

def render_spreadsheet(rows):
    with ods.writer(open("test.ods","wb")) as odsfile:
        for row in rows:
            odsfile.writerow(["String", "ABCDEF123456", "123456"])
        return Response(odsfile, content_type="application/vnd.oasis.opendocument.spreadsheet", status=200)


@hookimpl
def register_output_renderer():
    return {"extension": "ods", "render": render_spreadsheet}

I get the following error:

Traceback (most recent call last):
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/app.py", line 1128, in route_path
    await response.asgi_send(send)
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/utils/asgi.py", line 339, in asgi_send
    body = body.encode("utf-8")
AttributeError: 'ODSWriter' object has no attribute 'encode'
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/app.py", line 1128, in route_path
    await response.asgi_send(send)
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/utils/asgi.py", line 339, in asgi_send
    body = body.encode("utf-8")
AttributeError: 'ODSWriter' object has no attribute 'encode'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/colin/.local/lib/python3.8/site-packages/uvicorn/protocols/http/h11_impl.py", line 396, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/home/colin/.local/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 45, in __call__
    return await self.app(scope, receive, send)
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/utils/asgi.py", line 161, in __call__
    await self.app(scope, receive, send)
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/tracer.py", line 75, in __call__
    await self.app(scope, receive, send)
  File "/home/colin/.local/lib/python3.8/site-packages/asgi_csrf.py", line 107, in app_wrapped_with_csrf
    await app(scope, receive, wrapped_send)
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/app.py", line 1086, in __call__
    return await self.route_path(scope, receive, send, path)
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/app.py", line 1133, in route_path
    return await self.handle_500(request, send, exception)
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/app.py", line 1267, in handle_500
    await asgi_send_html(
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/utils/asgi.py", line 217, in asgi_send_html
    await asgi_send(
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/utils/asgi.py", line 237, in asgi_send
    await asgi_start(send, status, headers, content_type)
  File "/home/colin/.local/lib/python3.8/site-packages/datasette/utils/asgi.py", line 246, in asgi_start
    await send(
  File "/home/colin/.local/lib/python3.8/site-packages/asgi_csrf.py", line 103, in wrapped_send
    await send(event)
  File "/home/colin/.local/lib/python3.8/site-packages/uvicorn/protocols/http/h11_impl.py", line 482, in send
    raise RuntimeError(msg % message_type)
RuntimeError: Expected ASGI message 'http.response.body', but got 'http.response.start'.

I tried with AsgiFileDownload like in DatabaseDownload to deal with the binary nature of the ods file, but the renderer expects a Response:

<datasette.utils.asgi.AsgiFileDownload object at 0x7f5b9bd503d0> should be dict or Response

However, the Response class only supports the following methods, not binary:

• html
• text
• json
• redirect

How would you suggest me to proceed to have my ods file downloaded?

[ColinMaudry]

Apparently, beside a string, Reponse could also work with bytes.

[ColinMaudry]

I made it work with openpyxl. I'm not sure all the code under @hookimpl is necessary... but it works :)

from datasette import hookimpl
from datasette.utils.asgi import Response
from openpyxl import Workbook
from openpyxl.writer.excel import save_virtual_workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.styles import Alignment, Font, PatternFill
from tempfile import NamedTemporaryFile

def render_spreadsheet(rows):
    wb = Workbook(write_only=True)
    ws = wb.create_sheet()
    ws = wb.active
    ws.title = "decp"

    columns = rows[0].keys()
    headers = []
    for col in columns :
        c = WriteOnlyCell(ws, col)
        c.fill = PatternFill("solid", fgColor="DDEFFF")
        headers.append(c)
    ws.append(headers)

    for row in rows:
        wsRow = []
        for col in columns:
            c = WriteOnlyCell(ws, row[col])
            if col == "objet" :
                c.alignment = Alignment(wrapText = True)
            wsRow.append(c)
        ws.append(wsRow)

    with NamedTemporaryFile() as tmp:
        wb.save(tmp.name)
        tmp.seek(0)
        return Response(
            tmp.read(),
            headers={
                'Content-Disposition': 'attachment; filename=decp.xlsx',
                'Content-type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            }
        )

@hookimpl
def register_output_renderer():
    return {"extension": "xlsx",
    "render": render_spreadsheet,
    "can_render": lambda: False}

The key part was to find the right function to wrap the spreadsheet object wb. NamedTemporaryFile() did it!

I'll update this issue when the plugin is packaged and ready for broader use.