Print chosen worksheets in excel files to pdf in python

0 votes

I need to write a python script to read excel files, find each worksheet and then print these to pdf with the standard formatting defined in excel.

I found the following question How can I open an Excel file in Python? which pointed me to http://www.python-excel.org/

This gives me the ability to find the names of each worksheet.

import xlrd
book = xlrd.open_workbook("myfile.xls")
print "Worksheet name(s):", book.sheet_names()

This results in:

Worksheet name(s): [u'Form 5', u'Form 3', u'988172 Adams Road', u'379562 Adams Road', u'32380 Adams Road', u'676422 Alderman Road', u'819631 Appleyard Road', u'280998 Appleyard Road', u'781656 Atkinson Road', u'949461 Barretts Lagoon Road', u'735284 Bilyana Road', u'674784 Bilyana Road', u'490894 Blackman Road', u'721026 Blackman Road']

Now I want to print each worksheet which starts with a number into a pdf.

So I can:

worksheetList=book.sheet_names()
for worksheet in worksheetList:
 if worksheet.find('Form')!=0: #this just leaves out worksheets with the word 'form' in it
  <function to print to pdf> book.sheet_by_name(worksheet) #what can I use for this?

or something similar to the above...what can I use to achieve this?

The XLRD documentation is confusing it says:

Formatting features not included in xlrd version 0.6.1: Miscellaneous sheet-level and book-level items e.g. printing layout, screen panes

And

Formatting

Introduction

This collection of features, new in xlrd version 0.6.1, is intended to provide the information needed to (1) display/render spreadsheet contents (say) on a screen or in a PDF file

 

Sep 23, 2022 in Others by Kithuzzz
• 38,000 points
3,612 views

1 answer to this question.

0 votes

In the simplest form:

import win32com.client

o = win32com.client.Dispatch("Excel.Application")

o.Visible = False

wb_path = r'c:\user\desktop\sample.xls'

wb = o.Workbooks.Open(wb_path)

ws_index_list = [1,4,5] #say you want to print these sheets

path_to_pdf = r'C:\user\desktop\sample.pdf'

wb.WorkSheets(ws_index_list).Select()

wb.ActiveSheet.ExportAsFixedFormat(0, path_to_pdf)

Including a little formatting magic that scales to fit to a single page and sets the print area:

import win32com.client

o = win32com.client.Dispatch("Excel.Application")

o.Visible = False

wb_path = r'c:\user\desktop\sample.xls'

wb = o.Workbooks.Open(wb_path)

ws_index_list = [1,4,5] #say you want to print these sheets

path_to_pdf = r'C:\user\desktop\sample.pdf'

print_area = 'A1:G50'

for index in ws_index_list:

    #off-by-one so the user can start numbering the worksheets at 1

    ws = wb.Worksheets[index - 1]

    ws.PageSetup.Zoom = False

    ws.PageSetup.FitToPagesTall = 1

    ws.PageSetup.FitToPagesWide = 1

    ws.PageSetup.PrintArea = print_area

wb.WorkSheets(ws_index_list).Select()

wb.ActiveSheet.ExportAsFixedFormat(0, path_to_pdf)
answered Sep 24, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
14,044 views
0 votes
1 answer

How to open .xlsx files in MS Excel from VS Code?

Hello, to open xlxs files, or files ...READ MORE

answered Feb 17, 2022 in Others by gaurav
• 23,260 points
4,233 views
0 votes
1 answer

Convert Excel and Word files to PDF Using ruby

 You can combine some: For excel files - ...READ MORE

answered Sep 26, 2022 in Others by narikkadan
• 63,600 points
1,347 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,464 views
0 votes
1 answer
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,600 points
1,394 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,600 points
2,078 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