St.download button not working with .xlsx created with openpyxl

Using Streamlit
1

My app is 99% of the way there, but at the end I want the user to download the .xlsx file that I created. I am having a lot of trouble with the download button, however. I excerpt a snippet of code below and then show the error. Just so you know, I have tried creating the xlsx WITH specifying encoding (like below) and WITHOUT and it doesn’t seem to make any difference. The issue appears to be that when streamlit “reads” the filestream it cannot do so due to an encoding issue. The other things I have tried are to: (i) specify encoding on the with open line like this:

with open('./outputs/summary.xlsx', mode='r', encoding="utf-8") as f:

But that doesn’t solve the problem. I have also tried opening the file as a bytestream with ‘rb’ and that WILL allow me to download something, but it is a BIN file.

Here’s the code:

import streamlit as st
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment

class CreateWorkbook:

  def __init__(self):
    self.wb = Workbook()
    self.wb.encoding="utf-8"
    self.ws = self.wb.active

  def create_workbook(self):
    # Create column headers.
    self.ws['A1'] = "Country".encode(encoding='utf-8')
    self.ws['B1'] = "Article".encode(encoding='utf-8')
    self.ws['C1'] = "Question".encode(encoding='utf-8')
    self.ws['D1'] = "Answer".encode(encoding='utf-8')

    # Bold the headers.
    self.ws['A1'].font = Font(bold=True)
    self.ws['B1'].font = Font(bold=True)
    self.ws['C1'].font = Font(bold=True)
    self.ws['D1'].font = Font(bold=True)

    # Fill in the Data.
    current_row = 2
    for country, articles_frame in response_map.items():
      for article, qa_frame in articles_frame.items():
        for question, answer in qa_frame.items():
          self.ws['A' + str(current_row)] = country.encode(encoding = 'utf-8', errors = 'replace')
          self.ws['B' + str(current_row)] = article.encode(encoding = 'utf-8', errors = 'replace')
          self.ws['C' + str(current_row)] = question.encode(encoding = 'utf-8', errors = 'replace')
          self.ws['D' + str(current_row)] = answer.encode(encoding = 'utf-8', errors = 'replace')
          current_row = current_row + 1

    # Set Column Widths
    self.ws.column_dimensions['A'].width = 30
    self.ws.column_dimensions['B'].width = 20
    self.ws.column_dimensions['C'].width = 80
    self.ws.column_dimensions['D'].width = 80

    # Ensure every Cell wraps text.
    for row in self.ws.iter_rows():
      for cell in row:
        alignment = Alignment(wrap_text=True, vertical="top")
        cell.alignment = alignment

    self.wb.save('./outputs/summary.xlsx')

st.write(":balloon:   :ballooon")

create_wb_object = CreateWorkbook()
create_wb_object.create_workbook()

with open('./outputs/summary.xlsx', mode='r', encoding="utf-8") as f:
  st.download_button('Download', f)

Here’s the error:

2024-02-26 13:39:04.558 Uncaught app exception
Traceback (most recent call last):
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 535, in _run_script   
    exec(code, module.__dict__)
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\main.py", line 56, in <module>
    st.download_button('Download', f)
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\runtime\metrics_util.py", line 397, in wrapped_func
    result = non_optional_func(*args, **kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\elements\widgets\button.py", line 331, in download_button
    return self._download_button(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\elements\widgets\button.py", line 580, in _download_button
    marshall_file(
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\elements\widgets\button.py", line 787, in marshall_file
    string_data = data.read()
                  ^^^^^^^^^^^
  File "<frozen codecs>", line 322, in decode
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe2 in position 10: invalid continuation byte
2024-02-26 13:39:04.624 Uncaught app exception
Traceback (most recent call last):
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 535, in _run_script   
    exec(code, module.__dict__)
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\main.py", line 56, in <module>
    st.download_button('Download', f)
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\runtime\metrics_util.py", line 397, in wrapped_func
    result = non_optional_func(*args, **kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\elements\widgets\button.py", line 331, in download_button
    return self._download_button(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\elements\widgets\button.py", line 580, in _download_button
    marshall_file(
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\elements\widgets\button.py", line 787, in marshall_file
    string_data = data.read()
                  ^^^^^^^^^^^
  File "<frozen codecs>", line 322, in decode
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe2 in position 10: invalid continuation byte
2024-02-26 13:39:05.261 Uncaught app exception
Traceback (most recent call last):
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 535, in _run_script   
    exec(code, module.__dict__)
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\main.py", line 56, in <module>
    st.download_button('Download', f)
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\runtime\metrics_util.py", line 397, in wrapped_func
    result = non_optional_func(*args, **kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\elements\widgets\button.py", line 331, in download_button
    return self._download_button(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\elements\widgets\button.py", line 580, in _download_button
    marshall_file(
  File "C:\Users\mcdnj\PycharmProjects\Streamlit Experiment\.venv\Lib\site-packages\streamlit\elements\widgets\button.py", line 787, in marshall_file
    string_data = data.read()
                  ^^^^^^^^^^^
  File "<frozen codecs>", line 322, in decode
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe2 in position 10: invalid continuation byte

Any thoughts???

2

I finally figured this out if it helps anyone else … you can’t just read it as a filestream. You have to read it as a bytesstream and then specify the mime type:

with open("./outputs/summary.xlsx", "rb") as file:
  btn = st.download_button(
    label="Download",
    data=file,
    file_name="text_rb2.xlsx",
    mime="application/vnd.ms-excel"
  )
3

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.