Print Excel workbook using python

Question

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.

Solution 1

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.

Solution 2

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()

Solution 3

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')

Comments

[Chris Macaluso]

Are you able to change the default app with Python? What if you needed to flip between printing different file types?

[RottenCandy]

@55thSwiss I had faced this problem quite some time ago. Since then, I have started using the pywin32 module for Windows-specific tasks. In this case, I would probably had used the win32print module. os.startfile just sends the file to the OS which decides how it gets printed, so it is filetype independent. But maybe you could find a function in pywin32 docs that could change file types.

[Rastko Gojgic]

Thanks, this solves my problem !!! I have a question though. This block of code does the job, but leaves the excel file open. Is there a way to automatically close it as soon as the command is sent to the printer, so we don't have to close it manually? Thanks

[Rastko Gojgic]

I found it. For anyone wanting to this, here is how. wb.Close(False) - without saving then o.Quit()