I am implementing a button from ipywidget that by clicking it, it downloads an Excel created from a formatted/styled dataframe (i.e. with cells highlighted in colors).
If it is pure data (no color styling etc), I could have saved the data into csv format and solve the problem based on this thread How to download a file using ipywidget button?.
My code
import ipywidgets
import numpy as np
import pandas as pd
from IPython.display import HTML, display
from ipywidgets import widgets
import pandas.io.formats.style
class DownloadButtonExcel(ipywidgets.Button):
"""
Download button with dynamic content
The content is generated using a callback when the button is clicked.
The code is based on ollik1's answer at https://stackoverflow.com/questions/61708701/how-to-download-a-file-using-ipywidget-button/68683463#68683463
"""
def __init__(self, filename: str, contents: Callable[[], pandas.io.formats.style.Styler], **kwargs):
super(DownloadButtonExcel, self).__init__(**kwargs)
self.filename = filename
self.contents = contents
self.on_click(self.__on_click)
self.output = widgets.Output()
display(self.output)
def __on_click(self, b):
df = self.contents()
df.to_excel(self.filename, engine='openpyxl')
digest = pd.util.hash_pandas_object(df.data).sum() # bypass browser cache
id = f"dl_{digest}"
with self.output:
display(
HTML(
f"""
<html>
<body>
<a id="{id}" download ="{self.filename}" href="{self.filename}" download>
</a>
<script>
(function download() {{
document.getElementById('{id}').click();
}})()
</script>
</body>
</html>
"""
)
)
import pandas as pd
import numpy as np
import matplotlib as mpl
df = pd.DataFrame([[38.0, 2.0, 18.0, 22.0, 21, np.nan],[19, 439, 6, 452, 226,232]],
index=pd.Index(['Tumour (Positive)', 'Non-Tumour (Negative)'], name='Actual Label:'),
columns=pd.MultiIndex.from_product([['Decision Tree', 'Regression', 'Random'],['Tumour', 'Non-Tumour']], names=['Model:', 'Predicted:']))
df_style = df.style.format(precision = 2).background_gradient().hide(axis = 'index')
download_button_excel = DownloadButtonExcel(
filename="Test.xlsx",
contents=lambda: df_style,
description="Download",
style={"button_color": "transparent"},
)
The above code creates a button and download the Excel file with correct format.
Different from csv case, where the data can be encoded/decoded and saved in the cache, for current implementation, the df.to_excel(self.filename, engine='openpyxl') code downloads the Excel to local folder.
I am not sure (1) how to encode/decode pandas Styler just as in csv case and implement it using that approach.
(2) I feel I could have moved df.to_excel(self.filename, engine='openpyxl') to inside function download(), so that the click on button triggers download. But I don't know how.
Any help would be greatly appreciated.
