Print Excel workbook using python

by

Suppose I have an excel file excel_file.xlsx and i want to send it to my printer using Python so I use:

import os
os.startfile('path/to/file','print')

My problem is that this only prints the first sheet of the excel workbook but i want all the sheets printed. Is there any way to print the entire workbook?

Also, I used Openpyxl to create the file, but it doesn’t seem to have any option to select the number of sheets for printing.

Any help would be greatly appreciated.

  • Have you searched for a list of such commands? According to the Python docs, there are documented Microsoft command verbs. docs.python.org/3.5/library/…

    – 

  • stackoverflow.com/a/18495707/1767041 ?

    – 

  • Thanks @gotube , i know i could have printed the sheets individually. But i was searching for a more efficient way cause i might have to deal with workbooks containing lots of sheets.

    – 




  • How is using a loop inefficient? You know your Python code won’t wait for the printing to complete, right?

    – 

  • I didn’t mean that. The answer suggests creating a new temporary workbook for every sheet which i want to print and that would be quite inefficient if there were lots of sheets.

    –  

from xlrd import open_workbook
from openpyxl.reader.excel import load_workbook
import os
import shutil

path_to_workbook = "/Users/username/path/sheet.xlsx"
worksheets_folder = "/Users/username/path/worksheets/"
workbook = open_workbook(path_to_workbook)


def main():

    all_sheet_names = []
    for s in workbook.sheets():
        all_sheet_names.append(s.name)

    for sheet in workbook.sheets():

        if not os.path.exists("worksheets"):
            os.makedirs("worksheets")

        working_sheet = sheet.name

        path_to_new_workbook = worksheets_folder + '{}.xlsx'.format(sheet.name)

        shutil.copyfile(path_to_workbook, path_to_new_workbook)

        nwb = load_workbook(path_to_new_workbook)

        print "working_sheet = " + working_sheet

        for name in all_sheet_names:

            if name != working_sheet:
                nwb.remove_sheet(nwb.get_sheet_by_name(name))

        nwb.save(path_to_new_workbook)

    ws_files = get_file_names(worksheets_folder, ".xlsx")

    # Uncomment print command
    for f in xrange(0, len(ws_files)):
        path_to_file = worksheets_folder + ws_files[f]
        # os.startfile(path_to_file, 'print')
        print 'PRINT: ' + path_to_file

    # remove worksheets folder
    shutil.rmtree(worksheets_folder)


def get_file_names(folder, extension):
    names = []
    for file_name in os.listdir(folder):
        if file_name.endswith(extension):
            names.append(file_name)
    return names


if __name__ == '__main__':
    main()

probably not the best approach, but it should work.
As a workaround you can create separate .xlsx files where each has only one spreadsheet and then print them with os.startfile(path_to_file, 'print')

I have had this issue(on windows) and it was solved by using pywin32 module and this code block(in line 5 you can specify the sheets you want to print.)

    import win32com.client
    o = win32com.client.Dispatch('Excel.Application')
    o.visible = True
    wb = o.Workbooks.Open('/Users/1/Desktop/Sample.xlsx')
    ws = wb.Worksheets([1 ,2 ,3])
    ws.printout()

you could embed vBa on open() command to print the excel file to a default printer using xlsxwriter’s utility mentioned in this article:
PBPYthon’s Embed vBA in Excel

Turns out, the problem was with Microsoft Excel,
os.startfile just sends the file to the system’s default app used to open those file types. I just had to change the default to another app (WPS Office in my case) and the problem was solved.

Leave a Comment