Jupyter でインメモリで Excel を作成し、作成した Excel のダウンロードを自動で実行する

[Tobotobo]

https://pypi.org/project/XlsxWriter/

pip install pandas XlsxWriter

import io
import pandas
import base64
from IPython.display import Javascript, clear_output, display

buf = io.BytesIO()
excel_writer = pandas.ExcelWriter(buf, engine="xlsxwriter")
wkbook = excel_writer.book
wksheet = wkbook.add_worksheet()

wksheet.set_column('A:A', 20)
bold = wkbook.add_format({'bold': True})
wksheet.write('A1', 'Hello')
wksheet.write('A2', 'World', bold)

excel_writer.close()
xlsx_bytes = buf.getvalue()

filename = "data.xlsx"
b64 = base64.b64encode(xlsx_bytes)
payload = b64.decode()
display(Javascript(f'''
    var link = document.createElement('a');
    link.href = 'data:application/vnd.openxmlformats-officedocument\
        .spreadsheetml.sheet;base64,{payload}';
    link.download = '{filename}';
    link.click();
'''))
clear_output()