export sql query to excel

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

export sql query to excel

sum abiut
I wrote a function to export sql query to an excel file, but some how the excel file wasn't created when the function was call. appreciate any assistances

here is my view.py

def download_excel(request):
    if "selectdate" in request.POST:
        if "selectaccount" in request.POST:
            selected_date = request.POST["selectdate"]
            selected_acc = request.POST["selectaccount"]
        if selected_date==selected_date:
            if selected_acc==selected_acc:
                convert=datetime.datetime.strptime(selected_date, "%Y-%m-%d").toordinal()
                engine=create_engine('mssql+pymssql://username:password@servername /db')
                connection = engine.connect()
                metadata=MetaData()
                fund=Table('gltrxdet',metadata,autoload=True,autoload_with=engine)
                rate=Table('gltrx_all',metadata,autoload=True,autoload_with=engine)
                stmt=select([fund.columns.account_code,fund.columns.description,fund.columns.nat_balance,fund.columns.rate_type_home,rate.columns.date_applied,rate.columns.date_entered,fund.columns.journal_ctrl_num,rate.columns.journal_ctrl_num])
                stmt=stmt.where(and_(rate.columns.journal_ctrl_num==fund.columns.journal_ctrl_num,fund.columns.account_code==selected_acc,rate.columns.date_entered==convert))

                df = pd.read_sql(stmt,connection)

                writer = pd.ExcelWriter('C:\excel\export.xls')
                df.to_excel(writer, sheet_name ='bar')
                writer.save()

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAPCf-y7mrr_BQ5RNWaEOjaU%2BNBTyN2r7vvQPf17PV%3DbHUGQH3w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: export sql query to excel

sum abiut

my code actually worked. I thought it was going to save the excel file to 'C:\excel' folder so i was looking for the file in the folder but i couldn't find the excel file. The excel file was actually exported to my django project folder instead.

How to i allow the end user to be able to download the file to their desktop instead of exporting it to the server itself



On Mon, Apr 16, 2018 at 3:27 PM, sum abiut <[hidden email]> wrote:
I wrote a function to export sql query to an excel file, but some how the excel file wasn't created when the function was call. appreciate any assistances

here is my view.py

def download_excel(request):
    if "selectdate" in request.POST:
        if "selectaccount" in request.POST:
            selected_date = request.POST["selectdate"]
            selected_acc = request.POST["selectaccount"]
        if selected_date==selected_date:
            if selected_acc==selected_acc:
                convert=datetime.datetime.strptime(selected_date, "%Y-%m-%d").toordinal()
                engine=create_engine('mssql+pymssql://username:password@servername /db')
                connection = engine.connect()
                metadata=MetaData()
                fund=Table('gltrxdet',metadata,autoload=True,autoload_with=engine)
                rate=Table('gltrx_all',metadata,autoload=True,autoload_with=engine)
                stmt=select([fund.columns.account_code,fund.columns.description,fund.columns.nat_balance,fund.columns.rate_type_home,rate.columns.date_applied,rate.columns.date_entered,fund.columns.journal_ctrl_num,rate.columns.journal_ctrl_num])
                stmt=stmt.where(and_(rate.columns.journal_ctrl_num==fund.columns.journal_ctrl_num,fund.columns.account_code==selected_acc,rate.columns.date_entered==convert))

                df = pd.read_sql(stmt,connection)

                writer = pd.ExcelWriter('C:\excel\export.xls')
                df.to_excel(writer, sheet_name ='bar')
                writer.save()




--

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAPCf-y75oSOhthBTv8JjhCjEoZHy1_TC7dn%3DKwG%3D8GGqjwPA3Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: export sql query to excel

Larry Martell
I use xlsxwriter and I do it like this:

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        # write file
        output.seek(0)
        response = HttpResponse(output.read(),
content_type='application/ms-excel')
        response['Content-Disposition'] = "attachment; filename=%s" % xls_name
        return response

On Mon, Apr 16, 2018 at 2:05 AM, sum abiut <[hidden email]> wrote:

> my code actually worked. I thought it was going to save the excel file to
> 'C:\excel' folder so i was looking for the file in the folder but i couldn't
> find the excel file. The excel file was actually exported to my django
> project folder instead.
>
> How to i allow the end user to be able to download the file to their desktop
> instead of exporting it to the server itself
>
>
>
> On Mon, Apr 16, 2018 at 3:27 PM, sum abiut <[hidden email]> wrote:
>>
>> I wrote a function to export sql query to an excel file, but some how the
>> excel file wasn't created when the function was call. appreciate any
>> assistances
>>
>> here is my view.py
>>
>> def download_excel(request):
>>     if "selectdate" in request.POST:
>>         if "selectaccount" in request.POST:
>>             selected_date = request.POST["selectdate"]
>>             selected_acc = request.POST["selectaccount"]
>>         if selected_date==selected_date:
>>             if selected_acc==selected_acc:
>>                 convert=datetime.datetime.strptime(selected_date,
>> "%Y-%m-%d").toordinal()
>>
>> engine=create_engine('mssql+pymssql://username:password@servername /db')
>>                 connection = engine.connect()
>>                 metadata=MetaData()
>>
>> fund=Table('gltrxdet',metadata,autoload=True,autoload_with=engine)
>>
>> rate=Table('gltrx_all',metadata,autoload=True,autoload_with=engine)
>>
>> stmt=select([fund.columns.account_code,fund.columns.description,fund.columns.nat_balance,fund.columns.rate_type_home,rate.columns.date_applied,rate.columns.date_entered,fund.columns.journal_ctrl_num,rate.columns.journal_ctrl_num])
>>
>> stmt=stmt.where(and_(rate.columns.journal_ctrl_num==fund.columns.journal_ctrl_num,fund.columns.account_code==selected_acc,rate.columns.date_entered==convert))
>>
>>                 df = pd.read_sql(stmt,connection)
>>
>>                 writer = pd.ExcelWriter('C:\excel\export.xls')
>>                 df.to_excel(writer, sheet_name ='bar')
>>                 writer.save()
>>
>>
>
>
>
> --
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CAPCf-y75oSOhthBTv8JjhCjEoZHy1_TC7dn%3DKwG%3D8GGqjwPA3Q%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACwCsY5O0pfHkkXCd430fe6nO%2B0pJgedqtkXoMovropRUnqfUg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: export sql query to excel

sum abiut
Thanks Larry,
I haven't actually try xlsxwriter before so i find some difficulties understanding your code. Do you mind explaining the code, i will definitely have a read on the documentation.

cheers


On Tue, Apr 17, 2018 at 1:42 AM, Larry Martell <[hidden email]> wrote:
I use xlsxwriter and I do it like this:

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        # write file
        output.seek(0)
        response = HttpResponse(output.read(),
content_type='application/ms-excel')
        response['Content-Disposition'] = "attachment; filename=%s" % xls_name
        return response

On Mon, Apr 16, 2018 at 2:05 AM, sum abiut <[hidden email]> wrote:
> my code actually worked. I thought it was going to save the excel file to
> 'C:\excel' folder so i was looking for the file in the folder but i couldn't
> find the excel file. The excel file was actually exported to my django
> project folder instead.
>
> How to i allow the end user to be able to download the file to their desktop
> instead of exporting it to the server itself
>
>
>
> On Mon, Apr 16, 2018 at 3:27 PM, sum abiut <[hidden email]> wrote:
>>
>> I wrote a function to export sql query to an excel file, but some how the
>> excel file wasn't created when the function was call. appreciate any
>> assistances
>>
>> here is my view.py
>>
>> def download_excel(request):
>>     if "selectdate" in request.POST:
>>         if "selectaccount" in request.POST:
>>             selected_date = request.POST["selectdate"]
>>             selected_acc = request.POST["selectaccount"]
>>         if selected_date==selected_date:
>>             if selected_acc==selected_acc:
>>                 convert=datetime.datetime.strptime(selected_date,
>> "%Y-%m-%d").toordinal()
>>
>> engine=create_engine('mssql+pymssql://username:password@servername /db')
>>                 connection = engine.connect()
>>                 metadata=MetaData()
>>
>> fund=Table('gltrxdet',metadata,autoload=True,autoload_with=engine)
>>
>> rate=Table('gltrx_all',metadata,autoload=True,autoload_with=engine)
>>
>> stmt=select([fund.columns.account_code,fund.columns.description,fund.columns.nat_balance,fund.columns.rate_type_home,rate.columns.date_applied,rate.columns.date_entered,fund.columns.journal_ctrl_num,rate.columns.journal_ctrl_num])
>>
>> stmt=stmt.where(and_(rate.columns.journal_ctrl_num==fund.columns.journal_ctrl_num,fund.columns.account_code==selected_acc,rate.columns.date_entered==convert))
>>
>>                 df = pd.read_sql(stmt,connection)
>>
>>                 writer = pd.ExcelWriter('C:\excel\export.xls')
>>                 df.to_excel(writer, sheet_name ='bar')
>>                 writer.save()
>>
>>
>
>
>
> --
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CAPCf-y75oSOhthBTv8JjhCjEoZHy1_TC7dn%3DKwG%3D8GGqjwPA3Q%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACwCsY5O0pfHkkXCd430fe6nO%2B0pJgedqtkXoMovropRUnqfUg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.



--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAPCf-y5iFV_drWs9umJ05o8PCo9i9V_vF%3DXwqWx7woTQOq5L8A%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: export sql query to excel

sum abiut
In reply to this post by Larry Martell
Thanks Larry,
How to i pass my query parameter to the xlsxwriter.

Cheers



On Tue, Apr 17, 2018 at 1:42 AM, Larry Martell <[hidden email]> wrote:
I use xlsxwriter and I do it like this:

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        # write file
        output.seek(0)
        response = HttpResponse(output.read(),
content_type='application/ms-excel')
        response['Content-Disposition'] = "attachment; filename=%s" % xls_name
        return response

On Mon, Apr 16, 2018 at 2:05 AM, sum abiut <[hidden email]> wrote:
> my code actually worked. I thought it was going to save the excel file to
> 'C:\excel' folder so i was looking for the file in the folder but i couldn't
> find the excel file. The excel file was actually exported to my django
> project folder instead.
>
> How to i allow the end user to be able to download the file to their desktop
> instead of exporting it to the server itself
>
>
>
> On Mon, Apr 16, 2018 at 3:27 PM, sum abiut <[hidden email]> wrote:
>>
>> I wrote a function to export sql query to an excel file, but some how the
>> excel file wasn't created when the function was call. appreciate any
>> assistances
>>
>> here is my view.py
>>
>> def download_excel(request):
>>     if "selectdate" in request.POST:
>>         if "selectaccount" in request.POST:
>>             selected_date = request.POST["selectdate"]
>>             selected_acc = request.POST["selectaccount"]
>>         if selected_date==selected_date:
>>             if selected_acc==selected_acc:
>>                 convert=datetime.datetime.strptime(selected_date,
>> "%Y-%m-%d").toordinal()
>>
>> engine=create_engine('mssql+pymssql://username:password@servername /db')
>>                 connection = engine.connect()
>>                 metadata=MetaData()
>>
>> fund=Table('gltrxdet',metadata,autoload=True,autoload_with=engine)
>>
>> rate=Table('gltrx_all',metadata,autoload=True,autoload_with=engine)
>>
>> stmt=select([fund.columns.account_code,fund.columns.description,fund.columns.nat_balance,fund.columns.rate_type_home,rate.columns.date_applied,rate.columns.date_entered,fund.columns.journal_ctrl_num,rate.columns.journal_ctrl_num])
>>
>> stmt=stmt.where(and_(rate.columns.journal_ctrl_num==fund.columns.journal_ctrl_num,fund.columns.account_code==selected_acc,rate.columns.date_entered==convert))
>>
>>                 df = pd.read_sql(stmt,connection)
>>
>>                 writer = pd.ExcelWriter('C:\excel\export.xls')
>>                 df.to_excel(writer, sheet_name ='bar')
>>                 writer.save()
>>
>>
>
>
>
> --
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CAPCf-y75oSOhthBTv8JjhCjEoZHy1_TC7dn%3DKwG%3D8GGqjwPA3Q%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACwCsY5O0pfHkkXCd430fe6nO%2B0pJgedqtkXoMovropRUnqfUg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.



--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAPCf-y67FZ4p8igDdSQzyzSVdnx9UVO6aRW07UROfz2hAgeycA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: export sql query to excel

Larry Martell
In reply to this post by sum abiut
I was just giving you an example of how I do it. You do not need to
use xlsxwriter - you can use anything you want to generate your xls
files. The key to allowing the user to download it is returning a
HttpResponse with content_type='application/ms-excel'

On Mon, Apr 16, 2018 at 6:26 PM, sum abiut <[hidden email]> wrote:

> Thanks Larry,
> I haven't actually try xlsxwriter before so i find some difficulties
> understanding your code. Do you mind explaining the code, i will definitely
> have a read on the documentation.
>
> cheers
>
>
> On Tue, Apr 17, 2018 at 1:42 AM, Larry Martell <[hidden email]>
> wrote:
>>
>> I use xlsxwriter and I do it like this:
>>
>>         output = io.BytesIO()
>>         workbook = xlsxwriter.Workbook(output, {'in_memory': True})
>>         # write file
>>         output.seek(0)
>>         response = HttpResponse(output.read(),
>> content_type='application/ms-excel')
>>         response['Content-Disposition'] = "attachment; filename=%s" %
>> xls_name
>>         return response
>>
>> On Mon, Apr 16, 2018 at 2:05 AM, sum abiut <[hidden email]> wrote:
>> > my code actually worked. I thought it was going to save the excel file
>> > to
>> > 'C:\excel' folder so i was looking for the file in the folder but i
>> > couldn't
>> > find the excel file. The excel file was actually exported to my django
>> > project folder instead.
>> >
>> > How to i allow the end user to be able to download the file to their
>> > desktop
>> > instead of exporting it to the server itself
>> >
>> >
>> >
>> > On Mon, Apr 16, 2018 at 3:27 PM, sum abiut <[hidden email]> wrote:
>> >>
>> >> I wrote a function to export sql query to an excel file, but some how
>> >> the
>> >> excel file wasn't created when the function was call. appreciate any
>> >> assistances
>> >>
>> >> here is my view.py
>> >>
>> >> def download_excel(request):
>> >>     if "selectdate" in request.POST:
>> >>         if "selectaccount" in request.POST:
>> >>             selected_date = request.POST["selectdate"]
>> >>             selected_acc = request.POST["selectaccount"]
>> >>         if selected_date==selected_date:
>> >>             if selected_acc==selected_acc:
>> >>                 convert=datetime.datetime.strptime(selected_date,
>> >> "%Y-%m-%d").toordinal()
>> >>
>> >> engine=create_engine('mssql+pymssql://username:password@servername
>> >> /db')
>> >>                 connection = engine.connect()
>> >>                 metadata=MetaData()
>> >>
>> >> fund=Table('gltrxdet',metadata,autoload=True,autoload_with=engine)
>> >>
>> >> rate=Table('gltrx_all',metadata,autoload=True,autoload_with=engine)
>> >>
>> >>
>> >> stmt=select([fund.columns.account_code,fund.columns.description,fund.columns.nat_balance,fund.columns.rate_type_home,rate.columns.date_applied,rate.columns.date_entered,fund.columns.journal_ctrl_num,rate.columns.journal_ctrl_num])
>> >>
>> >>
>> >> stmt=stmt.where(and_(rate.columns.journal_ctrl_num==fund.columns.journal_ctrl_num,fund.columns.account_code==selected_acc,rate.columns.date_entered==convert))
>> >>
>> >>                 df = pd.read_sql(stmt,connection)
>> >>
>> >>                 writer = pd.ExcelWriter('C:\excel\export.xls')
>> >>                 df.to_excel(writer, sheet_name ='bar')
>> >>                 writer.save()

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACwCsY61bx7_oFFq4%3D7ShNJc3G-Em21DDD_b%3DwdJOxeK9wa-yg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: export sql query to excel

Larry Martell
In reply to this post by sum abiut
The same way you pass parameters to any view.

On Mon, Apr 16, 2018 at 8:50 PM, sum abiut <[hidden email]> wrote:

> Thanks Larry,
> How to i pass my query parameter to the xlsxwriter.
>
> Cheers
>
>
>
> On Tue, Apr 17, 2018 at 1:42 AM, Larry Martell <[hidden email]>
> wrote:
>>
>> I use xlsxwriter and I do it like this:
>>
>>         output = io.BytesIO()
>>         workbook = xlsxwriter.Workbook(output, {'in_memory': True})
>>         # write file
>>         output.seek(0)
>>         response = HttpResponse(output.read(),
>> content_type='application/ms-excel')
>>         response['Content-Disposition'] = "attachment; filename=%s" %
>> xls_name
>>         return response
>>
>> On Mon, Apr 16, 2018 at 2:05 AM, sum abiut <[hidden email]> wrote:
>> > my code actually worked. I thought it was going to save the excel file
>> > to
>> > 'C:\excel' folder so i was looking for the file in the folder but i
>> > couldn't
>> > find the excel file. The excel file was actually exported to my django
>> > project folder instead.
>> >
>> > How to i allow the end user to be able to download the file to their
>> > desktop
>> > instead of exporting it to the server itself
>> >
>> >
>> >
>> > On Mon, Apr 16, 2018 at 3:27 PM, sum abiut <[hidden email]> wrote:
>> >>
>> >> I wrote a function to export sql query to an excel file, but some how
>> >> the
>> >> excel file wasn't created when the function was call. appreciate any
>> >> assistances
>> >>
>> >> here is my view.py
>> >>
>> >> def download_excel(request):
>> >>     if "selectdate" in request.POST:
>> >>         if "selectaccount" in request.POST:
>> >>             selected_date = request.POST["selectdate"]
>> >>             selected_acc = request.POST["selectaccount"]
>> >>         if selected_date==selected_date:
>> >>             if selected_acc==selected_acc:
>> >>                 convert=datetime.datetime.strptime(selected_date,
>> >> "%Y-%m-%d").toordinal()
>> >>
>> >> engine=create_engine('mssql+pymssql://username:password@servername
>> >> /db')
>> >>                 connection = engine.connect()
>> >>                 metadata=MetaData()
>> >>
>> >> fund=Table('gltrxdet',metadata,autoload=True,autoload_with=engine)
>> >>
>> >> rate=Table('gltrx_all',metadata,autoload=True,autoload_with=engine)
>> >>
>> >>
>> >> stmt=select([fund.columns.account_code,fund.columns.description,fund.columns.nat_balance,fund.columns.rate_type_home,rate.columns.date_applied,rate.columns.date_entered,fund.columns.journal_ctrl_num,rate.columns.journal_ctrl_num])
>> >>
>> >>
>> >> stmt=stmt.where(and_(rate.columns.journal_ctrl_num==fund.columns.journal_ctrl_num,fund.columns.account_code==selected_acc,rate.columns.date_entered==convert))
>> >>
>> >>                 df = pd.read_sql(stmt,connection)
>> >>
>> >>                 writer = pd.ExcelWriter('C:\excel\export.xls')
>> >>                 df.to_excel(writer, sheet_name ='bar')
>> >>                 writer.save()

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACwCsY5CNHJ%2BM1AP__6g%2BqwXu1eGPmUZrcfnZJuq1xm%2BQikDtg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: export sql query to excel

Gerardo Palazuelos Guerrero
In reply to this post by sum abiut
hi,
I don´t have this github, so let me try to show you what I do.
Sorry if I´m not applying best practices, but this is working on my side; I run this manually from cmd in Windows 10 (no django on this).

Content of my requirements.txt:
et-xmlfile==1.0.1
jdcal==1.3
openpyxl==2.5.1
pyodbc==4.0.22



This is something optional I did. I extracted my database connection from the simple py file:
import pyodbc

class connection:
conn = None

def get_connection(self):
self.conn = pyodbc.connect(
r'DRIVER={ODBC Driver 13 for SQL Server};'
r'SERVER=<database server name>;'
r'DATABASE=<database name>;'
r'UID=<database user>;'
r'PWD=<user password'
)

return self.conn



This is my routine to generate excel file:
from mssqlconnection import connection
import sys
import openpyxl
import os
import calendar
import time
import datetime
import smtplib
import base64

cursor = connection().get_connection().cursor()


query_to_execute = """
the SQL query goes here
"""

def run_query(start_date, end_date):

# executing the query, also I´m passing parameters to my query (dates)
cursor.execute(query_to_execute, (start_date, end_date))

# load columns into a list
columns = [column[0] for column in cursor.description]
#print(columns)

dir_path = os.path.dirname(os.path.realpath(__file__))
print("file to be saved in following directory: %s" % dir_path)

os.chdir(dir_path)
wb = openpyxl.Workbook()
sheet = wb["Sheet"] # default sheet to be renamed

new_sheet_name = "CustomSheetName"
sheet.title = new_sheet_name

rows = cursor.fetchall()
tmpRows = 1
tmpColumns = 0

# save the columns names on first row
for column in columns:
tmpColumns += 1
sheet.cell(row = tmpRows, column = tmpColumns).value = column

# save rows, iterate over each and every row
# this process is fast, for my surprise
for row in rows:
tmpRows += 1
tmpColumns = 0
for column in columns:
tmpColumns += 1
sheet.cell(row = tmpRows, column = tmpColumns).value = str(getattr(row,column))

excel_file_name = "myfilenamegoeshere.xlsx"

full_path = "%s\\%s" % (dir_path, excel_file_name)

wb.save(excel_file_name)
cursor.close()

write_log("excel file created with the following filename: %s" % excel_file_name)


After Excel file is generated, I´m sending it by email.

I hopes that helps.

Gerardo.



--
Gerardo Palazuelos Guerrero


On Mon, Apr 16, 2018 at 6:50 PM, sum abiut <[hidden email]> wrote:
Thanks Larry,
How to i pass my query parameter to the xlsxwriter.

Cheers



On Tue, Apr 17, 2018 at 1:42 AM, Larry Martell <[hidden email]> wrote:
I use xlsxwriter and I do it like this:

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        # write file
        output.seek(0)
        response = HttpResponse(output.read(),
content_type='application/ms-excel')
        response['Content-Disposition'] = "attachment; filename=%s" % xls_name
        return response

On Mon, Apr 16, 2018 at 2:05 AM, sum abiut <[hidden email]> wrote:
> my code actually worked. I thought it was going to save the excel file to
> 'C:\excel' folder so i was looking for the file in the folder but i couldn't
> find the excel file. The excel file was actually exported to my django
> project folder instead.
>
> How to i allow the end user to be able to download the file to their desktop
> instead of exporting it to the server itself
>
>
>
> On Mon, Apr 16, 2018 at 3:27 PM, sum abiut <[hidden email]> wrote:
>>
>> I wrote a function to export sql query to an excel file, but some how the
>> excel file wasn't created when the function was call. appreciate any
>> assistances
>>
>> here is my view.py
>>
>> def download_excel(request):
>>     if "selectdate" in request.POST:
>>         if "selectaccount" in request.POST:
>>             selected_date = request.POST["selectdate"]
>>             selected_acc = request.POST["selectaccount"]
>>         if selected_date==selected_date:
>>             if selected_acc==selected_acc:
>>                 convert=datetime.datetime.strptime(selected_date,
>> "%Y-%m-%d").toordinal()
>>
>> engine=create_engine('mssql+pymssql://username:password@servername /db')
>>                 connection = engine.connect()
>>                 metadata=MetaData()
>>
>> fund=Table('gltrxdet',metadata,autoload=True,autoload_with=engine)
>>
>> rate=Table('gltrx_all',metadata,autoload=True,autoload_with=engine)
>>
>> stmt=select([fund.columns.account_code,fund.columns.description,fund.columns.nat_balance,fund.columns.rate_type_home,rate.columns.date_applied,rate.columns.date_entered,fund.columns.journal_ctrl_num,rate.columns.journal_ctrl_num])
>>
>> stmt=stmt.where(and_(rate.columns.journal_ctrl_num==fund.columns.journal_ctrl_num,fund.columns.account_code==selected_acc,rate.columns.date_entered==convert))
>>
>>                 df = pd.read_sql(stmt,connection)
>>
>>                 writer = pd.ExcelWriter('C:\excel\export.xls')
>>                 df.to_excel(writer, sheet_name ='bar')
>>                 writer.save()
>>
>>
>
>
>
> --
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CAPCf-y75oSOhthBTv8JjhCjEoZHy1_TC7dn%3DKwG%3D8GGqjwPA3Q%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACwCsY5O0pfHkkXCd430fe6nO%2B0pJgedqtkXoMovropRUnqfUg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.



--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAPCf-y67FZ4p8igDdSQzyzSVdnx9UVO6aRW07UROfz2hAgeycA%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAJ8iCyOysSnbNggtyaTVpPwthuUA_6ELKpii6pi-HC6kQ7okjA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: export sql query to excel

sum abiut
Thanks guys it took me a while and a lot of research, finally get it to work.

my view.py

import pandas as pd
from django.http import HttpResponse
try:
    from io import BytesIO as IO # for modern python
except ImportError:
    from StringIO import StringIO as IO # for legacy python

def download_excel(request):
    if "selectdate" in request.POST:
        if "selectaccount" in request.POST:
            selected_date = request.POST["selectdate"]
            selected_acc = request.POST["selectaccount"]
        if selected_date==selected_date:
            if selected_acc==selected_acc:
                convert=datetime.datetime.strptime(selected_date, "%Y-%m-%d").toordinal()
                engine=create_engine('mssql+pymssql://username:password@servername /db')

              
                metadata=MetaData(connection)
                fund=Table('gltrxdet',metadata,autoload=True,autoload_with=engine)
                rate=Table('gltrx_all',metadata,autoload=True,autoload_with=engine)
                stmt=select([fund.columns.account_code,fund.columns.description,fund.columns.nat_balance,rate.columns.date_applied,fund.columns.journal_ctrl_num,rate.columns.journal_ctrl_num])
                stmt=stmt.where(and_(rate.columns.journal_ctrl_num==fund.columns.journal_ctrl_num,fund.columns.account_code==selected_acc,rate.columns.date_applied==convert))
                results=connection.execute(stmt)
               
                sio = StringIO()
                df = pd.DataFrame(data=list(results), columns=results.keys())

                ####dowload excel file##########
                excel_file = IO()
                xlwriter = pd.ExcelWriter(excel_file, engine='xlsxwriter')
                df.to_excel(xlwriter, 'sheetname')
                xlwriter.save()
                xlwriter.close()
                excel_file.seek(0)

                response = HttpResponse(excel_file.read(), content_type='application/ms-excel vnd.openxmlformats-officedocument.spreadsheetml.sheet')
                # set the file name in the Content-Disposition header
                response['Content-Disposition'] = 'attachment; filename=myfile.xls'
                return response











On Tue, Apr 17, 2018 at 1:40 PM, Gerardo Palazuelos Guerrero <[hidden email]> wrote:
hi,
I don´t have this github, so let me try to show you what I do.
Sorry if I´m not applying best practices, but this is working on my side; I run this manually from cmd in Windows 10 (no django on this).

Content of my requirements.txt:
et-xmlfile==1.0.1
jdcal==1.3
openpyxl==2.5.1
pyodbc==4.0.22



This is something optional I did. I extracted my database connection from the simple py file:
import pyodbc

class connection:
conn = None

def get_connection(self):
self.conn = pyodbc.connect(
r'DRIVER={ODBC Driver 13 for SQL Server};'
r'SERVER=<database server name>;'
r'DATABASE=<database name>;'
r'UID=<database user>;'
r'PWD=<user password'
)

return self.conn



This is my routine to generate excel file:
from mssqlconnection import connection
import sys
import openpyxl
import os
import calendar
import time
import datetime
import smtplib
import base64

cursor = connection().get_connection().cursor()


query_to_execute = """
the SQL query goes here
"""

def run_query(start_date, end_date):

# executing the query, also I´m passing parameters to my query (dates)
cursor.execute(query_to_execute, (start_date, end_date))

# load columns into a list
columns = [column[0] for column in cursor.description]
#print(columns)

dir_path = os.path.dirname(os.path.realpath(__file__))
print("file to be saved in following directory: %s" % dir_path)

os.chdir(dir_path)
wb = openpyxl.Workbook()
sheet = wb["Sheet"] # default sheet to be renamed

new_sheet_name = "CustomSheetName"
sheet.title = new_sheet_name

rows = cursor.fetchall()
tmpRows = 1
tmpColumns = 0

# save the columns names on first row
for column in columns:
tmpColumns += 1
sheet.cell(row = tmpRows, column = tmpColumns).value = column

# save rows, iterate over each and every row
# this process is fast, for my surprise
for row in rows:
tmpRows += 1
tmpColumns = 0
for column in columns:
tmpColumns += 1
sheet.cell(row = tmpRows, column = tmpColumns).value = str(getattr(row,column))

excel_file_name = "myfilenamegoeshere.xlsx"

full_path = "%s\\%s" % (dir_path, excel_file_name)

wb.save(excel_file_name)
cursor.close()

write_log("excel file created with the following filename: %s" % excel_file_name)


After Excel file is generated, I´m sending it by email.

I hopes that helps.

Gerardo.



--
Gerardo Palazuelos Guerrero


On Mon, Apr 16, 2018 at 6:50 PM, sum abiut <[hidden email]> wrote:
Thanks Larry,
How to i pass my query parameter to the xlsxwriter.

Cheers



On Tue, Apr 17, 2018 at 1:42 AM, Larry Martell <[hidden email]> wrote:
I use xlsxwriter and I do it like this:

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        # write file
        output.seek(0)
        response = HttpResponse(output.read(),
content_type='application/ms-excel')
        response['Content-Disposition'] = "attachment; filename=%s" % xls_name
        return response

On Mon, Apr 16, 2018 at 2:05 AM, sum abiut <[hidden email]> wrote:
> my code actually worked. I thought it was going to save the excel file to
> 'C:\excel' folder so i was looking for the file in the folder but i couldn't
> find the excel file. The excel file was actually exported to my django
> project folder instead.
>
> How to i allow the end user to be able to download the file to their desktop
> instead of exporting it to the server itself
>
>
>
> On Mon, Apr 16, 2018 at 3:27 PM, sum abiut <[hidden email]> wrote:
>>
>> I wrote a function to export sql query to an excel file, but some how the
>> excel file wasn't created when the function was call. appreciate any
>> assistances
>>
>> here is my view.py
>>
>> def download_excel(request):
>>     if "selectdate" in request.POST:
>>         if "selectaccount" in request.POST:
>>             selected_date = request.POST["selectdate"]
>>             selected_acc = request.POST["selectaccount"]
>>         if selected_date==selected_date:
>>             if selected_acc==selected_acc:
>>                 convert=datetime.datetime.strptime(selected_date,
>> "%Y-%m-%d").toordinal()
>>
>> engine=create_engine('mssql+pymssql://username:password@servername /db')
>>                 connection = engine.connect()
>>                 metadata=MetaData()
>>
>> fund=Table('gltrxdet',metadata,autoload=True,autoload_with=engine)
>>
>> rate=Table('gltrx_all',metadata,autoload=True,autoload_with=engine)
>>
>> stmt=select([fund.columns.account_code,fund.columns.description,fund.columns.nat_balance,fund.columns.rate_type_home,rate.columns.date_applied,rate.columns.date_entered,fund.columns.journal_ctrl_num,rate.columns.journal_ctrl_num])
>>
>> stmt=stmt.where(and_(rate.columns.journal_ctrl_num==fund.columns.journal_ctrl_num,fund.columns.account_code==selected_acc,rate.columns.date_entered==convert))
>>
>>                 df = pd.read_sql(stmt,connection)
>>
>>                 writer = pd.ExcelWriter('C:\excel\export.xls')
>>                 df.to_excel(writer, sheet_name ='bar')
>>                 writer.save()
>>
>>
>
>
>
> --
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [hidden email].
> To post to this group, send email to [hidden email].
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CAPCf-y75oSOhthBTv8JjhCjEoZHy1_TC7dn%3DKwG%3D8GGqjwPA3Q%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACwCsY5O0pfHkkXCd430fe6nO%2B0pJgedqtkXoMovropRUnqfUg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.



--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAPCf-y67FZ4p8igDdSQzyzSVdnx9UVO6aRW07UROfz2hAgeycA%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAJ8iCyOysSnbNggtyaTVpPwthuUA_6ELKpii6pi-HC6kQ7okjA%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.



--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAPCf-y49pLT2WhpzKFgAanAna2RuftHnR91-MLhVOLyFmQM6zQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.