Flask app gives error when deployed on heroku and trying to open excel file

Question

I have Flask app and within the app, I have a view where I am using xlsxwriter to write and save Excel file. When I run the app locally it works perfectly. When I deploy it all the views work except the one where I am writing the Excel file, it gives me Error 500. In the logs I was that the error is that I am trying to start the file with function that is only for windows, can someone tell me how to start the file or download it? I don't need it to be stored in a database or cloud storage, just to be printed or downloaded immediately. P.s Please excuse me if I have made a mistake asking this question.

@app.route("/proverka", methods=['GET', 'POST'])
def proverka():
    stream = artikli.query.all()

    mah_id=db.session.query(db.func.max(info.id)).first()
    s=info.query.filter_by(id=mah_id[0]).first()
    if request.method=='POST':

        workbook = xlsxwriter.Workbook('ISPRATNICI\\{}, {}.xlsx'.format(s.id, s.ime))
        brojac=9
        worksheet = workbook.add_worksheet()
        #NASLOV
        merge_format = workbook.add_format({'align': 'center', "size": "30"})
        worksheet.merge_range('A1:F1',"„Оска-Пром“ Дооел Виница" , merge_format)
        #informacii za firmata
        worksheet.write("B3", "ул. Страшо Пинџур бр.2")
        worksheet.write("B4", "033/363-841")
        worksheet.write("B5", "071-229-482")
        #informacii za kupuvacot
        podatoci_format = workbook.add_format({'align': 'center', "size": "13"})
        worksheet.merge_range('D3:E3',s.ime, podatoci_format)
        worksheet.merge_range('D4:E4',s.kontakt, podatoci_format)
        worksheet.merge_range('D5:E5', s.ulica, podatoci_format)
        #Ispratnica br.
        ispratnica_format = workbook.add_format({'align': 'center', "size": "20"})

        worksheet.merge_range('A8:F8',"Испратница бр. {}".format(s.id) , ispratnica_format)

        #kategorii  
        meni_format = workbook.add_format({'align': 'center', "size":"14", "border":1})
        vkupno=workbook.add_format({"border":1,'align': 'center', "size":"14",  })
        worksheet.write("A9", "р.бр.", meni_format)
        worksheet.write("B9", "Назив на артикл", meni_format)
        worksheet.write("C9", "Количина", meni_format)
        worksheet.write("D9", "Цена", meni_format)
        worksheet.write("E9", "Износ", meni_format)
        worksheet.write("D39", "Вкупно", vkupno)

        worksheet.set_column("A:A", 7.43)
        worksheet.set_column("B:B", 32)
        worksheet.set_column("C:E", 12)
        worksheet.set_row(7, 38)

        #tabeli granica
        granica=workbook.add_format({"border":1})
        #artikli
        for i in stream:
            worksheet.write(brojac, 0, i.id, granica)
            worksheet.write(brojac, 1, i.model, granica)
            worksheet.write(brojac, 2, i.kolicina, granica)
            worksheet.write(brojac,3, i.cena, granica)
            worksheet.write(brojac, 4, i.vkupno, granica)
            brojac+=1
            saldo=workbook.add_format({"align":"center", "size":"16", "border":2})
            worksheet.write_formula('E39', '=SUM(E9:E38)', saldo)
        workbook.close()

        for i in stream:
            kolicini_za_menjanje=magacin.query.filter_by(model=i.model).first()
            print(kolicini_za_menjanje.kolicina)
            nova_kol=kolicini_za_menjanje.kolicina-i.kolicina
            print(nova_kol)
            kolicini_za_menjanje.kolicina = nova_kol
            db.session.commit()

        #IZBRISI ARTIKLI
        brisi=artikli.query.all()
        for i in brisi:
            artikli.query.filter_by(id=i.id).delete()
            db.session.commit()
        os.startfile("C:\\Users\\Nenad\\Desktop\\magacin vs\\ISPRATNICI\\{}, {}.xlsx".format(s.id, s.ime))
        return redirect(url_for("home"))

    return render_template("proverka.html", stream=stream, s=s)

Logs:

2020-02-22T16:10:18.789403+00:00 app[web.1]: [2020-02-22 16:10:18,787] ERROR in app: Exception on /proverka [POST]
2020-02-22T16:10:18.789405+00:00 app[web.1]: Traceback (most recent call last):
2020-02-22T16:10:18.789406+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.7/site-packages/flask/app.py", line 2446, in wsgi_app  
2020-02-22T16:10:18.789407+00:00 app[web.1]: response = self.full_dispatch_request()
2020-02-22T16:10:18.789408+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.7/site-packages/flask/app.py", line 1951, in full_dispatch_request
2020-02-22T16:10:18.789408+00:00 app[web.1]: rv = self.handle_user_exception(e)
2020-02-22T16:10:18.789408+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.7/site-packages/flask/app.py", line 1820, in handle_user_exception
2020-02-22T16:10:18.789409+00:00 app[web.1]: reraise(exc_type, exc_value, tb)
2020-02-22T16:10:18.789409+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise 
2020-02-22T16:10:18.789410+00:00 app[web.1]: raise value
2020-02-22T16:10:18.789410+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.7/site-packages/flask/app.py", line 1949, in full_dispatch_request
2020-02-22T16:10:18.789411+00:00 app[web.1]: rv = self.dispatch_request()
2020-02-22T16:10:18.789411+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.7/site-packages/flask/app.py", line 1935, in dispatch_request
2020-02-22T16:10:18.789412+00:00 app[web.1]: return self.view_functions[rule.endpoint](**req.view_args)
2020-02-22T16:10:18.789413+00:00 app[web.1]: File "/app/app.py", line 206, in proverka
2020-02-22T16:10:18.789413+00:00 app[web.1]: os.startfile("C:\\Users\\Nenad\\Desktop\\magacin vs\\ISPRATNICI\\{}, {}.xlsx".format(s.id, s.ime))
2020-02-22T16:10:18.789414+00:00 app[web.1]: AttributeError: module 'os' has no attribute 'startfile'
2020-02-22T16:10:18.790447+00:00 app[web.1]: 10.102.224.122 - - [22/Feb/2020:16:10:18 +0000] "POST /proverka HTTP/1.1" 500 290 "https://oska-prom.herokuapp.com/proverka" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36"
2020-02-22T16:10:18.790269+00:00 heroku[router]: at=info method=POST path="/proverka" host=oska-prom.herokuapp.com request_id=4012bbf1-c4ec-46e9-bfd0-5d8fdb23c23d fwd="77.29.30.58" dyno=web.1 connect=0ms service=43ms status=500 bytes=470 protocol=https
2020-02-22T16:10:19.267659+00:00 heroku[router]: at=info method=GET path="/proverka" host=oska-prom.herokuapp.com request_id=ee6b17a9-a2b0-4b77-adaf-d967c48ace12 fwd="77.29.30.58" dyno=web.1 connect=0ms service=15ms status=200 bytes=2539 protocol=https
2020-02-22T16:10:19.269189+00:00 app[web.1]: 10.102.224.122 - - [22/Feb/2020:16:10:19 +0000] "GET /proverka HTTP/1.1" 200 2377 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36"

EDIT I uploaded the code of the view where I am getting the error. Can you suggest another way, to replace the hardcoded path or how to download the file.

What I want to do is to create excel spreadsheet with data from the database, and then I want the user to print it or download it on his computer.

Answer 1

You hardcoded the path:

os.startfile("C:\\Users\\Nenad\\Desktop\\magacin vs\\ISPRATNICI\\{}, {}.xlsx".format(s.id, s.ime))

With os you can build a relative path so it works both on Windows and Linux. We need to see the source code to advice you how to build the path correctly.

With that said it seems you are writing data to a file which you probably are using later. Heroku is not suited for this.

https://devcenter.heroku.com/articles/dynos#ephemeral-filesystem

Each dyno gets its own ephemeral filesystem, with a fresh copy of the most recently deployed code. During the dyno’s lifetime its running processes can use the filesystem as a temporary scratchpad, but no files that are written are visible to processes in any other dyno and any files written will be discarded the moment the dyno is stopped or restarted. For example, this occurs any time a dyno is replaced due to application deployment and approximately once a day as part of normal dyno management.

Dynos are restarted once a day. Meaning your data will be lost. You need a proper database or use another hosting solution.

Answer 2

What I want to do is to create excel spreadsheet with data from the database, and then I want the user to print it or download it on his computer

os.startfile() will never do this.

It might look like it does in development, but that's only because your client and server are running on the same machine. If you host your code on Windows (where os.startfile() exists) and connect to it from another machine, your server will try to open the file. It won't show up on the client at all.

Get rid of

os.startfile("C:\\Users\\Nenad\\Desktop\\magacin vs\\ISPRATNICI\\{}, {}.xlsx".format(s.id, s.ime))

entirely.

Instead, end your function with send_file:

from flask import send_file

@app.route("/proverka", methods=['GET', 'POST'])
def proverka():
    # ...
    send_file('ISPRATNICI\\{}, {}.xlsx'.format(s.id, s.ime))

As Tin noted, Heroku's filesystem is ephemeral so the PDFs on your server will disappear. If that's okay, you probably won't run into too many issues with it. If somebody requests a PDF just before the dyno restarts you might have some issues, but they should be able to request it again.