How can we generate pdf file in python using dataframe-CodePudding

I am using python 3.9. I want to create an application where i can fetch data as json from database and output to pdf for report. The code is running fine and it generate json file.

def bill_gen(c_name):
    user_name=c_name.get()
    connection_create()
    cursor=sqliteConnection.cursor()
    cursor.execute("select * from sales where customer_name= '{}'".format(user_name))
    result=cursor.fetchall()
    print(result,type(result))
    connection_close()
    rowarray_list=[]
    for rows in result:
        t=(rows[0],rows[1],rows[2],rows[3],rows[4])
        rowarray_list.append(t) 
    j=json.dumps(rows)
    with open("bill.js", "w") as f:
        f.write(j)
# Convert query to objects of key-value pairs
    objects_list = []
    for row in result:
        d = collections.OrderedDict()
        d["id"] = row[0]
        d["Customer Name"] = row[1]
        d["Medicines"] = row[2]
        d["Quantity"] = row[3]
        d["Price"] = row[4]
        objects_list.append(d)
    j = json.dumps(objects_list)
    with open("bill_objects.js", "w") as f:
        f.write(j)

As a client i cannot understand what is json?

I want a pdf file so that i can mail or i can save it on a cloud or i can give hardcopy to my customers. There a number of tools available in internet(for generating report) it is not so efficient so i am wondering how can i transform this json data to pdf. It should look like a bill not exact but kind of.

Sorry for my langauge.

CodePudding user response：

If you're willing to use LaTeX, you can accomplish this pretty easily with the tabulate library.

Here's some code I whipped up that will print out TeX that can be piped into something like pdflatex:

import tabulate    
    
headers=['id', 'name', 'medicine', 'qty', 'price']    
items=[[1, 'Bob', 'advil', 4, 9.99],    
       [2, 'Mary', 'tylenol', 10, 5.99],    
       [3, 'Malcolm', 'vitamin_d', 1, 13.99]]    
report_name='Report Name'    
your_name='Foo Bar'    
    
document = f'''    
\\documentclass[10pt]{{article}}    
\\title{{{report_name}}}    
\\author{{{your_name}}}    
\\begin{{document}}    
\\maketitle    
\\begin{{center}}    
{tabulate.tabulate(items, headers, tablefmt='latex')}    
\\end{{center}}    
\\end{{document}}    
'''    
    
print(document)

Output:

\documentclass[10pt]{article}
\title{Report Name}
\author{Foo Bar}
\begin{document}
\maketitle
\begin{center}
\begin{tabular}{rllrr}
\hline
   id & name    & medicine   &   qty &   price \\
\hline
    1 & Bob     & advil      &     4 &    9.99 \\
    2 & Mary    & tylenol    &    10 &    5.99 \\
    3 & Malcolm & vitamin\_d  &     1 &   13.99 \\
\hline
\end{tabular}
\end{center}
\end{document}

I was able to turn this into a file "report.pdf" with one command (I'm on Linux so if you're on Windows this might be a bit different)

$ python3 report.py | pdflatex -jobname report

The "-jobname" flag on pdflatex will have the output file match the value of the flag (here, "report" generates "report.pdf")

This is what the output was for me, but LaTeX is very customizable and there's a lot that can be done to make this look a bit nicer.

See:

CodePudding user response：

Since you have a .json file, you can do something like below (I haven't tested it, but it's an idea that can serve you at this stage):

import pandas as pd

# load your json file to a pandas dataframe
df = pd.read_json('file_name.json', lines=True)

# generate an html table from the records
html = df.to_html()

# write the html to a file to be open in a browser (and there you can print it as a pdf, for example)
html_file = open("html_file_name.html", "w")
html_file.write(html)
html_file.close()