How to upload, modify and download Excel file?

🦄 Random
1

Hi everyone,

I would like to upload a xlsx file to streamlit, modify it (do some minor calculations and add a new sheet) and after that offer the user to download the file.
My code works so far but the Excel file is broken. Excel can restore it but this is annoying for the user.
To find out what is going on I renamed the Excel file to zip and opened it with 7zip. I found out that all files inside (except the new sheet) are duplicated and I do not understand why. I assume this causes the Excel error.
Do you have any idea what I am doing wrong?

My code:

import streamlit as st
import pandas as pd
import datetime
import numpy as np
import matplotlib.pyplot as plt
from io import StringIO
# Import statistics Library
import statistics
from openpyxl import load_workbook
import xlsxwriter
from openpyxl.utils.dataframe import dataframe_to_rows



st.header("Auswertung")

uploaded_file = st.file_uploader("Choose a file", type = 'xlsx')
if uploaded_file is not None:
    df1 = pd.read_excel(uploaded_file, sheet_name='Ergebnisse', decimal =',')
    #st.dataframe(df1)

    df2 = pd.read_excel(uploaded_file, sheet_name='Statistik')
    #st.dataframe(df2)

    df3 = pd.read_excel(uploaded_file, sheet_name='Probe 1', header = [0, 1, 2], decimal =',')
    #st.dataframe(df3)
    probe1Max = df3[df3.columns[0]].max()
    #st.write('Maximalwert Probe 1: ' + str(round(probe1Max, 2)))
    probe1Min = df3.iloc[-1, 0]
    #st.write('Minimalwert Probe 1: ' + str(round(probe1Min, 2)))


    df4 = pd.read_excel(uploaded_file, sheet_name='Probe 2', header = [0, 1, 2], converters={0:float})
    #st.dataframe(df4)
    probe2Max = df4[df4.columns[0]].max()
    #st.write('Maximalwert Probe 2: ' + str(round(probe2Max, 2)))
    probe2Min = df4.iloc[-1, 0]
    #st.write('Minimalwert Probe 2: ' + str(round(probe2Min, 2)))

    st.write("""---""")
    #st.write('Maximalwert Probe 1: ' + str(round(probe1Max, 4)))
    #st.write('Minimalwert Probe 1: ' + str(round(probe1Min, 4)))
    #st.write('Maximalwert Probe 2: ' + str(round(probe2Max, 4)))
    #st.write('Minimalwert Probe 2: ' + str(round(probe1Min, 4)))

    mittelwertMax = statistics.mean([probe1Max, probe2Max])
    mittelwertMin = statistics.mean([probe1Min, probe2Min])
    st.write('Spannung Beginn: ' + str(round(mittelwertMax, 4)))
    st.write('Spannung Ende: ' + str(round(mittelwertMin, 4)))

    stdevMax = statistics.stdev([probe1Max, probe2Max])
    stdevMin = statistics.stdev([probe1Min, probe2Min])
    st.write('STABW Beginn: ' + str(round(stdevMax, 4)))
    st.write('STABW Ende: ' + str(round(stdevMin, 4)))

    sigmaRelProbe1 = (probe1Min / probe1Max)
    sigmaRelProbe2 = (probe2Min / probe2Max)
    st.write('rel Spannung Probe 1: ' + str(round(sigmaRelProbe1, 4)))
    st.write('rel Spannung Probe 2: ' + str(round(sigmaRelProbe2, 4)))

    mittelwertSigmaRel = statistics.mean([sigmaRelProbe1, sigmaRelProbe2])
    st.write('rel Spannung Versuchsende: ' + str(round(mittelwertSigmaRel * 100, 4)))

    stdevSigmaRel = statistics.stdev([sigmaRelProbe1, sigmaRelProbe2])
    st.write('STABW rel Spannung Versuchsende: ' + str(round(stdevSigmaRel, 4)))

    df5 = pd.DataFrame([{'Spannung Beginn':mittelwertMax, 'STABW Beginn': stdevMax, 'Spannung Ende': mittelwertMin, 'STABW Ende': stdevMin, 'rel. Spannung Versuchsende': mittelwertSigmaRel * 100, 'rel. Spannung STABW': stdevSigmaRel}])
    st.dataframe(df5) 

    
    book = load_workbook(uploaded_file)
    #book.create_sheet('Auswertung')
    with pd.ExcelWriter(uploaded_file, engine = 'openpyxl', mode='a') as writer:
        writer.book = book
        writer.sheets = dict((ws.title, ws) for ws in book.worksheets)    

        df5.to_excel(writer, sheet_name='Auswertung', engine = 'openpyxl',index = False)
        writer.save()
        #book.save(uploaded_file)
        #book.close()
    st.download_button(label="Auswertung downloaden", data=uploaded_file, file_name = 'test.xlsx') #uploaded_file.name)

Debug info

  • Streamlit version: 1.25.0
  • Python version: 3.10.3
  • OS version: MacOS 13.5, Windows 11
  • Browser version: Safari 16.6
2

IMHO this issue is not related to streamlit.
Try to isolate the problem.

3

The issue you’re encountering might be related to how you’re saving the modified Excel file. Instead of appending the new sheet to the existing workbook using mode='a' when opening the Excel writer, you can create a new workbook, copy the existing sheets from the original file to the new workbook, add the new sheet, and then save the new workbook. This should prevent the duplication issue and create a clean Excel file.

4

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