Turning a xlsx into multiple PDFs

0 votes

I have a python script that takes a .xlsx file with multiple worksheets and splits each one of them into a seperate PDF with the worksheet name as the new file name.

import openpyxl
import pandas as pd
from matplotlib.backends.backend_pdf import PdfPages
import matplotlib.pyplot as plt

def xlsx_to_pdf(filename):
    wb = openpyxl.load_workbook(filename)
    for ws in wb:
        df = pd.DataFrame(ws.values)
        with PdfPages(f"{ws.title}.pdf") as pdf:
            fig, ax = plt.subplots()
            ax.axis('tight')
            ax.axis('off')
            the_table = ax.table(cellText=df.values,
                                  colLabels=df.columns,
                                  cellLoc='center',
                                  loc='center')
            pdf.savefig(fig, bbox_inches='tight')

xlsx_to_pdf("Provisionsabrechnung.xlsx")

The only problem I have is that the lines on the tabel it creates are really thick and the text is super small. imported worksheet exported PDF

Jan 31, 2023 in Others by Kithuzzz
• 38,010 points
432 views

1 answer to this question.

0 votes

You just need to adjust the font size with Table.set_font_size (after turning off the auto-size) :

the_table.auto_set_font_size(False)
the_table.set_fontsize(12) # <- adjust the size here

And for the borders, you can make thin borders with a table.Cell.set_linewidth for each cell separately:

cell.set_linewidth(0.3) # <- adjust the width here

The full code :

def xlsx_to_pdf(filename):
    wb = openpyxl.load_workbook(filename)
    for ws in wb:
        df = pd.DataFrame(ws.values)
        with PdfPages(f"{ws.title}.pdf") as pdf:
            fig, ax = plt.subplots()
            ax.axis("tight")
            ax.axis("off")
            the_table = ax.table(cellText=df.values,
                                  colLabels=df.columns,
                                  cellLoc="center",
                                  loc="center")
            the_table.auto_set_font_size(False)
            the_table.set_fontsize(10)
            for _, cell in the_table._cells.items():
                cell.set_linewidth(0.5)
            pdf.savefig(fig, bbox_inches="tight")

xlsx_to_pdf("file.xlsx")

Output :

enter image description here

answered Jan 31, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Combing data from multiple workbooks into a single master workbook using power query

PowerQuery would scan the contents of all ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
336 views
0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
502 views
0 votes
1 answer

Exporting a table from Amazon RDS into a CSV file

Presumably, you're attempting to export data from ...READ MORE

answered Mar 3, 2022 in Others by gaurav
• 23,260 points
2,070 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,181 views
0 votes
1 answer
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,700 points
2,075 views
0 votes
1 answer

Excel web query to login into a website

To be recognized by the web server ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,700 points
2,516 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP