2

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.

saud
  • 678
  • 1
  • 12
  • 24
  • Have you searched for a list of such commands? According to the Python docs, there are documented Microsoft command verbs. https://docs.python.org/3.5/library/os.html?highlight=startfile#os.startfile – gotube Mar 12 '17 at 18:40
  • http://stackoverflow.com/a/18495707/1767041 ? – gotube Mar 12 '17 at 19:36
  • 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. – saud Mar 13 '17 at 14:39
  • How is using a loop inefficient? You know your Python code won't wait for the printing to complete, right? – gotube Mar 15 '17 at 02:43
  • 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. – saud Mar 16 '17 at 14:30
  • Everything you just told me here should be in your original question to stop helpful people from spending time proposing answers you've already considered and dismissed. You can still edit your question to add it in. – gotube Mar 17 '17 at 19:35

5 Answers5

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

rsomething
  • 50
  • 4
3

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()
coder78
  • 31
  • 1
  • 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 Dec 10 '21 at 10:39
  • I found it. For anyone wanting to this, here is how. `wb.Close(False)` - without saving then `o.Quit()` – Rastko Gojgic Dec 10 '21 at 11:01
0

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

hpca01
  • 370
  • 4
  • 15
0

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.

saud
  • 678
  • 1
  • 12
  • 24
  • Are you able to change the default app with Python? What if you needed to flip between printing different file types? – Chris Macaluso Jan 31 '19 at 18:58
  • @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](http://timgolden.me.uk/pywin32-docs/win32print.html) 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](http://timgolden.me.uk/pywin32-docs/contents.html) that could change file types. – saud Feb 01 '19 at 19:35
0

Seems like you should be able to just loop through and change which page is active. I tried this and it did print out every sheet, BUT for whatever reason on the first print it grouped together two sheets, so it gave me one duplicate page for each workbook.

wb = op.load_workbook(filepath)

for sheet in wb.sheetnames:
    sel_sheet = wb[sheet]


    # find the max row and max column in the sheet
    max_row = sel_sheet.max_row
    max_column = sel_sheet.max_column

    # identify the sheets that have some data in them
    if (max_row > 1) & (max_column > 1):

        # Creating new file for each sheet
        sheet_names = wb.sheetnames
        wb.active = sheet_names.index(sheet)

        wb.save(filepath)
        os.startfile(filepath, "print")
Josh
  • 1