Upload an excel file in my streamlit app mantaining the format

🎈 Using Streamlit
,
1

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.

2

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

3

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

image
image969×845 38.4 KB

4

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}")
5

image
image780×631 20.3 KB

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

6

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

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

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”,)

8

Sorry first time I write here

9

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",)
10

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
11

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”,
)

image
image939×542 15.4 KB

12

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

wb = load_workbook(fkonzept, read_only=False)
13

Thank you very much you saved my live guys