Upload an excel file in my streamlit app mantaining the format

Summary

Hello, we have a streamlit app which upload an excel file process it using the format like colours of the cells and download another excelfile.

Expected behavior:

Currently we do not find a way to mantain the formats when we use uploader tool. The result is a BytesIO object but we can not transform it to a workbook openpyxl with the formats.

Actual behavior:

Explain the undesired behavior or error you see when you run the code above.
If you’re seeing an error message, share the full contents of the error message here.

Seems to be working for me. Can you share sample code and data showing the issue?

image
Thank you very much Goyo. This app was deployed from Github not local.

What about this example snippet:

fkonzept = st.file_uploader("Upload a file", type=["csv", "xlsx", "txt"])
if fkonzept:
    wb = openpyxl.load_workbook(fkonzept, read_only=True)
    st.info(f"File uploaded: {fkonzept.name}")
    st.info(f"Sheet names: {wb.sheetnames}")

Sorry for disturbing so much just this last example how would i do to download the excel i uploaded?

Thank you very much in advance

Please don’t post code snippets as images in the forum, put the code snippets in markdown syntax:

```python
# your python code here
```

import streamlit as st
from openpyxl import reader,load_workbook,Workbook

st.title(“FK_KM CHECK”)

fkonzept = st.file_uploader(“upload FK file”, type={“xlsx”,“csv”, “txt”})

wb=Workbook()

if fkonzept is not None:
wb =load_workbook(fkonzept, read_only=True)
st.write(wb.sheetnames)
st.title(wb)
st.write(wb.active)

wb.save(‘fk.xlsx’)

st.download_button(
label=“Download Excel worksheet without index”,
data=buffer,
file_name=“fk.xlsx”,
mime=“application/vnd.ms-excel”,)

Sorry first time I write here

Therefore please follow my advice and put your code into markdown syntax, otherwise all the code formatting is gone… :roll_eyes:

—.

This example should work:

import io
import streamlit as st
from openpyxl import load_workbook

local_xlsx_name = "fk.xlsx"

st.title("FK_KM CHECK")

fkonzept = st.file_uploader("upload FK file", type={"xlsx", "csv", "txt"})

if fkonzept:
    wb = load_workbook(fkonzept, read_only=False)
    st.write(wb.sheetnames)
    st.title(fkonzept.name)
    wb.save(local_xlsx_name)

    with open(local_xlsx_name, "rb") as fh:
        buffer = io.BytesIO(fh.read())

    st.download_button(
        label="Download Excel worksheet",
        data=buffer,
        file_name=local_xlsx_name,
        mime="application/vnd.ms-excel",)

Saving the workbook to a file with a hardcoded name is dangerous because there will be a race condition with two or more simultaneous sessions. The openpyxl docs say you can use a NamedTemporaryFile to save to a memory buffer. I tried that in Windows and it didn’t work, I guess because the code tries to open the same file twice.

What I do is passing the buffer to wb.save()

import io

#  Your code here: load the workbook and process it.
# ...

buffer = io.BytesIO()
wb.save(buffer)

st.download_button(
    label="Download Excel worksheet without index",
    data=buffer,
    file_name="fk.xlsx",
)
1 Like

import streamlit as st
from openpyxl import reader,load_workbook,Workbook
import io

st.title(“FK_KM CHECK”)

fkonzept = st.file_uploader(“upload FK file”, type={“xlsx”,“csv”, “txt”})

wb=Workbook()

if fkonzept is not None:
wb =load_workbook(fkonzept, read_only=True)
st.write(wb.sheetnames)
st.title(wb)
st.write(wb.active)

buffer = io.BytesIO()
wb.save(buffer)

st.download_button(
label=“Download Excel worksheet without index”,
data=buffer,
file_name=“fk.xlsx”,
)

You don’t need write protection for a uploaded file… therefore:

wb = load_workbook(fkonzept, read_only=False)

Thank you very much you saved my live guys